001: /*
002: * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/RankDAOImplJDBC.java,v 1.15 2007/12/17 09:09:39 minhnn Exp $
003: * $Author: minhnn $
004: * $Revision: 1.15 $
005: * $Date: 2007/12/17 09:09:39 $
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.RankBean;
048: import com.mvnforum.db.RankDAO;
049: import net.myvietnam.mvncore.db.DBUtils;
050: import net.myvietnam.mvncore.exception.*;
051: import org.apache.commons.logging.Log;
052: import org.apache.commons.logging.LogFactory;
053:
054: public class RankDAOImplJDBC implements RankDAO {
055:
056: private static Log log = LogFactory.getLog(RankDAOImplJDBC.class);
057:
058: // this variable will support caching if cache for this class is needed
059: private static boolean m_dirty = true;
060:
061: public RankDAOImplJDBC() {
062: }
063:
064: public static boolean isDirty() {
065: return m_dirty;
066: }
067:
068: public static void setDirty(boolean dirty) {
069: m_dirty = dirty;
070: }
071:
072: public void findByAlternateKey_RankTitle(String rankTitle)
073: throws ObjectNotFoundException, DatabaseException {
074:
075: Connection connection = null;
076: PreparedStatement statement = null;
077: ResultSet resultSet = null;
078: StringBuffer sql = new StringBuffer(512);
079: sql.append("SELECT RankTitle");
080: sql.append(" FROM " + TABLE_NAME);
081: if (DBUtils.isCaseSensitiveDatebase()) {
082: sql.append(" WHERE lower(RankTitle) = lower(?)");
083: } else {
084: sql.append(" WHERE RankTitle = ?");
085: }
086: try {
087: connection = DBUtils.getConnection();
088: statement = connection.prepareStatement(sql.toString());
089: statement.setString(1, rankTitle);
090: resultSet = statement.executeQuery();
091: if (!resultSet.next()) {
092: throw new ObjectNotFoundException(
093: "Cannot find the alternate key [RankTitle] ("
094: + rankTitle + ") in table 'Rank'.");
095: }
096: } catch (SQLException sqle) {
097: log.error("Sql Execution Error!", sqle);
098: throw new DatabaseException(
099: "Error executing SQL in RankDAOImplJDBC.findByAlternateKey_RankTitle.");
100: } finally {
101: DBUtils.closeResultSet(resultSet);
102: DBUtils.closeStatement(statement);
103: DBUtils.closeConnection(connection);
104: }
105: }
106:
107: public void findByAlternateKey_RankMinPosts(int rankMinPosts)
108: throws ObjectNotFoundException, DatabaseException {
109:
110: Connection connection = null;
111: PreparedStatement statement = null;
112: ResultSet resultSet = null;
113: StringBuffer sql = new StringBuffer(512);
114: sql.append("SELECT RankMinPosts");
115: sql.append(" FROM " + TABLE_NAME);
116: sql.append(" WHERE RankMinPosts = ?");
117: try {
118: connection = DBUtils.getConnection();
119: statement = connection.prepareStatement(sql.toString());
120: statement.setInt(1, rankMinPosts);
121: resultSet = statement.executeQuery();
122: if (!resultSet.next()) {
123: throw new ObjectNotFoundException(
124: "Cannot find the alternate key [RankMinPosts] ("
125: + rankMinPosts + ") in table 'Rank'.");
126: }
127: } catch (SQLException sqle) {
128: log.error("Sql Execution Error!", sqle);
129: throw new DatabaseException(
130: "Error executing SQL in RankDAOImplJDBC.findByAlternateKey_RankMinPosts.");
131: } finally {
132: DBUtils.closeResultSet(resultSet);
133: DBUtils.closeStatement(statement);
134: DBUtils.closeConnection(connection);
135: }
136: }
137:
138: /*
139: * Included columns: RankMinPosts, RankLevel, RankTitle, RankImage, RankType,
140: * RankOption
141: * Excluded columns: RankID
142: */
143: public void create(int rankMinPosts, int rankLevel,
144: String rankTitle, String rankImage, int rankType,
145: int rankOption) throws CreateException, DatabaseException,
146: DuplicateKeyException {
147:
148: // @todo: Comment this try-catch block if the needed columns dont have attribute 'include'
149: // If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
150: try {
151: //Check if alternate key already exists
152: findByAlternateKey_RankTitle(rankTitle);
153: //If so, then we have to throw an exception
154: throw new DuplicateKeyException(
155: "Alternate key already exists. Cannot create new Rank with the same [RankTitle] ("
156: + rankTitle + ").");
157: } catch (ObjectNotFoundException e) {
158: //Otherwise we can go ahead
159: }
160:
161: // @todo: Comment this try-catch block if the needed columns dont have attribute 'include'
162: // If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
163: try {
164: //Check if alternate key already exists
165: findByAlternateKey_RankMinPosts(rankMinPosts);
166: //If so, then we have to throw an exception
167: throw new DuplicateKeyException(
168: "Alternate key already exists. Cannot create new Rank with the same [RankMinPosts] ("
169: + rankMinPosts + ").");
170: } catch (ObjectNotFoundException e) {
171: //Otherwise we can go ahead
172: }
173:
174: Connection connection = null;
175: PreparedStatement statement = null;
176: StringBuffer sql = new StringBuffer(512);
177: sql
178: .append("INSERT INTO "
179: + TABLE_NAME
180: + " (RankMinPosts, RankLevel, RankTitle, RankImage, RankType, RankOption)");
181: sql.append(" VALUES (?, ?, ?, ?, ?, ?)");
182: try {
183: connection = DBUtils.getConnection();
184: statement = connection.prepareStatement(sql.toString());
185:
186: statement.setInt(1, rankMinPosts);
187: statement.setInt(2, rankLevel);
188: statement.setString(3, rankTitle);
189: statement.setString(4, rankImage);
190: statement.setInt(5, rankType);
191: statement.setInt(6, rankOption);
192:
193: if (statement.executeUpdate() != 1) {
194: throw new CreateException(
195: "Error adding a row into table 'Rank'.");
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 RankDAOImplJDBC.create.");
202: } finally {
203: DBUtils.closeStatement(statement);
204: DBUtils.closeConnection(connection);
205: }
206: }
207:
208: /*
209: * Included columns: RankMinPosts, RankLevel, RankTitle, RankImage, RankType,
210: * RankOption
211: * Excluded columns: RankID
212: */
213: public void update(
214: int rankID, // primary key
215: int rankMinPosts, int rankLevel, String rankTitle,
216: String rankImage, int rankType, int rankOption)
217: throws ObjectNotFoundException, DatabaseException,
218: DuplicateKeyException {
219:
220: RankBean bean = getRank(rankID);
221:
222: if (rankTitle.equalsIgnoreCase(bean.getRankTitle()) == false) {
223: // Rank tries to change its alternate key <RankTitle>, so we must check if it already exist
224: try {
225: findByAlternateKey_RankTitle(rankTitle);
226: throw new DuplicateKeyException(
227: "Alternate key [RankTitle] ("
228: + rankTitle
229: + ") already exists. Cannot update Rank.");
230: } catch (ObjectNotFoundException e) {
231: //Otherwise we can go ahead
232: }
233: }
234:
235: if (rankMinPosts != bean.getRankMinPosts()) {
236: // Rank tries to change its alternate key <RankMinPosts>, so we must check if it already exist
237: try {
238: findByAlternateKey_RankMinPosts(rankMinPosts);
239: throw new DuplicateKeyException(
240: "Alternate key [RankMinPosts] ("
241: + rankMinPosts
242: + ") already exists. Cannot update Rank.");
243: } catch (ObjectNotFoundException e) {
244: //Otherwise we can go ahead
245: }
246: }
247:
248: Connection connection = null;
249: PreparedStatement statement = null;
250: StringBuffer sql = new StringBuffer(512);
251: sql
252: .append("UPDATE "
253: + TABLE_NAME
254: + " SET RankMinPosts = ?, RankLevel = ?, RankTitle = ?, RankImage = ?, RankType = ?, RankOption = ?");
255: sql.append(" WHERE RankID = ?");
256: try {
257: connection = DBUtils.getConnection();
258: statement = connection.prepareStatement(sql.toString());
259:
260: // // column(s) to update
261: statement.setInt(1, rankMinPosts);
262: statement.setInt(2, rankLevel);
263: statement.setString(3, rankTitle);
264: statement.setString(4, rankImage);
265: statement.setInt(5, rankType);
266: statement.setInt(6, rankOption);
267:
268: // primary key column(s)
269: statement.setInt(7, rankID);
270:
271: if (statement.executeUpdate() != 1) {
272: throw new ObjectNotFoundException(
273: "Cannot update table Rank where primary key = ("
274: + rankID + ").");
275: }
276: m_dirty = true;
277: } catch (SQLException sqle) {
278: log.error("Sql Execution Error!", sqle);
279: throw new DatabaseException(
280: "Error executing SQL in RankDAOImplJDBC.update.");
281: } finally {
282: DBUtils.closeStatement(statement);
283: DBUtils.closeConnection(connection);
284: }
285: }
286:
287: public void delete(int rankID) throws DatabaseException,
288: ObjectNotFoundException {
289:
290: Connection connection = null;
291: PreparedStatement statement = null;
292: StringBuffer sql = new StringBuffer(512);
293: sql.append("DELETE FROM " + TABLE_NAME);
294: sql.append(" WHERE RankID = ?");
295:
296: try {
297: connection = DBUtils.getConnection();
298: statement = connection.prepareStatement(sql.toString());
299: statement.setInt(1, rankID);
300: if (statement.executeUpdate() != 1) {
301: throw new ObjectNotFoundException(
302: "Cannot delete a row in table Rank where primary key = ("
303: + rankID + ").");
304: }
305: m_dirty = true;
306: } catch (SQLException sqle) {
307: log.error("Sql Execution Error!", sqle);
308: throw new DatabaseException(
309: "Error executing SQL in RankDAOImplJDBC.delete.");
310: } finally {
311: DBUtils.closeStatement(statement);
312: DBUtils.closeConnection(connection);
313: }
314: }
315:
316: /*
317: * Included columns: RankMinPosts, RankLevel, RankTitle, RankImage, RankType,
318: * RankOption
319: * Excluded columns: RankID
320: */
321: public RankBean getRank(int rankID) throws ObjectNotFoundException,
322: DatabaseException {
323:
324: Connection connection = null;
325: PreparedStatement statement = null;
326: ResultSet resultSet = null;
327: StringBuffer sql = new StringBuffer(512);
328: sql
329: .append("SELECT RankMinPosts, RankLevel, RankTitle, RankImage, RankType, RankOption");
330: sql.append(" FROM " + TABLE_NAME);
331: sql.append(" WHERE RankID = ?");
332: try {
333: connection = DBUtils.getConnection();
334: statement = connection.prepareStatement(sql.toString());
335: statement.setInt(1, rankID);
336: resultSet = statement.executeQuery();
337: if (!resultSet.next()) {
338: throw new ObjectNotFoundException(
339: "Cannot find the row in table Rank where primary key = ("
340: + rankID + ").");
341: }
342:
343: RankBean bean = new RankBean();
344: // @todo: uncomment the following line(s) as needed
345: bean.setRankID(rankID);
346: bean.setRankMinPosts(resultSet.getInt("RankMinPosts"));
347: bean.setRankLevel(resultSet.getInt("RankLevel"));
348: bean.setRankTitle(resultSet.getString("RankTitle"));
349: bean.setRankImage(resultSet.getString("RankImage"));
350: bean.setRankType(resultSet.getInt("RankType"));
351: bean.setRankOption(resultSet.getInt("RankOption"));
352: return bean;
353: } catch (SQLException sqle) {
354: log.error("Sql Execution Error!", sqle);
355: throw new DatabaseException(
356: "Error executing SQL in RankDAOImplJDBC.getRank(pk).");
357: } finally {
358: DBUtils.closeResultSet(resultSet);
359: DBUtils.closeStatement(statement);
360: DBUtils.closeConnection(connection);
361: }
362: }
363:
364: /*
365: * Included columns: RankID, RankMinPosts, RankLevel, RankTitle, RankImage,
366: * RankType, RankOption
367: * Excluded columns:
368: */
369: public Collection getRanks() throws DatabaseException {
370:
371: Connection connection = null;
372: PreparedStatement statement = null;
373: ResultSet resultSet = null;
374: Collection retValue = new ArrayList();
375: StringBuffer sql = new StringBuffer(512);
376: sql
377: .append("SELECT RankID, RankMinPosts, RankLevel, RankTitle, RankImage, RankType, RankOption");
378: sql.append(" FROM " + TABLE_NAME);
379: //sql.append(" WHERE "); // @todo: uncomment as needed
380: sql.append(" ORDER BY RankMinPosts ASC ");
381: try {
382: connection = DBUtils.getConnection();
383: statement = connection.prepareStatement(sql.toString());
384: resultSet = statement.executeQuery();
385: while (resultSet.next()) {
386: RankBean bean = new RankBean();
387: bean.setRankID(resultSet.getInt("RankID"));
388: bean.setRankMinPosts(resultSet.getInt("RankMinPosts"));
389: bean.setRankLevel(resultSet.getInt("RankLevel"));
390: bean.setRankTitle(resultSet.getString("RankTitle"));
391: bean.setRankImage(resultSet.getString("RankImage"));
392: bean.setRankType(resultSet.getInt("RankType"));
393: bean.setRankOption(resultSet.getInt("RankOption"));
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 RankDAOImplJDBC.getRanks.");
401: } finally {
402: DBUtils.closeResultSet(resultSet);
403: DBUtils.closeStatement(statement);
404: DBUtils.closeConnection(connection);
405: }
406: }
407:
408: public int getRankIDFromRankTitle(String rankTitle)
409: throws ObjectNotFoundException, DatabaseException {
410:
411: Connection connection = null;
412: PreparedStatement statement = null;
413: ResultSet resultSet = null;
414: String sql = "SELECT RankID FROM " + TABLE_NAME
415: + " WHERE RankTitle = ?";
416: try {
417: connection = DBUtils.getConnection();
418: statement = connection.prepareStatement(sql);
419: statement.setString(1, rankTitle);
420: resultSet = statement.executeQuery();
421: if (!resultSet.next()) {
422: throw new ObjectNotFoundException(
423: "Cannot find the alternate key [RankTitle] ("
424: + rankTitle + ") in table 'Rank'.");
425: }
426: return resultSet.getInt(1);
427: } catch (SQLException sqle) {
428: log.error("Sql Execution Error!", sqle);
429: throw new DatabaseException(
430: "Error executing SQL in RankDAOImplJDBC.getRankIDFromRankTitle.");
431: } finally {
432: DBUtils.closeResultSet(resultSet);
433: DBUtils.closeStatement(statement);
434: DBUtils.closeConnection(connection);
435: }
436: }
437:
438: }// end of class RankDAOImplJDBC
|