0001: /*
0002: * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/ThreadDAOImplJDBC.java,v 1.73 2008/01/15 11:17:54 minhnn Exp $
0003: * $Author: minhnn $
0004: * $Revision: 1.73 $
0005: * $Date: 2008/01/15 11:17:54 $
0006: *
0007: * ====================================================================
0008: *
0009: * Copyright (C) 2002-2007 by MyVietnam.net
0010: *
0011: * All copyright notices regarding mvnForum MUST remain
0012: * intact in the scripts and in the outputted HTML.
0013: * The "powered by" text/logo with a link back to
0014: * http://www.mvnForum.com and http://www.MyVietnam.net in
0015: * the footer of the pages MUST remain visible when the pages
0016: * are viewed on the internet or intranet.
0017: *
0018: * This program is free software; you can redistribute it and/or modify
0019: * it under the terms of the GNU General Public License as published by
0020: * the Free Software Foundation; either version 2 of the License, or
0021: * any later version.
0022: *
0023: * This program is distributed in the hope that it will be useful,
0024: * but WITHOUT ANY WARRANTY; without even the implied warranty of
0025: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
0026: * GNU General Public License for more details.
0027: *
0028: * You should have received a copy of the GNU General Public License
0029: * along with this program; if not, write to the Free Software
0030: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
0031: *
0032: * Support can be obtained from support forums at:
0033: * http://www.mvnForum.com/mvnforum/index
0034: *
0035: * Correspondence and Marketing Questions can be sent to:
0036: * info at MyVietnam net
0037: *
0038: * @author: Minh Nguyen
0039: * @author: Mai Nguyen
0040: */
0041: package com.mvnforum.db.jdbc;
0042:
0043: import java.io.StringReader;
0044: import java.sql.*;
0045: import java.util.*;
0046:
0047: import com.mvnforum.db.*;
0048:
0049: import net.myvietnam.mvncore.db.DBUtils;
0050: import net.myvietnam.mvncore.exception.*;
0051: import net.myvietnam.mvncore.util.AssertionUtil;
0052:
0053: import org.apache.commons.logging.Log;
0054: import org.apache.commons.logging.LogFactory;
0055:
0056: public class ThreadDAOImplJDBC implements ThreadDAO {
0057:
0058: private static Log log = LogFactory.getLog(ThreadDAOImplJDBC.class);
0059:
0060: // this variable will support caching if cache for this class is needed
0061: private static boolean m_dirty = true;
0062:
0063: public ThreadDAOImplJDBC() {
0064: }
0065:
0066: protected static boolean isDirty() {
0067: return m_dirty;
0068: }
0069:
0070: protected static void setDirty(boolean dirty) {
0071: m_dirty = dirty;
0072: }
0073:
0074: public void findByPrimaryKey(int threadID)
0075: throws ObjectNotFoundException, DatabaseException {
0076:
0077: Connection connection = null;
0078: PreparedStatement statement = null;
0079: ResultSet resultSet = null;
0080: StringBuffer sql = new StringBuffer(512);
0081: sql.append("SELECT ThreadID");
0082: sql.append(" FROM " + TABLE_NAME);
0083: sql.append(" WHERE ThreadID = ?");
0084: try {
0085: connection = DBUtils.getConnection();
0086: statement = connection.prepareStatement(sql.toString());
0087: statement.setInt(1, threadID);
0088: resultSet = statement.executeQuery();
0089: if (!resultSet.next()) {
0090: throw new ObjectNotFoundException(
0091: "Cannot find the primary key (" + threadID
0092: + ") in table 'Thread'.");
0093: }
0094: } catch (SQLException sqle) {
0095: log.error("Sql Execution Error!", sqle);
0096: throw new DatabaseException(
0097: "Error executing SQL in ThreadDAOImplJDBC.findByPrimaryKey.");
0098: } finally {
0099: DBUtils.closeResultSet(resultSet);
0100: DBUtils.closeStatement(statement);
0101: DBUtils.closeConnection(connection);
0102: }
0103: }
0104:
0105: /*
0106: * Included columns: ForumID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody,
0107: * ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType,
0108: * ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount,
0109: * ThreadIcon, ThreadDuration
0110: * Excluded columns: ThreadID
0111: */
0112: private void create(int forumID, String memberName,
0113: String lastPostMemberName, String threadTopic,
0114: String threadBody, int threadVoteCount,
0115: int threadVoteTotalStars, Timestamp threadCreationDate,
0116: Timestamp threadLastPostDate, int threadType,
0117: int threadPriority, int threadOption, int threadStatus,
0118: int threadHasPoll, int threadViewCount,
0119: int threadReplyCount, String threadIcon,
0120: int threadDuration, int threadAttachCount)
0121: throws CreateException, DatabaseException,
0122: ForeignKeyNotFoundException {
0123:
0124: ThreadBean.validateThreadStatus(threadStatus);
0125:
0126: try {
0127: // @todo: modify the parameter list as needed
0128: // You may have to regenerate this method if the needed columns dont have attribute 'include'
0129: DAOFactory.getForumDAO().findByPrimaryKey(forumID);
0130: } catch (ObjectNotFoundException e) {
0131: throw new ForeignKeyNotFoundException(
0132: "Foreign key refers to table 'Forum' does not exist. Cannot create new Thread.");
0133: }
0134:
0135: /* Here we allow memberName to be empty or null, which means
0136: unknown user or guest created the thread. */
0137: if ((memberName != null) && (memberName.length() > 0)) {
0138: try {
0139: // @todo: modify the parameter list as needed
0140: // You may have to regenerate this method if the needed columns dont have attribute 'include'
0141: DAOFactory.getMemberDAO()
0142: .findByAlternateKey_MemberName(memberName);
0143: } catch (ObjectNotFoundException e) {
0144: throw new ForeignKeyNotFoundException(
0145: "Foreign key refers to table 'Member' does not exist. Cannot create new Post.");
0146: }
0147: } else {
0148: memberName = ""; // This is needed, otherwise we will get 'null' in the sql query, instead of ''
0149: }
0150:
0151: //we also allow guests to send posts (if admin allows that)
0152: if ((lastPostMemberName != null)
0153: && (lastPostMemberName.length() > 0)) {
0154: try {
0155: DAOFactory.getMemberDAO()
0156: .findByAlternateKey_MemberName(
0157: lastPostMemberName);
0158: } catch (ObjectNotFoundException e) {
0159: throw new ForeignKeyNotFoundException(
0160: "Foreign key refers to table 'Member' does not exist. Cannot create table 'Thread'.");
0161: }
0162: } else {
0163: lastPostMemberName = ""; //so we don't get 'null' in sql query
0164: }
0165:
0166: Connection connection = null;
0167: PreparedStatement statement = null;
0168: StringBuffer sql = new StringBuffer(512);
0169: sql
0170: .append("INSERT INTO "
0171: + TABLE_NAME
0172: + " (ForumID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadPriority, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration, ThreadAttachCount)");
0173: sql
0174: .append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
0175: try {
0176: connection = DBUtils.getConnection();
0177: statement = connection.prepareStatement(sql.toString());
0178:
0179: statement.setInt(1, forumID);
0180: statement.setString(2, memberName);
0181: statement.setString(3, lastPostMemberName);
0182: statement.setString(4, threadTopic);
0183: if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
0184: statement.setCharacterStream(5, new StringReader(
0185: threadBody), threadBody.length());
0186: } else {
0187: statement.setString(5, threadBody);
0188: }
0189: statement.setInt(6, threadVoteCount);
0190: statement.setInt(7, threadVoteTotalStars);
0191: statement.setTimestamp(8, threadCreationDate);
0192: statement.setTimestamp(9, threadLastPostDate);
0193: statement.setInt(10, threadType);
0194: statement.setInt(11, threadPriority);
0195: statement.setInt(12, threadOption);
0196: statement.setInt(13, threadStatus);
0197: statement.setInt(14, threadHasPoll);
0198: statement.setInt(15, threadViewCount);
0199: statement.setInt(16, threadReplyCount);
0200: statement.setString(17, threadIcon);
0201: statement.setInt(18, threadDuration);
0202: statement.setInt(19, threadAttachCount);
0203:
0204: if (statement.executeUpdate() != 1) {
0205: throw new CreateException(
0206: "Error adding a row into table 'Thread'.");
0207: }
0208: m_dirty = true;
0209: } catch (SQLException sqle) {
0210: log.error("Sql Execution Error!", sqle);
0211: throw new DatabaseException(
0212: "Error executing SQL in ThreadDAOImplJDBC.create.");
0213: } finally {
0214: DBUtils.closeStatement(statement);
0215: DBUtils.closeConnection(connection);
0216: }
0217: }
0218:
0219: public int createThread(int forumID, String memberName,
0220: String lastPostMemberName, String threadTopic,
0221: String threadBody, int threadVoteCount,
0222: int threadVoteTotalStars, Timestamp threadCreationDate,
0223: Timestamp threadLastPostDate, int threadType,
0224: int threadPriority, int threadOption, int threadStatus,
0225: int threadHasPoll, int threadViewCount,
0226: int threadReplyCount, String threadIcon,
0227: int threadDuration, int threadAttachCount)
0228: throws ObjectNotFoundException, CreateException,
0229: DatabaseException, ForeignKeyNotFoundException {
0230:
0231: create(forumID, memberName, lastPostMemberName, threadTopic,
0232: threadBody, threadVoteCount, threadVoteTotalStars,
0233: threadCreationDate, threadLastPostDate, threadType,
0234: threadPriority, threadOption, threadStatus,
0235: threadHasPoll, threadViewCount, threadReplyCount,
0236: threadIcon, threadDuration, threadAttachCount);
0237: int threadID = 0;
0238: try {
0239: threadID = findThreadID(forumID, memberName,
0240: threadCreationDate);
0241: } catch (ObjectNotFoundException ex) {
0242: // Hack the Oracle 9i problem
0243: Timestamp roundTimestamp = new Timestamp(
0244: (threadCreationDate.getTime() / 1000) * 1000);
0245: threadID = findThreadID(forumID, memberName, roundTimestamp);
0246: }
0247: return threadID;
0248: }
0249:
0250: public void delete(int threadID) throws DatabaseException,
0251: ObjectNotFoundException {
0252:
0253: Connection connection = null;
0254: PreparedStatement statement = null;
0255: StringBuffer sql = new StringBuffer(512);
0256: sql.append("DELETE FROM " + TABLE_NAME);
0257: sql.append(" WHERE ThreadID = ?");
0258:
0259: try {
0260: connection = DBUtils.getConnection();
0261: statement = connection.prepareStatement(sql.toString());
0262: statement.setInt(1, threadID);
0263: if (statement.executeUpdate() != 1) {
0264: throw new ObjectNotFoundException(
0265: "Cannot delete a row in table Thread where primary key = ("
0266: + threadID + ").");
0267: }
0268: m_dirty = true;
0269: } catch (SQLException sqle) {
0270: log.error("Sql Execution Error!", sqle);
0271: throw new DatabaseException(
0272: "Error executing SQL in ThreadDAOImplJDBC.delete.");
0273: } finally {
0274: DBUtils.closeStatement(statement);
0275: DBUtils.closeConnection(connection);
0276: }
0277: }
0278:
0279: public void delete_inForum(int forumID) throws DatabaseException {
0280:
0281: Connection connection = null;
0282: PreparedStatement statement = null;
0283: StringBuffer sql = new StringBuffer(512);
0284: sql.append("DELETE FROM " + TABLE_NAME);
0285: sql.append(" WHERE ForumID = ?");
0286:
0287: try {
0288: connection = DBUtils.getConnection();
0289: statement = connection.prepareStatement(sql.toString());
0290: statement.setInt(1, forumID);
0291:
0292: statement.executeUpdate();
0293: m_dirty = true;
0294: } catch (SQLException sqle) {
0295: log.error("Sql Execution Error!", sqle);
0296: throw new DatabaseException(
0297: "Error executing SQL in ThreadDAOImplJDBC.delete_inForum.");
0298: } finally {
0299: DBUtils.closeStatement(statement);
0300: DBUtils.closeConnection(connection);
0301: }
0302: }
0303:
0304: /*
0305: * Included columns: ThreadTopic, ThreadBody, ThreadIcon
0306: * Excluded columns: ThreadID, ForumID, MemberName, LastPostMemberName, ThreadVoteCount,
0307: * ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadOption,
0308: * ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount,
0309: * ThreadDuration
0310: */
0311: public void updateTopic_Body_Icon(
0312: int threadID, // primary key
0313: String threadTopic, String threadBody, String threadIcon,
0314: int threadPriority) throws ObjectNotFoundException,
0315: DatabaseException {
0316:
0317: Connection connection = null;
0318: PreparedStatement statement = null;
0319: StringBuffer sql = new StringBuffer(512);
0320: sql
0321: .append("UPDATE "
0322: + TABLE_NAME
0323: + " SET ThreadTopic = ?, ThreadBody = ?, ThreadIcon = ?, ThreadPriority = ?");
0324: sql.append(" WHERE ThreadID = ?");
0325: try {
0326: connection = DBUtils.getConnection();
0327: statement = connection.prepareStatement(sql.toString());
0328:
0329: // // column(s) to update
0330: statement.setString(1, threadTopic);
0331: if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
0332: statement.setCharacterStream(2, new StringReader(
0333: threadBody), threadBody.length());
0334: } else {
0335: statement.setString(2, threadBody);
0336: }
0337: statement.setString(3, threadIcon);
0338:
0339: statement.setInt(4, threadPriority);
0340:
0341: // primary key column(s)
0342: statement.setInt(5, threadID);
0343:
0344: if (statement.executeUpdate() != 1) {
0345: throw new ObjectNotFoundException(
0346: "Cannot update table Thread where primary key = ("
0347: + threadID + ").");
0348: }
0349: m_dirty = true;
0350: } catch (SQLException sqle) {
0351: log.error("Sql Execution Error!", sqle);
0352: throw new DatabaseException(
0353: "Error executing SQL in ThreadDAOImplJDBC.updateTopic_Body_Icon.");
0354: } finally {
0355: DBUtils.closeStatement(statement);
0356: DBUtils.closeConnection(connection);
0357: }
0358: }
0359:
0360: public void updateThreadAttachCount(int threadID, int attachCount)
0361: throws ObjectNotFoundException, DatabaseException {
0362:
0363: Connection connection = null;
0364: PreparedStatement statement = null;
0365: StringBuffer sql = new StringBuffer(512);
0366: sql.append("UPDATE " + TABLE_NAME
0367: + " SET ThreadAttachCount = ?");
0368: sql.append(" WHERE ThreadID = ?");
0369: try {
0370: connection = DBUtils.getConnection();
0371: statement = connection.prepareStatement(sql.toString());
0372:
0373: // // column(s) to update
0374: statement.setInt(1, attachCount);
0375:
0376: // primary key column(s)
0377: statement.setInt(2, threadID);
0378:
0379: if (statement.executeUpdate() != 1) {
0380: throw new ObjectNotFoundException(
0381: "Cannot update ThreadAttachCount in table Thread where primary key = ("
0382: + threadID + ").");
0383: }
0384: m_dirty = true;
0385: } catch (SQLException sqle) {
0386: log.error("Sql Execution Error!", sqle);
0387: throw new DatabaseException(
0388: "Error executing SQL in ThreadDAOImplJDBC.updateThreadAttachCount.");
0389: } finally {
0390: DBUtils.closeStatement(statement);
0391: DBUtils.closeConnection(connection);
0392: }
0393: }
0394:
0395: /**
0396: * This method should be call only when we can make sure that threadID is in database
0397: */
0398: public void increaseReplyCount(int threadID)
0399: throws DatabaseException, ObjectNotFoundException {
0400:
0401: Connection connection = null;
0402: PreparedStatement statement = null;
0403: String sql = "UPDATE "
0404: + TABLE_NAME
0405: + " SET ThreadReplyCount = ThreadReplyCount + 1 WHERE ThreadID = ?";
0406: try {
0407: connection = DBUtils.getConnection();
0408: statement = connection.prepareStatement(sql);
0409: statement.setInt(1, threadID);
0410: if (statement.executeUpdate() != 1) {
0411: throw new ObjectNotFoundException(
0412: "Cannot update the ThreadReplyCount in table Thread. Please contact Web site Administrator.");
0413: }
0414: //@todo: coi lai cho nay
0415: // ATTENTION !!!
0416: setDirty(true);
0417: } catch (SQLException sqle) {
0418: log.error("Sql Execution Error!", sqle);
0419: throw new DatabaseException(
0420: "Error executing SQL in ThreadDAOImplJDBC.increaseReplyCount.");
0421: } finally {
0422: DBUtils.closeStatement(statement);
0423: DBUtils.closeConnection(connection);
0424: }
0425: }
0426:
0427: /*
0428: * Included columns: LastPostMemberName
0429: * Excluded columns: ThreadID, ForumID, MemberName, ThreadTopic, ThreadBody,
0430: * ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType,
0431: * ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount,
0432: * ThreadIcon, ThreadDuration
0433: */
0434: public void updateLastPostMemberName(int threadID, // primary key
0435: String lastPostMemberName) throws ObjectNotFoundException,
0436: DatabaseException, ForeignKeyNotFoundException {
0437:
0438: //we also allow guests to send posts (if admin allows that)
0439: if ((lastPostMemberName != null)
0440: && (lastPostMemberName.length() > 0)) {
0441: try {
0442: // @todo: modify the parameter list as needed
0443: // If this method does not change the foreign key columns, you can comment this block of code.
0444: DAOFactory.getMemberDAO()
0445: .findByAlternateKey_MemberName(
0446: lastPostMemberName);
0447: } catch (ObjectNotFoundException e) {
0448: throw new ForeignKeyNotFoundException(
0449: "Foreign key refers to table 'Member' does not exist. Cannot update table 'Thread'.");
0450: }
0451: } else {
0452: lastPostMemberName = ""; //so we don't get 'null' in sql query
0453: }
0454:
0455: Connection connection = null;
0456: PreparedStatement statement = null;
0457: StringBuffer sql = new StringBuffer(512);
0458: sql.append("UPDATE " + TABLE_NAME
0459: + " SET LastPostMemberName = ?");
0460: sql.append(" WHERE ThreadID = ?");
0461: try {
0462: connection = DBUtils.getConnection();
0463: statement = connection.prepareStatement(sql.toString());
0464:
0465: // // column(s) to update
0466: statement.setString(1, lastPostMemberName);
0467:
0468: // primary key column(s)
0469: statement.setInt(2, threadID);
0470:
0471: if (statement.executeUpdate() != 1) {
0472: // Some drivers dont update database if it detect old and new data are the same
0473: // @todo: should check driver, not check database
0474: // Currently there is only one driver: Caucho MySql driver
0475: if (DBUtils.getDatabaseType() != DBUtils.DATABASE_MYSQL) {
0476: throw new ObjectNotFoundException(
0477: "Cannot update table Thread where primary key = ("
0478: + threadID + ").");
0479: }
0480: log
0481: .warn("WARNING: By pass the check for Caucho MySql driver.");
0482: }
0483: setDirty(true);
0484: } catch (SQLException sqle) {
0485: log.error("Sql Execution Error!", sqle);
0486: throw new DatabaseException(
0487: "Error executing SQL in ThreadDAOImplJDBC.updateLastPostMemberName.");
0488: } finally {
0489: DBUtils.closeStatement(statement);
0490: DBUtils.closeConnection(connection);
0491: }
0492: }
0493:
0494: /*
0495: * Included columns: ThreadLastPostDate
0496: * Excluded columns: ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic,
0497: * ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadType,
0498: * ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount,
0499: * ThreadIcon, ThreadDuration
0500: */
0501: public void updateLastPostDate(int threadID, // primary key
0502: Timestamp threadLastPostDate)
0503: throws ObjectNotFoundException, DatabaseException {
0504:
0505: Connection connection = null;
0506: PreparedStatement statement = null;
0507: StringBuffer sql = new StringBuffer(512);
0508: sql.append("UPDATE " + TABLE_NAME
0509: + " SET ThreadLastPostDate = ?");
0510: sql.append(" WHERE ThreadID = ?");
0511: try {
0512: connection = DBUtils.getConnection();
0513: statement = connection.prepareStatement(sql.toString());
0514:
0515: // // column(s) to update
0516: statement.setTimestamp(1, threadLastPostDate);
0517:
0518: // primary key column(s)
0519: statement.setInt(2, threadID);
0520:
0521: if (statement.executeUpdate() != 1) {
0522: throw new ObjectNotFoundException(
0523: "Cannot update table Thread where primary key = ("
0524: + threadID + ").");
0525: }
0526: m_dirty = true;
0527: } catch (SQLException sqle) {
0528: log.error("Sql Execution Error!", sqle);
0529: throw new DatabaseException(
0530: "Error executing SQL in ThreadDAOImplJDBC.updateLastPostDate.");
0531: } finally {
0532: DBUtils.closeStatement(statement);
0533: DBUtils.closeConnection(connection);
0534: }
0535: }
0536:
0537: /*
0538: * Included columns: ForumID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody,
0539: * ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType,
0540: * ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount,
0541: * ThreadIcon, ThreadDuration
0542: * Excluded columns: ThreadID
0543: */
0544: public ThreadBean getThread(int threadID)
0545: throws ObjectNotFoundException, DatabaseException {
0546:
0547: Connection connection = null;
0548: PreparedStatement statement = null;
0549: ResultSet resultSet = null;
0550: StringBuffer sql = new StringBuffer(512);
0551: sql
0552: .append("SELECT ForumID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadPriority, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration");
0553: sql.append(" FROM " + TABLE_NAME);
0554: sql.append(" WHERE ThreadID = ?");
0555: try {
0556: connection = DBUtils.getConnection();
0557: statement = connection.prepareStatement(sql.toString());
0558: statement.setInt(1, threadID);
0559: resultSet = statement.executeQuery();
0560: if (!resultSet.next()) {
0561: throw new ObjectNotFoundException(
0562: "Cannot find the row in table Thread where primary key = ("
0563: + threadID + ").");
0564: }
0565:
0566: ThreadBean bean = new ThreadBean();
0567: // @todo: uncomment the following line(s) as needed
0568: bean.setThreadID(threadID);
0569: bean.setForumID(resultSet.getInt("ForumID"));
0570: bean.setMemberName(resultSet.getString("MemberName"));
0571: bean.setLastPostMemberName(resultSet
0572: .getString("LastPostMemberName"));
0573: bean.setThreadTopic(resultSet.getString("ThreadTopic"));
0574: bean.setThreadBody(resultSet.getString("ThreadBody"));
0575: bean
0576: .setThreadVoteCount(resultSet
0577: .getInt("ThreadVoteCount"));
0578: bean.setThreadVoteTotalStars(resultSet
0579: .getInt("ThreadVoteTotalStars"));
0580: bean.setThreadCreationDate(resultSet
0581: .getTimestamp("ThreadCreationDate"));
0582: bean.setThreadLastPostDate(resultSet
0583: .getTimestamp("ThreadLastPostDate"));
0584: bean.setThreadType(resultSet.getInt("ThreadType"));
0585: bean.setThreadPriority(resultSet.getInt("ThreadPriority"));
0586: bean.setThreadOption(resultSet.getInt("ThreadOption"));
0587: bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
0588: bean.setThreadHasPoll(resultSet.getInt("ThreadHasPoll"));
0589: bean
0590: .setThreadViewCount(resultSet
0591: .getInt("ThreadViewCount"));
0592: bean.setThreadReplyCount(resultSet
0593: .getInt("ThreadReplyCount"));
0594: bean.setThreadIcon(resultSet.getString("ThreadIcon"));
0595: bean.setThreadDuration(resultSet.getInt("ThreadDuration"));
0596: return bean;
0597: } catch (SQLException sqle) {
0598: log.error("Sql Execution Error!", sqle);
0599: throw new DatabaseException(
0600: "Error executing SQL in ThreadDAOImplJDBC.getThread(pk).");
0601: } finally {
0602: DBUtils.closeResultSet(resultSet);
0603: DBUtils.closeStatement(statement);
0604: DBUtils.closeConnection(connection);
0605: }
0606: }
0607:
0608: /**
0609: * Note: this is a customized method
0610: */
0611: public int getNumberOfEnableThreads_inForum(int forumID)
0612: throws DatabaseException {
0613:
0614: return getNumberOfBeans_inForum(forumID, true);
0615: }
0616:
0617: public int getNumberOfDisableThreads_inForum(int forumID)
0618: throws DatabaseException {
0619:
0620: return getNumberOfBeans_inForum(forumID, false);
0621: }
0622:
0623: private int getNumberOfBeans_inForum(int forumID, boolean enable)
0624: throws DatabaseException {
0625:
0626: Connection connection = null;
0627: PreparedStatement statement = null;
0628: ResultSet resultSet = null;
0629: StringBuffer sql = new StringBuffer(512);
0630: sql.append("SELECT Count(*)");
0631: sql.append(" FROM " + TABLE_NAME);
0632: sql.append(" WHERE ForumID = ?");
0633: if (enable) {
0634: sql.append(" AND ThreadStatus <> 1 ");
0635: } else {//disable
0636: sql.append(" AND ThreadStatus = 1 ");
0637: }
0638: try {
0639: connection = DBUtils.getConnection();
0640: statement = connection.prepareStatement(sql.toString());
0641: statement.setInt(1, forumID);
0642: resultSet = statement.executeQuery();
0643: AssertionUtil
0644: .doAssert(resultSet.next(),
0645: "Assertion in ThreadDAOImplJDBC.getNumberOfBeans_inForum.");
0646: return resultSet.getInt(1);
0647: } catch (SQLException sqle) {
0648: log.error("Sql Execution Error!", sqle);
0649: throw new DatabaseException(
0650: "Error executing SQL in ThreadDAOImplJDBC.getNumberOfBeans_inForum.");
0651: } finally {
0652: DBUtils.closeResultSet(resultSet);
0653: DBUtils.closeStatement(statement);
0654: DBUtils.closeConnection(connection);
0655: }
0656: }
0657:
0658: public int getNumberOfNormalEnableThreads_inForum(int forumID)
0659: throws DatabaseException {
0660:
0661: Connection connection = null;
0662: PreparedStatement statement = null;
0663: ResultSet resultSet = null;
0664: StringBuffer sql = new StringBuffer(512);
0665: sql.append("SELECT Count(*)");
0666: sql.append(" FROM " + TABLE_NAME);
0667: sql.append(" WHERE ForumID = ? AND ThreadType = ").append(
0668: ThreadBean.THREAD_TYPE_DEFAULT);
0669: sql.append(" AND ThreadStatus <> 1 ");//mean enable thread
0670: try {
0671: connection = DBUtils.getConnection();
0672: statement = connection.prepareStatement(sql.toString());
0673: statement.setInt(1, forumID);
0674: resultSet = statement.executeQuery();
0675: AssertionUtil
0676: .doAssert(resultSet.next(),
0677: "Assertion in ThreadDAOImplJDBC.getNumberOfNormalEnableThreads_inForum.");
0678: return resultSet.getInt(1);
0679: } catch (SQLException sqle) {
0680: log.error("Sql Execution Error!", sqle);
0681: throw new DatabaseException(
0682: "Error executing SQL in ThreadDAOImplJDBC.getNumberOfNormalEnableThreads_inForum.");
0683: } finally {
0684: DBUtils.closeResultSet(resultSet);
0685: DBUtils.closeStatement(statement);
0686: DBUtils.closeConnection(connection);
0687: }
0688: }
0689:
0690: public int getNumberOfEnableThreads(boolean onlyNoReply,
0691: int status, int category, int forum)
0692: throws DatabaseException {
0693:
0694: return getNumberOfBeans(true, onlyNoReply, true, status,
0695: category, forum);
0696: }
0697:
0698: public int getNumberOfDisableThreads() throws DatabaseException {
0699:
0700: return getNumberOfBeans(false, false, false, -1, -1, -1);
0701: }
0702:
0703: private int getNumberOfBeans(boolean enable, boolean onlyNoReply,
0704: boolean checkForumStatus, int status, int category,
0705: int forum) throws DatabaseException {
0706:
0707: Connection connection = null;
0708: PreparedStatement statement = null;
0709: ResultSet resultSet = null;
0710: StringBuffer sql = new StringBuffer(512);
0711: sql.append("SELECT Count(*)");
0712: sql.append(" FROM " + TABLE_NAME + " thread");
0713: if (checkForumStatus) {
0714: sql.append(" , " + ForumDAO.TABLE_NAME + " forum");
0715: }
0716: if (enable) {
0717: sql.append(" WHERE ThreadStatus <> 1 ");
0718: } else {//disable
0719: sql.append(" WHERE ThreadStatus = 1 ");
0720: }
0721: if (onlyNoReply) {
0722: sql.append(" AND ThreadReplyCount = 0 ");
0723: }
0724: if (checkForumStatus) {
0725: sql
0726: .append(" AND (forum.ForumID = thread.ForumID AND forum.ForumStatus <> 1)");
0727: }
0728: if (status > -1) {
0729: sql.append(" AND ThreadStatus = ?");
0730: } else {
0731: AssertionUtil.doAssert(status >= -1,
0732: "Assertion in ThreadDAOImplJDBC.getNumberOfBeans.");
0733: }
0734: if ((category > -1) && (forum > -1)) {
0735: throw new AssertionError(
0736: "Assertion in ThreadDAOImplJDBC.getNumberOfBeans.");
0737: }
0738: if (category > -1) {
0739: sql
0740: .append(" AND (forum.ForumID = thread.ForumID AND forum.CategoryID = ?) ");
0741: } else if (forum > -1) {
0742: sql
0743: .append(" AND (forum.ForumID = thread.ForumID AND thread.ForumID = ?) ");
0744: }
0745:
0746: try {
0747: connection = DBUtils.getConnection();
0748: statement = connection.prepareStatement(sql.toString());
0749:
0750: if (status > -1) {
0751: statement.setInt(1, status);
0752: if (category > -1) {
0753: statement.setInt(2, category);
0754: } else if (forum > -1) {
0755: statement.setInt(2, forum);
0756: }
0757: } else {
0758: if (category > -1) {
0759: statement.setInt(1, category);
0760: } else if (forum > -1) {
0761: statement.setInt(1, forum);
0762: }
0763: }
0764:
0765: resultSet = statement.executeQuery();
0766: AssertionUtil.doAssert(resultSet.next(),
0767: "Assertion in ThreadDAOImplJDBC.getNumberOfBeans.");
0768: return resultSet.getInt(1);
0769: } catch (SQLException sqle) {
0770: log.error("Sql Execution Error!", sqle);
0771: throw new DatabaseException(
0772: "Error executing SQL in ThreadDAOImplJDBC.getNumberOfBeans.");
0773: } finally {
0774: DBUtils.closeResultSet(resultSet);
0775: DBUtils.closeStatement(statement);
0776: DBUtils.closeConnection(connection);
0777: }
0778: }
0779:
0780: /************************************************
0781: * Customized methods come below
0782: ************************************************/
0783:
0784: /**
0785: * This is a customized method
0786: */
0787: private int findThreadID(int forumID, String memberName,
0788: Timestamp threadCreationDate)
0789: throws ObjectNotFoundException, DatabaseException {
0790:
0791: Connection connection = null;
0792: PreparedStatement statement = null;
0793: ResultSet resultSet = null;
0794: StringBuffer sql = new StringBuffer(512);
0795: sql.append("SELECT ThreadID");
0796: sql.append(" FROM " + TABLE_NAME);
0797: sql
0798: .append(" WHERE ForumID = ? AND MemberName = ? AND ThreadCreationDate = ? ");
0799: try {
0800: connection = DBUtils.getConnection();
0801: statement = connection.prepareStatement(sql.toString());
0802: statement.setInt(1, forumID);
0803: statement.setString(2, memberName);
0804: statement.setTimestamp(3, threadCreationDate);
0805: resultSet = statement.executeQuery();
0806: if (!resultSet.next()) {
0807: throw new ObjectNotFoundException(
0808: "Cannot find the ThreadID in table Thread.");
0809: }
0810:
0811: return resultSet.getInt("ThreadID");
0812: } catch (SQLException sqle) {
0813: log.error("Sql Execution Error!", sqle);
0814: throw new DatabaseException(
0815: "Error executing SQL in ThreadDAOImplJDBC.findThreadID.");
0816: } finally {
0817: DBUtils.closeResultSet(resultSet);
0818: DBUtils.closeStatement(statement);
0819: DBUtils.closeConnection(connection);
0820: }
0821: }
0822:
0823: /*
0824: * Included columns: ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic,
0825: * ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate,
0826: * ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount,
0827: * ThreadReplyCount, ThreadIcon, ThreadDuration
0828: * Excluded columns:
0829: */
0830: public Collection getThreads_inFavorite_inMember(int memberID)
0831: throws DatabaseException {
0832:
0833: Connection connection = null;
0834: PreparedStatement statement = null;
0835: ResultSet resultSet = null;
0836: Collection retValue = new ArrayList();
0837: StringBuffer sql = new StringBuffer(512);
0838: sql
0839: .append("SELECT thread.ThreadID, thread.ForumID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadPriority, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration, ThreadAttachCount");
0840: sql.append(" FROM " + TABLE_NAME + " thread, "
0841: + FavoriteThreadDAO.TABLE_NAME + " favorite ");
0842: sql
0843: .append(" WHERE thread.ThreadID = favorite.ThreadID AND favorite.MemberID = ? ");
0844: //sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
0845: try {
0846: connection = DBUtils.getConnection();
0847: statement = connection.prepareStatement(sql.toString());
0848: statement.setInt(1, memberID);
0849: resultSet = statement.executeQuery();
0850: while (resultSet.next()) {
0851: ThreadBean bean = new ThreadBean();
0852: bean.setThreadID(resultSet.getInt("ThreadID"));
0853: bean.setForumID(resultSet.getInt("ForumID"));
0854: bean.setMemberName(resultSet.getString("MemberName"));
0855: bean.setLastPostMemberName(resultSet
0856: .getString("LastPostMemberName"));
0857: bean.setThreadTopic(resultSet.getString("ThreadTopic"));
0858: bean.setThreadBody(resultSet.getString("ThreadBody"));
0859: bean.setThreadVoteCount(resultSet
0860: .getInt("ThreadVoteCount"));
0861: bean.setThreadVoteTotalStars(resultSet
0862: .getInt("ThreadVoteTotalStars"));
0863: bean.setThreadCreationDate(resultSet
0864: .getTimestamp("ThreadCreationDate"));
0865: bean.setThreadLastPostDate(resultSet
0866: .getTimestamp("ThreadLastPostDate"));
0867: bean.setThreadType(resultSet.getInt("ThreadType"));
0868: bean.setThreadPriority(resultSet
0869: .getInt("ThreadPriority"));
0870: bean.setThreadOption(resultSet.getInt("ThreadOption"));
0871: bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
0872: bean
0873: .setThreadHasPoll(resultSet
0874: .getInt("ThreadHasPoll"));
0875: bean.setThreadViewCount(resultSet
0876: .getInt("ThreadViewCount"));
0877: bean.setThreadReplyCount(resultSet
0878: .getInt("ThreadReplyCount"));
0879: bean.setThreadIcon(resultSet.getString("ThreadIcon"));
0880: bean.setThreadDuration(resultSet
0881: .getInt("ThreadDuration"));
0882: bean.setThreadAttachCount(resultSet
0883: .getInt("ThreadAttachCount"));
0884: retValue.add(bean);
0885: }
0886: return retValue;
0887: } catch (SQLException sqle) {
0888: log.error("Sql Execution Error!", sqle);
0889: throw new DatabaseException(
0890: "Error executing SQL in ThreadDAOImplJDBC.getThreads_inFavorite_inMember.");
0891: } finally {
0892: DBUtils.closeResultSet(resultSet);
0893: DBUtils.closeStatement(statement);
0894: DBUtils.closeConnection(connection);
0895: }
0896: }
0897:
0898: public Collection getEnableThreads_withSortSupport_limit(
0899: int offset, int rowsToReturn, String sort, String order,
0900: boolean onlyNoReply, int status, int category, int forum)
0901: throws IllegalArgumentException, DatabaseException {
0902:
0903: if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
0904: return getBeans_withSortSupport_limit_mysql(offset,
0905: rowsToReturn, sort, order, true, onlyNoReply, true,
0906: status, category, forum);
0907: } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
0908: return getBeans_withSortSupport_limit_noscroll(offset,
0909: rowsToReturn, sort, order, true, onlyNoReply, true,
0910: status, category, forum);
0911: }
0912: return getBeans_withSortSupport_limit_general(offset,
0913: rowsToReturn, sort, order, true, onlyNoReply, true,
0914: status, category, forum);
0915: }
0916:
0917: public Collection getEnableThreads_withSortSupport_limit(
0918: int offset, int rowsToReturn, String sort, String order,
0919: Timestamp from, Timestamp to)
0920: throws IllegalArgumentException, DatabaseException {
0921:
0922: if (offset < 0)
0923: throw new IllegalArgumentException(
0924: "The offset < 0 is not allowed.");
0925: if (rowsToReturn <= 0)
0926: throw new IllegalArgumentException(
0927: "The rowsToReturn <= 0 is not allowed.");
0928:
0929: if ((!sort.equals("ThreadLastPostDate"))
0930: && (!sort.equals("ThreadCreationDate"))
0931: && (!sort.equals("MemberName"))
0932: && (!sort.equals("ThreadReplyCount"))
0933: && (!sort.equals("ThreadPriority"))
0934: && (!sort.equals("ThreadTopic"))
0935: && (!sort.equals("ThreadViewCount"))) {
0936: throw new IllegalArgumentException(
0937: "Cannot sort, reason: dont understand the criteria '"
0938: + sort + "'.");
0939: }
0940:
0941: if ((!order.equals("ASC")) && (!order.equals("DESC"))) {
0942: throw new IllegalArgumentException(
0943: "Cannot sort, reason: dont understand the order '"
0944: + order + "'.");
0945: }
0946:
0947: if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
0948: return getBeans_withSortSupport_limit_mysql(offset,
0949: rowsToReturn, sort, order, from, to);
0950: } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
0951: return getBeans_withSortSupport_limit_noscroll(offset,
0952: rowsToReturn, sort, order, from, to);
0953: }
0954: return getBeans_withSortSupport_limit_general(offset,
0955: rowsToReturn, sort, order, from, to);
0956: }
0957:
0958: public Collection getDisableBeans_withSortSupport_limit(int offset,
0959: int rowsToReturn, String sort, String order)
0960: throws IllegalArgumentException, DatabaseException {
0961:
0962: if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
0963: return getBeans_withSortSupport_limit_mysql(offset,
0964: rowsToReturn, sort, order, false, false, false, -1,
0965: -1, -1);
0966: } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
0967: return getBeans_withSortSupport_limit_noscroll(offset,
0968: rowsToReturn, sort, order, false, false, false, -1,
0969: -1, -1);
0970: }
0971: return getBeans_withSortSupport_limit_general(offset,
0972: rowsToReturn, sort, order, false, false, false, -1, -1,
0973: -1);
0974: }
0975:
0976: /*
0977: * Included columns: ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic,
0978: * ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate,
0979: * ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount,
0980: * ThreadReplyCount, ThreadIcon, ThreadDuration
0981: * Excluded columns:
0982: */
0983: private Collection getBeans_withSortSupport_limit_mysql(int offset,
0984: int rowsToReturn, String sort, String order,
0985: boolean enable, boolean onlyNoReply,
0986: boolean checkForumStatus, int status, int category,
0987: int forum) throws IllegalArgumentException,
0988: DatabaseException {
0989:
0990: if (offset < 0)
0991: throw new IllegalArgumentException(
0992: "The offset < 0 is not allowed.");
0993: if (rowsToReturn <= 0)
0994: throw new IllegalArgumentException(
0995: "The rowsToReturn <= 0 is not allowed.");
0996:
0997: if ((!sort.equals("ThreadLastPostDate"))
0998: && (!sort.equals("ThreadCreationDate"))
0999: && (!sort.equals("MemberName"))
1000: && (!sort.equals("ThreadReplyCount"))
1001: && (!sort.equals("ForumID"))
1002: && (!sort.equals("ThreadPriority"))
1003: && (!sort.equals("ThreadViewCount"))) {
1004: throw new IllegalArgumentException(
1005: "Cannot sort, reason: dont understand the criteria '"
1006: + sort + "'.");
1007: }
1008:
1009: if ((!order.equals("ASC")) && (!order.equals("DESC"))) {
1010: throw new IllegalArgumentException(
1011: "Cannot sort, reason: dont understand the order '"
1012: + order + "'.");
1013: }
1014:
1015: Connection connection = null;
1016: PreparedStatement statement = null;
1017: ResultSet resultSet = null;
1018: Collection retValue = new ArrayList();
1019: StringBuffer sql = new StringBuffer(512);
1020:
1021: sql
1022: .append("SELECT ThreadID, thread.ForumID, MemberName, thread.LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType,ThreadPriority, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration, ThreadAttachCount");
1023: sql.append(" FROM " + TABLE_NAME + " thread");
1024: if (checkForumStatus) {
1025: sql.append(", " + ForumDAO.TABLE_NAME + " forum");
1026: }
1027: if (enable) {
1028: sql.append(" WHERE ThreadStatus <> 1 ");
1029: } else {// disable
1030: sql.append(" WHERE ThreadStatus = 1 ");
1031: }
1032: if (onlyNoReply) {
1033: sql.append(" AND ThreadReplyCount = 0 ");
1034: }
1035: if (checkForumStatus) {
1036: sql
1037: .append(" AND (forum.ForumID = thread.ForumID AND forum.ForumStatus <> "
1038: + ForumBean.FORUM_STATUS_DISABLED + ")");
1039: }
1040: if (status > -1) {
1041: sql.append(" AND ThreadStatus = ?");
1042: } else {
1043: AssertionUtil
1044: .doAssert(status >= -1,
1045: "Assertion in ThreadDAOImplJDBC.getBeans_withSortSupport_limit_mysql.");
1046: }
1047: if ((category > -1) && (forum > -1)) {
1048: throw new AssertionError(
1049: "Assertion in ThreadDAOImplJDBC.getBeans_withSortSupport_limit_mysql.");
1050: } else if (category > -1) {
1051: sql
1052: .append(" AND (forum.ForumID = thread.ForumID AND forum.CategoryID = ?) ");
1053: } else if (forum > -1) {
1054: sql
1055: .append(" AND (forum.ForumID = thread.ForumID AND thread.ForumID = ?) ");
1056: }
1057: sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
1058: sql.append(" LIMIT ?, ?");
1059: try {
1060: connection = DBUtils.getConnection();
1061: statement = connection.prepareStatement(sql.toString());
1062: //ERROR HERE, CHECK AGAIN
1063: int i = 0;
1064: if (status > -1) {
1065: statement.setInt(++i, status);
1066: if (category > -1) {
1067: statement.setInt(++i, category);
1068: } else if (forum > -1) {
1069: statement.setInt(++i, forum);
1070: }
1071: } else {
1072: if (category > -1) {
1073: statement.setInt(++i, category);
1074: } else if (forum > -1) {
1075: statement.setInt(++i, forum);
1076: }
1077: }
1078: statement.setInt(++i, offset);
1079: statement.setInt(++i, rowsToReturn);
1080: resultSet = statement.executeQuery();
1081: while (resultSet.next()) {
1082: ThreadBean bean = new ThreadBean();
1083: bean.setThreadID(resultSet.getInt("ThreadID"));
1084: bean.setForumID(resultSet.getInt("ForumID"));
1085: bean.setMemberName(resultSet.getString("MemberName"));
1086: bean.setLastPostMemberName(resultSet
1087: .getString("LastPostMemberName"));
1088: bean.setThreadTopic(resultSet.getString("ThreadTopic"));
1089: bean.setThreadBody(resultSet.getString("ThreadBody"));
1090: bean.setThreadVoteCount(resultSet
1091: .getInt("ThreadVoteCount"));
1092: bean.setThreadVoteTotalStars(resultSet
1093: .getInt("ThreadVoteTotalStars"));
1094: bean.setThreadCreationDate(resultSet
1095: .getTimestamp("ThreadCreationDate"));
1096: bean.setThreadLastPostDate(resultSet
1097: .getTimestamp("ThreadLastPostDate"));
1098: bean.setThreadType(resultSet.getInt("ThreadType"));
1099: bean.setThreadPriority(resultSet
1100: .getInt("ThreadPriority"));
1101: bean.setThreadOption(resultSet.getInt("ThreadOption"));
1102: bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
1103: bean
1104: .setThreadHasPoll(resultSet
1105: .getInt("ThreadHasPoll"));
1106: bean.setThreadViewCount(resultSet
1107: .getInt("ThreadViewCount"));
1108: bean.setThreadReplyCount(resultSet
1109: .getInt("ThreadReplyCount"));
1110: bean.setThreadIcon(resultSet.getString("ThreadIcon"));
1111: bean.setThreadDuration(resultSet
1112: .getInt("ThreadDuration"));
1113: bean.setThreadAttachCount(resultSet
1114: .getInt("ThreadAttachCount"));
1115: retValue.add(bean);
1116: }
1117: return retValue;
1118: } catch (SQLException sqle) {
1119: log.error("Sql Execution Error!", sqle);
1120: throw new DatabaseException(
1121: "Error executing SQL in ThreadDAOImplJDBC.getBeans_withSortSupport_limit_mysql.");
1122: } finally {
1123: DBUtils.closeResultSet(resultSet);
1124: DBUtils.closeStatement(statement);
1125: DBUtils.closeConnection(connection);
1126: }
1127: }
1128:
1129: private Collection getBeans_withSortSupport_limit_mysql(int offset,
1130: int rowsToReturn, String sort, String order,
1131: Timestamp from, Timestamp to)
1132: throws IllegalArgumentException, DatabaseException {
1133:
1134: Connection connection = null;
1135: PreparedStatement statement = null;
1136: ResultSet resultSet = null;
1137: Collection retValue = new ArrayList();
1138: StringBuffer sql = new StringBuffer(512);
1139:
1140: sql
1141: .append("SELECT t.ThreadID, t.MemberName, t.LastPostMemberName, t.ThreadCreationDate, t.ThreadLastPostDate, t.ThreadViewCount, t.ThreadReplyCount, t.ThreadType, t.ThreadStatus, t.ThreadPriority, t.ThreadTopic, t.ThreadAttachCount");// postgreSQL need AS
1142: sql.append(" FROM ").append(TABLE_NAME).append(" p , ").append(
1143: ThreadDAO.TABLE_NAME).append(" t,").append(
1144: ForumDAO.TABLE_NAME).append(" f");
1145: sql.append(" WHERE (t.ThreadID = p.ThreadID");
1146: sql.append(" AND p.ForumID = f.ForumID");
1147: sql
1148: .append(" AND t.ThreadCreationDate >= ? AND t.ThreadCreationDate <= ?)");
1149: sql
1150: .append(" GROUP BY t.ThreadID, t.MemberName, t.LastPostMemberName, t.ThreadCreationDate, t.ThreadLastPostDate, t.ThreadViewCount, t.ThreadReplyCount, t.ThreadStatus, t.ThreadType, t.ThreadPriority, t.ThreadTopic, t.ThreadAttachCount");
1151: sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
1152: sql.append(" LIMIT ?, ?");
1153: try {
1154: connection = DBUtils.getConnection();
1155: statement = connection.prepareStatement(sql.toString());
1156: statement.setTimestamp(1, from);
1157: statement.setTimestamp(2, to);
1158: statement.setInt(3, offset);
1159: statement.setInt(4, rowsToReturn);
1160: resultSet = statement.executeQuery();
1161: while (resultSet.next()) {
1162: ThreadBean bean = new ThreadBean();
1163: bean.setThreadID(resultSet.getInt("ThreadID"));
1164: bean.setMemberName(resultSet.getString("MemberName"));
1165: bean.setLastPostMemberName(resultSet
1166: .getString("LastPostMemberName"));
1167: bean.setThreadTopic(resultSet.getString("ThreadTopic"));
1168: bean.setThreadCreationDate(resultSet
1169: .getTimestamp("ThreadCreationDate"));
1170: bean.setThreadLastPostDate(resultSet
1171: .getTimestamp("ThreadLastPostDate"));
1172: bean.setThreadType(resultSet.getInt("ThreadType"));
1173: bean.setThreadPriority(resultSet
1174: .getInt("ThreadPriority"));
1175: bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
1176: bean.setThreadViewCount(resultSet
1177: .getInt("ThreadViewCount"));
1178: bean.setThreadReplyCount(resultSet
1179: .getInt("ThreadReplyCount"));
1180: bean.setThreadAttachCount(resultSet
1181: .getInt("ThreadAttachCount"));
1182: retValue.add(bean);
1183: }
1184: return retValue;
1185: } catch (SQLException sqle) {
1186: log.error("Sql Execution Error!", sqle);
1187: throw new DatabaseException(
1188: "Error executing SQL in ThreadDAOImplJDBC.getBeans_withSortSupport_limit_mysql.");
1189: } finally {
1190: DBUtils.closeResultSet(resultSet);
1191: DBUtils.closeStatement(statement);
1192: DBUtils.closeConnection(connection);
1193: }
1194: }
1195:
1196: /*
1197: * Included columns: ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic,
1198: * ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate,
1199: * ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount,
1200: * ThreadReplyCount, ThreadIcon, ThreadDuration
1201: * Excluded columns:
1202: */
1203: private Collection getBeans_withSortSupport_limit_noscroll(
1204: int offset, int rowsToReturn, String sort, String order,
1205: boolean enable, boolean onlyNoReply,
1206: boolean checkForumStatus, int status, int category,
1207: int forum) throws IllegalArgumentException,
1208: DatabaseException {
1209:
1210: if (offset < 0)
1211: throw new IllegalArgumentException(
1212: "The offset < 0 is not allowed.");
1213: if (rowsToReturn <= 0)
1214: throw new IllegalArgumentException(
1215: "The rowsToReturn <= 0 is not allowed.");
1216:
1217: if ((!sort.equals("ThreadLastPostDate"))
1218: && (!sort.equals("ThreadCreationDate"))
1219: && (!sort.equals("MemberName"))
1220: && (!sort.equals("ThreadReplyCount"))
1221: && (!sort.equals("ForumID"))
1222: && (!sort.equals("ThreadViewCount"))) {
1223: throw new IllegalArgumentException(
1224: "Cannot sort, reason: dont understand the criteria '"
1225: + sort + "'.");
1226: }
1227:
1228: if ((!order.equals("ASC")) && (!order.equals("DESC"))) {
1229: throw new IllegalArgumentException(
1230: "Cannot sort, reason: dont understand the order '"
1231: + order + "'.");
1232: }
1233:
1234: Connection connection = null;
1235: PreparedStatement statement = null;
1236: ResultSet resultSet = null;
1237: Collection retValue = new ArrayList();
1238: StringBuffer sql = new StringBuffer(512);
1239: sql
1240: .append("SELECT ThreadID, thread.ForumID, MemberName, thread.LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadPriority, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration, ThreadAttachCount");
1241: sql.append(" FROM " + TABLE_NAME + " thread");
1242: if (checkForumStatus) {
1243: sql.append(", " + ForumDAO.TABLE_NAME + " forum");
1244: }
1245: if (enable) {
1246: sql.append(" WHERE ThreadStatus <> 1 ");
1247: } else {// disable
1248: sql.append(" WHERE ThreadStatus = 1 ");
1249: }
1250: if (onlyNoReply) {
1251: sql.append(" AND ThreadReplyCount = 0 ");
1252: }
1253: if (checkForumStatus) {
1254: sql
1255: .append(" AND (forum.ForumID = thread.ForumID AND forum.ForumStatus <> "
1256: + ForumBean.FORUM_STATUS_DISABLED + ")");
1257: }
1258: if (status > -1) {
1259: sql.append(" AND ThreadStatus = ?");
1260: } else {
1261: AssertionUtil
1262: .doAssert(status >= -1,
1263: "Assertion in ThreadDAOImplJDBC.getBeans_withSortSupport_limit_noscroll.");
1264: }
1265: if ((category > -1) && (forum > -1)) {
1266: throw new AssertionError(
1267: "Assertion in ThreadDAOImplJDBC.getBeans_withSortSupport_limit_noscroll.");
1268: }
1269: if (category > -1) {
1270: sql
1271: .append(" AND (forum.ForumID = thread.ForumID AND forum.CategoryID = ?) ");
1272: } else if (forum > -1) {
1273: sql
1274: .append(" AND (forum.ForumID = thread.ForumID AND thread.ForumID = ?) ");
1275: }
1276: sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
1277: try {
1278: connection = DBUtils.getConnection();
1279: statement = connection.prepareStatement(sql.toString());
1280: if (status > -1) {
1281: statement.setInt(1, status);
1282: if (category > -1) {
1283: statement.setInt(2, category);
1284: } else if (forum > -1) {
1285: statement.setInt(2, forum);
1286: }
1287: } else {
1288: if (category > -1) {
1289: statement.setInt(1, category);
1290: } else if (forum > -1) {
1291: statement.setInt(1, forum);
1292: }
1293: }
1294:
1295: statement.setMaxRows(offset + rowsToReturn);
1296: resultSet = statement.executeQuery();
1297: int rowIndex = -1;
1298: while (resultSet.next()) {
1299: rowIndex++;
1300: if (rowIndex < offset)
1301: continue;
1302: ThreadBean bean = new ThreadBean();
1303: bean.setThreadID(resultSet.getInt("ThreadID"));
1304: bean.setForumID(resultSet.getInt("ForumID"));
1305: bean.setMemberName(resultSet.getString("MemberName"));
1306: bean.setLastPostMemberName(resultSet
1307: .getString("LastPostMemberName"));
1308: bean.setThreadTopic(resultSet.getString("ThreadTopic"));
1309: bean.setThreadBody(resultSet.getString("ThreadBody"));
1310: bean.setThreadVoteCount(resultSet
1311: .getInt("ThreadVoteCount"));
1312: bean.setThreadVoteTotalStars(resultSet
1313: .getInt("ThreadVoteTotalStars"));
1314: bean.setThreadCreationDate(resultSet
1315: .getTimestamp("ThreadCreationDate"));
1316: bean.setThreadLastPostDate(resultSet
1317: .getTimestamp("ThreadLastPostDate"));
1318: bean.setThreadType(resultSet.getInt("ThreadType"));
1319: bean.setThreadPriority(resultSet
1320: .getInt("ThreadPriority"));
1321: bean.setThreadOption(resultSet.getInt("ThreadOption"));
1322: bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
1323: bean
1324: .setThreadHasPoll(resultSet
1325: .getInt("ThreadHasPoll"));
1326: bean.setThreadViewCount(resultSet
1327: .getInt("ThreadViewCount"));
1328: bean.setThreadReplyCount(resultSet
1329: .getInt("ThreadReplyCount"));
1330: bean.setThreadIcon(resultSet.getString("ThreadIcon"));
1331: bean.setThreadDuration(resultSet
1332: .getInt("ThreadDuration"));
1333: bean.setThreadAttachCount(resultSet
1334: .getInt("ThreadAttachCount"));
1335: retValue.add(bean);
1336: if (retValue.size() == rowsToReturn)
1337: break;// Fix the Sybase bug
1338: }
1339: return retValue;
1340: } catch (SQLException sqle) {
1341: log.error("Sql Execution Error!", sqle);
1342: throw new DatabaseException(
1343: "Error executing SQL in ThreadDAOImplJDBC.getBeans_withSortSupport_limit_noscroll.");
1344: } finally {
1345: DBUtils.closeResultSet(resultSet);
1346: DBUtils.resetStatement(statement);
1347: DBUtils.closeStatement(statement);
1348: DBUtils.closeConnection(connection);
1349: }
1350: }
1351:
1352: private Collection getBeans_withSortSupport_limit_noscroll(
1353: int offset, int rowsToReturn, String sort, String order,
1354: Timestamp from, Timestamp to)
1355: throws IllegalArgumentException, DatabaseException {
1356:
1357: Connection connection = null;
1358: PreparedStatement statement = null;
1359: ResultSet resultSet = null;
1360: Collection retValue = new ArrayList();
1361: StringBuffer sql = new StringBuffer(512);
1362: sql
1363: .append("SELECT t.ThreadID, t.MemberName, t.LastPostMemberName, t.ThreadCreationDate, t.ThreadLastPostDate, t.ThreadViewCount, t.ThreadReplyCount, t.ThreadType, t.ThreadStatus, t.ThreadPriority, t.ThreadTopic, t.ThreadAttachCount");// postgreSQL need AS
1364: sql.append(" FROM ").append(TABLE_NAME).append(" p , ").append(
1365: ThreadDAO.TABLE_NAME).append(" t,").append(
1366: ForumDAO.TABLE_NAME).append(" f");
1367: sql.append(" WHERE (t.ThreadID = p.ThreadID");
1368: sql.append(" AND p.ForumID = f.ForumID");
1369: sql
1370: .append(" AND t.ThreadCreationDate >= ? AND t.ThreadCreationDate <= ?)");
1371: sql
1372: .append(" GROUP BY t.ThreadID, t.MemberName, t.LastPostMemberName, t.ThreadCreationDate, t.ThreadLastPostDate, t.ThreadViewCount, t.ThreadReplyCount, t.ThreadStatus, t.ThreadType, t.ThreadPriority, t.ThreadTopic, t.ThreadAttachCount");
1373: sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
1374: sql.append(" LIMIT ?, ?");
1375: try {
1376: connection = DBUtils.getConnection();
1377: statement = connection.prepareStatement(sql.toString());
1378: statement.setTimestamp(1, from);
1379: statement.setTimestamp(2, to);
1380: statement.setMaxRows(offset + rowsToReturn);
1381: resultSet = statement.executeQuery();
1382: int rowIndex = -1;
1383: while (resultSet.next()) {
1384: rowIndex++;
1385: if (rowIndex < offset)
1386: continue;
1387: ThreadBean bean = new ThreadBean();
1388: bean.setThreadID(resultSet.getInt("ThreadID"));
1389: bean.setMemberName(resultSet.getString("MemberName"));
1390: bean.setLastPostMemberName(resultSet
1391: .getString("LastPostMemberName"));
1392: bean.setThreadTopic(resultSet.getString("ThreadTopic"));
1393: bean.setThreadCreationDate(resultSet
1394: .getTimestamp("ThreadCreationDate"));
1395: bean.setThreadLastPostDate(resultSet
1396: .getTimestamp("ThreadLastPostDate"));
1397: bean.setThreadType(resultSet.getInt("ThreadType"));
1398: bean.setThreadPriority(resultSet
1399: .getInt("ThreadPriority"));
1400: bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
1401: bean.setThreadViewCount(resultSet
1402: .getInt("ThreadViewCount"));
1403: bean.setThreadReplyCount(resultSet
1404: .getInt("ThreadReplyCount"));
1405: bean.setThreadAttachCount(resultSet
1406: .getInt("ThreadAttachCount"));
1407: retValue.add(bean);
1408: if (retValue.size() == rowsToReturn)
1409: break;// Fix the Sybase bug
1410: }
1411: return retValue;
1412: } catch (SQLException sqle) {
1413: log.error("Sql Execution Error!", sqle);
1414: throw new DatabaseException(
1415: "Error executing SQL in ThreadDAOImplJDBC.getBeans_withSortSupport_limit_noscroll.");
1416: } finally {
1417: DBUtils.closeResultSet(resultSet);
1418: DBUtils.resetStatement(statement);
1419: DBUtils.closeStatement(statement);
1420: DBUtils.closeConnection(connection);
1421: }
1422: }
1423:
1424: /*
1425: * Included columns: ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic,
1426: * ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate,
1427: * ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount,
1428: * ThreadReplyCount, ThreadIcon, ThreadDuration
1429: * Excluded columns:
1430: */
1431: private Collection getBeans_withSortSupport_limit_general(
1432: int offset, int rowsToReturn, String sort, String order,
1433: boolean enable, boolean onlyNoReply,
1434: boolean checkForumStatus, int status, int category,
1435: int forum) throws IllegalArgumentException,
1436: DatabaseException {
1437:
1438: if (offset < 0)
1439: throw new IllegalArgumentException(
1440: "The offset < 0 is not allowed.");
1441: if (rowsToReturn <= 0)
1442: throw new IllegalArgumentException(
1443: "The rowsToReturn <= 0 is not allowed.");
1444:
1445: if ((!sort.equals("ThreadLastPostDate"))
1446: && (!sort.equals("ThreadCreationDate"))
1447: && (!sort.equals("MemberName"))
1448: && (!sort.equals("ThreadReplyCount"))
1449: && (!sort.equals("ForumID"))
1450: && (!sort.equals("ThreadViewCount"))) {
1451: throw new IllegalArgumentException(
1452: "Cannot sort, reason: dont understand the criteria '"
1453: + sort + "'.");
1454: }
1455:
1456: if ((!order.equals("ASC")) && (!order.equals("DESC"))) {
1457: throw new IllegalArgumentException(
1458: "Cannot sort, reason: dont understand the order '"
1459: + order + "'.");
1460: }
1461:
1462: Connection connection = null;
1463: PreparedStatement statement = null;
1464: ResultSet resultSet = null;
1465: Collection retValue = new ArrayList();
1466: StringBuffer sql = new StringBuffer(512);
1467: sql
1468: .append("SELECT ThreadID, thread.ForumID, MemberName, thread.LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadPriority, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration, ThreadAttachCount");
1469: sql.append(" FROM " + TABLE_NAME + " thread");
1470: if (checkForumStatus) {
1471: sql.append(", " + ForumDAO.TABLE_NAME + " forum");
1472: }
1473: if (enable) {
1474: sql.append(" WHERE ThreadStatus <> 1 ");
1475: } else {// disable
1476: sql.append(" WHERE ThreadStatus = 1 ");
1477: }
1478: if (onlyNoReply) {
1479: sql.append(" AND ThreadReplyCount = 0 ");
1480: }
1481: if (checkForumStatus) {
1482: sql
1483: .append(" AND (forum.ForumID = thread.ForumID AND forum.ForumStatus <> "
1484: + ForumBean.FORUM_STATUS_DISABLED + ")");
1485: }
1486: if (status > -1) {
1487: sql.append(" AND ThreadStatus = ?");
1488: } else {
1489: AssertionUtil
1490: .doAssert(status >= -1,
1491: "Assertion in ThreadDAOImplJDBC.getBeans_withSortSupport_limit_general.");
1492: }
1493: if ((category > -1) && (forum > -1)) {
1494: throw new AssertionError(
1495: "Assertion in ThreadDAOImplJDBC.getBeans_withSortSupport_limit_general.");
1496: }
1497: if (category > -1) {
1498: sql
1499: .append(" AND (forum.ForumID = thread.ForumID AND forum.CategoryID = ?) ");
1500: } else if (forum > -1) {
1501: sql
1502: .append(" AND (forum.ForumID = thread.ForumID AND thread.ForumID = ?) ");
1503: }
1504: sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
1505: try {
1506: connection = DBUtils.getConnection();
1507: statement = connection.prepareStatement(sql.toString(),
1508: ResultSet.TYPE_SCROLL_INSENSITIVE,
1509: ResultSet.CONCUR_READ_ONLY);
1510:
1511: if (status > -1) {
1512: statement.setInt(1, status);
1513: if (category > -1) {
1514: statement.setInt(2, category);
1515: } else if (forum > -1) {
1516: statement.setInt(2, forum);
1517: }
1518: } else {
1519: if (category > -1) {
1520: statement.setInt(1, category);
1521: } else if (forum > -1) {
1522: statement.setInt(1, forum);
1523: }
1524: }
1525:
1526: statement.setMaxRows(offset + rowsToReturn);
1527: try {
1528: statement.setFetchSize(Math.min(rowsToReturn,
1529: DBUtils.MAX_FETCH_SIZE));
1530: } catch (SQLException sqle) {
1531: //do nothing, postgreSQL does not support this method
1532: }
1533: resultSet = statement.executeQuery();
1534: boolean loop = resultSet.absolute(offset + 1);// the absolute method begin with 1 instead of 0 as in the LIMIT clause
1535: while (loop) {
1536: ThreadBean bean = new ThreadBean();
1537: bean.setThreadID(resultSet.getInt("ThreadID"));
1538: bean.setForumID(resultSet.getInt("ForumID"));
1539: bean.setMemberName(resultSet.getString("MemberName"));
1540: bean.setLastPostMemberName(resultSet
1541: .getString("LastPostMemberName"));
1542: bean.setThreadTopic(resultSet.getString("ThreadTopic"));
1543: bean.setThreadBody(resultSet.getString("ThreadBody"));
1544: bean.setThreadVoteCount(resultSet
1545: .getInt("ThreadVoteCount"));
1546: bean.setThreadVoteTotalStars(resultSet
1547: .getInt("ThreadVoteTotalStars"));
1548: bean.setThreadCreationDate(resultSet
1549: .getTimestamp("ThreadCreationDate"));
1550: bean.setThreadLastPostDate(resultSet
1551: .getTimestamp("ThreadLastPostDate"));
1552: bean.setThreadType(resultSet.getInt("ThreadType"));
1553: bean.setThreadPriority(resultSet
1554: .getInt("ThreadPriority"));
1555: bean.setThreadOption(resultSet.getInt("ThreadOption"));
1556: bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
1557: bean
1558: .setThreadHasPoll(resultSet
1559: .getInt("ThreadHasPoll"));
1560: bean.setThreadViewCount(resultSet
1561: .getInt("ThreadViewCount"));
1562: bean.setThreadReplyCount(resultSet
1563: .getInt("ThreadReplyCount"));
1564: bean.setThreadIcon(resultSet.getString("ThreadIcon"));
1565: bean.setThreadDuration(resultSet
1566: .getInt("ThreadDuration"));
1567: bean.setThreadAttachCount(resultSet
1568: .getInt("ThreadAttachCount"));
1569: retValue.add(bean);
1570: if (retValue.size() == rowsToReturn)
1571: break;// Fix the Sybase bug
1572: loop = resultSet.next();
1573: }//while
1574: return retValue;
1575: } catch (SQLException sqle) {
1576: log.error("Sql Execution Error!", sqle);
1577: throw new DatabaseException(
1578: "Error executing SQL in ThreadDAOImplJDBC.getBeans_withSortSupport_limit_general.");
1579: } finally {
1580: DBUtils.closeResultSet(resultSet);
1581: DBUtils.resetStatement(statement);
1582: DBUtils.closeStatement(statement);
1583: DBUtils.closeConnection(connection);
1584: }
1585: }
1586:
1587: private Collection getBeans_withSortSupport_limit_general(
1588: int offset, int rowsToReturn, String sort, String order,
1589: Timestamp from, Timestamp to)
1590: throws IllegalArgumentException, DatabaseException {
1591:
1592: Connection connection = null;
1593: PreparedStatement statement = null;
1594: ResultSet resultSet = null;
1595: Collection retValue = new ArrayList();
1596: StringBuffer sql = new StringBuffer(512);
1597: sql
1598: .append("SELECT t.ThreadID, t.MemberName, t.LastPostMemberName, t.ThreadCreationDate, t.ThreadLastPostDate, t.ThreadViewCount, t.ThreadReplyCount, t.ThreadType, t.ThreadStatus, t.ThreadPriority, t.ThreadTopic, t.ThreadAttachCount");// postgreSQL need AS
1599: sql.append(" FROM ").append(TABLE_NAME).append(" p , ").append(
1600: ThreadDAO.TABLE_NAME).append(" t,").append(
1601: ForumDAO.TABLE_NAME).append(" f");
1602: sql.append(" WHERE (t.ThreadID = p.ThreadID");
1603: sql.append(" AND p.ForumID = f.ForumID");
1604: sql
1605: .append(" AND t.ThreadCreationDate >= ? AND t.ThreadCreationDate <= ?)");
1606: sql
1607: .append(" GROUP BY t.ThreadID, t.MemberName, t.LastPostMemberName, t.ThreadCreationDate, t.ThreadLastPostDate, t.ThreadViewCount, t.ThreadReplyCount, t.ThreadStatus, t.ThreadType, t.ThreadPriority, t.ThreadTopic, t.ThreadAttachCount");
1608: sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
1609: try {
1610: connection = DBUtils.getConnection();
1611: statement = connection.prepareStatement(sql.toString(),
1612: ResultSet.TYPE_SCROLL_INSENSITIVE,
1613: ResultSet.CONCUR_READ_ONLY);
1614: statement.setTimestamp(1, from);
1615: statement.setTimestamp(2, to);
1616: statement.setMaxRows(offset + rowsToReturn);
1617: try {
1618: statement.setFetchSize(Math.min(rowsToReturn,
1619: DBUtils.MAX_FETCH_SIZE));
1620: } catch (SQLException sqle) {
1621: //do nothing, postgreSQL does not support this method
1622: }
1623: resultSet = statement.executeQuery();
1624: boolean loop = resultSet.absolute(offset + 1);// the absolute method begin with 1 instead of 0 as in the LIMIT clause
1625: while (loop) {
1626: ThreadBean bean = new ThreadBean();
1627: bean.setThreadID(resultSet.getInt("ThreadID"));
1628: bean.setMemberName(resultSet.getString("MemberName"));
1629: bean.setLastPostMemberName(resultSet
1630: .getString("LastPostMemberName"));
1631: bean.setThreadTopic(resultSet.getString("ThreadTopic"));
1632: bean.setThreadCreationDate(resultSet
1633: .getTimestamp("ThreadCreationDate"));
1634: bean.setThreadLastPostDate(resultSet
1635: .getTimestamp("ThreadLastPostDate"));
1636: bean.setThreadType(resultSet.getInt("ThreadType"));
1637: bean.setThreadPriority(resultSet
1638: .getInt("ThreadPriority"));
1639: bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
1640: bean.setThreadViewCount(resultSet
1641: .getInt("ThreadViewCount"));
1642: bean.setThreadReplyCount(resultSet
1643: .getInt("ThreadReplyCount"));
1644: bean.setThreadAttachCount(resultSet
1645: .getInt("ThreadAttachCount"));
1646: retValue.add(bean);
1647: if (retValue.size() == rowsToReturn)
1648: break;// Fix the Sybase bug
1649: loop = resultSet.next();
1650: }//while
1651: return retValue;
1652: } catch (SQLException sqle) {
1653: log.error("Sql Execution Error!", sqle);
1654: throw new DatabaseException(
1655: "Error executing SQL in ThreadDAOImplJDBC.getBeans_withSortSupport_limit_general.");
1656: } finally {
1657: DBUtils.closeResultSet(resultSet);
1658: DBUtils.resetStatement(statement);
1659: DBUtils.closeStatement(statement);
1660: DBUtils.closeConnection(connection);
1661: }
1662: }
1663:
1664: public Collection getNormalEnableThreads_inForum_withSortSupport_limit(
1665: int forumID, int offset, int rowsToReturn, String sort,
1666: String order) throws IllegalArgumentException,
1667: DatabaseException {
1668:
1669: if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
1670: return getBeans_inForum_withSortSupport_limit_mysql(
1671: forumID, offset, rowsToReturn, sort, order, true,
1672: true);
1673: } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
1674: return getBeans_inForum_withSortSupport_limit_noscroll(
1675: forumID, offset, rowsToReturn, sort, order, true,
1676: true);
1677: }
1678: return getBeans_inForum_withSortSupport_limit_general(forumID,
1679: offset, rowsToReturn, sort, order, true, true);
1680: }
1681:
1682: public Collection getAllEnableThreads_inForum_withSortSupport_limit(
1683: int forumID, int offset, int rowsToReturn, String sort,
1684: String order) throws IllegalArgumentException,
1685: DatabaseException {
1686:
1687: if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
1688: return getBeans_inForum_withSortSupport_limit_mysql(
1689: forumID, offset, rowsToReturn, sort, order, true,
1690: false);
1691: } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
1692: return getBeans_inForum_withSortSupport_limit_noscroll(
1693: forumID, offset, rowsToReturn, sort, order, true,
1694: false);
1695: }
1696: return getBeans_inForum_withSortSupport_limit_general(forumID,
1697: offset, rowsToReturn, sort, order, true, false);
1698: }
1699:
1700: public Collection getDisableThreads_inForum_withSortSupport_limit(
1701: int forumID, int offset, int rowsToReturn, String sort,
1702: String order) throws IllegalArgumentException,
1703: DatabaseException {
1704:
1705: if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
1706: return getBeans_inForum_withSortSupport_limit_mysql(
1707: forumID, offset, rowsToReturn, sort, order, false,
1708: false);
1709: } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
1710: return getBeans_inForum_withSortSupport_limit_noscroll(
1711: forumID, offset, rowsToReturn, sort, order, false,
1712: false);
1713: }
1714: return getBeans_inForum_withSortSupport_limit_general(forumID,
1715: offset, rowsToReturn, sort, order, false, false);
1716: }
1717:
1718: /*
1719: * Included columns: ThreadID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody,
1720: * ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType,
1721: * ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount,
1722: * ThreadIcon, ThreadDuration
1723: * Excluded columns: ForumID
1724: */
1725: /**
1726: * Note: This is a customized method
1727: */
1728: private Collection getBeans_inForum_withSortSupport_limit_mysql(
1729: int forumID, int offset, int rowsToReturn, String sort,
1730: String order, boolean enable, boolean onlyNormalThread)
1731: throws IllegalArgumentException, DatabaseException {
1732:
1733: if (offset < 0)
1734: throw new IllegalArgumentException(
1735: "The offset < 0 is not allowed.");
1736: if (rowsToReturn <= 0)
1737: throw new IllegalArgumentException(
1738: "The rowsToReturn <= 0 is not allowed.");
1739:
1740: if ((!sort.equals("ThreadLastPostDate"))
1741: && (!sort.equals("ThreadCreationDate"))
1742: && (!sort.equals("MemberName"))
1743: && (!sort.equals("ThreadReplyCount"))
1744: && (!sort.equals("ThreadPriority"))
1745: && (!sort.equals("ThreadViewCount"))) {
1746: throw new IllegalArgumentException(
1747: "Cannot sort, reason: dont understand the criteria '"
1748: + sort + "'.");
1749: }
1750:
1751: if ((!order.equals("ASC")) && (!order.equals("DESC"))) {
1752: throw new IllegalArgumentException(
1753: "Cannot sort, reason: dont understand the order '"
1754: + order + "'.");
1755: }
1756:
1757: Connection connection = null;
1758: PreparedStatement statement = null;
1759: ResultSet resultSet = null;
1760: Collection retValue = new ArrayList();
1761: StringBuffer sql = new StringBuffer(512);
1762: sql
1763: .append("SELECT ThreadID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadPriority, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration, ThreadAttachCount");
1764: sql.append(" FROM " + TABLE_NAME);
1765: sql.append(" WHERE ForumID = ? ");
1766: if (onlyNormalThread) {
1767: sql.append(" AND ThreadType = ").append(
1768: ThreadBean.THREAD_TYPE_DEFAULT);
1769: }
1770: if (enable) {
1771: sql.append(" AND ThreadStatus <> 1 ");
1772: } else {//disable
1773: sql.append(" AND ThreadStatus = 1 ");
1774: }
1775: sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
1776: sql.append(" LIMIT ?, ?");
1777: try {
1778: connection = DBUtils.getConnection();
1779: statement = connection.prepareStatement(sql.toString());
1780: statement.setInt(1, forumID);
1781: statement.setInt(2, offset);
1782: statement.setInt(3, rowsToReturn);
1783: resultSet = statement.executeQuery();
1784: while (resultSet.next()) {
1785: ThreadBean bean = new ThreadBean();
1786: bean.setThreadID(resultSet.getInt("ThreadID"));
1787: bean.setForumID(forumID);
1788: bean.setMemberName(resultSet.getString("MemberName"));
1789: bean.setLastPostMemberName(resultSet
1790: .getString("LastPostMemberName"));
1791: bean.setThreadTopic(resultSet.getString("ThreadTopic"));
1792: bean.setThreadBody(resultSet.getString("ThreadBody"));
1793: bean.setThreadVoteCount(resultSet
1794: .getInt("ThreadVoteCount"));
1795: bean.setThreadVoteTotalStars(resultSet
1796: .getInt("ThreadVoteTotalStars"));
1797: bean.setThreadCreationDate(resultSet
1798: .getTimestamp("ThreadCreationDate"));
1799: bean.setThreadLastPostDate(resultSet
1800: .getTimestamp("ThreadLastPostDate"));
1801: bean.setThreadType(resultSet.getInt("ThreadType"));
1802: bean.setThreadOption(resultSet.getInt("ThreadOption"));
1803: bean.setThreadPriority(resultSet
1804: .getInt("ThreadPriority"));
1805: bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
1806: bean
1807: .setThreadHasPoll(resultSet
1808: .getInt("ThreadHasPoll"));
1809: bean.setThreadViewCount(resultSet
1810: .getInt("ThreadViewCount"));
1811: bean.setThreadReplyCount(resultSet
1812: .getInt("ThreadReplyCount"));
1813: bean.setThreadIcon(resultSet.getString("ThreadIcon"));
1814: bean.setThreadDuration(resultSet
1815: .getInt("ThreadDuration"));
1816: bean.setThreadAttachCount(resultSet
1817: .getInt("ThreadAttachCount"));
1818: retValue.add(bean);
1819: }
1820: return retValue;
1821: } catch (SQLException sqle) {
1822: log.error("Sql Execution Error!", sqle);
1823: throw new DatabaseException(
1824: "Error executing SQL in ThreadDAOImplJDBC.getBeans_inForum_withSortSupport_limit_mysql.");
1825: } finally {
1826: DBUtils.closeResultSet(resultSet);
1827: DBUtils.closeStatement(statement);
1828: DBUtils.closeConnection(connection);
1829: }
1830: }
1831:
1832: /*
1833: * Included columns: ThreadID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody,
1834: * ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType,
1835: * ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount,
1836: * ThreadIcon, ThreadDuration
1837: * Excluded columns: ForumID
1838: */
1839: /**
1840: * Note: This is a customized method
1841: */
1842: private Collection getBeans_inForum_withSortSupport_limit_noscroll(
1843: int forumID, int offset, int rowsToReturn, String sort,
1844: String order, boolean enable, boolean onlyNormalThread)
1845: throws IllegalArgumentException, DatabaseException {
1846:
1847: if (offset < 0)
1848: throw new IllegalArgumentException(
1849: "The offset < 0 is not allowed.");
1850: if (rowsToReturn <= 0)
1851: throw new IllegalArgumentException(
1852: "The rowsToReturn <= 0 is not allowed.");
1853:
1854: if ((!sort.equals("ThreadLastPostDate"))
1855: && (!sort.equals("ThreadCreationDate"))
1856: && (!sort.equals("MemberName"))
1857: && (!sort.equals("ThreadReplyCount"))
1858: && (!sort.equals("ThreadPriority"))
1859: && (!sort.equals("ThreadViewCount"))) {
1860: throw new IllegalArgumentException(
1861: "Cannot sort, reason: dont understand the criteria '"
1862: + sort + "'.");
1863: }
1864:
1865: if ((!order.equals("ASC")) && (!order.equals("DESC"))) {
1866: throw new IllegalArgumentException(
1867: "Cannot sort, reason: dont understand the order '"
1868: + order + "'.");
1869: }
1870:
1871: Connection connection = null;
1872: PreparedStatement statement = null;
1873: ResultSet resultSet = null;
1874: Collection retValue = new ArrayList();
1875: StringBuffer sql = new StringBuffer(512);
1876: sql
1877: .append("SELECT ThreadID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadPriority, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration, ThreadAttachCount");
1878: sql.append(" FROM " + TABLE_NAME);
1879: sql.append(" WHERE ForumID = ? ");
1880: if (onlyNormalThread) {
1881: sql.append(" AND ThreadType = ").append(
1882: ThreadBean.THREAD_TYPE_DEFAULT);
1883: }
1884: if (enable) {
1885: sql.append(" AND ThreadStatus <> 1 ");
1886: } else {//disable
1887: sql.append(" AND ThreadStatus = 1 ");
1888: }
1889: sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
1890: try {
1891: connection = DBUtils.getConnection();
1892: statement = connection.prepareStatement(sql.toString());
1893: statement.setMaxRows(offset + rowsToReturn);
1894: statement.setInt(1, forumID);
1895: resultSet = statement.executeQuery();
1896: int rowIndex = -1;
1897: while (resultSet.next()) {
1898: rowIndex++;
1899: if (rowIndex < offset)
1900: continue;
1901: ThreadBean bean = new ThreadBean();
1902: bean.setThreadID(resultSet.getInt("ThreadID"));
1903: bean.setForumID(forumID);
1904: bean.setMemberName(resultSet.getString("MemberName"));
1905: bean.setLastPostMemberName(resultSet
1906: .getString("LastPostMemberName"));
1907: bean.setThreadTopic(resultSet.getString("ThreadTopic"));
1908: bean.setThreadBody(resultSet.getString("ThreadBody"));
1909: bean.setThreadVoteCount(resultSet
1910: .getInt("ThreadVoteCount"));
1911: bean.setThreadVoteTotalStars(resultSet
1912: .getInt("ThreadVoteTotalStars"));
1913: bean.setThreadCreationDate(resultSet
1914: .getTimestamp("ThreadCreationDate"));
1915: bean.setThreadLastPostDate(resultSet
1916: .getTimestamp("ThreadLastPostDate"));
1917: bean.setThreadType(resultSet.getInt("ThreadType"));
1918: bean.setThreadPriority(resultSet
1919: .getInt("ThreadPriority"));
1920: bean.setThreadOption(resultSet.getInt("ThreadOption"));
1921: bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
1922: bean
1923: .setThreadHasPoll(resultSet
1924: .getInt("ThreadHasPoll"));
1925: bean.setThreadViewCount(resultSet
1926: .getInt("ThreadViewCount"));
1927: bean.setThreadReplyCount(resultSet
1928: .getInt("ThreadReplyCount"));
1929: bean.setThreadIcon(resultSet.getString("ThreadIcon"));
1930: bean.setThreadDuration(resultSet
1931: .getInt("ThreadDuration"));
1932: bean.setThreadAttachCount(resultSet
1933: .getInt("ThreadAttachCount"));
1934: retValue.add(bean);
1935: if (retValue.size() == rowsToReturn)
1936: break;// Fix the Sybase bug
1937: }
1938: return retValue;
1939: } catch (SQLException sqle) {
1940: log.error("Sql Execution Error!", sqle);
1941: throw new DatabaseException(
1942: "Error executing SQL in ThreadDAOImplJDBC.getBeans_inForum_withSortSupport_limit_noscroll.");
1943: } finally {
1944: DBUtils.closeResultSet(resultSet);
1945: DBUtils.resetStatement(statement);
1946: DBUtils.closeStatement(statement);
1947: DBUtils.closeConnection(connection);
1948: }
1949: }
1950:
1951: /*
1952: * Included columns: ThreadID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody,
1953: * ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType,
1954: * ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount,
1955: * ThreadIcon, ThreadDuration
1956: * Excluded columns: ForumID
1957: */
1958: /**
1959: * Note: This is a customized method
1960: */
1961: private Collection getBeans_inForum_withSortSupport_limit_general(
1962: int forumID, int offset, int rowsToReturn, String sort,
1963: String order, boolean enable, boolean onlyNormalThread)
1964: throws IllegalArgumentException, DatabaseException {
1965:
1966: if (offset < 0)
1967: throw new IllegalArgumentException(
1968: "The offset < 0 is not allowed.");
1969: if (rowsToReturn <= 0)
1970: throw new IllegalArgumentException(
1971: "The rowsToReturn <= 0 is not allowed.");
1972:
1973: if ((!sort.equals("ThreadLastPostDate"))
1974: && (!sort.equals("ThreadCreationDate"))
1975: && (!sort.equals("MemberName"))
1976: && (!sort.equals("ThreadReplyCount"))
1977: && (!sort.equals("ThreadPriority"))
1978: && (!sort.equals("ThreadViewCount"))) {
1979: throw new IllegalArgumentException(
1980: "Cannot sort, reason: dont understand the criteria '"
1981: + sort + "'.");
1982: }
1983:
1984: if ((!order.equals("ASC")) && (!order.equals("DESC"))) {
1985: throw new IllegalArgumentException(
1986: "Cannot sort, reason: dont understand the order '"
1987: + order + "'.");
1988: }
1989:
1990: Connection connection = null;
1991: PreparedStatement statement = null;
1992: ResultSet resultSet = null;
1993: Collection retValue = new ArrayList();
1994: StringBuffer sql = new StringBuffer(512);
1995: sql
1996: .append("SELECT ThreadID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadPriority, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration, ThreadAttachCount");
1997: sql.append(" FROM " + TABLE_NAME);
1998: sql.append(" WHERE ForumID = ? ");
1999: if (onlyNormalThread) {
2000: sql.append(" AND ThreadType = ").append(
2001: ThreadBean.THREAD_TYPE_DEFAULT);
2002: }
2003: if (enable) {
2004: sql.append(" AND ThreadStatus <> 1 ");
2005: } else {//disable
2006: sql.append(" AND ThreadStatus = 1 ");
2007: }
2008: sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
2009: try {
2010: connection = DBUtils.getConnection();
2011: statement = connection.prepareStatement(sql.toString(),
2012: ResultSet.TYPE_SCROLL_INSENSITIVE,
2013: ResultSet.CONCUR_READ_ONLY);
2014: statement.setInt(1, forumID);
2015: statement.setMaxRows(offset + rowsToReturn);
2016: try {
2017: statement.setFetchSize(Math.min(rowsToReturn,
2018: DBUtils.MAX_FETCH_SIZE));
2019: } catch (SQLException sqle) {
2020: //do nothing, postgreSQL does not support this method
2021: }
2022: resultSet = statement.executeQuery();
2023: boolean loop = resultSet.absolute(offset + 1);// the absolute method begin with 1 instead of 0 as in the LIMIT clause
2024: while (loop) {
2025: ThreadBean bean = new ThreadBean();
2026: bean.setThreadID(resultSet.getInt("ThreadID"));
2027: bean.setForumID(forumID);
2028: bean.setMemberName(resultSet.getString("MemberName"));
2029: bean.setLastPostMemberName(resultSet
2030: .getString("LastPostMemberName"));
2031: bean.setThreadTopic(resultSet.getString("ThreadTopic"));
2032: bean.setThreadBody(resultSet.getString("ThreadBody"));
2033: bean.setThreadVoteCount(resultSet
2034: .getInt("ThreadVoteCount"));
2035: bean.setThreadVoteTotalStars(resultSet
2036: .getInt("ThreadVoteTotalStars"));
2037: bean.setThreadCreationDate(resultSet
2038: .getTimestamp("ThreadCreationDate"));
2039: bean.setThreadLastPostDate(resultSet
2040: .getTimestamp("ThreadLastPostDate"));
2041: bean.setThreadType(resultSet.getInt("ThreadType"));
2042: bean.setThreadPriority(resultSet
2043: .getInt("ThreadPriority"));
2044: bean.setThreadOption(resultSet.getInt("ThreadOption"));
2045: bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
2046: bean
2047: .setThreadHasPoll(resultSet
2048: .getInt("ThreadHasPoll"));
2049: bean.setThreadViewCount(resultSet
2050: .getInt("ThreadViewCount"));
2051: bean.setThreadReplyCount(resultSet
2052: .getInt("ThreadReplyCount"));
2053: bean.setThreadIcon(resultSet.getString("ThreadIcon"));
2054: bean.setThreadDuration(resultSet
2055: .getInt("ThreadDuration"));
2056: bean.setThreadAttachCount(resultSet
2057: .getInt("ThreadAttachCount"));
2058: retValue.add(bean);
2059: if (retValue.size() == rowsToReturn)
2060: break;// Fix the Sybase bug
2061: loop = resultSet.next();
2062: }//while
2063: return retValue;
2064: } catch (SQLException sqle) {
2065: log.error("Sql Execution Error!", sqle);
2066: throw new DatabaseException(
2067: "Error executing SQL in ThreadDAOImplJDBC.getBeans_inForum_withSortSupport_limit_general.");
2068: } finally {
2069: DBUtils.closeResultSet(resultSet);
2070: DBUtils.resetStatement(statement);
2071: DBUtils.closeStatement(statement);
2072: DBUtils.closeConnection(connection);
2073: }
2074: }
2075:
2076: /*
2077: * Included columns: ForumID
2078: */
2079: public void updateForumID(int threadID, // primary key
2080: int forumID) throws ObjectNotFoundException,
2081: DatabaseException, ForeignKeyNotFoundException {
2082:
2083: try {
2084: // @todo: modify the parameter list as needed
2085: // If this method does not change the foreign key columns, you can comment this block of code.
2086: DAOFactory.getForumDAO().findByPrimaryKey(forumID);
2087: } catch (ObjectNotFoundException e) {
2088: throw new ForeignKeyNotFoundException(
2089: "Foreign key refers to table 'Forum' does not exist. Cannot update table 'Thread'.");
2090: }
2091:
2092: Connection connection = null;
2093: PreparedStatement statement = null;
2094: StringBuffer sql = new StringBuffer(512);
2095: sql.append("UPDATE " + TABLE_NAME + " SET ForumID = ?");
2096: sql.append(" WHERE ThreadID = ?");
2097: try {
2098: connection = DBUtils.getConnection();
2099: statement = connection.prepareStatement(sql.toString());
2100:
2101: // // column(s) to update
2102: statement.setInt(1, forumID);
2103:
2104: // primary key column(s)
2105: statement.setInt(2, threadID);
2106:
2107: if (statement.executeUpdate() != 1) {
2108: throw new ObjectNotFoundException(
2109: "Cannot update table Thread where primary key = ("
2110: + threadID + ").");
2111: }
2112: m_dirty = true;
2113: } catch (SQLException sqle) {
2114: log.error("Sql Execution Error!", sqle);
2115: throw new DatabaseException(
2116: "Error executing SQL in ThreadDAOImplJDBC.updateForumID.");
2117: } finally {
2118: DBUtils.closeStatement(statement);
2119: DBUtils.closeConnection(connection);
2120: }
2121: }
2122:
2123: /*
2124: * Included columns: ThreadStatus
2125: */
2126: public void updateThreadStatus(int threadID, // primary key
2127: int threadStatus) throws ObjectNotFoundException,
2128: DatabaseException {
2129:
2130: ThreadBean.validateThreadStatus(threadStatus);
2131:
2132: Connection connection = null;
2133: PreparedStatement statement = null;
2134: StringBuffer sql = new StringBuffer(512);
2135: sql.append("UPDATE " + TABLE_NAME + " SET ThreadStatus = ?");
2136: sql.append(" WHERE ThreadID = ?");
2137: try {
2138: connection = DBUtils.getConnection();
2139: statement = connection.prepareStatement(sql.toString());
2140:
2141: // // column(s) to update
2142: statement.setInt(1, threadStatus);
2143:
2144: // primary key column(s)
2145: statement.setInt(2, threadID);
2146:
2147: if (statement.executeUpdate() != 1) {
2148: throw new ObjectNotFoundException(
2149: "Cannot update table Thread (ThreadStatus) where primary key = ("
2150: + threadID + ").");
2151: }
2152: m_dirty = true;
2153: } catch (SQLException sqle) {
2154: log.error("Sql Execution Error!", sqle);
2155: throw new DatabaseException(
2156: "Error executing SQL in ThreadDAOImplJDBC.updateThreadStatus.");
2157: } finally {
2158: DBUtils.closeStatement(statement);
2159: DBUtils.closeConnection(connection);
2160: }
2161: }
2162:
2163: /*
2164: * Included columns: ThreadType
2165: */
2166: public void updateThreadType(int threadID, // primary key
2167: int threadType) throws ObjectNotFoundException,
2168: DatabaseException {
2169:
2170: ThreadBean.validateThreadType(threadType);
2171:
2172: Connection connection = null;
2173: PreparedStatement statement = null;
2174: StringBuffer sql = new StringBuffer(512);
2175: sql.append("UPDATE " + TABLE_NAME + " SET ThreadType = ?");
2176: sql.append(" WHERE ThreadID = ?");
2177: try {
2178: connection = DBUtils.getConnection();
2179: statement = connection.prepareStatement(sql.toString());
2180:
2181: // // column(s) to update
2182: statement.setInt(1, threadType);
2183:
2184: // primary key column(s)
2185: statement.setInt(2, threadID);
2186:
2187: if (statement.executeUpdate() != 1) {
2188: throw new ObjectNotFoundException(
2189: "Cannot update table Thread (ThreadType) where primary key = ("
2190: + threadID + ").");
2191: }
2192: m_dirty = true;
2193: } catch (SQLException sqle) {
2194: log.error("Sql Execution Error!", sqle);
2195: throw new DatabaseException(
2196: "Error executing SQL in ThreadDAOImplJDBC.updateThreadType.");
2197: } finally {
2198: DBUtils.closeStatement(statement);
2199: DBUtils.closeConnection(connection);
2200: }
2201: }
2202:
2203: /**
2204: * This method should be call only when we can make sure that threadID is in database
2205: */
2206: public void increaseViewCount(int threadID)
2207: throws DatabaseException, ObjectNotFoundException {
2208:
2209: Connection connection = null;
2210: PreparedStatement statement = null;
2211: String sql = "UPDATE "
2212: + TABLE_NAME
2213: + " SET ThreadViewCount = ThreadViewCount + 1 WHERE ThreadID = ?";
2214: try {
2215: connection = DBUtils.getConnection();
2216: statement = connection.prepareStatement(sql);
2217: statement.setInt(1, threadID);
2218: if (statement.executeUpdate() != 1) {
2219: throw new ObjectNotFoundException(
2220: "Cannot update the ThreadViewCount in table Thread. Please contact Web site Administrator.");
2221: }
2222: //@todo: coi lai cho nay
2223: // ATTENTION !!!
2224: setDirty(true);
2225: } catch (SQLException sqle) {
2226: log.error("Sql Execution Error!", sqle);
2227: throw new DatabaseException(
2228: "Error executing SQL in ThreadDAOImplJDBC.increaseViewCount.");
2229: } finally {
2230: DBUtils.closeStatement(statement);
2231: DBUtils.closeConnection(connection);
2232: }
2233: }
2234:
2235: public void updateReplyCount(int threadID, // primary key
2236: int threadReplyCount) throws IllegalArgumentException,
2237: DatabaseException, ObjectNotFoundException {
2238:
2239: if (threadReplyCount < 0) {
2240: throw new IllegalArgumentException(
2241: "Cannot update a negative reply count.");
2242: }
2243:
2244: Connection connection = null;
2245: PreparedStatement statement = null;
2246: StringBuffer sql = new StringBuffer(512);
2247: sql
2248: .append("UPDATE " + TABLE_NAME
2249: + " SET ThreadReplyCount = ?");
2250: sql.append(" WHERE ThreadID = ?");
2251: try {
2252: connection = DBUtils.getConnection();
2253: statement = connection.prepareStatement(sql.toString());
2254:
2255: // // column(s) to update
2256: statement.setInt(1, threadReplyCount);
2257:
2258: // primary key column(s)
2259: statement.setInt(2, threadID);
2260:
2261: if (statement.executeUpdate() != 1) {
2262: throw new ObjectNotFoundException(
2263: "Cannot update table Thread where primary key = ("
2264: + threadID + ").");
2265: }
2266: setDirty(true);
2267: } catch (SQLException sqle) {
2268: log.error("Sql Execution Error!", sqle);
2269: throw new DatabaseException(
2270: "Error executing SQL in ThreadDAOImplJDBC.updateReplyCount.");
2271: } finally {
2272: DBUtils.closeStatement(statement);
2273: DBUtils.closeConnection(connection);
2274: }
2275: }
2276:
2277: public void updateThreadHasPoll(int threadID, int pollCount)
2278: throws ObjectNotFoundException, DatabaseException {
2279:
2280: Connection connection = null;
2281: PreparedStatement statement = null;
2282: StringBuffer sql = new StringBuffer(512);
2283: sql.append("UPDATE " + TABLE_NAME + " SET ThreadHasPoll = ?");
2284: sql.append(" WHERE ThreadID = ?");
2285: try {
2286: connection = DBUtils.getConnection();
2287: statement = connection.prepareStatement(sql.toString());
2288:
2289: statement.setInt(1, pollCount);
2290: statement.setInt(2, threadID);
2291:
2292: if (statement.executeUpdate() != 1) {
2293: throw new ObjectNotFoundException(
2294: "Cannot update ThreadHasPoll in table Thread where primary key = ("
2295: + threadID + ").");
2296: }
2297: m_dirty = true;
2298: } catch (SQLException sqle) {
2299: log.error("Sql Execution Error!", sqle);
2300: throw new DatabaseException(
2301: "Error executing SQL in ThreadDAOImplJDBC.updateThreadHasPoll.");
2302: } finally {
2303: DBUtils.closeStatement(statement);
2304: DBUtils.closeConnection(connection);
2305: }
2306: }
2307:
2308: public int getPreviousEnableThread(int forumID, int threadID)
2309: throws DatabaseException {
2310:
2311: Connection connection = null;
2312: PreparedStatement statement = null;
2313: ResultSet resultSet = null;
2314: String sql = "SELECT MAX(ThreadID) FROM "
2315: + TABLE_NAME
2316: + " WHERE ThreadID < ? AND ForumID = ? AND ThreadStatus <> 1 ";
2317: try {
2318: connection = DBUtils.getConnection();
2319: statement = connection.prepareStatement(sql);
2320: statement.setInt(1, threadID);
2321: statement.setInt(2, forumID);
2322: resultSet = statement.executeQuery();
2323: AssertionUtil.doAssert(resultSet.next(),
2324: "Cannot get the previous thread of the thread you requested: ThreadID = "
2325: + threadID);
2326: return resultSet.getInt(1);
2327: } catch (SQLException sqle) {
2328: log.error("Sql Execution Error!", sqle);
2329: throw new DatabaseException(
2330: "Error executing SQL in ThreadDAOImplJDBC.getPreviousEnableThread.");
2331: } finally {
2332: DBUtils.closeResultSet(resultSet);
2333: DBUtils.closeStatement(statement);
2334: DBUtils.closeConnection(connection);
2335: }
2336: }
2337:
2338: public int getNextEnableThread(int forumID, int threadID)
2339: throws DatabaseException {
2340:
2341: Connection connection = null;
2342: PreparedStatement statement = null;
2343: ResultSet resultSet = null;
2344: String sql = "SELECT MIN(ThreadID) FROM "
2345: + TABLE_NAME
2346: + " WHERE ThreadID > ? AND ForumID = ? AND ThreadStatus <> 1 ";
2347: try {
2348: connection = DBUtils.getConnection();
2349: statement = connection.prepareStatement(sql);
2350: statement.setInt(1, threadID);
2351: statement.setInt(2, forumID);
2352: resultSet = statement.executeQuery();
2353: AssertionUtil.doAssert(resultSet.next(),
2354: "Cannot get the next thread of the thread you requested: ThreadID = "
2355: + threadID);
2356: return resultSet.getInt(1);
2357: } catch (SQLException sqle) {
2358: log.error("Sql Execution Error!", sqle);
2359: throw new DatabaseException(
2360: "Error executing SQL in ThreadDAOImplJDBC.getNextEnableThread.");
2361: } finally {
2362: DBUtils.closeResultSet(resultSet);
2363: DBUtils.closeStatement(statement);
2364: DBUtils.closeConnection(connection);
2365: }
2366: }
2367:
2368: /*
2369: * Included columns: ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic,
2370: * ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate,
2371: * ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount,
2372: * ThreadReplyCount, ThreadIcon, ThreadDuration
2373: * Excluded columns:
2374: */
2375: public Collection getEnableThreads_inGlobal(Timestamp sinceDate)
2376: throws DatabaseException {
2377:
2378: Connection connection = null;
2379: PreparedStatement statement = null;
2380: ResultSet resultSet = null;
2381: Collection retValue = new ArrayList();
2382: StringBuffer sql = new StringBuffer(512);
2383: sql
2384: .append("SELECT ThreadID, thread.ForumID, MemberName, thread.LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadPriority, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration");
2385: sql.append(" FROM " + TABLE_NAME + " thread, "
2386: + ForumDAO.TABLE_NAME + " forum ");
2387: sql
2388: .append(" WHERE (thread.ThreadStatus <> 1) AND (thread.ForumID = forum.ForumID) AND (ThreadLastPostDate > ?) ");
2389: sql
2390: .append(" ORDER BY forum.CategoryID ASC, thread.ForumID ASC ");
2391: try {
2392: connection = DBUtils.getConnection();
2393: statement = connection.prepareStatement(sql.toString());
2394: statement.setTimestamp(1, sinceDate);
2395: resultSet = statement.executeQuery();
2396: while (resultSet.next()) {
2397: ThreadBean bean = new ThreadBean();
2398: bean.setThreadID(resultSet.getInt("ThreadID"));
2399: bean.setForumID(resultSet.getInt("ForumID"));
2400: bean.setMemberName(resultSet.getString("MemberName"));
2401: bean.setLastPostMemberName(resultSet
2402: .getString("LastPostMemberName"));
2403: bean.setThreadTopic(resultSet.getString("ThreadTopic"));
2404: bean.setThreadBody(resultSet.getString("ThreadBody"));
2405: bean.setThreadVoteCount(resultSet
2406: .getInt("ThreadVoteCount"));
2407: bean.setThreadVoteTotalStars(resultSet
2408: .getInt("ThreadVoteTotalStars"));
2409: bean.setThreadCreationDate(resultSet
2410: .getTimestamp("ThreadCreationDate"));
2411: bean.setThreadLastPostDate(resultSet
2412: .getTimestamp("ThreadLastPostDate"));
2413: bean.setThreadType(resultSet.getInt("ThreadType"));
2414: bean.setThreadPriority(resultSet
2415: .getInt("ThreadPriority"));
2416: bean.setThreadOption(resultSet.getInt("ThreadOption"));
2417: bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
2418: bean
2419: .setThreadHasPoll(resultSet
2420: .getInt("ThreadHasPoll"));
2421: bean.setThreadViewCount(resultSet
2422: .getInt("ThreadViewCount"));
2423: bean.setThreadReplyCount(resultSet
2424: .getInt("ThreadReplyCount"));
2425: bean.setThreadIcon(resultSet.getString("ThreadIcon"));
2426: bean.setThreadDuration(resultSet
2427: .getInt("ThreadDuration"));
2428: retValue.add(bean);
2429: }
2430: return retValue;
2431: } catch (SQLException sqle) {
2432: log.error("Sql Execution Error!", sqle);
2433: throw new DatabaseException(
2434: "Error executing SQL in ThreadDAOImplJDBC.getEnableThreads_inGlobal.");
2435: } finally {
2436: DBUtils.closeResultSet(resultSet);
2437: DBUtils.closeStatement(statement);
2438: DBUtils.closeConnection(connection);
2439: }
2440: }
2441:
2442: /*
2443: * Included columns: ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic,
2444: * ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate,
2445: * ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount,
2446: * ThreadReplyCount, ThreadIcon, ThreadDuration
2447: * Excluded columns:
2448: */
2449: public Collection getEnableThreads_inCategory(int categoryID,
2450: Timestamp sinceDate) throws DatabaseException {
2451:
2452: Connection connection = null;
2453: PreparedStatement statement = null;
2454: ResultSet resultSet = null;
2455: Collection retValue = new ArrayList();
2456: StringBuffer sql = new StringBuffer(512);
2457: sql
2458: .append("SELECT ThreadID, thread.ForumID, MemberName, thread.LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadPriority, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration");
2459: sql.append(" FROM " + TABLE_NAME + " thread, "
2460: + ForumDAO.TABLE_NAME + " forum ");
2461: sql
2462: .append(" WHERE (thread.ThreadStatus <> 1) AND (thread.ForumID = forum.ForumID) AND (forum.CategoryID = ?) AND (ThreadLastPostDate > ?) ");
2463: sql.append(" ORDER BY thread.ForumID ASC ");
2464: try {
2465: connection = DBUtils.getConnection();
2466: statement = connection.prepareStatement(sql.toString());
2467: statement.setInt(1, categoryID);
2468: statement.setTimestamp(2, sinceDate);
2469: resultSet = statement.executeQuery();
2470: while (resultSet.next()) {
2471: ThreadBean bean = new ThreadBean();
2472: bean.setThreadID(resultSet.getInt("ThreadID"));
2473: bean.setForumID(resultSet.getInt("ForumID"));
2474: bean.setMemberName(resultSet.getString("MemberName"));
2475: bean.setLastPostMemberName(resultSet
2476: .getString("LastPostMemberName"));
2477: bean.setThreadTopic(resultSet.getString("ThreadTopic"));
2478: bean.setThreadBody(resultSet.getString("ThreadBody"));
2479: bean.setThreadVoteCount(resultSet
2480: .getInt("ThreadVoteCount"));
2481: bean.setThreadVoteTotalStars(resultSet
2482: .getInt("ThreadVoteTotalStars"));
2483: bean.setThreadCreationDate(resultSet
2484: .getTimestamp("ThreadCreationDate"));
2485: bean.setThreadLastPostDate(resultSet
2486: .getTimestamp("ThreadLastPostDate"));
2487: bean.setThreadType(resultSet.getInt("ThreadType"));
2488: bean.setThreadPriority(resultSet
2489: .getInt("ThreadPriority"));
2490: bean.setThreadOption(resultSet.getInt("ThreadOption"));
2491: bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
2492: bean
2493: .setThreadHasPoll(resultSet
2494: .getInt("ThreadHasPoll"));
2495: bean.setThreadViewCount(resultSet
2496: .getInt("ThreadViewCount"));
2497: bean.setThreadReplyCount(resultSet
2498: .getInt("ThreadReplyCount"));
2499: bean.setThreadIcon(resultSet.getString("ThreadIcon"));
2500: bean.setThreadDuration(resultSet
2501: .getInt("ThreadDuration"));
2502: retValue.add(bean);
2503: }
2504: return retValue;
2505: } catch (SQLException sqle) {
2506: log.error("Sql Execution Error!", sqle);
2507: throw new DatabaseException(
2508: "Error executing SQL in ThreadDAOImplJDBC.getEnableThreads_inCategory.");
2509: } finally {
2510: DBUtils.closeResultSet(resultSet);
2511: DBUtils.closeStatement(statement);
2512: DBUtils.closeConnection(connection);
2513: }
2514: }
2515:
2516: /*
2517: * Included columns: ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic,
2518: * ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate,
2519: * ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount,
2520: * ThreadReplyCount, ThreadIcon, ThreadDuration
2521: * Excluded columns:
2522: */
2523: public Collection getEnableThreads_inForum(int forumID,
2524: Timestamp sinceDate) throws DatabaseException {
2525:
2526: Connection connection = null;
2527: PreparedStatement statement = null;
2528: ResultSet resultSet = null;
2529: Collection retValue = new ArrayList();
2530: StringBuffer sql = new StringBuffer(512);
2531: sql
2532: .append("SELECT ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadPriority, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration");
2533: sql.append(" FROM " + TABLE_NAME);
2534: sql
2535: .append(" WHERE (ThreadStatus <> 1) AND (ForumID = ?) AND (ThreadLastPostDate > ?) ");
2536: //sql.append(" ORDER BY ColumnName ASC|DESC "); // @todo: uncomment as needed
2537: try {
2538: connection = DBUtils.getConnection();
2539: statement = connection.prepareStatement(sql.toString());
2540: statement.setInt(1, forumID);
2541: statement.setTimestamp(2, sinceDate);
2542: resultSet = statement.executeQuery();
2543: while (resultSet.next()) {
2544: ThreadBean bean = new ThreadBean();
2545: bean.setThreadID(resultSet.getInt("ThreadID"));
2546: bean.setForumID(resultSet.getInt("ForumID"));
2547: bean.setMemberName(resultSet.getString("MemberName"));
2548: bean.setLastPostMemberName(resultSet
2549: .getString("LastPostMemberName"));
2550: bean.setThreadTopic(resultSet.getString("ThreadTopic"));
2551: bean.setThreadBody(resultSet.getString("ThreadBody"));
2552: bean.setThreadVoteCount(resultSet
2553: .getInt("ThreadVoteCount"));
2554: bean.setThreadVoteTotalStars(resultSet
2555: .getInt("ThreadVoteTotalStars"));
2556: bean.setThreadCreationDate(resultSet
2557: .getTimestamp("ThreadCreationDate"));
2558: bean.setThreadLastPostDate(resultSet
2559: .getTimestamp("ThreadLastPostDate"));
2560: bean.setThreadType(resultSet.getInt("ThreadType"));
2561: bean.setThreadPriority(resultSet
2562: .getInt("ThreadPriority"));
2563: bean.setThreadOption(resultSet.getInt("ThreadOption"));
2564: bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
2565: bean
2566: .setThreadHasPoll(resultSet
2567: .getInt("ThreadHasPoll"));
2568: bean.setThreadViewCount(resultSet
2569: .getInt("ThreadViewCount"));
2570: bean.setThreadReplyCount(resultSet
2571: .getInt("ThreadReplyCount"));
2572: bean.setThreadIcon(resultSet.getString("ThreadIcon"));
2573: bean.setThreadDuration(resultSet
2574: .getInt("ThreadDuration"));
2575: retValue.add(bean);
2576: }
2577: return retValue;
2578: } catch (SQLException sqle) {
2579: log.error("Sql Execution Error!", sqle);
2580: throw new DatabaseException(
2581: "Error executing SQL in ThreadDAOImplJDBC.getEnableThreads_inForum.");
2582: } finally {
2583: DBUtils.closeResultSet(resultSet);
2584: DBUtils.closeStatement(statement);
2585: DBUtils.closeConnection(connection);
2586: }
2587: }
2588:
2589: /*
2590: * Included columns: ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic,
2591: * ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate,
2592: * ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount,
2593: * ThreadReplyCount, ThreadIcon, ThreadDuration
2594: * Excluded columns:
2595: */
2596: public Collection getEnableThreads_inThread(int threadID,
2597: Timestamp sinceDate) throws DatabaseException {
2598:
2599: Connection connection = null;
2600: PreparedStatement statement = null;
2601: ResultSet resultSet = null;
2602: Collection retValue = new ArrayList();
2603: StringBuffer sql = new StringBuffer(512);
2604: sql
2605: .append("SELECT ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadPriority, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration");
2606: sql.append(" FROM " + TABLE_NAME);
2607: sql
2608: .append(" WHERE (ThreadStatus <> 1) AND (ThreadID = ?) AND (ThreadLastPostDate > ?) ");
2609: //sql.append(" ORDER BY ColumnName ASC|DESC "); // @todo: uncomment as needed
2610: try {
2611: connection = DBUtils.getConnection();
2612: statement = connection.prepareStatement(sql.toString());
2613: statement.setInt(1, threadID);
2614: statement.setTimestamp(2, sinceDate);
2615: resultSet = statement.executeQuery();
2616: while (resultSet.next()) {
2617: ThreadBean bean = new ThreadBean();
2618: bean.setThreadID(resultSet.getInt("ThreadID"));
2619: bean.setForumID(resultSet.getInt("ForumID"));
2620: bean.setMemberName(resultSet.getString("MemberName"));
2621: bean.setLastPostMemberName(resultSet
2622: .getString("LastPostMemberName"));
2623: bean.setThreadTopic(resultSet.getString("ThreadTopic"));
2624: bean.setThreadBody(resultSet.getString("ThreadBody"));
2625: bean.setThreadVoteCount(resultSet
2626: .getInt("ThreadVoteCount"));
2627: bean.setThreadVoteTotalStars(resultSet
2628: .getInt("ThreadVoteTotalStars"));
2629: bean.setThreadCreationDate(resultSet
2630: .getTimestamp("ThreadCreationDate"));
2631: bean.setThreadLastPostDate(resultSet
2632: .getTimestamp("ThreadLastPostDate"));
2633: bean.setThreadType(resultSet.getInt("ThreadType"));
2634: bean.setThreadPriority(resultSet
2635: .getInt("ThreadPriority"));
2636: bean.setThreadOption(resultSet.getInt("ThreadOption"));
2637: bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
2638: bean
2639: .setThreadHasPoll(resultSet
2640: .getInt("ThreadHasPoll"));
2641: bean.setThreadViewCount(resultSet
2642: .getInt("ThreadViewCount"));
2643: bean.setThreadReplyCount(resultSet
2644: .getInt("ThreadReplyCount"));
2645: bean.setThreadIcon(resultSet.getString("ThreadIcon"));
2646: bean.setThreadDuration(resultSet
2647: .getInt("ThreadDuration"));
2648: retValue.add(bean);
2649: }
2650: return retValue;
2651: } catch (SQLException sqle) {
2652: log.error("Sql Execution Error!", sqle);
2653: throw new DatabaseException(
2654: "Error executing SQL in ThreadDAOImplJDBC.getEnableThreads_inThread.");
2655: } finally {
2656: DBUtils.closeResultSet(resultSet);
2657: DBUtils.closeStatement(statement);
2658: DBUtils.closeConnection(connection);
2659: }
2660: }
2661:
2662: public int getNumberOfEnableThreadsWithPendingPosts()
2663: throws DatabaseException {
2664:
2665: Connection connection = null;
2666: PreparedStatement statement = null;
2667: ResultSet resultSet = null;
2668: StringBuffer sql = new StringBuffer(512);
2669: sql.append("SELECT Count(DISTINCT thread.ThreadID)");
2670: sql.append(" FROM " + TABLE_NAME + " thread, "
2671: + PostDAO.TABLE_NAME + " post ");
2672: sql
2673: .append(" WHERE post.ThreadID = thread.ThreadID AND PostStatus = 1 AND ThreadStatus <> 1");
2674: try {
2675: connection = DBUtils.getConnection();
2676: statement = connection.prepareStatement(sql.toString());
2677: resultSet = statement.executeQuery();
2678: AssertionUtil
2679: .doAssert(resultSet.next(),
2680: "Assertion in ThreadDAOImplJDBC.getNumberOfEnableThreadsWithPendingPosts.");
2681: return resultSet.getInt(1);
2682: } catch (SQLException sqle) {
2683: log.error("Sql Execution Error!", sqle);
2684: throw new DatabaseException(
2685: "Error executing SQL in ThreadDAOImplJDBC.getNumberOfEnableThreadsWithPendingPosts.");
2686: } finally {
2687: DBUtils.closeResultSet(resultSet);
2688: DBUtils.closeStatement(statement);
2689: DBUtils.closeConnection(connection);
2690: }
2691: }
2692:
2693: public int getNumberOfEnableThreadsWithPendingPosts_inForum(
2694: int forumID) throws DatabaseException {
2695:
2696: Connection connection = null;
2697: PreparedStatement statement = null;
2698: ResultSet resultSet = null;
2699: StringBuffer sql = new StringBuffer(512);
2700: sql.append("SELECT Count(DISTINCT thread.ThreadID)");
2701: sql.append(" FROM " + TABLE_NAME + " thread, "
2702: + PostDAO.TABLE_NAME + " post ");
2703: sql
2704: .append(" WHERE post.ThreadID = thread.ThreadID AND PostStatus = 1 AND ThreadStatus <> 1 AND thread.ForumID = ?");
2705: try {
2706: connection = DBUtils.getConnection();
2707: statement = connection.prepareStatement(sql.toString());
2708: statement.setInt(1, forumID);
2709: resultSet = statement.executeQuery();
2710: AssertionUtil
2711: .doAssert(
2712: resultSet.next(),
2713: "Assertion in ThreadDAOImplJDBC.getNumberOfEnableThreadsWithPendingPosts_inForum.");
2714: return resultSet.getInt(1);
2715: } catch (SQLException sqle) {
2716: log.error("Sql Execution Error!", sqle);
2717: throw new DatabaseException(
2718: "Error executing SQL in ThreadDAOImplJDBC.getNumberOfEnableThreadsWithPendingPosts_inForum.");
2719: } finally {
2720: DBUtils.closeResultSet(resultSet);
2721: DBUtils.closeStatement(statement);
2722: DBUtils.closeConnection(connection);
2723: }
2724: }
2725:
2726: /**
2727: * This method is used to get enable threads that having at least one pending post (for moderation)
2728: *
2729: * Note: current implementation use the NOSCROLL method which is quite slow
2730: *
2731: * @param offset
2732: * @param rowsToReturn
2733: * @param sort
2734: * @param order
2735: * @return
2736: * @throws java.lang.IllegalArgumentException if the arguments are not valid
2737: * @throws DatabaseException
2738: */
2739: public Collection getEnableThreadsWithPendingPosts_withSortSupport_limit(
2740: int offset, int rowsToReturn, String sort, String order)
2741: throws IllegalArgumentException, DatabaseException,
2742: ObjectNotFoundException {
2743:
2744: return getEnableThreadsWithPendingPosts_withSortSupport_limit_noscroll(
2745: -1/*mean all forums*/, offset, rowsToReturn, sort,
2746: order);
2747: }
2748:
2749: /**
2750: * This method is used to get enable threads that having at least one pending post (for moderation)
2751: *
2752: * Note: current implementation use the NOSCROLL method which is quite slow
2753: * NOTE: This method is a hack for Oracle because of the error "Not a Group By expression"
2754: * and when add to the group by clause, then it cannot group by a LONG column
2755: *
2756: * @param offset
2757: * @param rowsToReturn
2758: * @param sort
2759: * @param order
2760: * @return
2761: * @throws java.lang.IllegalArgumentException if the arguments are not valid
2762: * @throws DatabaseException
2763: */
2764: public Collection getEnableThreadsWithPendingPosts_inForum_withSortSupport_limit(
2765: int forumID, int offset, int rowsToReturn, String sort,
2766: String order) throws IllegalArgumentException,
2767: DatabaseException, ObjectNotFoundException {
2768:
2769: return getEnableThreadsWithPendingPosts_withSortSupport_limit_noscroll(
2770: forumID, offset, rowsToReturn, sort, order);
2771: }
2772:
2773: /**
2774: *
2775: * @param forumID the forumID to get threads, or -1 mean get threads in all forums
2776: */
2777: private Collection getEnableThreadsWithPendingPosts_withSortSupport_limit_noscroll(
2778: int forumID, int offset, int rowsToReturn, String sort,
2779: String order) throws IllegalArgumentException,
2780: DatabaseException, ObjectNotFoundException {
2781:
2782: if (offset < 0)
2783: throw new IllegalArgumentException(
2784: "The offset < 0 is not allowed.");
2785: if (rowsToReturn <= 0)
2786: throw new IllegalArgumentException(
2787: "The rowsToReturn <= 0 is not allowed.");
2788:
2789: if ((!sort.equals("ThreadLastPostDate"))
2790: && (!sort.equals("ThreadCreationDate"))
2791: && (!sort.equals("MemberName"))
2792: && (!sort.equals("ThreadReplyCount"))
2793: && (!sort.equals("ThreadViewCount"))
2794: && (!sort.equals("ForumID"))
2795: && (!sort.equals("ThreadPendingPostCount"))) {//ThreadPendingPostCount get from GROUP BY clause
2796: throw new IllegalArgumentException(
2797: "Cannot sort, reason: dont understand the criteria '"
2798: + sort + "'.");
2799: }
2800:
2801: if ((!order.equals("ASC")) && (!order.equals("DESC"))) {
2802: throw new IllegalArgumentException(
2803: "Cannot sort, reason: dont understand the order '"
2804: + order + "'.");
2805: }
2806:
2807: Connection connection = null;
2808: PreparedStatement statement = null;
2809: ResultSet resultSet = null;
2810: Collection retValue = new ArrayList();
2811: StringBuffer sql = new StringBuffer(512);
2812: sql
2813: .append("SELECT thread.ThreadID, thread.ForumID, thread.MemberName, ThreadCreationDate, ThreadLastPostDate, ThreadViewCount, ThreadReplyCount, COUNT(thread.ThreadID) AS ThreadPendingPostCount");
2814: sql.append(" FROM " + TABLE_NAME + " thread, "
2815: + PostDAO.TABLE_NAME + " post ");
2816: // PostStatus = 1 means pending(disabled) posts
2817: // ThreadStatus <> 1 means enable threads
2818: sql
2819: .append(" WHERE post.ThreadID = thread.ThreadID AND PostStatus = 1 AND ThreadStatus <> 1");
2820: if (forumID != -1) {
2821: sql.append(" AND thread.ForumID = ?");
2822: }
2823: sql
2824: .append(" GROUP BY thread.ThreadID, thread.ForumID, thread.MemberName, ThreadCreationDate, ThreadLastPostDate, ThreadViewCount, ThreadReplyCount");
2825: if (sort.equals("ThreadPendingPostCount")) {
2826: sql.append(" ORDER BY ThreadPendingPostCount " + order);// ColumnName, ASC|DESC
2827: } else {
2828: sql.append(" ORDER BY thread." + sort + " " + order);// ColumnName, ASC|DESC
2829: }
2830: try {
2831: connection = DBUtils.getConnection();
2832: statement = connection.prepareStatement(sql.toString());
2833: if (forumID != -1) {
2834: statement.setInt(1, forumID);
2835: }
2836: statement.setMaxRows(offset + rowsToReturn);
2837: resultSet = statement.executeQuery();
2838: int rowIndex = -1;
2839: while (resultSet.next()) {
2840: rowIndex++;
2841: if (rowIndex < offset)
2842: continue;
2843: ThreadBean bean = new ThreadBean();
2844: bean.setThreadID(resultSet.getInt("ThreadID"));
2845: bean.setForumID(resultSet.getInt("ForumID"));
2846: bean.setMemberName(resultSet.getString("MemberName"));
2847: bean.setThreadCreationDate(resultSet
2848: .getTimestamp("ThreadCreationDate"));
2849: bean.setThreadLastPostDate(resultSet
2850: .getTimestamp("ThreadLastPostDate"));
2851: bean.setThreadViewCount(resultSet
2852: .getInt("ThreadViewCount"));
2853: bean.setThreadReplyCount(resultSet
2854: .getInt("ThreadReplyCount"));
2855: bean.setThreadPendingPostCount(resultSet
2856: .getInt("ThreadPendingPostCount"));
2857: retValue.add(bean);
2858: if (retValue.size() == rowsToReturn)
2859: break;// Fix the Sybase bug
2860: }
2861: } catch (SQLException sqle) {
2862: log.error("Sql Execution Error!", sqle);
2863: throw new DatabaseException(
2864: "Error executing SQL in ThreadDAOImplJDBC.getEnableThreadsHavingPendingPosts_withSortSupport_limit_noscroll.");
2865: } finally {
2866: DBUtils.closeResultSet(resultSet);
2867: DBUtils.resetStatement(statement);
2868: DBUtils.closeStatement(statement);
2869: DBUtils.closeConnection(connection);
2870: }
2871:
2872: ArrayList newThreadBeans = new ArrayList();
2873: for (Iterator iter = retValue.iterator(); iter.hasNext();) {
2874: ThreadBean threadBean = (ThreadBean) iter.next();
2875: ThreadBean fullThreadBean = getThread(threadBean
2876: .getThreadID());
2877: fullThreadBean.setThreadPendingPostCount(threadBean
2878: .getThreadPendingPostCount());
2879: newThreadBeans.add(fullThreadBean);
2880: }
2881: return newThreadBeans;
2882: }
2883:
2884: private Collection getEnableThreads_inType_inForum(int forumID,
2885: int threadType, boolean checkForumStatus)
2886: throws DatabaseException {
2887:
2888: Connection connection = null;
2889: PreparedStatement statement = null;
2890: ResultSet resultSet = null;
2891: Collection retValue = new ArrayList();
2892: StringBuffer sql = new StringBuffer(512);
2893: sql
2894: .append("SELECT ThreadID, thread.ForumID, MemberName, thread.LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadPriority, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration, ThreadAttachCount");
2895: sql.append(" FROM " + TABLE_NAME + " thread, "
2896: + ForumDAO.TABLE_NAME + " forum ");
2897: sql.append(" WHERE thread.ForumID = forum.ForumID ");
2898: boolean isGlobalAnnoucement = ((forumID == ALL_FORUMS) && (threadType == ThreadBean.THREAD_TYPE_GLOBAL_ANNOUNCEMENT));
2899: sql.append(" AND ThreadType = ? ");
2900: if (isGlobalAnnoucement == false) {
2901: sql.append(" AND thread.ForumID = ? ");
2902: }
2903: if (checkForumStatus) {
2904: sql.append(" AND forum.ForumStatus <> ").append(
2905: ForumBean.FORUM_STATUS_DISABLED);
2906: }
2907: sql.append(" AND ThreadStatus <> ").append(
2908: ThreadBean.THREAD_STATUS_DISABLED);
2909: sql.append(" ORDER BY ThreadLastPostDate DESC");
2910: // log.debug("SQL:: " + sql);
2911: try {
2912: connection = DBUtils.getConnection();
2913: statement = connection.prepareStatement(sql.toString());
2914: statement.setInt(1, threadType);
2915: if (isGlobalAnnoucement == false) {
2916: statement.setInt(2, forumID);
2917: }
2918:
2919: resultSet = statement.executeQuery();
2920: while (resultSet.next()) {
2921: ThreadBean bean = new ThreadBean();
2922: bean.setThreadID(resultSet.getInt("ThreadID"));
2923: bean.setForumID(resultSet.getInt("ForumID"));
2924: bean.setMemberName(resultSet.getString("MemberName"));
2925: bean.setLastPostMemberName(resultSet
2926: .getString("LastPostMemberName"));
2927: bean.setThreadTopic(resultSet.getString("ThreadTopic"));
2928: bean.setThreadBody(resultSet.getString("ThreadBody"));
2929: bean.setThreadVoteCount(resultSet
2930: .getInt("ThreadVoteCount"));
2931: bean.setThreadVoteTotalStars(resultSet
2932: .getInt("ThreadVoteTotalStars"));
2933: bean.setThreadCreationDate(resultSet
2934: .getTimestamp("ThreadCreationDate"));
2935: bean.setThreadLastPostDate(resultSet
2936: .getTimestamp("ThreadLastPostDate"));
2937: bean.setThreadType(resultSet.getInt("ThreadType"));
2938: bean.setThreadPriority(resultSet
2939: .getInt("ThreadPriority"));
2940: bean.setThreadOption(resultSet.getInt("ThreadOption"));
2941: bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
2942: bean
2943: .setThreadHasPoll(resultSet
2944: .getInt("ThreadHasPoll"));
2945: bean.setThreadViewCount(resultSet
2946: .getInt("ThreadViewCount"));
2947: bean.setThreadReplyCount(resultSet
2948: .getInt("ThreadReplyCount"));
2949: bean.setThreadIcon(resultSet.getString("ThreadIcon"));
2950: bean.setThreadDuration(resultSet
2951: .getInt("ThreadDuration"));
2952: bean.setThreadAttachCount(resultSet
2953: .getInt("ThreadAttachCount"));
2954: retValue.add(bean);
2955: }
2956: } catch (SQLException sqle) {
2957: log.error("Sql Execution Error!", sqle);
2958: throw new DatabaseException(
2959: "Error executing SQL in ThreadDAOImplJDBC.getEnableThreads_inType_inForum.");
2960: } finally {
2961: DBUtils.closeResultSet(resultSet);
2962: DBUtils.resetStatement(statement);
2963: DBUtils.closeStatement(statement);
2964: DBUtils.closeConnection(connection);
2965: }
2966: return retValue;
2967: }
2968:
2969: public Collection getEnableStickies_inForum(int forumID)
2970: throws DatabaseException {
2971: return this .getEnableThreads_inType_inForum(forumID,
2972: ThreadBean.THREAD_TYPE_STICKY, true);
2973: }
2974:
2975: public Collection getEnableForumAnnouncements_inForum(int forumID)
2976: throws DatabaseException {
2977: return this .getEnableThreads_inType_inForum(forumID,
2978: ThreadBean.THREAD_TYPE_FORUM_ANNOUNCEMENT, true);
2979: }
2980:
2981: public Collection getEnableGlobalAnnouncements()
2982: throws DatabaseException {
2983: return this .getEnableThreads_inType_inForum(
2984: ALL_FORUMS/* not belongs to any forum */,
2985: ThreadBean.THREAD_TYPE_GLOBAL_ANNOUNCEMENT, true);
2986: }
2987:
2988: }// end of class ThreadDAOImplJDBC
|