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