1 41 package com.mvnforum.db.jdbc; 42 43 import java.io.StringReader ; 44 import java.sql.*; 45 import java.util.ArrayList ; 46 import java.util.Collection ; 47 48 import com.mvnforum.db.*; 49 import net.myvietnam.mvncore.db.DBUtils; 50 import net.myvietnam.mvncore.exception.*; 51 52 import org.apache.commons.logging.Log; 53 import org.apache.commons.logging.LogFactory; 54 55 public class PmAttachmentDAOImplJDBC implements PmAttachmentDAO { 56 57 private static Log log = LogFactory.getLog(PmAttachmentDAOImplJDBC.class); 58 59 private static boolean m_dirty = true; 61 62 public PmAttachmentDAOImplJDBC() { 63 } 64 65 protected static boolean isDirty() { 66 return m_dirty; 67 } 68 69 protected static void setDirty(boolean dirty) { 70 m_dirty = dirty; 71 } 72 73 76 protected static int findPmAttachID(int memberID, Timestamp pmAttachCreationDate) 77 throws ObjectNotFoundException, DatabaseException { 78 79 Connection connection = null; 80 PreparedStatement statement = null; 81 ResultSet resultSet = null; 82 StringBuffer sql = new StringBuffer (512); 83 sql.append("SELECT PmAttachID"); 84 sql.append(" FROM " + TABLE_NAME); 85 sql.append(" WHERE MemberID = ? AND PmAttachCreationDate = ? "); 86 try { 87 connection = DBUtils.getConnection(); 88 statement = connection.prepareStatement(sql.toString()); 89 statement.setInt(1, memberID); 90 statement.setTimestamp(2, pmAttachCreationDate); 91 resultSet = statement.executeQuery(); 92 if(!resultSet.next()) { 93 throw new ObjectNotFoundException("Cannot find the PmAttachID in table PmAttachment."); 94 } 95 96 return resultSet.getInt("PmAttachID"); 97 } catch(SQLException sqle) { 98 log.error("Sql Execution Error!", sqle); 99 throw new DatabaseException("Error executing SQL in PmAttachmentDAOImplJDBC.findPmAttachID."); 100 } finally { 101 DBUtils.closeResultSet(resultSet); 102 DBUtils.closeStatement(statement); 103 DBUtils.closeConnection(connection); 104 } 105 } 106 107 public void findByPrimaryKey(int pmAttachID) 108 throws ObjectNotFoundException, DatabaseException { 109 110 Connection connection = null; 111 PreparedStatement statement = null; 112 ResultSet resultSet = null; 113 StringBuffer sql = new StringBuffer (512); 114 sql.append("SELECT PmAttachID"); 115 sql.append(" FROM " + TABLE_NAME); 116 sql.append(" WHERE PmAttachID = ?"); 117 try { 118 connection = DBUtils.getConnection(); 119 statement = connection.prepareStatement(sql.toString()); 120 statement.setInt(1, pmAttachID); 121 resultSet = statement.executeQuery(); 122 if (!resultSet.next()) { 123 throw new ObjectNotFoundException("Cannot find the primary key (" + pmAttachID + ") in table 'PmAttachment'."); 124 } 125 } catch(SQLException sqle) { 126 log.error("Sql Execution Error!", sqle); 127 throw new DatabaseException("Error executing SQL in PmAttachmentDAOImplJDBC.findByPrimaryKey."); 128 } finally { 129 DBUtils.closeResultSet(resultSet); 130 DBUtils.closeStatement(statement); 131 DBUtils.closeConnection(connection); 132 } 133 } 134 135 141 public int create(int memberID, String pmAttachFilename, int pmAttachFileSize, 142 String pmAttachMimeType, String pmAttachDesc, String pmAttachCreationIP, 143 Timestamp pmAttachCreationDate, Timestamp pmAttachModifiedDate, int pmAttachDownloadCount, 144 int pmAttachOption, int pmAttachStatus) 145 throws CreateException, DatabaseException, ForeignKeyNotFoundException, ObjectNotFoundException { 146 147 try { 148 DAOFactory.getMemberDAO().findByPrimaryKey(memberID); 151 } catch(ObjectNotFoundException e) { 152 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Member' does not exist. Cannot create new PmAttachment."); 153 } 154 155 Connection connection = null; 156 PreparedStatement statement = null; 157 StringBuffer sql = new StringBuffer (512); 158 sql.append("INSERT INTO " + TABLE_NAME + " (MemberID, PmAttachFilename, PmAttachFileSize, PmAttachMimeType, PmAttachDesc, PmAttachCreationIP, PmAttachCreationDate, PmAttachModifiedDate, PmAttachDownloadCount, PmAttachOption, PmAttachStatus)"); 159 sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); 160 try { 161 connection = DBUtils.getConnection(); 162 statement = connection.prepareStatement(sql.toString()); 163 164 statement.setInt(1, memberID); 165 statement.setString(2, pmAttachFilename); 166 statement.setInt(3, pmAttachFileSize); 167 statement.setString(4, pmAttachMimeType); 168 if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) { 169 statement.setCharacterStream(5, new StringReader (pmAttachDesc), pmAttachDesc.length()); 170 } else { 171 statement.setString(5, pmAttachDesc); 172 } 173 statement.setString(6, pmAttachCreationIP); 174 statement.setTimestamp(7, pmAttachCreationDate); 175 statement.setTimestamp(8, pmAttachModifiedDate); 176 statement.setInt(9, pmAttachDownloadCount); 177 statement.setInt(10, pmAttachOption); 178 statement.setInt(11, pmAttachStatus); 179 180 if (statement.executeUpdate() != 1) { 181 throw new CreateException("Error adding a row into table 'PmAttachment'."); 182 } 183 m_dirty = true; 184 } catch(SQLException sqle) { 185 log.error("Sql Execution Error!", sqle); 186 throw new DatabaseException("Error executing SQL in PmAttachmentDAOImplJDBC.create."); 187 } finally { 188 DBUtils.closeStatement(statement); 189 DBUtils.closeConnection(connection); 190 } 191 192 int pmAttachID = 0; 193 try { 194 pmAttachID = findPmAttachID(memberID, pmAttachCreationDate); 195 } catch (ObjectNotFoundException ex) { 196 Timestamp roundTimestamp = new Timestamp((pmAttachCreationDate.getTime()/1000)*1000); 198 pmAttachID = findPmAttachID(memberID, roundTimestamp); 199 } 200 return pmAttachID; 201 } 202 203 public void delete(int pmAttachID) 204 throws DatabaseException, ObjectNotFoundException { 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 PmAttachID = ?"); 211 212 try { 213 connection = DBUtils.getConnection(); 214 statement = connection.prepareStatement(sql.toString()); 215 statement.setInt(1, pmAttachID); 216 if (statement.executeUpdate() != 1) { 217 throw new ObjectNotFoundException("Cannot delete a row in table PmAttachment where primary key = (" + pmAttachID + ")."); 218 } 219 m_dirty = true; 220 } catch(SQLException sqle) { 221 log.error("Sql Execution Error!", sqle); 222 throw new DatabaseException("Error executing SQL in PmAttachmentDAOImplJDBC.delete."); 223 } finally { 224 DBUtils.closeStatement(statement); 225 DBUtils.closeConnection(connection); 226 } 227 } 228 229 235 public PmAttachmentBean getPmAttachment(int pmAttachID) 236 throws ObjectNotFoundException, DatabaseException { 237 238 Connection connection = null; 239 PreparedStatement statement = null; 240 ResultSet resultSet = null; 241 StringBuffer sql = new StringBuffer (512); 242 sql.append("SELECT PmAttachID, MemberID, PmAttachFilename, PmAttachFileSize, PmAttachMimeType, PmAttachDesc, PmAttachCreationIP, PmAttachCreationDate, PmAttachModifiedDate, PmAttachDownloadCount, PmAttachOption, PmAttachStatus"); 243 sql.append(" FROM " + TABLE_NAME); 244 sql.append(" WHERE PmAttachID = ?"); 245 try { 246 connection = DBUtils.getConnection(); 247 statement = connection.prepareStatement(sql.toString()); 248 statement.setInt(1, pmAttachID); 249 resultSet = statement.executeQuery(); 250 if(!resultSet.next()) { 251 throw new ObjectNotFoundException("Cannot find the row in table PmAttachment where primary key = (" + pmAttachID + ")."); 252 } 253 254 PmAttachmentBean bean = new PmAttachmentBean(); 255 bean.setPmAttachID(resultSet.getInt("PmAttachID")); 258 bean.setMemberID(resultSet.getInt("MemberID")); 259 bean.setPmAttachFilename(resultSet.getString("PmAttachFilename")); 260 bean.setPmAttachFileSize(resultSet.getInt("PmAttachFileSize")); 261 bean.setPmAttachMimeType(resultSet.getString("PmAttachMimeType")); 262 bean.setPmAttachDesc(resultSet.getString("PmAttachDesc")); 263 bean.setPmAttachCreationIP(resultSet.getString("PmAttachCreationIP")); 264 bean.setPmAttachCreationDate(resultSet.getTimestamp("PmAttachCreationDate")); 265 bean.setPmAttachModifiedDate(resultSet.getTimestamp("PmAttachModifiedDate")); 266 bean.setPmAttachDownloadCount(resultSet.getInt("PmAttachDownloadCount")); 267 bean.setPmAttachOption(resultSet.getInt("PmAttachOption")); 268 bean.setPmAttachStatus(resultSet.getInt("PmAttachStatus")); 269 return bean; 270 } catch(SQLException sqle) { 271 log.error("Sql Execution Error!", sqle); 272 throw new DatabaseException("Error executing SQL in PmAttachmentDAOImplJDBC.getPmAttachment(pk)."); 273 } finally { 274 DBUtils.closeResultSet(resultSet); 275 DBUtils.closeStatement(statement); 276 DBUtils.closeConnection(connection); 277 } 278 } 279 280 283 public void increaseDownloadCount(int pmAttachID) 284 throws DatabaseException, ObjectNotFoundException { 285 286 Connection connection = null; 287 PreparedStatement statement = null; 288 String sql = "UPDATE " + TABLE_NAME + " SET PmAttachDownloadCount = PmAttachDownloadCount + 1 WHERE PmAttachID = ?"; 289 try { 290 connection = DBUtils.getConnection(); 291 statement = connection.prepareStatement(sql); 292 statement.setInt(1, pmAttachID); 293 if (statement.executeUpdate() != 1) { 294 throw new ObjectNotFoundException("Cannot update the PmAttachDownloadCount in table PmAttachment. Please contact Web site Administrator."); 295 } 296 setDirty(true); 299 } catch (SQLException sqle) { 300 log.error("Sql Execution Error!", sqle); 301 throw new DatabaseException("Error executing SQL in PmAttachmentDAOImplJDBC.increaseDownloadCount(pk)."); 302 } finally { 303 DBUtils.closeStatement(statement); 304 DBUtils.closeConnection(connection); 305 } 306 } 307 308 314 public Collection getPmAttachments_inMessage(int messageID) 315 throws DatabaseException { 316 317 Connection connection = null; 318 PreparedStatement statement = null; 319 ResultSet resultSet = null; 320 Collection retValue = new ArrayList (); 321 StringBuffer sql = new StringBuffer (512); 322 sql.append("SELECT pmattachment.PmAttachID, MemberID, PmAttachFilename, PmAttachFileSize, PmAttachMimeType, PmAttachDesc, PmAttachCreationIP, PmAttachCreationDate, PmAttachModifiedDate, PmAttachDownloadCount, PmAttachOption, PmAttachStatus"); 323 sql.append(" FROM " + TABLE_NAME + " pmattachment, " + PmAttachMessageDAO.TABLE_NAME + " pmattachmessage"); 324 sql.append(" WHERE pmattachment.PmAttachID = pmattachmessage.PmAttachID AND pmattachmessage.MessageID = ?"); 325 sql.append(" ORDER BY pmattachment.PmAttachID ASC "); 326 try { 327 connection = DBUtils.getConnection(); 328 statement = connection.prepareStatement(sql.toString()); 329 statement.setInt(1, messageID); 330 resultSet = statement.executeQuery(); 331 while (resultSet.next()) { 332 PmAttachmentBean bean = new PmAttachmentBean(); 333 bean.setPmAttachID(resultSet.getInt("PmAttachID")); 334 bean.setMemberID(resultSet.getInt("MemberID")); 335 bean.setPmAttachFilename(resultSet.getString("PmAttachFilename")); 336 bean.setPmAttachFileSize(resultSet.getInt("PmAttachFileSize")); 337 bean.setPmAttachMimeType(resultSet.getString("PmAttachMimeType")); 338 bean.setPmAttachDesc(resultSet.getString("PmAttachDesc")); 339 bean.setPmAttachCreationIP(resultSet.getString("PmAttachCreationIP")); 340 bean.setPmAttachCreationDate(resultSet.getTimestamp("PmAttachCreationDate")); 341 bean.setPmAttachModifiedDate(resultSet.getTimestamp("PmAttachModifiedDate")); 342 bean.setPmAttachDownloadCount(resultSet.getInt("PmAttachDownloadCount")); 343 bean.setPmAttachOption(resultSet.getInt("PmAttachOption")); 344 bean.setPmAttachStatus(resultSet.getInt("PmAttachStatus")); 345 retValue.add(bean); 346 } 347 return retValue; 348 } catch(SQLException sqle) { 349 log.error("Sql Execution Error!", sqle); 350 throw new DatabaseException("Error executing SQL in PmAttachmentDAOImplJDBC.getPmAttachments_inMessage."); 351 } finally { 352 DBUtils.closeResultSet(resultSet); 353 DBUtils.closeStatement(statement); 354 DBUtils.closeConnection(connection); 355 } 356 } 357 358 364 public Collection getOrphanPmAttachments() 366 throws DatabaseException { 367 368 Connection connection = null; 369 PreparedStatement statement = null; 370 ResultSet resultSet = null; 371 Collection retValue = new ArrayList (); 372 StringBuffer sql = new StringBuffer (512); 373 if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) { 374 sql.append("SELECT pmattachment.PmAttachID, MemberID, PmAttachFilename, PmAttachFileSize, PmAttachMimeType, PmAttachDesc, PmAttachCreationIP, PmAttachCreationDate, PmAttachModifiedDate, PmAttachDownloadCount, PmAttachOption, PmAttachStatus"); 376 sql.append(" FROM " + TABLE_NAME + " pmattachment , " + PmAttachMessageDAO.TABLE_NAME + " pmattachmessage"); 377 sql.append(" WHERE pmattachment.PmAttachID = pmattachmessage.PmAttachID (+) "); 378 sql.append(" AND pmattachmessage.PmAttachID IS NULL "); 379 } else { 380 sql.append("SELECT pmattachment.PmAttachID, MemberID, PmAttachFilename, PmAttachFileSize, PmAttachMimeType, PmAttachDesc, PmAttachCreationIP, PmAttachCreationDate, PmAttachModifiedDate, PmAttachDownloadCount, PmAttachOption, PmAttachStatus"); 382 sql.append(" FROM " + TABLE_NAME + " pmattachment LEFT JOIN " + PmAttachMessageDAO.TABLE_NAME + " pmattachmessage"); 383 sql.append(" ON pmattachment.PmAttachID = pmattachmessage.PmAttachID "); 384 sql.append(" WHERE pmattachmessage.PmAttachID IS NULL "); 385 } 386 try { 387 connection = DBUtils.getConnection(); 388 statement = connection.prepareStatement(sql.toString()); 389 resultSet = statement.executeQuery(); 390 while (resultSet.next()) { 391 PmAttachmentBean bean = new PmAttachmentBean(); 392 bean.setPmAttachID(resultSet.getInt("PmAttachID")); 393 bean.setMemberID(resultSet.getInt("MemberID")); 394 bean.setPmAttachFilename(resultSet.getString("PmAttachFilename")); 395 bean.setPmAttachFileSize(resultSet.getInt("PmAttachFileSize")); 396 bean.setPmAttachMimeType(resultSet.getString("PmAttachMimeType")); 397 bean.setPmAttachDesc(resultSet.getString("PmAttachDesc")); 398 bean.setPmAttachCreationIP(resultSet.getString("PmAttachCreationIP")); 399 bean.setPmAttachCreationDate(resultSet.getTimestamp("PmAttachCreationDate")); 400 bean.setPmAttachModifiedDate(resultSet.getTimestamp("PmAttachModifiedDate")); 401 bean.setPmAttachDownloadCount(resultSet.getInt("PmAttachDownloadCount")); 402 bean.setPmAttachOption(resultSet.getInt("PmAttachOption")); 403 bean.setPmAttachStatus(resultSet.getInt("PmAttachStatus")); 404 retValue.add(bean); 405 } 406 return retValue; 407 } catch(SQLException sqle) { 408 log.error("Sql Execution Error!", sqle); 409 throw new DatabaseException("Error executing SQL in PmAttachmentDAOImplJDBC.getOrphanPmAttachments."); 410 } finally { 411 DBUtils.closeResultSet(resultSet); 412 DBUtils.closeStatement(statement); 413 DBUtils.closeConnection(connection); 414 } 415 } 416 417 public void updatePmAttachOption(int pmAttachID, int pmAttachOption) 418 throws DatabaseException, ObjectNotFoundException { 419 420 Connection connection = null; 421 PreparedStatement statement = null; 422 String sql = "UPDATE " + TABLE_NAME + " SET PmAttachOption = ? WHERE PmAttachID = ?"; 423 try { 424 connection = DBUtils.getConnection(); 425 statement = connection.prepareStatement(sql); 426 427 statement.setInt(1, pmAttachOption); 428 statement.setInt(2, pmAttachID); 429 if (statement.executeUpdate() != 1) { 430 throw new ObjectNotFoundException("Cannot update the Option in table PmAttachment. Please contact Web site Administrator."); 431 } 432 setDirty(true); 433 } catch (SQLException sqle) { 434 log.error("Sql Execution Error!", sqle); 435 throw new DatabaseException("Error executing SQL in PmAttachmentDAOImplJDBC.updatePmAttachOption."); 436 } finally { 437 DBUtils.closeStatement(statement); 438 DBUtils.closeConnection(connection); 439 } 440 } 441 442 } | Popular Tags |