001: /*
002: * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/WatchDAOImplJDBC.java,v 1.20 2007/10/09 11:09:21 lexuanttkhtn Exp $
003: * $Author: lexuanttkhtn $
004: * $Revision: 1.20 $
005: * $Date: 2007/10/09 11:09:21 $
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.sql.*;
044: import java.util.ArrayList;
045: import java.util.Collection;
046:
047: import org.apache.commons.logging.Log;
048: import org.apache.commons.logging.LogFactory;
049: import com.mvnforum.MVNForumConstant;
050: import com.mvnforum.db.*;
051: import net.myvietnam.mvncore.db.DBUtils;
052: import net.myvietnam.mvncore.exception.*;
053: import net.myvietnam.mvncore.util.AssertionUtil;
054:
055: public class WatchDAOImplJDBC implements WatchDAO {
056:
057: private static Log log = LogFactory.getLog(WatchDAOImplJDBC.class);
058:
059: // this variable will support caching if cache for this class is needed
060: private static boolean m_dirty = true;
061:
062: public WatchDAOImplJDBC() {
063: }
064:
065: protected static boolean isDirty() {
066: return m_dirty;
067: }
068:
069: protected static void setDirty(boolean dirty) {
070: m_dirty = dirty;
071: }
072:
073: public void findByPrimaryKey(int watchID)
074: throws ObjectNotFoundException, DatabaseException {
075:
076: Connection connection = null;
077: PreparedStatement statement = null;
078: ResultSet resultSet = null;
079: StringBuffer sql = new StringBuffer(512);
080: sql.append("SELECT WatchID");
081: sql.append(" FROM " + TABLE_NAME);
082: sql.append(" WHERE WatchID = ?");
083: try {
084: connection = DBUtils.getConnection();
085: statement = connection.prepareStatement(sql.toString());
086: statement.setInt(1, watchID);
087: resultSet = statement.executeQuery();
088: if (!resultSet.next()) {
089: throw new ObjectNotFoundException(
090: "Cannot find the primary key (" + watchID
091: + ") in table 'Watch'.");
092: }
093: } catch (SQLException sqle) {
094: log.error("Sql Execution Error!", sqle);
095: throw new DatabaseException(
096: "Error executing SQL in WatchDAOImplJDBC.findByPrimaryKey.");
097: } finally {
098: DBUtils.closeResultSet(resultSet);
099: DBUtils.closeStatement(statement);
100: DBUtils.closeConnection(connection);
101: }
102: }
103:
104: public void findByAlternateKey_MemberID_CategoryID_ForumID_ThreadID(
105: int memberID, int categoryID, int forumID, int threadID)
106: throws ObjectNotFoundException, DatabaseException {
107:
108: Connection connection = null;
109: PreparedStatement statement = null;
110: ResultSet resultSet = null;
111: StringBuffer sql = new StringBuffer(512);
112: sql.append("SELECT MemberID, CategoryID, ForumID, ThreadID");
113: sql.append(" FROM " + TABLE_NAME);
114: sql
115: .append(" WHERE MemberID = ? AND CategoryID = ? AND ForumID = ? AND ThreadID = ?");
116: try {
117: connection = DBUtils.getConnection();
118: statement = connection.prepareStatement(sql.toString());
119: statement.setInt(1, memberID);
120: statement.setInt(2, categoryID);
121: statement.setInt(3, forumID);
122: statement.setInt(4, threadID);
123: resultSet = statement.executeQuery();
124: if (!resultSet.next()) {
125: throw new ObjectNotFoundException(
126: "Cannot find the alternate key [MemberID, CategoryID, ForumID, ThreadID] ("
127: + memberID + ", " + categoryID + ", "
128: + forumID + ", " + threadID
129: + ") in table 'Watch'.");
130: }
131: } catch (SQLException sqle) {
132: log.error("Sql Execution Error!", sqle);
133: throw new DatabaseException(
134: "Error executing SQL in WatchDAOImplJDBC.findByAlternateKey_MemberID_CategoryID_ForumID_ThreadID.");
135: } finally {
136: DBUtils.closeResultSet(resultSet);
137: DBUtils.closeStatement(statement);
138: DBUtils.closeConnection(connection);
139: }
140: }
141:
142: /*
143: * Included columns: MemberID, CategoryID, ForumID, ThreadID, WatchType,
144: * WatchOption, WatchStatus, WatchCreationDate, WatchLastSentDate, WatchEndDate
145: * Excluded columns: WatchID
146: */
147: public void create(int memberID, int categoryID, int forumID,
148: int threadID, int watchType, int watchOption,
149: int watchStatus, Timestamp watchCreationDate,
150: Timestamp watchLastSentDate, Timestamp watchEndDate)
151: throws IllegalArgumentException, CreateException,
152: DatabaseException, DuplicateKeyException,
153: ForeignKeyNotFoundException {
154:
155: if ((memberID == 0)
156: || (memberID == MVNForumConstant.MEMBER_ID_OF_GUEST)) {
157: throw new IllegalArgumentException(
158: "Cannot add a new watch for Guest (id = "
159: + memberID + ")");
160: }
161: int notZeroCount = 0;
162: if (categoryID != 0) {
163: notZeroCount++;
164: }
165: if (forumID != 0) {
166: notZeroCount++;
167: }
168: if (threadID != 0) {
169: notZeroCount++;
170: }
171: if (notZeroCount > 1) {
172: throw new IllegalArgumentException(
173: "Cannot add watch with more than 1 element.");
174: }
175:
176: // @todo: Comment this try-catch block if the needed columns dont have attribute 'include'
177: // If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
178: try {
179: //Check if alternate key already exists
180: findByAlternateKey_MemberID_CategoryID_ForumID_ThreadID(
181: memberID, categoryID, forumID, threadID);
182: //If so, then we have to throw an exception
183: throw new DuplicateKeyException(
184: "Alternate key already exists. Cannot create new Watch with the same [MemberID, CategoryID, ForumID, ThreadID] ("
185: + memberID
186: + ", "
187: + categoryID
188: + ", "
189: + forumID + ", " + threadID + ").");
190: } catch (ObjectNotFoundException e) {
191: //Otherwise we can go ahead
192: }
193:
194: try {
195: // @todo: modify the parameter list as needed
196: // You may have to regenerate this method if the needed columns dont have attribute 'include'
197: DAOFactory.getMemberDAO().findByPrimaryKey(memberID);
198: } catch (ObjectNotFoundException e) {
199: throw new ForeignKeyNotFoundException(
200: "Foreign key refers to table 'Member' does not exist. Cannot create new Watch.");
201: }
202:
203: try {
204: // @todo: modify the parameter list as needed
205: // You may have to regenerate this method if the needed columns dont have attribute 'include'
206: if (categoryID != 0) {
207: DAOFactory.getCategoryDAO()
208: .findByPrimaryKey(categoryID);
209: }
210: } catch (ObjectNotFoundException e) {
211: throw new ForeignKeyNotFoundException(
212: "Foreign key refers to table 'Category' does not exist. Cannot create new Watch.");
213: }
214:
215: try {
216: // @todo: modify the parameter list as needed
217: // You may have to regenerate this method if the needed columns dont have attribute 'include'
218: if (forumID != 0) {
219: DAOFactory.getForumDAO().findByPrimaryKey(forumID);
220: }
221: } catch (ObjectNotFoundException e) {
222: throw new ForeignKeyNotFoundException(
223: "Foreign key refers to table 'Forum' does not exist. Cannot create new Watch.");
224: }
225:
226: try {
227: // @todo: modify the parameter list as needed
228: // You may have to regenerate this method if the needed columns dont have attribute 'include'
229: if (threadID != 0) {
230: DAOFactory.getThreadDAO().findByPrimaryKey(threadID);
231: }
232: } catch (ObjectNotFoundException e) {
233: throw new ForeignKeyNotFoundException(
234: "Foreign key refers to table 'Thread' does not exist. Cannot create new Watch.");
235: }
236:
237: Connection connection = null;
238: PreparedStatement statement = null;
239: StringBuffer sql = new StringBuffer(512);
240: sql
241: .append("INSERT INTO "
242: + TABLE_NAME
243: + " (MemberID, CategoryID, ForumID, ThreadID, WatchType, WatchOption, WatchStatus, WatchCreationDate, WatchLastSentDate, WatchEndDate)");
244: sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
245: try {
246: connection = DBUtils.getConnection();
247: statement = connection.prepareStatement(sql.toString());
248:
249: statement.setInt(1, memberID);
250: statement.setInt(2, categoryID);
251: statement.setInt(3, forumID);
252: statement.setInt(4, threadID);
253: statement.setInt(5, watchType);
254: statement.setInt(6, watchOption);
255: statement.setInt(7, watchStatus);
256: statement.setTimestamp(8, watchCreationDate);
257: statement.setTimestamp(9, watchLastSentDate);
258: statement.setTimestamp(10, watchEndDate);
259:
260: if (statement.executeUpdate() != 1) {
261: throw new CreateException(
262: "Error adding a row into table 'Watch'.");
263: }
264: m_dirty = true;
265: } catch (SQLException sqle) {
266: log.error("Sql Execution Error!", sqle);
267: throw new DatabaseException(
268: "Error executing SQL in WatchDAOImplJDBC.create.");
269: } finally {
270: DBUtils.closeStatement(statement);
271: DBUtils.closeConnection(connection);
272: }
273: }
274:
275: public void delete(int watchID) throws DatabaseException,
276: ObjectNotFoundException {
277:
278: Connection connection = null;
279: PreparedStatement statement = null;
280: StringBuffer sql = new StringBuffer(512);
281: sql.append("DELETE FROM " + TABLE_NAME);
282: sql.append(" WHERE WatchID = ?");
283:
284: try {
285: connection = DBUtils.getConnection();
286: statement = connection.prepareStatement(sql.toString());
287: statement.setInt(1, watchID);
288: if (statement.executeUpdate() != 1) {
289: throw new ObjectNotFoundException(
290: "Cannot delete a row in table Watch where primary key = ("
291: + watchID + ").");
292: }
293: m_dirty = true;
294: } catch (SQLException sqle) {
295: log.error("Sql Execution Error!", sqle);
296: throw new DatabaseException(
297: "Error executing SQL in WatchDAOImplJDBC.delete.");
298: } finally {
299: DBUtils.closeStatement(statement);
300: DBUtils.closeConnection(connection);
301: }
302: }
303:
304: public void delete_inMember(int memberID) throws DatabaseException {
305:
306: Connection connection = null;
307: PreparedStatement statement = null;
308: StringBuffer sql = new StringBuffer(512);
309: sql.append("DELETE FROM " + TABLE_NAME);
310: sql.append(" WHERE MemberID = ?");
311:
312: try {
313: connection = DBUtils.getConnection();
314: statement = connection.prepareStatement(sql.toString());
315: statement.setInt(1, memberID);
316: statement.executeUpdate();
317: m_dirty = true;
318: } catch (SQLException sqle) {
319: log.error("Sql Execution Error!", sqle);
320: throw new DatabaseException(
321: "Error executing SQL in WatchDAOImplJDBC.delete_inMember.");
322: } finally {
323: DBUtils.closeStatement(statement);
324: DBUtils.closeConnection(connection);
325: }
326: }
327:
328: public void delete_inCategory(int categoryID)
329: throws DatabaseException {
330:
331: Connection connection = null;
332: PreparedStatement statement = null;
333: StringBuffer sql = new StringBuffer(512);
334: sql.append("DELETE FROM " + TABLE_NAME);
335: sql.append(" WHERE CategoryID = ?");
336:
337: try {
338: connection = DBUtils.getConnection();
339: statement = connection.prepareStatement(sql.toString());
340: statement.setInt(1, categoryID);
341: statement.executeUpdate();
342: m_dirty = true;
343: } catch (SQLException sqle) {
344: log.error("Sql Execution Error!", sqle);
345: throw new DatabaseException(
346: "Error executing SQL in WatchDAOImplJDBC.delete_inCategory.");
347: } finally {
348: DBUtils.closeStatement(statement);
349: DBUtils.closeConnection(connection);
350: }
351: }
352:
353: public void delete_inForum(int forumID) throws DatabaseException {
354:
355: Connection connection = null;
356: PreparedStatement statement = null;
357: StringBuffer sql = new StringBuffer(512);
358: sql.append("DELETE FROM " + TABLE_NAME);
359: sql.append(" WHERE ForumID = ?");
360:
361: try {
362: connection = DBUtils.getConnection();
363: statement = connection.prepareStatement(sql.toString());
364: statement.setInt(1, forumID);
365: statement.executeUpdate();
366: m_dirty = true;
367: } catch (SQLException sqle) {
368: log.error("Sql Execution Error!", sqle);
369: throw new DatabaseException(
370: "Error executing SQL in WatchDAOImplJDBC.delete_inForum.");
371: } finally {
372: DBUtils.closeStatement(statement);
373: DBUtils.closeConnection(connection);
374: }
375: }
376:
377: public void delete_inThread(int threadID) throws DatabaseException {
378:
379: Connection connection = null;
380: PreparedStatement statement = null;
381: StringBuffer sql = new StringBuffer(512);
382: sql.append("DELETE FROM " + TABLE_NAME);
383: sql.append(" WHERE ThreadID = ?");
384:
385: try {
386: connection = DBUtils.getConnection();
387: statement = connection.prepareStatement(sql.toString());
388: statement.setInt(1, threadID);
389: statement.executeUpdate();
390: m_dirty = true;
391: } catch (SQLException sqle) {
392: log.error("Sql Execution Error!", sqle);
393: throw new DatabaseException(
394: "Error executing SQL in WatchDAOImplJDBC.delete_inThread.");
395: } finally {
396: DBUtils.closeStatement(statement);
397: DBUtils.closeConnection(connection);
398: }
399: }
400:
401: /*
402: * Included columns: WatchLastSentDate
403: * Excluded columns: WatchID, MemberID, CategoryID, ForumID, ThreadID,
404: * WatchType, WatchOption, WatchStatus, WatchCreationDate, WatchEndDate
405: */
406: public void updateLastSentDate(int watchID, // primary key
407: Timestamp watchLastSentDate)
408: throws ObjectNotFoundException, DatabaseException {
409:
410: Connection connection = null;
411: PreparedStatement statement = null;
412: StringBuffer sql = new StringBuffer(512);
413: sql.append("UPDATE " + TABLE_NAME
414: + " SET WatchLastSentDate = ?");
415: sql.append(" WHERE WatchID = ?");
416: try {
417: connection = DBUtils.getConnection();
418: statement = connection.prepareStatement(sql.toString());
419:
420: // // column(s) to update
421: statement.setTimestamp(1, watchLastSentDate);
422:
423: // primary key column(s)
424: statement.setInt(2, watchID);
425:
426: if (statement.executeUpdate() != 1) {
427: throw new ObjectNotFoundException(
428: "Cannot update table Watch where primary key = ("
429: + watchID + ").");
430: }
431: m_dirty = true;
432: } catch (SQLException sqle) {
433: log.error("Sql Execution Error!", sqle);
434: throw new DatabaseException(
435: "Error executing SQL in WatchDAOImplJDBC.updateLastSentDate.");
436: } finally {
437: DBUtils.closeStatement(statement);
438: DBUtils.closeConnection(connection);
439: }
440: }
441:
442: /*
443: * Included columns: MemberID, CategoryID, ForumID, ThreadID, WatchType,
444: * WatchOption, WatchStatus, WatchCreationDate, WatchLastSentDate, WatchEndDate
445: * Excluded columns: WatchID
446: */
447: public WatchBean getWatch(int watchID)
448: throws ObjectNotFoundException, DatabaseException {
449:
450: Connection connection = null;
451: PreparedStatement statement = null;
452: ResultSet resultSet = null;
453: StringBuffer sql = new StringBuffer(512);
454: sql
455: .append("SELECT MemberID, CategoryID, ForumID, ThreadID, WatchType, WatchOption, WatchStatus, WatchCreationDate, WatchLastSentDate, WatchEndDate");
456: sql.append(" FROM " + TABLE_NAME);
457: sql.append(" WHERE WatchID = ?");
458: try {
459: connection = DBUtils.getConnection();
460: statement = connection.prepareStatement(sql.toString());
461: statement.setInt(1, watchID);
462: resultSet = statement.executeQuery();
463: if (!resultSet.next()) {
464: throw new ObjectNotFoundException(
465: "Cannot find the row in table Watch where primary key = ("
466: + watchID + ").");
467: }
468:
469: WatchBean bean = new WatchBean();
470: // @todo: uncomment the following line(s) as needed
471: bean.setWatchID(watchID);
472: bean.setMemberID(resultSet.getInt("MemberID"));
473: bean.setCategoryID(resultSet.getInt("CategoryID"));
474: bean.setForumID(resultSet.getInt("ForumID"));
475: bean.setThreadID(resultSet.getInt("ThreadID"));
476: bean.setWatchType(resultSet.getInt("WatchType"));
477: bean.setWatchOption(resultSet.getInt("WatchOption"));
478: bean.setWatchStatus(resultSet.getInt("WatchStatus"));
479: bean.setWatchCreationDate(resultSet
480: .getTimestamp("WatchCreationDate"));
481: bean.setWatchLastSentDate(resultSet
482: .getTimestamp("WatchLastSentDate"));
483: bean
484: .setWatchEndDate(resultSet
485: .getTimestamp("WatchEndDate"));
486: return bean;
487: } catch (SQLException sqle) {
488: log.error("Sql Execution Error!", sqle);
489: throw new DatabaseException(
490: "Error executing SQL in WatchDAOImplJDBC.getWatch(pk).");
491: } finally {
492: DBUtils.closeResultSet(resultSet);
493: DBUtils.closeStatement(statement);
494: DBUtils.closeConnection(connection);
495: }
496: }
497:
498: /*
499: * Included columns: WatchID, WatchType, WatchOption, WatchStatus, WatchCreationDate,
500: * WatchLastSentDate, WatchEndDate
501: * Excluded columns: MemberID, CategoryID, ForumID, ThreadID
502: */
503: public WatchBean getWatch_byAlternateKey_MemberID_CategoryID_ForumID_ThreadID(
504: int memberID, int categoryID, int forumID, int threadID)
505: throws ObjectNotFoundException, DatabaseException {
506:
507: Connection connection = null;
508: PreparedStatement statement = null;
509: ResultSet resultSet = null;
510: StringBuffer sql = new StringBuffer(512);
511: sql
512: .append("SELECT WatchID, WatchType, WatchOption, WatchStatus, WatchCreationDate, WatchLastSentDate, WatchEndDate");
513: sql.append(" FROM " + TABLE_NAME);
514: sql
515: .append(" WHERE MemberID = ? AND CategoryID = ? AND ForumID = ? AND ThreadID = ?");
516: try {
517: connection = DBUtils.getConnection();
518: statement = connection.prepareStatement(sql.toString());
519: statement.setInt(1, memberID);
520: statement.setInt(2, categoryID);
521: statement.setInt(3, forumID);
522: statement.setInt(4, threadID);
523: resultSet = statement.executeQuery();
524: if (!resultSet.next()) {
525: throw new ObjectNotFoundException(
526: "Cannot find the row in table Watch where alternate key [MemberID, CategoryID, ForumID, ThreadID] = ("
527: + memberID
528: + ", "
529: + categoryID
530: + ", "
531: + forumID + ", " + threadID + ").");
532: }
533:
534: WatchBean bean = new WatchBean();
535: // @todo: uncomment the following line(s) as needed
536: bean.setMemberID(memberID);
537: bean.setCategoryID(categoryID);
538: bean.setForumID(forumID);
539: bean.setThreadID(threadID);
540: bean.setWatchID(resultSet.getInt("WatchID"));
541: bean.setWatchType(resultSet.getInt("WatchType"));
542: bean.setWatchOption(resultSet.getInt("WatchOption"));
543: bean.setWatchStatus(resultSet.getInt("WatchStatus"));
544: bean.setWatchCreationDate(resultSet
545: .getTimestamp("WatchCreationDate"));
546: bean.setWatchLastSentDate(resultSet
547: .getTimestamp("WatchLastSentDate"));
548: bean
549: .setWatchEndDate(resultSet
550: .getTimestamp("WatchEndDate"));
551: return bean;
552: } catch (SQLException sqle) {
553: log.error("Sql Execution Error!", sqle);
554: throw new DatabaseException(
555: "Error executing SQL in WatchDAOImplJDBC.getWatch_byAlternateKey_MemberID_CategoryID_ForumID_ThreadID(ak).");
556: } finally {
557: DBUtils.closeResultSet(resultSet);
558: DBUtils.closeStatement(statement);
559: DBUtils.closeConnection(connection);
560: }
561: }
562:
563: /*
564: * Included columns: WatchID, MemberID, CategoryID, ForumID, ThreadID,
565: * WatchType, WatchOption, WatchStatus, WatchCreationDate, WatchLastSentDate,
566: * WatchEndDate
567: * Excluded columns:
568: */
569: public Collection getWatches() throws DatabaseException {
570:
571: Connection connection = null;
572: PreparedStatement statement = null;
573: ResultSet resultSet = null;
574: Collection retValue = new ArrayList();
575: StringBuffer sql = new StringBuffer(512);
576: sql
577: .append("SELECT WatchID, MemberID, CategoryID, ForumID, ThreadID, WatchType, WatchOption, WatchStatus, WatchCreationDate, WatchLastSentDate, WatchEndDate");
578: sql.append(" FROM " + TABLE_NAME);
579: //sql.append(" WHERE "); // @todo: uncomment as needed
580: //sql.append(" ORDER BY ColumnName ASC|DESC "); // @todo: uncomment as needed
581: try {
582: connection = DBUtils.getConnection();
583: statement = connection.prepareStatement(sql.toString());
584: resultSet = statement.executeQuery();
585: while (resultSet.next()) {
586: WatchBean bean = new WatchBean();
587: bean.setWatchID(resultSet.getInt("WatchID"));
588: bean.setMemberID(resultSet.getInt("MemberID"));
589: bean.setCategoryID(resultSet.getInt("CategoryID"));
590: bean.setForumID(resultSet.getInt("ForumID"));
591: bean.setThreadID(resultSet.getInt("ThreadID"));
592: bean.setWatchType(resultSet.getInt("WatchType"));
593: bean.setWatchOption(resultSet.getInt("WatchOption"));
594: bean.setWatchStatus(resultSet.getInt("WatchStatus"));
595: bean.setWatchCreationDate(resultSet
596: .getTimestamp("WatchCreationDate"));
597: bean.setWatchLastSentDate(resultSet
598: .getTimestamp("WatchLastSentDate"));
599: bean.setWatchEndDate(resultSet
600: .getTimestamp("WatchEndDate"));
601: retValue.add(bean);
602: }
603: return retValue;
604: } catch (SQLException sqle) {
605: log.error("Sql Execution Error!", sqle);
606: throw new DatabaseException(
607: "Error executing SQL in WatchDAOImplJDBC.getWatchs.");
608: } finally {
609: DBUtils.closeResultSet(resultSet);
610: DBUtils.closeStatement(statement);
611: DBUtils.closeConnection(connection);
612: }
613: }
614:
615: public int getNumberOfWatches() throws DatabaseException {
616:
617: Connection connection = null;
618: PreparedStatement statement = null;
619: ResultSet resultSet = null;
620: StringBuffer sql = new StringBuffer(512);
621: sql.append("SELECT Count(*)");
622: sql.append(" FROM " + TABLE_NAME);
623: //sql.append(" WHERE "); // @todo: uncomment as needed
624: try {
625: connection = DBUtils.getConnection();
626: statement = connection.prepareStatement(sql.toString());
627: resultSet = statement.executeQuery();
628: AssertionUtil
629: .doAssert(resultSet.next(),
630: "Assertion in WatchDAOImplJDBC.getNumberOfWatches.");
631: return resultSet.getInt(1);
632: } catch (SQLException sqle) {
633: log.error("Sql Execution Error!", sqle);
634: throw new DatabaseException(
635: "Error executing SQL in WatchDAOImplJDBC.getNumberOfWatches.");
636: } finally {
637: DBUtils.closeResultSet(resultSet);
638: DBUtils.closeStatement(statement);
639: DBUtils.closeConnection(connection);
640: }
641: }
642:
643: public int getNumberOfWatches_forMember(int memberID)
644: throws DatabaseException {
645:
646: Connection connection = null;
647: PreparedStatement statement = null;
648: ResultSet resultSet = null;
649: StringBuffer sql = new StringBuffer(512);
650: sql.append("SELECT Count(*)");
651: sql.append(" FROM " + TABLE_NAME);
652: sql.append(" WHERE MemberID = ?");
653: try {
654: connection = DBUtils.getConnection();
655: statement = connection.prepareStatement(sql.toString());
656: statement.setInt(1, memberID);
657: resultSet = statement.executeQuery();
658: AssertionUtil
659: .doAssert(resultSet.next(),
660: "Assertion in WatchDAOImplJDBC.getNumberOfWatches_forMember.");
661: return resultSet.getInt(1);
662: } catch (SQLException sqle) {
663: log.error("Sql Execution Error!", sqle);
664: throw new DatabaseException(
665: "Error executing SQL in WatchDAOImplJDBC.getNumberOfWatches_forMember.");
666: } finally {
667: DBUtils.closeResultSet(resultSet);
668: DBUtils.closeStatement(statement);
669: DBUtils.closeConnection(connection);
670: }
671: }
672:
673: /***************************************************************************
674: * Customized methods come below
675: ***************************************************************************/
676:
677: /*
678: * Included columns: MemberID, WatchLastSentDate
679: * Excluded columns: WatchID, CategoryID, ForumID, ThreadID, WatchType,
680: * WatchOption, WatchStatus, WatchCreationDate, WatchEndDate
681: */
682: public Collection getMemberBeans() throws DatabaseException {
683:
684: Connection connection = null;
685: PreparedStatement statement = null;
686: ResultSet resultSet = null;
687: Collection retValue = new ArrayList();
688: StringBuffer sql = new StringBuffer(512);
689: sql
690: .append("SELECT DISTINCT MemberID, MIN(WatchLastSentDate) AS lastsent");// postgreSQL need AS
691: sql.append(" FROM " + TABLE_NAME);
692: //sql.append(" WHERE "); // @todo: uncomment as needed
693: //sql.append(" ORDER BY ColumnName ASC|DESC "); // @todo: uncomment as needed
694: sql.append(" GROUP BY MemberID ");
695: try {
696: connection = DBUtils.getConnection();
697: statement = connection.prepareStatement(sql.toString());
698: resultSet = statement.executeQuery();
699: while (resultSet.next()) {
700: WatchBean bean = new WatchBean();
701: bean.setMemberID(resultSet.getInt("MemberID"));
702: bean.setWatchLastSentDate(resultSet
703: .getTimestamp("lastsent"));
704: retValue.add(bean);
705: }
706: return retValue;
707: } catch (SQLException sqle) {
708: log.error("Sql Execution Error!", sqle);
709: throw new DatabaseException(
710: "Error executing SQL in WatchDAOImplJDBC.getMemberBeans.");
711: } finally {
712: DBUtils.closeResultSet(resultSet);
713: DBUtils.closeStatement(statement);
714: DBUtils.closeConnection(connection);
715: }
716: }
717:
718: /*
719: * Included columns: WatchID, MemberID, CategoryID, ForumID, ThreadID,
720: * WatchType, WatchOption, WatchStatus, WatchCreationDate, WatchLastSentDate,
721: * WatchEndDate
722: * Excluded columns:
723: */
724: public Collection getWatches_forMember(int memberID)
725: throws DatabaseException {
726:
727: Connection connection = null;
728: PreparedStatement statement = null;
729: ResultSet resultSet = null;
730: Collection retValue = new ArrayList();
731: StringBuffer sql = new StringBuffer(512);
732: sql
733: .append("SELECT WatchID, MemberID, CategoryID, ForumID, ThreadID, WatchType, WatchOption, WatchStatus, WatchCreationDate, WatchLastSentDate, WatchEndDate");
734: sql.append(" FROM " + TABLE_NAME);
735: sql.append(" WHERE MemberID = ? ");
736: //sql.append(" ORDER BY ColumnName ASC|DESC "); // @todo: uncomment as needed
737: try {
738: connection = DBUtils.getConnection();
739: statement = connection.prepareStatement(sql.toString());
740: statement.setInt(1, memberID);
741: resultSet = statement.executeQuery();
742: while (resultSet.next()) {
743: WatchBean bean = new WatchBean();
744: bean.setWatchID(resultSet.getInt("WatchID"));
745: bean.setMemberID(resultSet.getInt("MemberID"));
746: bean.setCategoryID(resultSet.getInt("CategoryID"));
747: bean.setForumID(resultSet.getInt("ForumID"));
748: bean.setThreadID(resultSet.getInt("ThreadID"));
749: bean.setWatchType(resultSet.getInt("WatchType"));
750: bean.setWatchOption(resultSet.getInt("WatchOption"));
751: bean.setWatchStatus(resultSet.getInt("WatchStatus"));
752: bean.setWatchCreationDate(resultSet
753: .getTimestamp("WatchCreationDate"));
754: bean.setWatchLastSentDate(resultSet
755: .getTimestamp("WatchLastSentDate"));
756: bean.setWatchEndDate(resultSet
757: .getTimestamp("WatchEndDate"));
758: retValue.add(bean);
759: }
760: return retValue;
761: } catch (SQLException sqle) {
762: log.error("Sql Execution Error!", sqle);
763: throw new DatabaseException(
764: "Error executing SQL in WatchDAOImplJDBC.getWatches_forMember.");
765: } finally {
766: DBUtils.closeResultSet(resultSet);
767: DBUtils.closeStatement(statement);
768: DBUtils.closeConnection(connection);
769: }
770: }
771:
772: /*
773: * Included columns: WatchLastSentDate
774: * Excluded columns: WatchID, MemberID, CategoryID, ForumID, ThreadID,
775: * WatchType, WatchOption, WatchStatus, WatchCreationDate, WatchEndDate
776: */
777: public void updateLastSentDate_forMember(int memberID,
778: Timestamp watchLastSentDate)
779: throws ObjectNotFoundException, DatabaseException {
780:
781: Connection connection = null;
782: PreparedStatement statement = null;
783: StringBuffer sql = new StringBuffer(512);
784: sql.append("UPDATE " + TABLE_NAME
785: + " SET WatchLastSentDate = ?");
786: sql.append(" WHERE MemberID = ?");
787: try {
788: connection = DBUtils.getConnection();
789: statement = connection.prepareStatement(sql.toString());
790:
791: // // column(s) to update
792: statement.setTimestamp(1, watchLastSentDate);
793:
794: // primary key column(s)
795: statement.setInt(2, memberID);
796:
797: if (statement.executeUpdate() < 1) {
798: throw new ObjectNotFoundException(
799: "Cannot update table Watch where primary key = ("
800: + memberID + ").");
801: }
802: m_dirty = true;
803: } catch (SQLException sqle) {
804: log.error("Sql Execution Error!", sqle);
805: throw new DatabaseException(
806: "Error executing SQL in WatchDAOImplJDBC.updateLastSentDate_forMember.");
807: } finally {
808: DBUtils.closeStatement(statement);
809: DBUtils.closeConnection(connection);
810: }
811: }
812:
813: public void updateWatchType(int watchID, int watchType)
814: throws ObjectNotFoundException, DatabaseException {
815:
816: Connection connection = null;
817: PreparedStatement statement = null;
818: StringBuffer sql = new StringBuffer(512);
819: sql.append("UPDATE " + TABLE_NAME + " SET WatchType = ?");
820: sql.append(" WHERE WatchID = ?");
821: try {
822: connection = DBUtils.getConnection();
823: statement = connection.prepareStatement(sql.toString());
824:
825: // // column(s) to update
826: statement.setInt(1, watchType);
827: statement.setInt(2, watchID);
828:
829: if (statement.executeUpdate() < 1) {
830: throw new ObjectNotFoundException(
831: "Cannot update table Watch where primary key = ("
832: + watchID + ").");
833: }
834: m_dirty = true;
835: } catch (SQLException sqle) {
836: log.error("Sql Execution Error!", sqle);
837: throw new DatabaseException(
838: "Error executing SQL in WatchDAOImplJDBC.updateWatchType.");
839: } finally {
840: DBUtils.closeStatement(statement);
841: DBUtils.closeConnection(connection);
842: }
843: }
844:
845: }// end of class WatchDAOImplJDBC
|