001: /*
002: * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/CategoryDAOImplJDBC.java,v 1.17 2008/01/24 08:01:28 lexuanttkhtn Exp $
003: * $Author: lexuanttkhtn $
004: * $Revision: 1.17 $
005: * $Date: 2008/01/24 08:01:28 $
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 net.myvietnam.mvncore.db.DBUtils;
049: import net.myvietnam.mvncore.exception.*;
050:
051: import org.apache.commons.logging.Log;
052: import org.apache.commons.logging.LogFactory;
053:
054: import com.mvnforum.db.CategoryBean;
055: import com.mvnforum.db.CategoryDAO;
056:
057: public class CategoryDAOImplJDBC implements CategoryDAO {
058:
059: private static Log log = LogFactory
060: .getLog(CategoryDAOImplJDBC.class);
061:
062: // this variable will support caching if cache for this class is needed
063: private static boolean m_dirty = true;
064:
065: public CategoryDAOImplJDBC() {
066: }
067:
068: public static boolean isDirty() {
069: return m_dirty;
070: }
071:
072: public static void setDirty(boolean dirty) {
073: m_dirty = dirty;
074: }
075:
076: public int findByPrimaryKey(int categoryID)
077: throws ObjectNotFoundException, DatabaseException {
078:
079: Connection connection = null;
080: PreparedStatement statement = null;
081: ResultSet resultSet = null;
082: StringBuffer sql = new StringBuffer(512);
083: sql.append("SELECT CategoryID");
084: sql.append(" FROM " + TABLE_NAME);
085: sql.append(" WHERE CategoryID = ?");
086: try {
087: connection = DBUtils.getConnection();
088: statement = connection.prepareStatement(sql.toString());
089: statement.setInt(1, categoryID);
090: resultSet = statement.executeQuery();
091: if (!resultSet.next()) {
092: throw new ObjectNotFoundException(
093: "Cannot find the primary key (" + categoryID
094: + ") in table 'Category'.");
095: }
096:
097: return resultSet.getInt(1);
098: } catch (SQLException sqle) {
099: log.error("Sql Execution Error!", sqle);
100: throw new DatabaseException(
101: "Error executing SQL in CategoryDAOImplJDBC.findByPrimaryKey.");
102: } finally {
103: DBUtils.closeResultSet(resultSet);
104: DBUtils.closeStatement(statement);
105: DBUtils.closeConnection(connection);
106: }
107: }
108:
109: public int findByAlternateKey_CategoryName(String categoryName)
110: throws ObjectNotFoundException, DatabaseException {
111:
112: Connection connection = null;
113: PreparedStatement statement = null;
114: ResultSet resultSet = null;
115: StringBuffer sql = new StringBuffer(512);
116: sql.append("SELECT CategoryID");
117: sql.append(" FROM " + TABLE_NAME);
118: if (DBUtils.isCaseSensitiveDatebase()) {
119: sql.append(" WHERE lower(CategoryName) = lower(?)");
120: } else {
121: sql.append(" WHERE CategoryName = ?");
122: }
123: try {
124: connection = DBUtils.getConnection();
125: statement = connection.prepareStatement(sql.toString());
126: statement.setString(1, categoryName);
127: resultSet = statement.executeQuery();
128: if (!resultSet.next()) {
129: throw new ObjectNotFoundException(
130: "Cannot find the alternate key [CategoryName] ("
131: + categoryName
132: + ") in table 'Category'.");
133: }
134:
135: return resultSet.getInt(1);
136: } catch (SQLException sqle) {
137: log.error("Sql Execution Error!", sqle);
138: throw new DatabaseException(
139: "Error executing SQL in CategoryDAOImplJDBC.findByAlternateKey_CategoryName.");
140: } finally {
141: DBUtils.closeResultSet(resultSet);
142: DBUtils.closeStatement(statement);
143: DBUtils.closeConnection(connection);
144: }
145: }
146:
147: /*
148: * Included columns: ParentCategoryID, CategoryName, CategoryDesc, CategoryCreationDate, CategoryModifiedDate,
149: * CategoryOrder, CategoryOption, CategoryStatus
150: * Excluded columns: CategoryID
151: */
152: public void create(int parentCategoryID, String categoryName,
153: String categoryDesc, Timestamp categoryCreationDate,
154: Timestamp categoryModifiedDate, int categoryOrder,
155: int categoryOption, int categoryStatus)
156: throws CreateException, DatabaseException,
157: DuplicateKeyException, ForeignKeyNotFoundException {
158:
159: // @todo: Comment this try-catch block if the needed columns dont have attribute 'include'
160: // If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
161: try {
162: //Check if alternate key already exists
163: findByAlternateKey_CategoryName(categoryName);
164: //If so, then we have to throw an exception
165: throw new DuplicateKeyException(
166: "Alternate key already exists. Cannot create new Category with the same [CategoryName] ("
167: + categoryName + ").");
168: } catch (ObjectNotFoundException e) {
169: //Otherwise we can go ahead
170: }
171:
172: try {
173: // @todo: modify the parameter list as needed
174: // You may have to regenerate this method if the needed columns dont have attribute 'include'
175: if (parentCategoryID != 0) {
176: findByPrimaryKey(parentCategoryID);
177: }
178: } catch (ObjectNotFoundException e) {
179: throw new ForeignKeyNotFoundException(
180: "Foreign key refers to table 'Category' does not exist. Cannot create new Category.");
181: }
182:
183: Connection connection = null;
184: PreparedStatement statement = null;
185: StringBuffer sql = new StringBuffer(512);
186: sql
187: .append("INSERT INTO "
188: + TABLE_NAME
189: + " (ParentCategoryID, CategoryName, CategoryDesc, CategoryCreationDate, CategoryModifiedDate, CategoryOrder, CategoryOption, CategoryStatus)");
190: sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
191: try {
192: connection = DBUtils.getConnection();
193: statement = connection.prepareStatement(sql.toString());
194:
195: statement.setInt(1, parentCategoryID);
196: statement.setString(2, categoryName);
197: if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
198: statement.setCharacterStream(3, new StringReader(
199: categoryDesc), categoryDesc.length());
200: } else {
201: statement.setString(3, categoryDesc);
202: }
203: statement.setTimestamp(4, categoryCreationDate);
204: statement.setTimestamp(5, categoryModifiedDate);
205: statement.setInt(6, categoryOrder);
206: statement.setInt(7, categoryOption);
207: statement.setInt(8, categoryStatus);
208:
209: if (statement.executeUpdate() != 1) {
210: throw new CreateException(
211: "Error adding a row into table 'Category'.");
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 CategoryDAOImplJDBC.create.");
218: } finally {
219: DBUtils.closeStatement(statement);
220: DBUtils.closeConnection(connection);
221: }
222: }
223:
224: public void delete(int categoryID) throws DatabaseException,
225: ObjectNotFoundException {
226:
227: Connection connection = null;
228: PreparedStatement statement = null;
229: StringBuffer sql = new StringBuffer(512);
230: sql.append("DELETE FROM " + TABLE_NAME);
231: sql.append(" WHERE CategoryID = ?");
232:
233: try {
234: connection = DBUtils.getConnection();
235: statement = connection.prepareStatement(sql.toString());
236: statement.setInt(1, categoryID);
237: if (statement.executeUpdate() != 1) {
238: throw new ObjectNotFoundException(
239: "Cannot delete a row in table Category where primary key = ("
240: + categoryID + ").");
241: }
242: m_dirty = true;
243: } catch (SQLException sqle) {
244: log.error("Sql Execution Error!", sqle);
245: throw new DatabaseException(
246: "Error executing SQL in CategoryDAOImplJDBC.delete.");
247: } finally {
248: DBUtils.closeStatement(statement);
249: DBUtils.closeConnection(connection);
250: }
251: }
252:
253: /*
254: * Included columns: CategoryName, CategoryDesc, CategoryModifiedDate, CategoryOrder, CategoryOption,
255: * CategoryStatus
256: * Excluded columns: CategoryID, ParentCategoryID, CategoryCreationDate
257: */
258: public void update(
259: int categoryID, // primary key
260: String categoryName, String categoryDesc,
261: Timestamp categoryModifiedDate, int categoryOrder,
262: int categoryOption, int categoryStatus)
263: throws ObjectNotFoundException, DatabaseException,
264: DuplicateKeyException {
265:
266: CategoryBean bean = getCategory(categoryID); // @todo: comment or delete this line if no alternate key are included
267:
268: if (categoryName.equalsIgnoreCase(bean.getCategoryName()) == false) {
269: // Category tries to change its alternate key <CategoryName>, so we must check if it already exist
270: try {
271: findByAlternateKey_CategoryName(categoryName);
272: throw new DuplicateKeyException(
273: "Alternate key [CategoryName] ("
274: + categoryName
275: + ") already exists. Cannot update Category.");
276: } catch (ObjectNotFoundException e) {
277: //Otherwise we can go ahead
278: }
279: }
280:
281: Connection connection = null;
282: PreparedStatement statement = null;
283: StringBuffer sql = new StringBuffer(512);
284: sql
285: .append("UPDATE "
286: + TABLE_NAME
287: + " SET CategoryName = ?, CategoryDesc = ?, CategoryModifiedDate = ?, CategoryOrder = ?, CategoryOption = ?, CategoryStatus = ?");
288: sql.append(" WHERE CategoryID = ?");
289: try {
290: connection = DBUtils.getConnection();
291: statement = connection.prepareStatement(sql.toString());
292:
293: // // column(s) to update
294: statement.setString(1, categoryName);
295: if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
296: statement.setCharacterStream(2, new StringReader(
297: categoryDesc), categoryDesc.length());
298: } else {
299: statement.setString(2, categoryDesc);
300: }
301: statement.setTimestamp(3, categoryModifiedDate);
302: statement.setInt(4, categoryOrder);
303: statement.setInt(5, categoryOption);
304: statement.setInt(6, categoryStatus);
305:
306: // primary key column(s)
307: statement.setInt(7, categoryID);
308:
309: if (statement.executeUpdate() != 1) {
310: throw new ObjectNotFoundException(
311: "Cannot update table Category where primary key = ("
312: + categoryID + ").");
313: }
314: m_dirty = true;
315: } catch (SQLException sqle) {
316: log.error("Sql Execution Error!", sqle);
317: throw new DatabaseException(
318: "Error executing SQL in CategoryDAOImplJDBC.update.");
319: } finally {
320: DBUtils.closeStatement(statement);
321: DBUtils.closeConnection(connection);
322: }
323: }
324:
325: /*
326: * Included columns: ParentCategoryID, CategoryName, CategoryDesc, CategoryCreationDate, CategoryModifiedDate,
327: * CategoryOrder, CategoryOption, CategoryStatus
328: * Excluded columns: CategoryID
329: */
330: public CategoryBean getCategory(int categoryID)
331: throws ObjectNotFoundException, DatabaseException {
332:
333: Connection connection = null;
334: PreparedStatement statement = null;
335: ResultSet resultSet = null;
336: StringBuffer sql = new StringBuffer(512);
337: sql
338: .append("SELECT ParentCategoryID, CategoryName, CategoryDesc, CategoryCreationDate, CategoryModifiedDate, CategoryOrder, CategoryOption, CategoryStatus");
339: sql.append(" FROM " + TABLE_NAME);
340: sql.append(" WHERE CategoryID = ?");
341: try {
342: connection = DBUtils.getConnection();
343: statement = connection.prepareStatement(sql.toString());
344: statement.setInt(1, categoryID);
345: resultSet = statement.executeQuery();
346: if (!resultSet.next()) {
347: throw new ObjectNotFoundException(
348: "Cannot find the row in table Category where primary key = ("
349: + categoryID + ").");
350: }
351:
352: CategoryBean bean = new CategoryBean();
353: // @todo: uncomment the following line(s) as needed
354: bean.setCategoryID(categoryID);
355: bean.setParentCategoryID(resultSet
356: .getInt("ParentCategoryID"));
357: bean.setCategoryName(resultSet.getString("CategoryName"));
358: bean.setCategoryDesc(resultSet.getString("CategoryDesc"));
359: bean.setCategoryCreationDate(resultSet
360: .getTimestamp("CategoryCreationDate"));
361: bean.setCategoryModifiedDate(resultSet
362: .getTimestamp("CategoryModifiedDate"));
363: bean.setCategoryOrder(resultSet.getInt("CategoryOrder"));
364: bean.setCategoryOption(resultSet.getInt("CategoryOption"));
365: bean.setCategoryStatus(resultSet.getInt("CategoryStatus"));
366: return bean;
367: } catch (SQLException sqle) {
368: log.error("Sql Execution Error!", sqle);
369: throw new DatabaseException(
370: "Error executing SQL in CategoryDAOImplJDBC.getCategory(pk).");
371: } finally {
372: DBUtils.closeResultSet(resultSet);
373: DBUtils.closeStatement(statement);
374: DBUtils.closeConnection(connection);
375: }
376: }
377:
378: /*
379: * Included columns: CategoryID, ParentCategoryID, CategoryName, CategoryDesc, CategoryCreationDate,
380: * CategoryModifiedDate, CategoryOrder, CategoryOption, CategoryStatus
381: * Excluded columns:
382: */
383: public Collection getCategories() throws DatabaseException {
384:
385: Connection connection = null;
386: PreparedStatement statement = null;
387: ResultSet resultSet = null;
388: Collection retValue = new ArrayList();
389: StringBuffer sql = new StringBuffer(512);
390: sql
391: .append("SELECT CategoryID, ParentCategoryID, CategoryName, CategoryDesc, CategoryCreationDate, CategoryModifiedDate, CategoryOrder, CategoryOption, CategoryStatus");
392: sql.append(" FROM " + TABLE_NAME);
393: //sql.append(" WHERE "); // @todo: uncomment as needed
394: sql.append(" ORDER BY CategoryOrder ASC "); // @todo: uncomment as needed
395: try {
396: connection = DBUtils.getConnection();
397: statement = connection.prepareStatement(sql.toString());
398: resultSet = statement.executeQuery();
399: while (resultSet.next()) {
400: CategoryBean bean = new CategoryBean();
401: bean.setCategoryID(resultSet.getInt("CategoryID"));
402: bean.setParentCategoryID(resultSet
403: .getInt("ParentCategoryID"));
404: bean.setCategoryName(resultSet
405: .getString("CategoryName"));
406: bean.setCategoryDesc(resultSet
407: .getString("CategoryDesc"));
408: bean.setCategoryCreationDate(resultSet
409: .getTimestamp("CategoryCreationDate"));
410: bean.setCategoryModifiedDate(resultSet
411: .getTimestamp("CategoryModifiedDate"));
412: bean
413: .setCategoryOrder(resultSet
414: .getInt("CategoryOrder"));
415: bean.setCategoryOption(resultSet
416: .getInt("CategoryOption"));
417: bean.setCategoryStatus(resultSet
418: .getInt("CategoryStatus"));
419: retValue.add(bean);
420: }
421: return retValue;
422: } catch (SQLException sqle) {
423: log.error("Sql Execution Error!", sqle);
424: throw new DatabaseException(
425: "Error executing SQL in CategoryDAOImplJDBC.getCategories.");
426: } finally {
427: DBUtils.closeResultSet(resultSet);
428: DBUtils.closeStatement(statement);
429: DBUtils.closeConnection(connection);
430: }
431: }
432:
433: /************************************************
434: * Customized methods come below
435: ************************************************/
436:
437: /**
438: * This method should be call only when we can make sure that memberID is in database
439: */
440: public void decreaseCategoryOrder(int categoryID,
441: Timestamp categoryModifiedDate) throws DatabaseException,
442: ObjectNotFoundException {
443:
444: Connection connection = null;
445: PreparedStatement statement = null;
446: String sql = "UPDATE "
447: + TABLE_NAME
448: + " SET CategoryOrder = CategoryOrder - 1, CategoryModifiedDate = ? WHERE CategoryID = ?";
449: try {
450: connection = DBUtils.getConnection();
451: statement = connection.prepareStatement(sql);
452: statement.setTimestamp(1, categoryModifiedDate);
453: statement.setInt(2, categoryID);
454: if (statement.executeUpdate() != 1) {
455: throw new ObjectNotFoundException(
456: "Cannot update the CategoryOrder in table Category. Please contact Web site Administrator.");
457: }
458: //@todo: coi lai cho nay
459: // ATTENTION !!!
460: setDirty(true);
461: } catch (SQLException sqle) {
462: log.error("Sql Execution Error!", sqle);
463: throw new DatabaseException(
464: "Error executing SQL in CategoryDAOImplJDBC.decreaseCategoryOrder.");
465: } finally {
466: DBUtils.closeStatement(statement);
467: DBUtils.closeConnection(connection);
468: }
469: }
470:
471: /**
472: * This method should be call only when we can make sure that memberID is in database
473: */
474: public void increaseCategoryOrder(int categoryID,
475: Timestamp categoryModifiedDate) throws DatabaseException,
476: ObjectNotFoundException {
477:
478: Connection connection = null;
479: PreparedStatement statement = null;
480: String sql = "UPDATE "
481: + TABLE_NAME
482: + " SET CategoryOrder = CategoryOrder + 1, CategoryModifiedDate = ? WHERE CategoryID = ?";
483: try {
484: connection = DBUtils.getConnection();
485: statement = connection.prepareStatement(sql);
486: statement.setTimestamp(1, categoryModifiedDate);
487: statement.setInt(2, categoryID);
488: if (statement.executeUpdate() != 1) {
489: throw new ObjectNotFoundException(
490: "Cannot update the CategoryOrder in table Category. Please contact Web site Administrator.");
491: }
492: //@todo: coi lai cho nay
493: // ATTENTION !!!
494: setDirty(true);
495: } catch (SQLException sqle) {
496: log.error("Sql Execution Error!", sqle);
497: throw new DatabaseException(
498: "Error executing SQL in CategoryDAOImplJDBC.increaseCategoryOrder.");
499: } finally {
500: DBUtils.closeStatement(statement);
501: DBUtils.closeConnection(connection);
502: }
503: }
504:
505: }// end of class CategoryDAOImplJDBC
|