001: /*
002: * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/PmAttachmentDAOImplJDBC.java,v 1.20 2007/10/09 11:09:20 lexuanttkhtn Exp $
003: * $Author: lexuanttkhtn $
004: * $Revision: 1.20 $
005: * $Date: 2007/10/09 11:09:20 $
006: *
007: * ====================================================================
008: *
009: * Copyright (C) 2002-2007 by MyVietnam.net
010: *
011: * All copyright notices regarding mvnForum MUST remain
012: * intact in the scripts and in the outputted HTML.
013: * The "powered by" text/logo with a link back to
014: * http://www.mvnForum.com and http://www.MyVietnam.net in
015: * the footer of the pages MUST remain visible when the pages
016: * are viewed on the internet or intranet.
017: *
018: * This program is free software; you can redistribute it and/or modify
019: * it under the terms of the GNU General Public License as published by
020: * the Free Software Foundation; either version 2 of the License, or
021: * any later version.
022: *
023: * This program is distributed in the hope that it will be useful,
024: * but WITHOUT ANY WARRANTY; without even the implied warranty of
025: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
026: * GNU General Public License for more details.
027: *
028: * You should have received a copy of the GNU General Public License
029: * along with this program; if not, write to the Free Software
030: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
031: *
032: * Support can be obtained from support forums at:
033: * http://www.mvnForum.com/mvnforum/index
034: *
035: * Correspondence and Marketing Questions can be sent to:
036: * info at MyVietnam net
037: *
038: * @author: Minh Nguyen
039: * @author: Mai Nguyen
040: */
041: package com.mvnforum.db.jdbc;
042:
043: import java.io.StringReader;
044: import java.sql.*;
045: import java.util.ArrayList;
046: import java.util.Collection;
047:
048: import com.mvnforum.db.*;
049: import net.myvietnam.mvncore.db.DBUtils;
050: import net.myvietnam.mvncore.exception.*;
051:
052: import org.apache.commons.logging.Log;
053: import org.apache.commons.logging.LogFactory;
054:
055: public class PmAttachmentDAOImplJDBC implements PmAttachmentDAO {
056:
057: private static Log log = LogFactory
058: .getLog(PmAttachmentDAOImplJDBC.class);
059:
060: // this variable will support caching if cache for this class is needed
061: private static boolean m_dirty = true;
062:
063: public PmAttachmentDAOImplJDBC() {
064: }
065:
066: protected static boolean isDirty() {
067: return m_dirty;
068: }
069:
070: protected static void setDirty(boolean dirty) {
071: m_dirty = dirty;
072: }
073:
074: /**
075: * This is a customized method
076: */
077: protected static int findPmAttachID(int memberID,
078: Timestamp pmAttachCreationDate)
079: throws ObjectNotFoundException, DatabaseException {
080:
081: Connection connection = null;
082: PreparedStatement statement = null;
083: ResultSet resultSet = null;
084: StringBuffer sql = new StringBuffer(512);
085: sql.append("SELECT PmAttachID");
086: sql.append(" FROM " + TABLE_NAME);
087: sql.append(" WHERE MemberID = ? AND PmAttachCreationDate = ? ");
088: try {
089: connection = DBUtils.getConnection();
090: statement = connection.prepareStatement(sql.toString());
091: statement.setInt(1, memberID);
092: statement.setTimestamp(2, pmAttachCreationDate);
093: resultSet = statement.executeQuery();
094: if (!resultSet.next()) {
095: throw new ObjectNotFoundException(
096: "Cannot find the PmAttachID in table PmAttachment.");
097: }
098:
099: return resultSet.getInt("PmAttachID");
100: } catch (SQLException sqle) {
101: log.error("Sql Execution Error!", sqle);
102: throw new DatabaseException(
103: "Error executing SQL in PmAttachmentDAOImplJDBC.findPmAttachID.");
104: } finally {
105: DBUtils.closeResultSet(resultSet);
106: DBUtils.closeStatement(statement);
107: DBUtils.closeConnection(connection);
108: }
109: }
110:
111: public void findByPrimaryKey(int pmAttachID)
112: throws ObjectNotFoundException, DatabaseException {
113:
114: Connection connection = null;
115: PreparedStatement statement = null;
116: ResultSet resultSet = null;
117: StringBuffer sql = new StringBuffer(512);
118: sql.append("SELECT PmAttachID");
119: sql.append(" FROM " + TABLE_NAME);
120: sql.append(" WHERE PmAttachID = ?");
121: try {
122: connection = DBUtils.getConnection();
123: statement = connection.prepareStatement(sql.toString());
124: statement.setInt(1, pmAttachID);
125: resultSet = statement.executeQuery();
126: if (!resultSet.next()) {
127: throw new ObjectNotFoundException(
128: "Cannot find the primary key (" + pmAttachID
129: + ") in table 'PmAttachment'.");
130: }
131: } catch (SQLException sqle) {
132: log.error("Sql Execution Error!", sqle);
133: throw new DatabaseException(
134: "Error executing SQL in PmAttachmentDAOImplJDBC.findByPrimaryKey.");
135: } finally {
136: DBUtils.closeResultSet(resultSet);
137: DBUtils.closeStatement(statement);
138: DBUtils.closeConnection(connection);
139: }
140: }
141:
142: /*
143: * Included columns: MemberID, PmAttachFilename, PmAttachFileSize, PmAttachMimeType, PmAttachDesc,
144: * PmAttachCreationIP, PmAttachCreationDate, PmAttachModifiedDate, PmAttachDownloadCount, PmAttachOption,
145: * PmAttachStatus
146: * Excluded columns: PmAttachID
147: */
148: public int create(int memberID, String pmAttachFilename,
149: int pmAttachFileSize, String pmAttachMimeType,
150: String pmAttachDesc, String pmAttachCreationIP,
151: Timestamp pmAttachCreationDate,
152: Timestamp pmAttachModifiedDate, int pmAttachDownloadCount,
153: int pmAttachOption, int pmAttachStatus)
154: throws CreateException, DatabaseException,
155: ForeignKeyNotFoundException, ObjectNotFoundException {
156:
157: try {
158: // @todo: modify the parameter list as needed
159: // You may have to regenerate this method if the needed columns dont have attribute 'include'
160: DAOFactory.getMemberDAO().findByPrimaryKey(memberID);
161: } catch (ObjectNotFoundException e) {
162: throw new ForeignKeyNotFoundException(
163: "Foreign key refers to table 'Member' does not exist. Cannot create new PmAttachment.");
164: }
165:
166: Connection connection = null;
167: PreparedStatement statement = null;
168: StringBuffer sql = new StringBuffer(512);
169: sql
170: .append("INSERT INTO "
171: + TABLE_NAME
172: + " (MemberID, PmAttachFilename, PmAttachFileSize, PmAttachMimeType, PmAttachDesc, PmAttachCreationIP, PmAttachCreationDate, PmAttachModifiedDate, PmAttachDownloadCount, PmAttachOption, PmAttachStatus)");
173: sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
174: try {
175: connection = DBUtils.getConnection();
176: statement = connection.prepareStatement(sql.toString());
177:
178: statement.setInt(1, memberID);
179: statement.setString(2, pmAttachFilename);
180: statement.setInt(3, pmAttachFileSize);
181: statement.setString(4, pmAttachMimeType);
182: if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
183: statement.setCharacterStream(5, new StringReader(
184: pmAttachDesc), pmAttachDesc.length());
185: } else {
186: statement.setString(5, pmAttachDesc);
187: }
188: statement.setString(6, pmAttachCreationIP);
189: statement.setTimestamp(7, pmAttachCreationDate);
190: statement.setTimestamp(8, pmAttachModifiedDate);
191: statement.setInt(9, pmAttachDownloadCount);
192: statement.setInt(10, pmAttachOption);
193: statement.setInt(11, pmAttachStatus);
194:
195: if (statement.executeUpdate() != 1) {
196: throw new CreateException(
197: "Error adding a row into table 'PmAttachment'.");
198: }
199: m_dirty = true;
200: } catch (SQLException sqle) {
201: log.error("Sql Execution Error!", sqle);
202: throw new DatabaseException(
203: "Error executing SQL in PmAttachmentDAOImplJDBC.create.");
204: } finally {
205: DBUtils.closeStatement(statement);
206: DBUtils.closeConnection(connection);
207: }
208:
209: int pmAttachID = 0;
210: try {
211: pmAttachID = findPmAttachID(memberID, pmAttachCreationDate);
212: } catch (ObjectNotFoundException ex) {
213: // Hack the Oracle 9i problem
214: Timestamp roundTimestamp = new Timestamp(
215: (pmAttachCreationDate.getTime() / 1000) * 1000);
216: pmAttachID = findPmAttachID(memberID, roundTimestamp);
217: }
218: return pmAttachID;
219: }
220:
221: public void delete(int pmAttachID) throws DatabaseException,
222: ObjectNotFoundException {
223:
224: Connection connection = null;
225: PreparedStatement statement = null;
226: StringBuffer sql = new StringBuffer(512);
227: sql.append("DELETE FROM " + TABLE_NAME);
228: sql.append(" WHERE PmAttachID = ?");
229:
230: try {
231: connection = DBUtils.getConnection();
232: statement = connection.prepareStatement(sql.toString());
233: statement.setInt(1, pmAttachID);
234: if (statement.executeUpdate() != 1) {
235: throw new ObjectNotFoundException(
236: "Cannot delete a row in table PmAttachment where primary key = ("
237: + pmAttachID + ").");
238: }
239: m_dirty = true;
240: } catch (SQLException sqle) {
241: log.error("Sql Execution Error!", sqle);
242: throw new DatabaseException(
243: "Error executing SQL in PmAttachmentDAOImplJDBC.delete.");
244: } finally {
245: DBUtils.closeStatement(statement);
246: DBUtils.closeConnection(connection);
247: }
248: }
249:
250: /*
251: * Included columns: PmAttachID, MemberID, PmAttachFilename, PmAttachFileSize, PmAttachMimeType,
252: * PmAttachDesc, PmAttachCreationIP, PmAttachCreationDate, PmAttachModifiedDate, PmAttachDownloadCount,
253: * PmAttachOption, PmAttachStatus
254: * Excluded columns:
255: */
256: public PmAttachmentBean getPmAttachment(int pmAttachID)
257: throws ObjectNotFoundException, DatabaseException {
258:
259: Connection connection = null;
260: PreparedStatement statement = null;
261: ResultSet resultSet = null;
262: StringBuffer sql = new StringBuffer(512);
263: sql
264: .append("SELECT PmAttachID, MemberID, PmAttachFilename, PmAttachFileSize, PmAttachMimeType, PmAttachDesc, PmAttachCreationIP, PmAttachCreationDate, PmAttachModifiedDate, PmAttachDownloadCount, PmAttachOption, PmAttachStatus");
265: sql.append(" FROM " + TABLE_NAME);
266: sql.append(" WHERE PmAttachID = ?");
267: try {
268: connection = DBUtils.getConnection();
269: statement = connection.prepareStatement(sql.toString());
270: statement.setInt(1, pmAttachID);
271: resultSet = statement.executeQuery();
272: if (!resultSet.next()) {
273: throw new ObjectNotFoundException(
274: "Cannot find the row in table PmAttachment where primary key = ("
275: + pmAttachID + ").");
276: }
277:
278: PmAttachmentBean bean = new PmAttachmentBean();
279: // @todo: uncomment the following line(s) as needed
280: //bean.setPmAttachID(pmAttachID);
281: bean.setPmAttachID(resultSet.getInt("PmAttachID"));
282: bean.setMemberID(resultSet.getInt("MemberID"));
283: bean.setPmAttachFilename(resultSet
284: .getString("PmAttachFilename"));
285: bean.setPmAttachFileSize(resultSet
286: .getInt("PmAttachFileSize"));
287: bean.setPmAttachMimeType(resultSet
288: .getString("PmAttachMimeType"));
289: bean.setPmAttachDesc(resultSet.getString("PmAttachDesc"));
290: bean.setPmAttachCreationIP(resultSet
291: .getString("PmAttachCreationIP"));
292: bean.setPmAttachCreationDate(resultSet
293: .getTimestamp("PmAttachCreationDate"));
294: bean.setPmAttachModifiedDate(resultSet
295: .getTimestamp("PmAttachModifiedDate"));
296: bean.setPmAttachDownloadCount(resultSet
297: .getInt("PmAttachDownloadCount"));
298: bean.setPmAttachOption(resultSet.getInt("PmAttachOption"));
299: bean.setPmAttachStatus(resultSet.getInt("PmAttachStatus"));
300: return bean;
301: } catch (SQLException sqle) {
302: log.error("Sql Execution Error!", sqle);
303: throw new DatabaseException(
304: "Error executing SQL in PmAttachmentDAOImplJDBC.getPmAttachment(pk).");
305: } finally {
306: DBUtils.closeResultSet(resultSet);
307: DBUtils.closeStatement(statement);
308: DBUtils.closeConnection(connection);
309: }
310: }
311:
312: /**
313: * This method should be call only when we can make sure that postID is in database
314: */
315: public void increaseDownloadCount(int pmAttachID)
316: throws DatabaseException, ObjectNotFoundException {
317:
318: Connection connection = null;
319: PreparedStatement statement = null;
320: String sql = "UPDATE "
321: + TABLE_NAME
322: + " SET PmAttachDownloadCount = PmAttachDownloadCount + 1 WHERE PmAttachID = ?";
323: try {
324: connection = DBUtils.getConnection();
325: statement = connection.prepareStatement(sql);
326: statement.setInt(1, pmAttachID);
327: if (statement.executeUpdate() != 1) {
328: throw new ObjectNotFoundException(
329: "Cannot update the PmAttachDownloadCount in table PmAttachment. Please contact Web site Administrator.");
330: }
331: // @todo: review these lines below
332: // ATTENTION !!!
333: setDirty(true);
334: } catch (SQLException sqle) {
335: log.error("Sql Execution Error!", sqle);
336: throw new DatabaseException(
337: "Error executing SQL in PmAttachmentDAOImplJDBC.increaseDownloadCount(pk).");
338: } finally {
339: DBUtils.closeStatement(statement);
340: DBUtils.closeConnection(connection);
341: }
342: }
343:
344: /*
345: * Included columns: PmAttachID, MemberID, PmAttachFilename, PmAttachFileSize, PmAttachMimeType,
346: * PmAttachDesc, PmAttachCreationIP, PmAttachCreationDate, PmAttachModifiedDate, PmAttachDownloadCount,
347: * PmAttachOption, PmAttachStatus
348: * Excluded columns:
349: */
350: public Collection getPmAttachments_inMessage(int messageID)
351: throws DatabaseException {
352:
353: Connection connection = null;
354: PreparedStatement statement = null;
355: ResultSet resultSet = null;
356: Collection retValue = new ArrayList();
357: StringBuffer sql = new StringBuffer(512);
358: sql
359: .append("SELECT pmattachment.PmAttachID, MemberID, PmAttachFilename, PmAttachFileSize, PmAttachMimeType, PmAttachDesc, PmAttachCreationIP, PmAttachCreationDate, PmAttachModifiedDate, PmAttachDownloadCount, PmAttachOption, PmAttachStatus");
360: sql.append(" FROM " + TABLE_NAME + " pmattachment, "
361: + PmAttachMessageDAO.TABLE_NAME + " pmattachmessage");
362: sql
363: .append(" WHERE pmattachment.PmAttachID = pmattachmessage.PmAttachID AND pmattachmessage.MessageID = ?");
364: sql.append(" ORDER BY pmattachment.PmAttachID ASC ");
365: try {
366: connection = DBUtils.getConnection();
367: statement = connection.prepareStatement(sql.toString());
368: statement.setInt(1, messageID);
369: resultSet = statement.executeQuery();
370: while (resultSet.next()) {
371: PmAttachmentBean bean = new PmAttachmentBean();
372: bean.setPmAttachID(resultSet.getInt("PmAttachID"));
373: bean.setMemberID(resultSet.getInt("MemberID"));
374: bean.setPmAttachFilename(resultSet
375: .getString("PmAttachFilename"));
376: bean.setPmAttachFileSize(resultSet
377: .getInt("PmAttachFileSize"));
378: bean.setPmAttachMimeType(resultSet
379: .getString("PmAttachMimeType"));
380: bean.setPmAttachDesc(resultSet
381: .getString("PmAttachDesc"));
382: bean.setPmAttachCreationIP(resultSet
383: .getString("PmAttachCreationIP"));
384: bean.setPmAttachCreationDate(resultSet
385: .getTimestamp("PmAttachCreationDate"));
386: bean.setPmAttachModifiedDate(resultSet
387: .getTimestamp("PmAttachModifiedDate"));
388: bean.setPmAttachDownloadCount(resultSet
389: .getInt("PmAttachDownloadCount"));
390: bean.setPmAttachOption(resultSet
391: .getInt("PmAttachOption"));
392: bean.setPmAttachStatus(resultSet
393: .getInt("PmAttachStatus"));
394: retValue.add(bean);
395: }
396: return retValue;
397: } catch (SQLException sqle) {
398: log.error("Sql Execution Error!", sqle);
399: throw new DatabaseException(
400: "Error executing SQL in PmAttachmentDAOImplJDBC.getPmAttachments_inMessage.");
401: } finally {
402: DBUtils.closeResultSet(resultSet);
403: DBUtils.closeStatement(statement);
404: DBUtils.closeConnection(connection);
405: }
406: }
407:
408: /*
409: * Included columns: PmAttachID, MemberID, PmAttachFilename, PmAttachFileSize, PmAttachMimeType,
410: * PmAttachDesc, PmAttachCreationIP, PmAttachCreationDate, PmAttachModifiedDate, PmAttachDownloadCount,
411: * PmAttachOption, PmAttachStatus
412: * Excluded columns:
413: */
414: // Note: this method using LEFT JOIN
415: public Collection getOrphanPmAttachments() throws DatabaseException {
416:
417: Connection connection = null;
418: PreparedStatement statement = null;
419: ResultSet resultSet = null;
420: Collection retValue = new ArrayList();
421: StringBuffer sql = new StringBuffer(512);
422: if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
423: // Oracle query
424: sql
425: .append("SELECT pmattachment.PmAttachID, MemberID, PmAttachFilename, PmAttachFileSize, PmAttachMimeType, PmAttachDesc, PmAttachCreationIP, PmAttachCreationDate, PmAttachModifiedDate, PmAttachDownloadCount, PmAttachOption, PmAttachStatus");
426: sql.append(" FROM " + TABLE_NAME + " pmattachment , "
427: + PmAttachMessageDAO.TABLE_NAME
428: + " pmattachmessage");
429: sql
430: .append(" WHERE pmattachment.PmAttachID = pmattachmessage.PmAttachID (+) ");
431: sql.append(" AND pmattachmessage.PmAttachID IS NULL ");
432: } else {
433: // standard query
434: sql
435: .append("SELECT pmattachment.PmAttachID, MemberID, PmAttachFilename, PmAttachFileSize, PmAttachMimeType, PmAttachDesc, PmAttachCreationIP, PmAttachCreationDate, PmAttachModifiedDate, PmAttachDownloadCount, PmAttachOption, PmAttachStatus");
436: sql.append(" FROM " + TABLE_NAME
437: + " pmattachment LEFT JOIN "
438: + PmAttachMessageDAO.TABLE_NAME
439: + " pmattachmessage");
440: sql
441: .append(" ON pmattachment.PmAttachID = pmattachmessage.PmAttachID ");
442: sql.append(" WHERE pmattachmessage.PmAttachID IS NULL ");
443: }
444: try {
445: connection = DBUtils.getConnection();
446: statement = connection.prepareStatement(sql.toString());
447: resultSet = statement.executeQuery();
448: while (resultSet.next()) {
449: PmAttachmentBean bean = new PmAttachmentBean();
450: bean.setPmAttachID(resultSet.getInt("PmAttachID"));
451: bean.setMemberID(resultSet.getInt("MemberID"));
452: bean.setPmAttachFilename(resultSet
453: .getString("PmAttachFilename"));
454: bean.setPmAttachFileSize(resultSet
455: .getInt("PmAttachFileSize"));
456: bean.setPmAttachMimeType(resultSet
457: .getString("PmAttachMimeType"));
458: bean.setPmAttachDesc(resultSet
459: .getString("PmAttachDesc"));
460: bean.setPmAttachCreationIP(resultSet
461: .getString("PmAttachCreationIP"));
462: bean.setPmAttachCreationDate(resultSet
463: .getTimestamp("PmAttachCreationDate"));
464: bean.setPmAttachModifiedDate(resultSet
465: .getTimestamp("PmAttachModifiedDate"));
466: bean.setPmAttachDownloadCount(resultSet
467: .getInt("PmAttachDownloadCount"));
468: bean.setPmAttachOption(resultSet
469: .getInt("PmAttachOption"));
470: bean.setPmAttachStatus(resultSet
471: .getInt("PmAttachStatus"));
472: retValue.add(bean);
473: }
474: return retValue;
475: } catch (SQLException sqle) {
476: log.error("Sql Execution Error!", sqle);
477: throw new DatabaseException(
478: "Error executing SQL in PmAttachmentDAOImplJDBC.getOrphanPmAttachments.");
479: } finally {
480: DBUtils.closeResultSet(resultSet);
481: DBUtils.closeStatement(statement);
482: DBUtils.closeConnection(connection);
483: }
484: }
485:
486: public void updatePmAttachOption(int pmAttachID, int pmAttachOption)
487: throws DatabaseException, ObjectNotFoundException {
488:
489: Connection connection = null;
490: PreparedStatement statement = null;
491: String sql = "UPDATE " + TABLE_NAME
492: + " SET PmAttachOption = ? WHERE PmAttachID = ?";
493: try {
494: connection = DBUtils.getConnection();
495: statement = connection.prepareStatement(sql);
496:
497: statement.setInt(1, pmAttachOption);
498: statement.setInt(2, pmAttachID);
499: if (statement.executeUpdate() != 1) {
500: throw new ObjectNotFoundException(
501: "Cannot update the Option in table PmAttachment. Please contact Web site Administrator.");
502: }
503: setDirty(true);
504: } catch (SQLException sqle) {
505: log.error("Sql Execution Error!", sqle);
506: throw new DatabaseException(
507: "Error executing SQL in PmAttachmentDAOImplJDBC.updatePmAttachOption.");
508: } finally {
509: DBUtils.closeStatement(statement);
510: DBUtils.closeConnection(connection);
511: }
512: }
513:
514: }// end of class PmAttachmentDAOImplJDBC
|