1 41 package com.mvnforum.db.jdbc; 42 43 import java.sql.*; 44 import java.util.ArrayList ; 45 import java.util.Collection ; 46 47 import com.mvnforum.db.*; 48 import net.myvietnam.mvncore.db.DBUtils; 49 import net.myvietnam.mvncore.exception.*; 50 import org.apache.commons.logging.Log; 51 import org.apache.commons.logging.LogFactory; 52 53 public class FavoriteThreadDAOImplJDBC implements FavoriteThreadDAO { 54 55 private static Log log = LogFactory.getLog(FavoriteThreadDAOImplJDBC.class); 56 57 private static boolean m_dirty = true; 59 60 public FavoriteThreadDAOImplJDBC() { 61 } 62 63 protected static boolean isDirty() { 64 return m_dirty; 65 } 66 67 protected static void setDirty(boolean dirty) { 68 m_dirty = dirty; 69 } 70 71 public void findByPrimaryKey(int memberID, int threadID) 72 throws ObjectNotFoundException, DatabaseException { 73 74 Connection connection = null; 75 PreparedStatement statement = null; 76 ResultSet resultSet = null; 77 StringBuffer sql = new StringBuffer (512); 78 sql.append("SELECT MemberID, ThreadID"); 79 sql.append(" FROM " + TABLE_NAME); 80 sql.append(" WHERE MemberID = ? AND ThreadID = ?"); 81 try { 82 connection = DBUtils.getConnection(); 83 statement = connection.prepareStatement(sql.toString()); 84 statement.setInt(1, memberID); 85 statement.setInt(2, threadID); 86 resultSet = statement.executeQuery(); 87 if (!resultSet.next()) { 88 throw new ObjectNotFoundException("Cannot find the primary key (" + memberID + ", " + threadID + ") in table 'FavoriteThread'."); 89 } 90 } catch(SQLException sqle) { 91 log.error("Sql Execution Error!", sqle); 92 throw new DatabaseException("Error executing SQL in FavoriteThreadDAOImplJDBC.findByPrimaryKey."); 93 } finally { 94 DBUtils.closeResultSet(resultSet); 95 DBUtils.closeStatement(statement); 96 DBUtils.closeConnection(connection); 97 } 98 } 99 100 105 public void create(int memberID, int threadID, int forumID, 106 Timestamp favoriteCreationDate, int favoriteType, int favoriteOption, 107 int favoriteStatus) 108 throws CreateException, DatabaseException, DuplicateKeyException, ForeignKeyNotFoundException { 109 110 try { 114 findByPrimaryKey(memberID, threadID); 116 throw new DuplicateKeyException("Primary key already exists. Cannot create new FavoriteThread with the same [MemberID, ThreadID] (" + memberID + ", " + threadID + ")."); 118 } catch(ObjectNotFoundException e) { 119 } 121 122 try { 123 DAOFactory.getMemberDAO().findByPrimaryKey(memberID); 126 } catch(ObjectNotFoundException e) { 127 throw new ForeignKeyNotFoundException("Foreign key refers to table 'mvnforumMember' does not exist. Cannot create new FavoriteThread."); 128 } 129 130 try { 131 DAOFactory.getThreadDAO().findByPrimaryKey(threadID); 134 } catch(ObjectNotFoundException e) { 135 throw new ForeignKeyNotFoundException("Foreign key refers to table 'mvnforumThread' does not exist. Cannot create new FavoriteThread."); 136 } 137 138 try { 139 DAOFactory.getForumDAO().findByPrimaryKey(forumID); 142 } catch(ObjectNotFoundException e) { 143 throw new ForeignKeyNotFoundException("Foreign key refers to table 'mvnforumForum' does not exist. Cannot create new FavoriteThread."); 144 } 145 146 Connection connection = null; 147 PreparedStatement statement = null; 148 StringBuffer sql = new StringBuffer (512); 149 sql.append("INSERT INTO " + TABLE_NAME + " (MemberID, ThreadID, ForumID, FavoriteCreationDate, FavoriteType, FavoriteOption, FavoriteStatus)"); 150 sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?)"); 151 try { 152 connection = DBUtils.getConnection(); 153 statement = connection.prepareStatement(sql.toString()); 154 155 statement.setInt(1, memberID); 156 statement.setInt(2, threadID); 157 statement.setInt(3, forumID); 158 statement.setTimestamp(4, favoriteCreationDate); 159 statement.setInt(5, favoriteType); 160 statement.setInt(6, favoriteOption); 161 statement.setInt(7, favoriteStatus); 162 163 if (statement.executeUpdate() != 1) { 164 throw new CreateException("Error adding a row into table 'FavoriteThread'."); 165 } 166 m_dirty = true; 167 } catch(SQLException sqle) { 168 log.error("Sql Execution Error!", sqle); 169 throw new DatabaseException("Error executing SQL in FavoriteThreadDAOImplJDBC.create."); 170 } finally { 171 DBUtils.closeStatement(statement); 172 DBUtils.closeConnection(connection); 173 } 174 } 175 176 public void delete(int memberID, int threadID) 177 throws DatabaseException, ObjectNotFoundException { 178 179 Connection connection = null; 180 PreparedStatement statement = null; 181 StringBuffer sql = new StringBuffer (512); 182 sql.append("DELETE FROM " + TABLE_NAME); 183 sql.append(" WHERE MemberID = ? AND ThreadID = ?"); 184 185 try { 186 connection = DBUtils.getConnection(); 187 statement = connection.prepareStatement(sql.toString()); 188 statement.setInt(1, memberID); 189 statement.setInt(2, threadID); 190 if (statement.executeUpdate() != 1) { 191 throw new ObjectNotFoundException("Cannot delete a row in table FavoriteThread where primary key = (" + memberID + ", " + threadID + ")."); 192 } 193 m_dirty = true; 194 } catch(SQLException sqle) { 195 log.error("Sql Execution Error!", sqle); 196 throw new DatabaseException("Error executing SQL in FavoriteThreadDAOImplJDBC.delete."); 197 } finally { 198 DBUtils.closeStatement(statement); 199 DBUtils.closeConnection(connection); 200 } 201 } 202 203 public void delete_inThread(int threadID) 204 throws DatabaseException { 205 206 Connection connection = null; 207 PreparedStatement statement = null; 208 StringBuffer sql = new StringBuffer (512); 209 sql.append("DELETE FROM " + TABLE_NAME); 210 sql.append(" WHERE ThreadID = ?"); 211 212 try { 213 connection = DBUtils.getConnection(); 214 statement = connection.prepareStatement(sql.toString()); 215 statement.setInt(1, threadID); 216 217 statement.executeUpdate(); 218 m_dirty = true; 219 } catch(SQLException sqle) { 220 log.error("Sql Execution Error!", sqle); 221 throw new DatabaseException("Error executing SQL in FavoriteThreadDAOImplJDBC.delete_inThread."); 222 } finally { 223 DBUtils.closeStatement(statement); 224 DBUtils.closeConnection(connection); 225 } 226 } 227 228 public void delete_inForum(int forumID) 229 throws DatabaseException { 230 231 Connection connection = null; 232 PreparedStatement statement = null; 233 StringBuffer sql = new StringBuffer (512); 234 sql.append("DELETE FROM " + TABLE_NAME); 235 sql.append(" WHERE ForumID = ?"); 236 237 try { 238 connection = DBUtils.getConnection(); 239 statement = connection.prepareStatement(sql.toString()); 240 statement.setInt(1, forumID); 241 242 statement.executeUpdate(); 243 m_dirty = true; 244 } catch(SQLException sqle) { 245 log.error("Sql Execution Error!", sqle); 246 throw new DatabaseException("Error executing SQL in FavoriteThreadDAOImplJDBC.delete_inForum."); 247 } finally { 248 DBUtils.closeStatement(statement); 249 DBUtils.closeConnection(connection); 250 } 251 } 252 253 public void delete_inMember(int memberID) 254 throws DatabaseException { 255 256 Connection connection = null; 257 PreparedStatement statement = null; 258 StringBuffer sql = new StringBuffer (512); 259 sql.append("DELETE FROM " + TABLE_NAME); 260 sql.append(" WHERE MemberID = ?"); 261 262 try { 263 connection = DBUtils.getConnection(); 264 statement = connection.prepareStatement(sql.toString()); 265 statement.setInt(1, memberID); 266 statement.executeUpdate(); 267 m_dirty = true; 268 } catch(SQLException sqle) { 269 log.error("Sql Execution Error!", sqle); 270 throw new DatabaseException("Error executing SQL in FavoriteThreadDAOImplJDBC.delete_inMember."); 271 } finally { 272 DBUtils.closeStatement(statement); 273 DBUtils.closeConnection(connection); 274 } 275 } 276 277 280 public void update_ForumID_inThread(int threadID, int forumID) 281 throws DatabaseException, ForeignKeyNotFoundException { 282 283 try { 284 DAOFactory.getForumDAO().findByPrimaryKey(forumID); 287 } catch(ObjectNotFoundException e) { 288 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Forum' does not exist. Cannot update table 'FavoriteThread'."); 289 } 290 291 Connection connection = null; 292 PreparedStatement statement = null; 293 StringBuffer sql = new StringBuffer (512); 294 sql.append("UPDATE " + TABLE_NAME + " SET ForumID = ?"); 295 sql.append(" WHERE ThreadID = ?"); 296 try { 297 connection = DBUtils.getConnection(); 298 statement = connection.prepareStatement(sql.toString()); 299 300 statement.setInt(1, forumID); 302 303 statement.setInt(2, threadID); 305 306 statement.executeUpdate(); 307 m_dirty = true; 308 } catch(SQLException sqle) { 309 log.error("Sql Execution Error!", sqle); 310 throw new DatabaseException("Error executing SQL in FavoriteThreadDAOImplJDBC.update_ForumID_inThread."); 311 } finally { 312 DBUtils.closeStatement(statement); 313 DBUtils.closeConnection(connection); 314 } 315 } 316 317 322 public Collection getFavoriteThreads_inMember(int memberID) 323 throws DatabaseException { 324 325 Connection connection = null; 326 PreparedStatement statement = null; 327 ResultSet resultSet = null; 328 Collection retValue = new ArrayList (); 329 StringBuffer sql = new StringBuffer (512); 330 sql.append("SELECT MemberID, ThreadID, FavoriteCreationDate, FavoriteType, FavoriteOption, FavoriteStatus"); 331 sql.append(" FROM " + TABLE_NAME); 332 sql.append(" WHERE MemberID = ?"); 333 sql.append(" ORDER BY ThreadID ASC "); 334 try { 335 connection = DBUtils.getConnection(); 336 statement = connection.prepareStatement(sql.toString()); 337 statement.setInt(1, memberID); 338 resultSet = statement.executeQuery(); 339 while (resultSet.next()) { 340 FavoriteThreadBean bean = new FavoriteThreadBean(); 341 bean.setMemberID(resultSet.getInt("MemberID")); 342 bean.setThreadID(resultSet.getInt("ThreadID")); 343 bean.setFavoriteCreationDate(resultSet.getTimestamp("FavoriteCreationDate")); 344 bean.setFavoriteType(resultSet.getInt("FavoriteType")); 345 bean.setFavoriteOption(resultSet.getInt("FavoriteOption")); 346 bean.setFavoriteStatus(resultSet.getInt("FavoriteStatus")); 347 retValue.add(bean); 348 } 349 return retValue; 350 } catch(SQLException sqle) { 351 log.error("Sql Execution Error!", sqle); 352 throw new DatabaseException("Error executing SQL in FavoriteThreadDAOImplJDBC.getFavoriteThreads_inMember."); 353 } finally { 354 DBUtils.closeResultSet(resultSet); 355 DBUtils.closeStatement(statement); 356 DBUtils.closeConnection(connection); 357 } 358 } 359 387 388 public int getNumberOfFavoriteThreads_inMember(int memberID) 389 throws AssertionException, DatabaseException { 390 391 Connection connection = null; 392 PreparedStatement statement = null; 393 ResultSet resultSet = null; 394 StringBuffer sql = new StringBuffer (512); 395 sql.append("SELECT Count(*)"); 396 sql.append(" FROM " + TABLE_NAME); 397 sql.append(" WHERE MemberID = ? "); 398 try { 399 connection = DBUtils.getConnection(); 400 statement = connection.prepareStatement(sql.toString()); 401 statement.setInt(1, memberID); 402 resultSet = statement.executeQuery(); 403 if (!resultSet.next()) { 404 throw new AssertionException("Assertion in FavoriteThreadDAOImplJDBC.getNumberOfFavoriteThreads_inMember."); 405 } 406 return resultSet.getInt(1); 407 } catch(SQLException sqle) { 408 log.error("Sql Execution Error!", sqle); 409 throw new DatabaseException("Error executing SQL in FavoriteThreadDAOImplJDBC.getNumberOfFavoriteThreads_inMember."); 410 } finally { 411 DBUtils.closeResultSet(resultSet); 412 DBUtils.closeStatement(statement); 413 DBUtils.closeConnection(connection); 414 } 415 } 416 417 } | Popular Tags |