001: /*
002: * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/FavoriteThreadDAOImplJDBC.java,v 1.13 2007/10/09 11:09:20 lexuanttkhtn Exp $
003: * $Author: lexuanttkhtn $
004: * $Revision: 1.13 $
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.sql.*;
044: import java.util.ArrayList;
045: import java.util.Collection;
046:
047: import com.mvnforum.db.*;
048: import net.myvietnam.mvncore.db.DBUtils;
049: import net.myvietnam.mvncore.exception.*;
050: import net.myvietnam.mvncore.util.AssertionUtil;
051:
052: import org.apache.commons.logging.Log;
053: import org.apache.commons.logging.LogFactory;
054:
055: public class FavoriteThreadDAOImplJDBC implements FavoriteThreadDAO {
056:
057: private static Log log = LogFactory
058: .getLog(FavoriteThreadDAOImplJDBC.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 FavoriteThreadDAOImplJDBC() {
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: public void findByPrimaryKey(int memberID, int threadID)
075: throws ObjectNotFoundException, DatabaseException {
076:
077: Connection connection = null;
078: PreparedStatement statement = null;
079: ResultSet resultSet = null;
080: StringBuffer sql = new StringBuffer(512);
081: sql.append("SELECT MemberID, ThreadID");
082: sql.append(" FROM " + TABLE_NAME);
083: sql.append(" WHERE MemberID = ? AND ThreadID = ?");
084: try {
085: connection = DBUtils.getConnection();
086: statement = connection.prepareStatement(sql.toString());
087: statement.setInt(1, memberID);
088: statement.setInt(2, threadID);
089: resultSet = statement.executeQuery();
090: if (!resultSet.next()) {
091: throw new ObjectNotFoundException(
092: "Cannot find the primary key (" + memberID
093: + ", " + threadID
094: + ") in table 'FavoriteThread'.");
095: }
096: } catch (SQLException sqle) {
097: log.error("Sql Execution Error!", sqle);
098: throw new DatabaseException(
099: "Error executing SQL in FavoriteThreadDAOImplJDBC.findByPrimaryKey.");
100: } finally {
101: DBUtils.closeResultSet(resultSet);
102: DBUtils.closeStatement(statement);
103: DBUtils.closeConnection(connection);
104: }
105: }
106:
107: /*
108: * Included columns: MemberID, ThreadID, ForumID, FavoriteCreationDate, FavoriteType,
109: * FavoriteOption, FavoriteStatus
110: * Excluded columns:
111: */
112: public void create(int memberID, int threadID, int forumID,
113: Timestamp favoriteCreationDate, int favoriteType,
114: int favoriteOption, int favoriteStatus)
115: throws CreateException, DatabaseException,
116: DuplicateKeyException, ForeignKeyNotFoundException {
117:
118: // @todo: comment this try-catch block if the needed columns dont have attribute 'include'
119: // If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
120: // However, if primary key is a auto_increament column, then you can safely delete this block
121: try {
122: //Check if primary key already exists
123: findByPrimaryKey(memberID, threadID);
124: //If so, then we have to throw an exception
125: throw new DuplicateKeyException(
126: "Primary key already exists. Cannot create new FavoriteThread with the same [MemberID, ThreadID] ("
127: + memberID + ", " + threadID + ").");
128: } catch (ObjectNotFoundException e) {
129: //Otherwise we can go ahead
130: }
131:
132: try {
133: // @todo: modify the parameter list as needed
134: // You may have to regenerate this method if the needed columns dont have attribute 'include'
135: DAOFactory.getMemberDAO().findByPrimaryKey(memberID);
136: } catch (ObjectNotFoundException e) {
137: throw new ForeignKeyNotFoundException(
138: "Foreign key refers to table 'mvnforumMember' does not exist. Cannot create new FavoriteThread.");
139: }
140:
141: try {
142: // @todo: modify the parameter list as needed
143: // You may have to regenerate this method if the needed columns dont have attribute 'include'
144: DAOFactory.getThreadDAO().findByPrimaryKey(threadID);
145: } catch (ObjectNotFoundException e) {
146: throw new ForeignKeyNotFoundException(
147: "Foreign key refers to table 'mvnforumThread' does not exist. Cannot create new FavoriteThread.");
148: }
149:
150: try {
151: // @todo: modify the parameter list as needed
152: // You may have to regenerate this method if the needed columns dont have attribute 'include'
153: DAOFactory.getForumDAO().findByPrimaryKey(forumID);
154: } catch (ObjectNotFoundException e) {
155: throw new ForeignKeyNotFoundException(
156: "Foreign key refers to table 'mvnforumForum' does not exist. Cannot create new FavoriteThread.");
157: }
158:
159: Connection connection = null;
160: PreparedStatement statement = null;
161: StringBuffer sql = new StringBuffer(512);
162: sql
163: .append("INSERT INTO "
164: + TABLE_NAME
165: + " (MemberID, ThreadID, ForumID, FavoriteCreationDate, FavoriteType, FavoriteOption, FavoriteStatus)");
166: sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?)");
167: try {
168: connection = DBUtils.getConnection();
169: statement = connection.prepareStatement(sql.toString());
170:
171: statement.setInt(1, memberID);
172: statement.setInt(2, threadID);
173: statement.setInt(3, forumID);
174: statement.setTimestamp(4, favoriteCreationDate);
175: statement.setInt(5, favoriteType);
176: statement.setInt(6, favoriteOption);
177: statement.setInt(7, favoriteStatus);
178:
179: if (statement.executeUpdate() != 1) {
180: throw new CreateException(
181: "Error adding a row into table 'FavoriteThread'.");
182: }
183: m_dirty = true;
184: } catch (SQLException sqle) {
185: log.error("Sql Execution Error!", sqle);
186: throw new DatabaseException(
187: "Error executing SQL in FavoriteThreadDAOImplJDBC.create.");
188: } finally {
189: DBUtils.closeStatement(statement);
190: DBUtils.closeConnection(connection);
191: }
192: }
193:
194: public void delete(int memberID, int threadID)
195: throws DatabaseException, ObjectNotFoundException {
196:
197: Connection connection = null;
198: PreparedStatement statement = null;
199: StringBuffer sql = new StringBuffer(512);
200: sql.append("DELETE FROM " + TABLE_NAME);
201: sql.append(" WHERE MemberID = ? AND ThreadID = ?");
202:
203: try {
204: connection = DBUtils.getConnection();
205: statement = connection.prepareStatement(sql.toString());
206: statement.setInt(1, memberID);
207: statement.setInt(2, threadID);
208: if (statement.executeUpdate() != 1) {
209: throw new ObjectNotFoundException(
210: "Cannot delete a row in table FavoriteThread where primary key = ("
211: + memberID + ", " + threadID + ").");
212: }
213: m_dirty = true;
214: } catch (SQLException sqle) {
215: log.error("Sql Execution Error!", sqle);
216: throw new DatabaseException(
217: "Error executing SQL in FavoriteThreadDAOImplJDBC.delete.");
218: } finally {
219: DBUtils.closeStatement(statement);
220: DBUtils.closeConnection(connection);
221: }
222: }
223:
224: public void delete_inThread(int threadID) throws DatabaseException {
225:
226: Connection connection = null;
227: PreparedStatement statement = null;
228: StringBuffer sql = new StringBuffer(512);
229: sql.append("DELETE FROM " + TABLE_NAME);
230: sql.append(" WHERE ThreadID = ?");
231:
232: try {
233: connection = DBUtils.getConnection();
234: statement = connection.prepareStatement(sql.toString());
235: statement.setInt(1, threadID);
236:
237: statement.executeUpdate();
238: m_dirty = true;
239: } catch (SQLException sqle) {
240: log.error("Sql Execution Error!", sqle);
241: throw new DatabaseException(
242: "Error executing SQL in FavoriteThreadDAOImplJDBC.delete_inThread.");
243: } finally {
244: DBUtils.closeStatement(statement);
245: DBUtils.closeConnection(connection);
246: }
247: }
248:
249: public void delete_inForum(int forumID) throws DatabaseException {
250:
251: Connection connection = null;
252: PreparedStatement statement = null;
253: StringBuffer sql = new StringBuffer(512);
254: sql.append("DELETE FROM " + TABLE_NAME);
255: sql.append(" WHERE ForumID = ?");
256:
257: try {
258: connection = DBUtils.getConnection();
259: statement = connection.prepareStatement(sql.toString());
260: statement.setInt(1, forumID);
261:
262: statement.executeUpdate();
263: m_dirty = true;
264: } catch (SQLException sqle) {
265: log.error("Sql Execution Error!", sqle);
266: throw new DatabaseException(
267: "Error executing SQL in FavoriteThreadDAOImplJDBC.delete_inForum.");
268: } finally {
269: DBUtils.closeStatement(statement);
270: DBUtils.closeConnection(connection);
271: }
272: }
273:
274: public void delete_inMember(int memberID) throws DatabaseException {
275:
276: Connection connection = null;
277: PreparedStatement statement = null;
278: StringBuffer sql = new StringBuffer(512);
279: sql.append("DELETE FROM " + TABLE_NAME);
280: sql.append(" WHERE MemberID = ?");
281:
282: try {
283: connection = DBUtils.getConnection();
284: statement = connection.prepareStatement(sql.toString());
285: statement.setInt(1, memberID);
286: statement.executeUpdate();
287: m_dirty = true;
288: } catch (SQLException sqle) {
289: log.error("Sql Execution Error!", sqle);
290: throw new DatabaseException(
291: "Error executing SQL in FavoriteThreadDAOImplJDBC.delete_inMember.");
292: } finally {
293: DBUtils.closeStatement(statement);
294: DBUtils.closeConnection(connection);
295: }
296: }
297:
298: /*
299: * Included columns: ForumID
300: */
301: public void update_ForumID_inThread(int threadID, int forumID)
302: throws DatabaseException, ForeignKeyNotFoundException {
303:
304: try {
305: // @todo: modify the parameter list as needed
306: // If this method does not change the foreign key columns, you can comment this block of code.
307: DAOFactory.getForumDAO().findByPrimaryKey(forumID);
308: } catch (ObjectNotFoundException e) {
309: throw new ForeignKeyNotFoundException(
310: "Foreign key refers to table 'Forum' does not exist. Cannot update table 'FavoriteThread'.");
311: }
312:
313: Connection connection = null;
314: PreparedStatement statement = null;
315: StringBuffer sql = new StringBuffer(512);
316: sql.append("UPDATE " + TABLE_NAME + " SET ForumID = ?");
317: sql.append(" WHERE ThreadID = ?");
318: try {
319: connection = DBUtils.getConnection();
320: statement = connection.prepareStatement(sql.toString());
321:
322: // // column(s) to update
323: statement.setInt(1, forumID);
324:
325: // primary key column(s)
326: statement.setInt(2, threadID);
327:
328: statement.executeUpdate();
329: m_dirty = true;
330: } catch (SQLException sqle) {
331: log.error("Sql Execution Error!", sqle);
332: throw new DatabaseException(
333: "Error executing SQL in FavoriteThreadDAOImplJDBC.update_ForumID_inThread.");
334: } finally {
335: DBUtils.closeStatement(statement);
336: DBUtils.closeConnection(connection);
337: }
338: }
339:
340: /*
341: * Included columns: MemberID, ThreadID, FavoriteCreationDate, FavoriteType, FavoriteOption,
342: * FavoriteStatus
343: * Excluded columns:
344: */
345: public Collection getFavoriteThreads_inMember(int memberID)
346: throws DatabaseException {
347:
348: Connection connection = null;
349: PreparedStatement statement = null;
350: ResultSet resultSet = null;
351: Collection retValue = new ArrayList();
352: StringBuffer sql = new StringBuffer(512);
353: sql
354: .append("SELECT MemberID, ThreadID, FavoriteCreationDate, FavoriteType, FavoriteOption, FavoriteStatus");
355: sql.append(" FROM " + TABLE_NAME);
356: sql.append(" WHERE MemberID = ?");
357: sql.append(" ORDER BY ThreadID ASC ");
358: try {
359: connection = DBUtils.getConnection();
360: statement = connection.prepareStatement(sql.toString());
361: statement.setInt(1, memberID);
362: resultSet = statement.executeQuery();
363: while (resultSet.next()) {
364: FavoriteThreadBean bean = new FavoriteThreadBean();
365: bean.setMemberID(resultSet.getInt("MemberID"));
366: bean.setThreadID(resultSet.getInt("ThreadID"));
367: bean.setFavoriteCreationDate(resultSet
368: .getTimestamp("FavoriteCreationDate"));
369: bean.setFavoriteType(resultSet.getInt("FavoriteType"));
370: bean.setFavoriteOption(resultSet
371: .getInt("FavoriteOption"));
372: bean.setFavoriteStatus(resultSet
373: .getInt("FavoriteStatus"));
374: retValue.add(bean);
375: }
376: return retValue;
377: } catch (SQLException sqle) {
378: log.error("Sql Execution Error!", sqle);
379: throw new DatabaseException(
380: "Error executing SQL in FavoriteThreadDAOImplJDBC.getFavoriteThreads_inMember.");
381: } finally {
382: DBUtils.closeResultSet(resultSet);
383: DBUtils.closeStatement(statement);
384: DBUtils.closeConnection(connection);
385: }
386: }
387:
388: /*
389: public int getNumberOfBeans()
390: throws DatabaseException {
391:
392: Connection connection = null;
393: PreparedStatement statement = null;
394: ResultSet resultSet = null;
395: StringBuffer sql = new StringBuffer(512);
396: sql.append("SELECT Count(*)");
397: sql.append(" FROM " + TABLE_NAME);
398: //sql.append(" WHERE "); // @todo: uncomment as needed
399: try {
400: connection = DBUtils.getConnection();
401: statement = connection.prepareStatement(sql.toString());
402: resultSet = statement.executeQuery();
403: if (!resultSet.next()) {
404: throw new AssertionError("Assertion in FavoriteThreadDAOImplJDBC.getNumberOfBeans.");
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.getNumberOfBeans.");
410: } finally {
411: DBUtils.closeResultSet(resultSet);
412: DBUtils.closeStatement(statement);
413: DBUtils.closeConnection(connection);
414: }
415: }*/
416:
417: public int getNumberOfFavoriteThreads_inMember(int memberID)
418: throws DatabaseException {
419:
420: Connection connection = null;
421: PreparedStatement statement = null;
422: ResultSet resultSet = null;
423: StringBuffer sql = new StringBuffer(512);
424: sql.append("SELECT Count(*)");
425: sql.append(" FROM " + TABLE_NAME);
426: sql.append(" WHERE MemberID = ? ");
427: try {
428: connection = DBUtils.getConnection();
429: statement = connection.prepareStatement(sql.toString());
430: statement.setInt(1, memberID);
431: resultSet = statement.executeQuery();
432: AssertionUtil
433: .doAssert(resultSet.next(),
434: "Assertion in FavoriteThreadDAOImplJDBC.getNumberOfFavoriteThreads_inMember.");
435: return resultSet.getInt(1);
436: } catch (SQLException sqle) {
437: log.error("Sql Execution Error!", sqle);
438: throw new DatabaseException(
439: "Error executing SQL in FavoriteThreadDAOImplJDBC.getNumberOfFavoriteThreads_inMember.");
440: } finally {
441: DBUtils.closeResultSet(resultSet);
442: DBUtils.closeStatement(statement);
443: DBUtils.closeConnection(connection);
444: }
445: }
446:
447: }// end of class FavoriteThreadDAOImplJDBC
|