0001: /*
0002: * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/PostDAOImplJDBC.java,v 1.49 2008/01/15 11:17:54 minhnn Exp $
0003: * $Author: minhnn $
0004: * $Revision: 1.49 $
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.common.ActiveMember;
0048: import com.mvnforum.common.ActiveThread;
0049: import com.mvnforum.db.*;
0050: import net.myvietnam.mvncore.db.DBUtils;
0051: import net.myvietnam.mvncore.exception.*;
0052: import net.myvietnam.mvncore.util.AssertionUtil;
0053:
0054: import org.apache.commons.logging.Log;
0055: import org.apache.commons.logging.LogFactory;
0056:
0057: public class PostDAOImplJDBC implements PostDAO {
0058:
0059: private static Log log = LogFactory.getLog(PostDAOImplJDBC.class);
0060:
0061: // this variable will support caching if cache for this class is needed
0062: private static boolean m_dirty = true;
0063:
0064: public PostDAOImplJDBC() {
0065: }
0066:
0067: protected static boolean isDirty() {
0068: return m_dirty;
0069: }
0070:
0071: protected static void setDirty(boolean dirty) {
0072: m_dirty = dirty;
0073: }
0074:
0075: public void findByPrimaryKey(int postID)
0076: throws ObjectNotFoundException, DatabaseException {
0077:
0078: Connection connection = null;
0079: PreparedStatement statement = null;
0080: ResultSet resultSet = null;
0081: StringBuffer sql = new StringBuffer(512);
0082: sql.append("SELECT PostID");
0083: sql.append(" FROM " + TABLE_NAME);
0084: sql.append(" WHERE PostID = ?");
0085: try {
0086: connection = DBUtils.getConnection();
0087: statement = connection.prepareStatement(sql.toString());
0088: statement.setInt(1, postID);
0089: resultSet = statement.executeQuery();
0090: if (!resultSet.next()) {
0091: throw new ObjectNotFoundException(
0092: "Cannot find the primary key (" + postID
0093: + ") in table 'Post'.");
0094: }
0095: } catch (SQLException sqle) {
0096: log.error("Sql Execution Error!", sqle);
0097: throw new DatabaseException(
0098: "Error executing SQL in PostDAOImplJDBC.findByPrimaryKey.");
0099: } finally {
0100: DBUtils.closeResultSet(resultSet);
0101: DBUtils.closeStatement(statement);
0102: DBUtils.closeConnection(connection);
0103: }
0104: }
0105:
0106: /*
0107: * Included columns: ParentPostID, ForumID, ThreadID, MemberID, MemberName,
0108: * LastEditMemberName, PostTopic, PostBody, PostCreationDate, PostLastEditDate,
0109: * PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption, PostOption,
0110: * PostStatus, PostIcon, PostAttachCount
0111: * Excluded columns: PostID
0112: */
0113: public void create(int parentPostID, int forumID, int threadID,
0114: int memberID, String memberName, String lastEditMemberName,
0115: String postTopic, String postBody,
0116: Timestamp postCreationDate, Timestamp postLastEditDate,
0117: String postCreationIP, String postLastEditIP,
0118: int postEditCount, int postFormatOption, int postOption,
0119: int postStatus, String postIcon, int postAttachCount)
0120: throws CreateException, DatabaseException,
0121: ForeignKeyNotFoundException {
0122:
0123: try {
0124: // @todo: modify the parameter list as needed
0125: // You may have to regenerate this method if the needed columns dont have attribute 'include'
0126: DAOFactory.getForumDAO().findByPrimaryKey(forumID);
0127: } catch (ObjectNotFoundException e) {
0128: throw new ForeignKeyNotFoundException(
0129: "Foreign key refers to table 'Forum' does not exist. Cannot create new Post.");
0130: }
0131:
0132: //allow anonymous/guests to post
0133: if (memberID != 0) {
0134: try {
0135: // @todo: modify the parameter list as needed
0136: // You may have to regenerate this method if the needed columns dont have attribute 'include'
0137: DAOFactory.getMemberDAO().findByPrimaryKey2(memberID,
0138: memberName);
0139: } catch (ObjectNotFoundException e) {
0140: throw new ForeignKeyNotFoundException(
0141: "Foreign key refers to table 'Member' does not exist. Cannot create new Post.");
0142: }
0143: }
0144:
0145: try {
0146: // @todo: modify the parameter list as needed
0147: // You may have to regenerate this method if the needed columns dont have attribute 'include'
0148: DAOFactory.getThreadDAO().findByPrimaryKey(threadID);
0149: } catch (ObjectNotFoundException e) {
0150: throw new ForeignKeyNotFoundException(
0151: "Foreign key refers to table 'Thread' does not exist. Cannot create new Post.");
0152: }
0153:
0154: //We allow anonymous/guests to send posts too (if admin allows them to).
0155: if ((memberName != null) && (memberName.length() > 0)) {
0156: try {
0157: // @todo: modify the parameter list as needed
0158: // You may have to regenerate this method if the needed columns dont have attribute 'include'
0159: DAOFactory.getMemberDAO()
0160: .findByAlternateKey_MemberName(memberName);
0161: } catch (ObjectNotFoundException e) {
0162: throw new ForeignKeyNotFoundException(
0163: "Foreign key refers to table 'Member' does not exist. Cannot create new Post.");
0164: }
0165: } else {
0166: // This is needed, otherwise we will get 'null' in the sql query, instead of ''
0167: memberName = "";
0168: }
0169:
0170: if ((lastEditMemberName != null)
0171: && (lastEditMemberName.length() > 0)) {
0172: try {
0173: DAOFactory.getMemberDAO()
0174: .findByAlternateKey_MemberName(
0175: lastEditMemberName);
0176: } catch (ObjectNotFoundException e) {
0177: throw new ForeignKeyNotFoundException(
0178: "Foreign key refers to table 'Member' does not exist. Cannot create table 'Post'.");
0179: }
0180: } else {
0181: lastEditMemberName = ""; //so we don't get 'null' in sql query
0182: }
0183:
0184: try {
0185: // @todo: modify the parameter list as needed
0186: // You may have to regenerate this method if the needed columns dont have attribute 'include'
0187: if (parentPostID != 0) {
0188: findByPrimaryKey(parentPostID);
0189: }
0190: } catch (ObjectNotFoundException e) {
0191: throw new ForeignKeyNotFoundException(
0192: "Foreign key refers to table 'Post' does not exist. Cannot create new Post.");
0193: }
0194:
0195: Connection connection = null;
0196: PreparedStatement statement = null;
0197: StringBuffer sql = new StringBuffer(512);
0198: sql
0199: .append("INSERT INTO "
0200: + TABLE_NAME
0201: + " (ParentPostID, ForumID, ThreadID, MemberID, MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate, PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption, PostOption, PostStatus, PostIcon, PostAttachCount)");
0202: sql
0203: .append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
0204: try {
0205: connection = DBUtils.getConnection();
0206: statement = connection.prepareStatement(sql.toString());
0207:
0208: statement.setInt(1, parentPostID);
0209: statement.setInt(2, forumID);
0210: statement.setInt(3, threadID);
0211: statement.setInt(4, memberID);
0212: statement.setString(5, memberName);
0213: statement.setString(6, lastEditMemberName);
0214: statement.setString(7, postTopic);
0215: if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
0216: statement.setCharacterStream(8, new StringReader(
0217: postBody), postBody.length());
0218: } else {
0219: statement.setString(8, postBody);
0220: }
0221: statement.setTimestamp(9, postCreationDate);
0222: statement.setTimestamp(10, postLastEditDate);
0223: statement.setString(11, postCreationIP);
0224: statement.setString(12, postLastEditIP);
0225: statement.setInt(13, postEditCount);
0226: statement.setInt(14, postFormatOption);
0227: statement.setInt(15, postOption);
0228: statement.setInt(16, postStatus);
0229: statement.setString(17, postIcon);
0230: statement.setInt(18, postAttachCount);
0231:
0232: if (statement.executeUpdate() != 1) {
0233: throw new CreateException(
0234: "Error adding a row into table 'Post'.");
0235: }
0236: m_dirty = true;
0237: } catch (SQLException sqle) {
0238: log.error("Sql Execution Error!", sqle);
0239: throw new DatabaseException(
0240: "Error executing SQL in PostDAOImplJDBC.create.");
0241: } finally {
0242: DBUtils.closeStatement(statement);
0243: DBUtils.closeConnection(connection);
0244: }
0245: }
0246:
0247: public int createPost(int parentPostID, int forumID, int threadID,
0248: int memberID, String memberName, String lastEditMemberName,
0249: String postTopic, String postBody,
0250: Timestamp postCreationDate, Timestamp postLastEditDate,
0251: String postCreationIP, String postLastEditIP,
0252: int postEditCount, int postFormatOption, int postOption,
0253: int postStatus, String postIcon, int postAttachCount)
0254: throws CreateException, DatabaseException,
0255: ForeignKeyNotFoundException {
0256:
0257: create(parentPostID, forumID, threadID, memberID, memberName,
0258: lastEditMemberName, postTopic, postBody,
0259: postCreationDate, postLastEditDate, postCreationIP,
0260: postLastEditIP, postEditCount, postFormatOption,
0261: postOption, postStatus, postIcon, postAttachCount);
0262:
0263: int postID = 0;
0264: try {
0265: postID = findPostID(forumID, memberName, postCreationDate);
0266: } catch (ObjectNotFoundException ex) {
0267: // Hack the Oracle 9i problem
0268: Timestamp roundTimestamp = new Timestamp((postCreationDate
0269: .getTime() / 1000) * 1000);
0270: try {
0271: postID = findPostID(forumID, memberName, roundTimestamp);
0272: } catch (ObjectNotFoundException e) {
0273: throw new CreateException(
0274: "Cannot find the PostID in table Post.");
0275: }
0276: }
0277: return postID;
0278: }
0279:
0280: public void delete(int postID) throws DatabaseException,
0281: ObjectNotFoundException {
0282:
0283: Connection connection = null;
0284: PreparedStatement statement = null;
0285: StringBuffer sql = new StringBuffer(512);
0286: sql.append("DELETE FROM " + TABLE_NAME);
0287: sql.append(" WHERE PostID = ?");
0288:
0289: try {
0290: connection = DBUtils.getConnection();
0291: statement = connection.prepareStatement(sql.toString());
0292: statement.setInt(1, postID);
0293: if (statement.executeUpdate() != 1) {
0294: throw new ObjectNotFoundException(
0295: "Cannot delete a row in table Post where PostID = ("
0296: + postID + ").");
0297: }
0298: m_dirty = true;
0299: } catch (SQLException sqle) {
0300: log.error("Sql Execution Error!", sqle);
0301: throw new DatabaseException(
0302: "Error executing SQL in PostDAOImplJDBC.delete.");
0303: } finally {
0304: DBUtils.closeStatement(statement);
0305: DBUtils.closeConnection(connection);
0306: }
0307: }
0308:
0309: public void delete_inThread(int threadID) throws DatabaseException {
0310:
0311: Connection connection = null;
0312: PreparedStatement statement = null;
0313: StringBuffer sql = new StringBuffer(512);
0314: sql.append("DELETE FROM " + TABLE_NAME);
0315: sql.append(" WHERE ThreadID = ?");
0316:
0317: try {
0318: connection = DBUtils.getConnection();
0319: statement = connection.prepareStatement(sql.toString());
0320: statement.setInt(1, threadID);
0321:
0322: statement.executeUpdate();
0323:
0324: m_dirty = true;
0325: } catch (SQLException sqle) {
0326: log.error("Sql Execution Error!", sqle);
0327: throw new DatabaseException(
0328: "Error executing SQL in PostDAOImplJDBC.delete_inThread.");
0329: } finally {
0330: DBUtils.closeStatement(statement);
0331: DBUtils.closeConnection(connection);
0332: }
0333: }
0334:
0335: public void delete_inForum(int forumID) throws DatabaseException {
0336:
0337: Connection connection = null;
0338: PreparedStatement statement = null;
0339: StringBuffer sql = new StringBuffer(512);
0340: sql.append("DELETE FROM " + TABLE_NAME);
0341: sql.append(" WHERE ForumID = ?");
0342:
0343: try {
0344: connection = DBUtils.getConnection();
0345: statement = connection.prepareStatement(sql.toString());
0346: statement.setInt(1, forumID);
0347: statement.executeUpdate();
0348: m_dirty = true;
0349: } catch (SQLException sqle) {
0350: log.error("Sql Execution Error!", sqle);
0351: throw new DatabaseException(
0352: "Error executing SQL in PostDAOImplJDBC.delete_inForum.");
0353: } finally {
0354: DBUtils.closeStatement(statement);
0355: DBUtils.closeConnection(connection);
0356: }
0357: }
0358:
0359: /*
0360: * Included columns: LastEditMemberName, PostTopic, PostBody, PostLastEditDate, PostLastEditIP,
0361: * PostFormatOption, PostOption, PostStatus, PostIcon
0362: * Excluded columns: PostID, ParentPostID, ForumID, ThreadID, MemberID,
0363: * MemberName, PostCreationDate, PostCreationIP, PostEditCount, PostAttachCount
0364: */
0365: public void update(
0366: int postID, // primary key
0367: String lastEditMemberName, String postTopic,
0368: String postBody, Timestamp postLastEditDate,
0369: String postLastEditIP, int postFormatOption,
0370: int postOption, int postStatus, String postIcon)
0371: throws ObjectNotFoundException, DatabaseException,
0372: ForeignKeyNotFoundException {
0373:
0374: //if admin allows guests to edit posts
0375: if ((lastEditMemberName != null)
0376: && (lastEditMemberName.length() > 0)) {
0377: try {
0378: // @todo: modify the parameter list as needed
0379: // If this method does not change the foreign key columns, you can comment this block of code.
0380: DAOFactory.getMemberDAO()
0381: .findByAlternateKey_MemberName(
0382: lastEditMemberName);
0383: } catch (ObjectNotFoundException e) {
0384: throw new ForeignKeyNotFoundException(
0385: "Foreign key refers to table 'Member' does not exist. Cannot update table 'Post'.");
0386: }
0387: } else {
0388: lastEditMemberName = ""; //so we don't get 'null' in sql query
0389: }
0390:
0391: Connection connection = null;
0392: PreparedStatement statement = null;
0393: StringBuffer sql = new StringBuffer(512);
0394: sql
0395: .append("UPDATE "
0396: + TABLE_NAME
0397: + " SET LastEditMemberName = ?, PostTopic = ?, PostBody = ?, PostLastEditDate = ?, PostLastEditIP = ?, PostFormatOption = ?, PostOption = ?, PostStatus = ?, PostIcon = ?");
0398: sql.append(" WHERE PostID = ?");
0399: try {
0400: connection = DBUtils.getConnection();
0401: statement = connection.prepareStatement(sql.toString());
0402:
0403: // // column(s) to update
0404: statement.setString(1, lastEditMemberName);
0405: statement.setString(2, postTopic);
0406: if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
0407: statement.setCharacterStream(3, new StringReader(
0408: postBody), postBody.length());
0409: } else {
0410: statement.setString(3, postBody);
0411: }
0412: statement.setTimestamp(4, postLastEditDate);
0413: statement.setString(5, postLastEditIP);
0414: statement.setInt(6, postFormatOption);
0415: statement.setInt(7, postOption);
0416: statement.setInt(8, postStatus);
0417: statement.setString(9, postIcon);
0418:
0419: // primary key column(s)
0420: statement.setInt(10, postID);
0421:
0422: if (statement.executeUpdate() != 1) {
0423: throw new ObjectNotFoundException(
0424: "Cannot update table Post where primary key = ("
0425: + postID + ").");
0426: }
0427: m_dirty = true;
0428: } catch (SQLException sqle) {
0429: log.error("Sql Execution Error!", sqle);
0430: throw new DatabaseException(
0431: "Error executing SQL in PostDAOImplJDBC.update.");
0432: } finally {
0433: DBUtils.closeStatement(statement);
0434: DBUtils.closeConnection(connection);
0435: }
0436: }
0437:
0438: /*
0439: * Included columns: ParentPostID, ForumID, ThreadID
0440: * Excluded columns: PostID, MemberID, LastEditMemberName, PostTopic, PostBody, PostLastEditDate
0441: * PostFormatOption, PostOption, PostStatus, PostIcon, PostLastEditIP,
0442: * MemberName, PostCreationDate, PostCreationIP, PostEditCount, PostAttachCount
0443: */
0444: public void update(int postID, // primary key
0445: int parentPostID, int forumID, int threadID)
0446: throws ObjectNotFoundException, DatabaseException,
0447: ForeignKeyNotFoundException {
0448:
0449: Connection connection = null;
0450: PreparedStatement statement = null;
0451: StringBuffer sql = new StringBuffer(512);
0452: sql.append("UPDATE " + TABLE_NAME
0453: + " SET ParentPostID = ?, ForumID = ?, ThreadID = ?");
0454: sql.append(" WHERE PostID = ?");
0455: try {
0456: connection = DBUtils.getConnection();
0457: statement = connection.prepareStatement(sql.toString());
0458:
0459: // column(s) to be updated
0460: statement.setInt(1, parentPostID);
0461: statement.setInt(2, forumID);
0462: statement.setInt(3, threadID);
0463:
0464: // primary key column(s)
0465: statement.setInt(4, postID);
0466:
0467: if (statement.executeUpdate() != 1) {
0468: throw new ObjectNotFoundException(
0469: "Cannot update table Post where primary key = ("
0470: + postID + ").");
0471: }
0472: m_dirty = true;
0473: } catch (SQLException sqle) {
0474: log.error("Sql Execution Error!", sqle);
0475: throw new DatabaseException(
0476: "Error executing SQL in PostDAOImplJDBC.update.");
0477: } finally {
0478: DBUtils.closeStatement(statement);
0479: DBUtils.closeConnection(connection);
0480: }
0481: }
0482:
0483: /*
0484: * Included columns: PostAttachCount
0485: * Excluded columns: PostID, ParentPostID, ForumID, ThreadID, MemberID,
0486: * MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate,
0487: * PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption,
0488: * PostOption, PostStatus, PostIcon
0489: */
0490: public void updateAttachCount(int postID, // primary key
0491: int postAttachCount) throws ObjectNotFoundException,
0492: DatabaseException {
0493:
0494: Connection connection = null;
0495: PreparedStatement statement = null;
0496: StringBuffer sql = new StringBuffer(512);
0497: sql.append("UPDATE " + TABLE_NAME + " SET PostAttachCount = ?");
0498: sql.append(" WHERE PostID = ?");
0499: try {
0500: connection = DBUtils.getConnection();
0501: statement = connection.prepareStatement(sql.toString());
0502:
0503: // // column(s) to update
0504: statement.setInt(1, postAttachCount);
0505:
0506: // primary key column(s)
0507: statement.setInt(2, postID);
0508:
0509: if (statement.executeUpdate() != 1) {
0510: throw new ObjectNotFoundException(
0511: "Cannot update AttachCount in table Post where primary key = ("
0512: + postID + ").");
0513: }
0514: m_dirty = true;
0515: } catch (SQLException sqle) {
0516: log.error("Sql Execution Error!", sqle);
0517: throw new DatabaseException(
0518: "Error executing SQL in PostDAOImplJDBC.updateAttachCount.");
0519: } finally {
0520: DBUtils.closeStatement(statement);
0521: DBUtils.closeConnection(connection);
0522: }
0523: }
0524:
0525: /*
0526: * Included columns: PostStatus
0527: * Excluded columns: PostID, ParentPostID, ForumID, ThreadID, MemberID,
0528: * MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate,
0529: * PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption,
0530: * PostOption, PostIcon, PostAttachCount
0531: */
0532: public void updateStatus(int postID, // primary key
0533: int postStatus) throws ObjectNotFoundException,
0534: DatabaseException {
0535:
0536: Connection connection = null;
0537: PreparedStatement statement = null;
0538: StringBuffer sql = new StringBuffer(512);
0539: sql.append("UPDATE " + TABLE_NAME + " SET PostStatus = ?");
0540: sql.append(" WHERE PostID = ?");
0541: try {
0542: connection = DBUtils.getConnection();
0543: statement = connection.prepareStatement(sql.toString());
0544:
0545: // // column(s) to update
0546: statement.setInt(1, postStatus);
0547:
0548: // primary key column(s)
0549: statement.setInt(2, postID);
0550:
0551: if (statement.executeUpdate() != 1) {
0552: throw new ObjectNotFoundException(
0553: "Cannot update PostStatus in table Post where primary key = ("
0554: + postID + ").");
0555: }
0556: m_dirty = true;
0557: } catch (SQLException sqle) {
0558: log.error("Sql Execution Error!", sqle);
0559: throw new DatabaseException(
0560: "Error executing SQL in PostDAOImplJDBC.updateStatus.");
0561: } finally {
0562: DBUtils.closeStatement(statement);
0563: DBUtils.closeConnection(connection);
0564: }
0565: }
0566:
0567: /*
0568: * Included columns: ForumID
0569: */
0570: public void update_ForumID_inThread(int threadID, int forumID)
0571: throws DatabaseException, ForeignKeyNotFoundException {
0572:
0573: try {
0574: // @todo: modify the parameter list as needed
0575: // If this method does not change the foreign key columns, you can comment this block of code.
0576: DAOFactory.getForumDAO().findByPrimaryKey(forumID);
0577: } catch (ObjectNotFoundException e) {
0578: throw new ForeignKeyNotFoundException(
0579: "Foreign key refers to table 'Forum' does not exist. Cannot update table 'Post'.");
0580: }
0581:
0582: Connection connection = null;
0583: PreparedStatement statement = null;
0584: StringBuffer sql = new StringBuffer(512);
0585: sql.append("UPDATE " + TABLE_NAME + " SET ForumID = ?");
0586: sql.append(" WHERE ThreadID = ?");
0587: try {
0588: connection = DBUtils.getConnection();
0589: statement = connection.prepareStatement(sql.toString());
0590:
0591: // // column(s) to update
0592: statement.setInt(1, forumID);
0593:
0594: // primary key column(s)
0595: statement.setInt(2, threadID);
0596:
0597: statement.executeUpdate();
0598: m_dirty = true;
0599: } catch (SQLException sqle) {
0600: log.error("Sql Execution Error!", sqle);
0601: throw new DatabaseException(
0602: "Error executing SQL in PostDAOImplJDBC.update_ForumID_inThread.");
0603: } finally {
0604: DBUtils.closeStatement(statement);
0605: DBUtils.closeConnection(connection);
0606: }
0607: }
0608:
0609: private int findPostID(int forumID, String memberName,
0610: Timestamp postCreationDate) throws ObjectNotFoundException,
0611: DatabaseException {
0612:
0613: Connection connection = null;
0614: PreparedStatement statement = null;
0615: ResultSet resultSet = null;
0616: StringBuffer sql = new StringBuffer(512);
0617: sql.append("SELECT PostID");
0618: sql.append(" FROM " + TABLE_NAME);
0619: sql
0620: .append(" WHERE ForumID = ? AND MemberName = ? AND PostCreationDate = ? ");
0621: try {
0622: connection = DBUtils.getConnection();
0623: statement = connection.prepareStatement(sql.toString());
0624: statement.setInt(1, forumID);
0625: statement.setString(2, memberName);
0626: statement.setTimestamp(3, postCreationDate);
0627: resultSet = statement.executeQuery();
0628: if (!resultSet.next()) {
0629: throw new ObjectNotFoundException(
0630: "Cannot find the PostID in table Post.");
0631: }
0632:
0633: return resultSet.getInt("PostID");
0634: } catch (SQLException sqle) {
0635: log.error("Sql Execution Error!", sqle);
0636: throw new DatabaseException(
0637: "Error executing SQL in PostDAOImplJDBC.findPostID.");
0638: } finally {
0639: DBUtils.closeResultSet(resultSet);
0640: DBUtils.closeStatement(statement);
0641: DBUtils.closeConnection(connection);
0642: }
0643: }
0644:
0645: /*
0646: * Included columns: ParentPostID, ForumID, ThreadID, MemberID, MemberName,
0647: * LastEditMemberName, PostTopic, PostBody, PostCreationDate, PostLastEditDate,
0648: * PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption, PostOption,
0649: * PostStatus, PostIcon, PostAttachCount
0650: * Excluded columns: PostID
0651: */
0652: public PostBean getPost(int postID) throws ObjectNotFoundException,
0653: DatabaseException {
0654:
0655: Connection connection = null;
0656: PreparedStatement statement = null;
0657: ResultSet resultSet = null;
0658: StringBuffer sql = new StringBuffer(512);
0659: sql
0660: .append("SELECT ParentPostID, ForumID, ThreadID, MemberID, MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate, PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption, PostOption, PostStatus, PostIcon, PostAttachCount");
0661: sql.append(" FROM " + TABLE_NAME);
0662: sql.append(" WHERE PostID = ?");
0663: try {
0664: connection = DBUtils.getConnection();
0665: statement = connection.prepareStatement(sql.toString());
0666: statement.setInt(1, postID);
0667: resultSet = statement.executeQuery();
0668: if (!resultSet.next()) {
0669: throw new ObjectNotFoundException(
0670: "Cannot find the row in table Post where primary key = ("
0671: + postID + ").");
0672: }
0673:
0674: PostBean bean = new PostBean();
0675: // @todo: uncomment the following line(s) as needed
0676: bean.setPostID(postID);
0677: bean.setParentPostID(resultSet.getInt("ParentPostID"));
0678: bean.setForumID(resultSet.getInt("ForumID"));
0679: bean.setThreadID(resultSet.getInt("ThreadID"));
0680: bean.setMemberID(resultSet.getInt("MemberID"));
0681: bean.setMemberName(resultSet.getString("MemberName"));
0682: bean.setLastEditMemberName(resultSet
0683: .getString("LastEditMemberName"));
0684: bean.setPostTopic(resultSet.getString("PostTopic"));
0685: bean.setPostBody(resultSet.getString("PostBody"));
0686: bean.setPostCreationDate(resultSet
0687: .getTimestamp("PostCreationDate"));
0688: bean.setPostLastEditDate(resultSet
0689: .getTimestamp("PostLastEditDate"));
0690: bean.setPostCreationIP(resultSet
0691: .getString("PostCreationIP"));
0692: bean.setPostLastEditIP(resultSet
0693: .getString("PostLastEditIP"));
0694: bean.setPostEditCount(resultSet.getInt("PostEditCount"));
0695: bean.setPostFormatOption(resultSet
0696: .getInt("PostFormatOption"));
0697: bean.setPostOption(resultSet.getInt("PostOption"));
0698: bean.setPostStatus(resultSet.getInt("PostStatus"));
0699: bean.setPostIcon(resultSet.getString("PostIcon"));
0700: bean
0701: .setPostAttachCount(resultSet
0702: .getInt("PostAttachCount"));
0703: return bean;
0704: } catch (SQLException sqle) {
0705: log.error("Sql Execution Error!", sqle);
0706: throw new DatabaseException(
0707: "Error executing SQL in PostDAOImplJDBC.getPost(pk).");
0708: } finally {
0709: DBUtils.closeResultSet(resultSet);
0710: DBUtils.closeStatement(statement);
0711: DBUtils.closeConnection(connection);
0712: }
0713: }
0714:
0715: public PostBean getFirstPost_inThread(int threadID)
0716: throws ObjectNotFoundException, DatabaseException {
0717:
0718: // Note that because the status of the first post are always Enable
0719: // so that we can safely use the below method
0720: Collection enablePostBeans = getEnablePosts_inThread_limit(
0721: threadID, 0, 1);
0722: Iterator iter = enablePostBeans.iterator();
0723: if (iter.hasNext()) {
0724: PostBean postBean = (PostBean) iter.next();
0725: return postBean;
0726: }
0727: throw new ObjectNotFoundException(
0728: "Cannot find the first post in thread = " + threadID);
0729: }
0730:
0731: public Collection getEnablePosts_inThread_limit(int threadID,
0732: int offset, int rowsToReturn)
0733: throws IllegalArgumentException, DatabaseException {
0734: if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
0735: return getBeans_inThread_limit_mysql(threadID, offset,
0736: rowsToReturn, true);
0737: } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
0738: return getBeans_inThread_limit_noscroll(threadID, offset,
0739: rowsToReturn, true);
0740: }
0741: return getBeans_inThread_limit_general(threadID, offset,
0742: rowsToReturn, true);
0743: }
0744:
0745: public Collection getDisablePosts_inThread_limit(int threadID,
0746: int offset, int rowsToReturn)
0747: throws IllegalArgumentException, DatabaseException {
0748: if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
0749: return getBeans_inThread_limit_mysql(threadID, offset,
0750: rowsToReturn, false);
0751: } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
0752: return getBeans_inThread_limit_noscroll(threadID, offset,
0753: rowsToReturn, false);
0754: }
0755: return getBeans_inThread_limit_general(threadID, offset,
0756: rowsToReturn, false);
0757: }
0758:
0759: /*
0760: * Included columns: PostID, ParentPostID, ForumID, ThreadID, MemberID,
0761: * MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate,
0762: * PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption,
0763: * PostOption, PostStatus, PostIcon, PostAttachCount
0764: * Excluded columns:
0765: */
0766: private Collection getBeans_inThread_limit_mysql(int threadID,
0767: int offset, int rowsToReturn, boolean enable)
0768: throws IllegalArgumentException, DatabaseException {
0769: if (offset < 0)
0770: throw new IllegalArgumentException(
0771: "The offset < 0 is not allowed.");
0772: if (rowsToReturn <= 0)
0773: throw new IllegalArgumentException(
0774: "The rowsToReturn <= 0 is not allowed.");
0775:
0776: Connection connection = null;
0777: PreparedStatement statement = null;
0778: ResultSet resultSet = null;
0779: Collection retValue = new ArrayList();
0780: StringBuffer sql = new StringBuffer(512);
0781: sql
0782: .append("SELECT PostID, ParentPostID, ForumID, ThreadID, MemberID, MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate, PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption, PostOption, PostStatus, PostIcon, PostAttachCount");
0783: sql.append(" FROM " + TABLE_NAME);
0784: sql.append(" WHERE ThreadID = ?");
0785: if (enable) {
0786: sql.append(" AND PostStatus <> 1 ");
0787: } else {//disable
0788: sql.append(" AND PostStatus = 1 ");
0789: }
0790: sql.append(" ORDER BY PostID ASC ");
0791: sql.append(" LIMIT ?, ?");
0792: try {
0793: connection = DBUtils.getConnection();
0794: statement = connection.prepareStatement(sql.toString());
0795: statement.setInt(1, threadID);
0796: statement.setInt(2, offset);
0797: statement.setInt(3, rowsToReturn);
0798: resultSet = statement.executeQuery();
0799: while (resultSet.next()) {
0800: PostBean bean = new PostBean();
0801: bean.setPostID(resultSet.getInt("PostID"));
0802: bean.setParentPostID(resultSet.getInt("ParentPostID"));
0803: bean.setForumID(resultSet.getInt("ForumID"));
0804: bean.setThreadID(resultSet.getInt("ThreadID"));
0805: bean.setMemberID(resultSet.getInt("MemberID"));
0806: bean.setMemberName(resultSet.getString("MemberName"));
0807: bean.setLastEditMemberName(resultSet
0808: .getString("LastEditMemberName"));
0809: bean.setPostTopic(resultSet.getString("PostTopic"));
0810: bean.setPostBody(resultSet.getString("PostBody"));
0811: bean.setPostCreationDate(resultSet
0812: .getTimestamp("PostCreationDate"));
0813: bean.setPostLastEditDate(resultSet
0814: .getTimestamp("PostLastEditDate"));
0815: bean.setPostCreationIP(resultSet
0816: .getString("PostCreationIP"));
0817: bean.setPostLastEditIP(resultSet
0818: .getString("PostLastEditIP"));
0819: bean
0820: .setPostEditCount(resultSet
0821: .getInt("PostEditCount"));
0822: bean.setPostFormatOption(resultSet
0823: .getInt("PostFormatOption"));
0824: bean.setPostOption(resultSet.getInt("PostOption"));
0825: bean.setPostStatus(resultSet.getInt("PostStatus"));
0826: bean.setPostIcon(resultSet.getString("PostIcon"));
0827: bean.setPostAttachCount(resultSet
0828: .getInt("PostAttachCount"));
0829: retValue.add(bean);
0830: }
0831: return retValue;
0832: } catch (SQLException sqle) {
0833: log.error("Sql Execution Error!", sqle);
0834: throw new DatabaseException(
0835: "Error executing SQL in PostDAOImplJDBC.getBeans_inThread_limit_mysql.");
0836: } finally {
0837: DBUtils.closeResultSet(resultSet);
0838: DBUtils.closeStatement(statement);
0839: DBUtils.closeConnection(connection);
0840: }
0841: }
0842:
0843: /*
0844: * Included columns: PostID, ParentPostID, ForumID, ThreadID, MemberID,
0845: * MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate,
0846: * PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption,
0847: * PostOption, PostStatus, PostIcon, PostAttachCount
0848: * Excluded columns:
0849: */
0850: private Collection getBeans_inThread_limit_noscroll(int threadID,
0851: int offset, int rowsToReturn, boolean enable)
0852: throws IllegalArgumentException, DatabaseException {
0853: if (offset < 0)
0854: throw new IllegalArgumentException(
0855: "The offset < 0 is not allowed.");
0856: if (rowsToReturn <= 0)
0857: throw new IllegalArgumentException(
0858: "The rowsToReturn <= 0 is not allowed.");
0859:
0860: Connection connection = null;
0861: PreparedStatement statement = null;
0862: ResultSet resultSet = null;
0863: Collection retValue = new ArrayList();
0864: StringBuffer sql = new StringBuffer(512);
0865: sql
0866: .append("SELECT PostID, ParentPostID, ForumID, ThreadID, MemberID, MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate, PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption, PostOption, PostStatus, PostIcon, PostAttachCount");
0867: sql.append(" FROM " + TABLE_NAME);
0868: sql.append(" WHERE ThreadID = ?");
0869: if (enable) {
0870: sql.append(" AND PostStatus <> 1 ");
0871: } else {//disable
0872: sql.append(" AND PostStatus = 1 ");
0873: }
0874: sql.append(" ORDER BY PostID ASC ");
0875: try {
0876: connection = DBUtils.getConnection();
0877: statement = connection.prepareStatement(sql.toString());
0878: statement.setMaxRows(offset + rowsToReturn);
0879: statement.setInt(1, threadID);
0880: resultSet = statement.executeQuery();
0881: int rowIndex = -1;
0882: while (resultSet.next()) {
0883: rowIndex++;
0884: if (rowIndex < offset)
0885: continue;
0886: PostBean bean = new PostBean();
0887: bean.setPostID(resultSet.getInt("PostID"));
0888: bean.setParentPostID(resultSet.getInt("ParentPostID"));
0889: bean.setForumID(resultSet.getInt("ForumID"));
0890: bean.setThreadID(resultSet.getInt("ThreadID"));
0891: bean.setMemberID(resultSet.getInt("MemberID"));
0892: bean.setMemberName(resultSet.getString("MemberName"));
0893: bean.setLastEditMemberName(resultSet
0894: .getString("LastEditMemberName"));
0895: bean.setPostTopic(resultSet.getString("PostTopic"));
0896: bean.setPostBody(resultSet.getString("PostBody"));
0897: bean.setPostCreationDate(resultSet
0898: .getTimestamp("PostCreationDate"));
0899: bean.setPostLastEditDate(resultSet
0900: .getTimestamp("PostLastEditDate"));
0901: bean.setPostCreationIP(resultSet
0902: .getString("PostCreationIP"));
0903: bean.setPostLastEditIP(resultSet
0904: .getString("PostLastEditIP"));
0905: bean
0906: .setPostEditCount(resultSet
0907: .getInt("PostEditCount"));
0908: bean.setPostFormatOption(resultSet
0909: .getInt("PostFormatOption"));
0910: bean.setPostOption(resultSet.getInt("PostOption"));
0911: bean.setPostStatus(resultSet.getInt("PostStatus"));
0912: bean.setPostIcon(resultSet.getString("PostIcon"));
0913: bean.setPostAttachCount(resultSet
0914: .getInt("PostAttachCount"));
0915: retValue.add(bean);
0916: if (retValue.size() == rowsToReturn)
0917: break;// Fix the Sybase bug
0918: }
0919: return retValue;
0920: } catch (SQLException sqle) {
0921: log.error("Sql Execution Error!", sqle);
0922: throw new DatabaseException(
0923: "Error executing SQL in PostDAOImplJDBC.getBeans_inThread_limit_noscroll.");
0924: } finally {
0925: DBUtils.closeResultSet(resultSet);
0926: DBUtils.resetStatement(statement);
0927: DBUtils.closeStatement(statement);
0928: DBUtils.closeConnection(connection);
0929: }
0930: }
0931:
0932: /*
0933: * Included columns: PostID, ParentPostID, ForumID, ThreadID, MemberID,
0934: * MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate,
0935: * PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption,
0936: * PostOption, PostStatus, PostIcon, PostAttachCount
0937: * Excluded columns:
0938: */
0939: private Collection getBeans_inThread_limit_general(int threadID,
0940: int offset, int rowsToReturn, boolean enable)
0941: throws IllegalArgumentException, DatabaseException {
0942: if (offset < 0)
0943: throw new IllegalArgumentException(
0944: "The offset < 0 is not allowed.");
0945: if (rowsToReturn <= 0)
0946: throw new IllegalArgumentException(
0947: "The rowsToReturn <= 0 is not allowed.");
0948:
0949: Connection connection = null;
0950: PreparedStatement statement = null;
0951: ResultSet resultSet = null;
0952: Collection retValue = new ArrayList();
0953: StringBuffer sql = new StringBuffer(512);
0954: sql
0955: .append("SELECT PostID, ParentPostID, ForumID, ThreadID, MemberID, MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate, PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption, PostOption, PostStatus, PostIcon, PostAttachCount");
0956: sql.append(" FROM " + TABLE_NAME);
0957: sql.append(" WHERE ThreadID = ?");
0958: if (enable) {
0959: sql.append(" AND PostStatus <> 1 ");
0960: } else {//disable
0961: sql.append(" AND PostStatus = 1 ");
0962: }
0963: sql.append(" ORDER BY PostID ASC ");
0964: try {
0965: connection = DBUtils.getConnection();
0966: statement = connection.prepareStatement(sql.toString(),
0967: ResultSet.TYPE_SCROLL_INSENSITIVE,
0968: ResultSet.CONCUR_READ_ONLY);
0969: statement.setMaxRows(offset + rowsToReturn);
0970: try {
0971: statement.setFetchSize(Math.min(rowsToReturn,
0972: DBUtils.MAX_FETCH_SIZE));
0973: } catch (SQLException sqle) {
0974: //do nothing, postgreSQL does not support this method
0975: }
0976:
0977: statement.setInt(1, threadID);
0978: resultSet = statement.executeQuery();
0979: boolean loop = resultSet.absolute(offset + 1);// the absolute method begin with 1 instead of 0 as in the LIMIT clause
0980: while (loop) {
0981: PostBean bean = new PostBean();
0982: bean.setPostID(resultSet.getInt("PostID"));
0983: bean.setParentPostID(resultSet.getInt("ParentPostID"));
0984: bean.setForumID(resultSet.getInt("ForumID"));
0985: bean.setThreadID(resultSet.getInt("ThreadID"));
0986: bean.setMemberID(resultSet.getInt("MemberID"));
0987: bean.setMemberName(resultSet.getString("MemberName"));
0988: bean.setLastEditMemberName(resultSet
0989: .getString("LastEditMemberName"));
0990: bean.setPostTopic(resultSet.getString("PostTopic"));
0991: bean.setPostBody(resultSet.getString("PostBody"));
0992: bean.setPostCreationDate(resultSet
0993: .getTimestamp("PostCreationDate"));
0994: bean.setPostLastEditDate(resultSet
0995: .getTimestamp("PostLastEditDate"));
0996: bean.setPostCreationIP(resultSet
0997: .getString("PostCreationIP"));
0998: bean.setPostLastEditIP(resultSet
0999: .getString("PostLastEditIP"));
1000: bean
1001: .setPostEditCount(resultSet
1002: .getInt("PostEditCount"));
1003: bean.setPostFormatOption(resultSet
1004: .getInt("PostFormatOption"));
1005: bean.setPostOption(resultSet.getInt("PostOption"));
1006: bean.setPostStatus(resultSet.getInt("PostStatus"));
1007: bean.setPostIcon(resultSet.getString("PostIcon"));
1008: bean.setPostAttachCount(resultSet
1009: .getInt("PostAttachCount"));
1010: retValue.add(bean);
1011: if (retValue.size() == rowsToReturn)
1012: break;// Fix the Sybase bug
1013: loop = resultSet.next();
1014: }//while
1015: return retValue;
1016: } catch (SQLException sqle) {
1017: log.error("Sql Execution Error!", sqle);
1018: throw new DatabaseException(
1019: "Error executing SQL in PostDAOImplJDBC.getBeans_inThread_limit_general.");
1020: } finally {
1021: DBUtils.closeResultSet(resultSet);
1022: DBUtils.resetStatement(statement);
1023: DBUtils.closeStatement(statement);
1024: DBUtils.closeConnection(connection);
1025: }
1026: }
1027:
1028: public int getNumberOfPosts_inMember(int memberID)
1029: throws DatabaseException {
1030:
1031: Connection connection = null;
1032: PreparedStatement statement = null;
1033: ResultSet resultSet = null;
1034: StringBuffer sql = new StringBuffer(512);
1035: sql.append("SELECT Count(*)");
1036: sql.append(" FROM " + TABLE_NAME);
1037: sql.append(" WHERE MemberID = ?");
1038: try {
1039: connection = DBUtils.getConnection();
1040: statement = connection.prepareStatement(sql.toString());
1041:
1042: statement.setInt(1, memberID);
1043:
1044: resultSet = statement.executeQuery();
1045: AssertionUtil
1046: .doAssert(resultSet.next(),
1047: "Assertion in PostDAOImplJDBC.getNumberOfPosts_inMember.");
1048: return resultSet.getInt(1);
1049: } catch (SQLException sqle) {
1050: log.error("Sql Execution Error!", sqle);
1051: throw new DatabaseException(
1052: "Error executing SQL in PostDAOImplJDBC.getNumberOfPosts_inMember(memberID).");
1053: } finally {
1054: DBUtils.closeResultSet(resultSet);
1055: DBUtils.closeStatement(statement);
1056: DBUtils.closeConnection(connection);
1057: }
1058: }
1059:
1060: public int getNumberOfEnablePosts_inForum(int forumID)
1061: throws DatabaseException {
1062:
1063: return getNumberOfPosts_inForum(forumID, true);
1064: }
1065:
1066: public int getNumberOfDisablePosts_inForum(int forumID)
1067: throws DatabaseException {
1068:
1069: return getNumberOfPosts_inForum(forumID, false);
1070: }
1071:
1072: public int getNumberOfPosts_inForum(int forumID, boolean enable)
1073: throws DatabaseException {
1074:
1075: Connection connection = null;
1076: PreparedStatement statement = null;
1077: ResultSet resultSet = null;
1078: StringBuffer sql = new StringBuffer(512);
1079: sql.append("SELECT Count(*)");
1080: sql.append(" FROM " + TABLE_NAME);
1081: sql.append(" WHERE ForumID = ? ");
1082: if (enable) {
1083: sql.append(" AND PostStatus <> 1 ");
1084: } else {//disable
1085: sql.append(" AND PostStatus = 1 ");
1086: }
1087: try {
1088: connection = DBUtils.getConnection();
1089: statement = connection.prepareStatement(sql.toString());
1090:
1091: statement.setInt(1, forumID);
1092:
1093: resultSet = statement.executeQuery();
1094: AssertionUtil
1095: .doAssert(resultSet.next(),
1096: "Assertion in PostDAOImplJDBC.getNumberOfEnablePosts_inForum.");
1097: return resultSet.getInt(1);
1098: } catch (SQLException sqle) {
1099: log.error("Sql Execution Error!", sqle);
1100: throw new DatabaseException(
1101: "Error executing SQL in PostDAOImplJDBC.getNumberOfPosts_inForum(forumID).");
1102: } finally {
1103: DBUtils.closeResultSet(resultSet);
1104: DBUtils.closeStatement(statement);
1105: DBUtils.closeConnection(connection);
1106: }
1107: }
1108:
1109: public int getNumberOfEnablePosts_inThread(int threadID)
1110: throws DatabaseException {
1111:
1112: return getNumberOfPosts_inThread(threadID, true);
1113: }
1114:
1115: public int getNumberOfDisablePosts_inThread(int threadID)
1116: throws DatabaseException {
1117:
1118: return getNumberOfPosts_inThread(threadID, false);
1119: }
1120:
1121: public int getNumberOfPosts_inThread(int threadID, boolean enable)
1122: throws DatabaseException {
1123:
1124: Connection connection = null;
1125: PreparedStatement statement = null;
1126: ResultSet resultSet = null;
1127: StringBuffer sql = new StringBuffer(512);
1128: sql.append("SELECT Count(*)");
1129: sql.append(" FROM " + TABLE_NAME);
1130: sql.append(" WHERE ThreadID = ?");
1131: if (enable) {
1132: sql.append(" AND PostStatus <> 1 ");
1133: } else {//disable
1134: sql.append(" AND PostStatus = 1 ");
1135: }
1136: try {
1137: connection = DBUtils.getConnection();
1138: statement = connection.prepareStatement(sql.toString());
1139:
1140: statement.setInt(1, threadID);
1141:
1142: resultSet = statement.executeQuery();
1143: AssertionUtil
1144: .doAssert(resultSet.next(),
1145: "Assertion in PostDAOImplJDBC.getNumberOfPosts_inThread.");
1146: return resultSet.getInt(1);
1147: } catch (SQLException sqle) {
1148: log.error("Sql Execution Error!", sqle);
1149: throw new DatabaseException(
1150: "Error executing SQL in PostDAOImplJDBC.getNumberOfPosts_inThread(threadID).");
1151: } finally {
1152: DBUtils.closeResultSet(resultSet);
1153: DBUtils.closeStatement(statement);
1154: DBUtils.closeConnection(connection);
1155: }
1156: }
1157:
1158: public void updateParentPostID(int oldParentPostID,
1159: int newParentPostID) throws ObjectNotFoundException,
1160: DatabaseException {
1161:
1162: Connection connection = null;
1163: PreparedStatement statement = null;
1164: StringBuffer sql = new StringBuffer(512);
1165: sql.append("UPDATE " + TABLE_NAME + " SET ParentPostID = ?");
1166: sql.append(" WHERE ParentPostID = ?");
1167: try {
1168: connection = DBUtils.getConnection();
1169: statement = connection.prepareStatement(sql.toString());
1170:
1171: // column(s) to update
1172: statement.setInt(1, newParentPostID);
1173:
1174: // condition column
1175: statement.setInt(2, oldParentPostID);
1176:
1177: if (statement.executeUpdate() != 1) {
1178: throw new ObjectNotFoundException(
1179: "No row is updated in table Post where ParentPostID = ("
1180: + oldParentPostID + ").");
1181: }
1182: setDirty(true);
1183: } catch (SQLException sqle) {
1184: log.error("Sql Execution Error!", sqle);
1185: throw new DatabaseException(
1186: "Error executing SQL in PostDAOImplJDBC.updateParentPostID.");
1187: } finally {
1188: DBUtils.closeStatement(statement);
1189: DBUtils.closeConnection(connection);
1190: }
1191: }
1192:
1193: /**
1194: * This method should be call only when we can make sure that postID is in database
1195: */
1196: public void increaseEditCount(int postID) throws DatabaseException,
1197: ObjectNotFoundException {
1198:
1199: Connection connection = null;
1200: PreparedStatement statement = null;
1201: String sql = "UPDATE "
1202: + TABLE_NAME
1203: + " SET PostEditCount = PostEditCount + 1 WHERE PostID = ?";
1204: try {
1205: connection = DBUtils.getConnection();
1206: statement = connection.prepareStatement(sql);
1207: statement.setInt(1, postID);
1208: if (statement.executeUpdate() != 1) {
1209: throw new ObjectNotFoundException(
1210: "Cannot update the PostEditCount in table Post. Please contact Web site Administrator.");
1211: }
1212: //@todo: coi lai cho nay
1213: // ATTENTION !!!
1214: setDirty(true);
1215: } catch (SQLException sqle) {
1216: log.error("Sql Execution Error!", sqle);
1217: throw new DatabaseException(
1218: "Error executing SQL in PostDAOImplJDBC.increaseEditCount.");
1219: } finally {
1220: DBUtils.closeStatement(statement);
1221: DBUtils.closeConnection(connection);
1222: }
1223: }
1224:
1225: /*
1226: * Included columns: PostID, ParentPostID, ForumID, ThreadID, MemberID,
1227: * MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate,
1228: * PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption,
1229: * PostOption, PostStatus, PostIcon, PostAttachCount
1230: * Excluded columns:
1231: */
1232: public Collection getLastEnablePosts_inThread_limit(int threadID,
1233: int rowsToReturn) throws IllegalArgumentException,
1234: DatabaseException {
1235: if (rowsToReturn <= 0)
1236: throw new IllegalArgumentException(
1237: "The rowsToReturn <= 0 is not allowed.");
1238:
1239: Connection connection = null;
1240: PreparedStatement statement = null;
1241: ResultSet resultSet = null;
1242: Collection retValue = new ArrayList();
1243: StringBuffer sql = new StringBuffer(512);
1244: sql
1245: .append("SELECT PostID, ParentPostID, ForumID, ThreadID, MemberID, MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate, PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption, PostOption, PostStatus, PostIcon, PostAttachCount");
1246: sql.append(" FROM " + TABLE_NAME);
1247: sql.append(" WHERE ThreadID = ? AND PostStatus = 0");
1248: sql.append(" ORDER BY PostCreationDate DESC ");
1249: try {
1250: connection = DBUtils.getConnection();
1251: statement = connection.prepareStatement(sql.toString());
1252: statement.setMaxRows(rowsToReturn);
1253: try {
1254: statement.setFetchSize(Math.min(rowsToReturn,
1255: DBUtils.MAX_FETCH_SIZE));
1256: } catch (SQLException sqle) {
1257: //do nothing, postgreSQL does not support this method
1258: }
1259:
1260: statement.setInt(1, threadID);
1261: resultSet = statement.executeQuery();
1262: while (resultSet.next()) {
1263: PostBean bean = new PostBean();
1264: bean.setPostID(resultSet.getInt("PostID"));
1265: bean.setParentPostID(resultSet.getInt("ParentPostID"));
1266: bean.setForumID(resultSet.getInt("ForumID"));
1267: bean.setThreadID(resultSet.getInt("ThreadID"));
1268: bean.setMemberID(resultSet.getInt("MemberID"));
1269: bean.setMemberName(resultSet.getString("MemberName"));
1270: bean.setLastEditMemberName(resultSet
1271: .getString("LastEditMemberName"));
1272: bean.setPostTopic(resultSet.getString("PostTopic"));
1273: bean.setPostBody(resultSet.getString("PostBody"));
1274: bean.setPostCreationDate(resultSet
1275: .getTimestamp("PostCreationDate"));
1276: bean.setPostLastEditDate(resultSet
1277: .getTimestamp("PostLastEditDate"));
1278: bean.setPostCreationIP(resultSet
1279: .getString("PostCreationIP"));
1280: bean.setPostLastEditIP(resultSet
1281: .getString("PostLastEditIP"));
1282: bean
1283: .setPostEditCount(resultSet
1284: .getInt("PostEditCount"));
1285: bean.setPostFormatOption(resultSet
1286: .getInt("PostFormatOption"));
1287: bean.setPostOption(resultSet.getInt("PostOption"));
1288: bean.setPostStatus(resultSet.getInt("PostStatus"));
1289: bean.setPostIcon(resultSet.getString("PostIcon"));
1290: bean.setPostAttachCount(resultSet
1291: .getInt("PostAttachCount"));
1292: retValue.add(bean);
1293: if (retValue.size() == rowsToReturn)
1294: break;// Fix the Sybase bug
1295: }
1296: return retValue;
1297: } catch (SQLException sqle) {
1298: log.error("Sql Execution Error!", sqle);
1299: throw new DatabaseException(
1300: "Error executing SQL in PostDAOImplJDBC.getLastEnablePosts_inThread_limit.");
1301: } finally {
1302: DBUtils.closeResultSet(resultSet);
1303: DBUtils.resetStatement(statement);
1304: DBUtils.closeStatement(statement);
1305: DBUtils.closeConnection(connection);
1306: }
1307: }
1308:
1309: /*
1310: * Included columns: PostID, ParentPostID, ForumID, ThreadID, MemberID,
1311: * MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate,
1312: * PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption,
1313: * PostOption, PostStatus, PostIcon, PostAttachCount
1314: * Excluded columns:
1315: */
1316: public Collection getLastEnablePosts_inForum_limit(int forumID,
1317: int rowsToReturn) throws IllegalArgumentException,
1318: DatabaseException {
1319: if (rowsToReturn <= 0)
1320: throw new IllegalArgumentException(
1321: "The rowsToReturn <= 0 is not allowed.");
1322:
1323: Connection connection = null;
1324: PreparedStatement statement = null;
1325: ResultSet resultSet = null;
1326: Collection retValue = new ArrayList();
1327: StringBuffer sql = new StringBuffer(512);
1328: sql
1329: .append("SELECT PostID, ParentPostID, ForumID, ThreadID, MemberID, MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate, PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption, PostOption, PostStatus, PostIcon, PostAttachCount");
1330: sql.append(" FROM " + TABLE_NAME);
1331: sql.append(" WHERE ForumID = ? AND PostStatus = 0");// only get enable posts
1332: sql.append(" ORDER BY PostCreationDate DESC ");
1333: try {
1334: connection = DBUtils.getConnection();
1335: statement = connection.prepareStatement(sql.toString());
1336: statement.setMaxRows(rowsToReturn);
1337: try {
1338: statement.setFetchSize(Math.min(rowsToReturn,
1339: DBUtils.MAX_FETCH_SIZE));
1340: } catch (SQLException sqle) {
1341: //do nothing, postgreSQL does not support this method
1342: }
1343:
1344: statement.setInt(1, forumID);
1345: resultSet = statement.executeQuery();
1346: while (resultSet.next()) {
1347: PostBean bean = new PostBean();
1348: bean.setPostID(resultSet.getInt("PostID"));
1349: bean.setParentPostID(resultSet.getInt("ParentPostID"));
1350: bean.setForumID(resultSet.getInt("ForumID"));
1351: bean.setThreadID(resultSet.getInt("ThreadID"));
1352: bean.setMemberID(resultSet.getInt("MemberID"));
1353: bean.setMemberName(resultSet.getString("MemberName"));
1354: bean.setLastEditMemberName(resultSet
1355: .getString("LastEditMemberName"));
1356: bean.setPostTopic(resultSet.getString("PostTopic"));
1357: bean.setPostBody(resultSet.getString("PostBody"));
1358: bean.setPostCreationDate(resultSet
1359: .getTimestamp("PostCreationDate"));
1360: bean.setPostLastEditDate(resultSet
1361: .getTimestamp("PostLastEditDate"));
1362: bean.setPostCreationIP(resultSet
1363: .getString("PostCreationIP"));
1364: bean.setPostLastEditIP(resultSet
1365: .getString("PostLastEditIP"));
1366: bean
1367: .setPostEditCount(resultSet
1368: .getInt("PostEditCount"));
1369: bean.setPostFormatOption(resultSet
1370: .getInt("PostFormatOption"));
1371: bean.setPostOption(resultSet.getInt("PostOption"));
1372: bean.setPostStatus(resultSet.getInt("PostStatus"));
1373: bean.setPostIcon(resultSet.getString("PostIcon"));
1374: bean.setPostAttachCount(resultSet
1375: .getInt("PostAttachCount"));
1376: retValue.add(bean);
1377: if (retValue.size() == rowsToReturn)
1378: break;// Fix the Sybase bug
1379: }
1380: return retValue;
1381: } catch (SQLException sqle) {
1382: log.error("Sql Execution Error!", sqle);
1383: throw new DatabaseException(
1384: "Error executing SQL in PostDAOImplJDBC.getLastEnablePosts_inForum_limit.");
1385: } finally {
1386: DBUtils.closeResultSet(resultSet);
1387: DBUtils.resetStatement(statement);
1388: DBUtils.closeStatement(statement);
1389: DBUtils.closeConnection(connection);
1390: }
1391: }
1392:
1393: public int getNumberOfPosts() throws DatabaseException {
1394:
1395: Connection connection = null;
1396: PreparedStatement statement = null;
1397: ResultSet resultSet = null;
1398: StringBuffer sql = new StringBuffer(512);
1399: sql.append("SELECT Count(*)");
1400: sql.append(" FROM " + TABLE_NAME);
1401: //sql.append(" WHERE PostStatus = 0");
1402: try {
1403: connection = DBUtils.getConnection();
1404: statement = connection.prepareStatement(sql.toString());
1405: resultSet = statement.executeQuery();
1406: AssertionUtil.doAssert(resultSet.next(),
1407: "Assertion in PostDAOImplJDBC.getNumberOfPosts.");
1408: return resultSet.getInt(1);
1409: } catch (SQLException sqle) {
1410: log.error("Sql Execution Error!", sqle);
1411: throw new DatabaseException(
1412: "Error executing SQL in PostDAOImplJDBC.getNumberOfPosts.");
1413: } finally {
1414: DBUtils.closeResultSet(resultSet);
1415: DBUtils.closeStatement(statement);
1416: DBUtils.closeConnection(connection);
1417: }
1418: }
1419:
1420: /*
1421: * Included columns: PostID, ParentPostID, ForumID, ThreadID, MemberID,
1422: * PostTopic, PostBody, PostCreationDate, PostLastEditDate
1423: * Excluded columns: MemberName, LastEditMemberName, PostCreationIP, PostLastEditIP, PostEditCount,
1424: * PostFormatOption, PostOption, PostStatus, PostIcon, PostAttachCount
1425: */
1426: public Collection getPosts() throws DatabaseException {
1427:
1428: Connection connection = null;
1429: PreparedStatement statement = null;
1430: ResultSet resultSet = null;
1431: Collection retValue = new ArrayList();
1432: StringBuffer sql = new StringBuffer(512);
1433: sql
1434: .append("SELECT PostID, ParentPostID, ForumID, ThreadID, MemberID, PostTopic, PostBody, PostCreationDate, PostLastEditDate, PostAttachCount");
1435: sql.append(" FROM " + TABLE_NAME);
1436: //sql.append(" WHERE "); // @todo: uncomment as needed
1437: //sql.append(" ORDER BY ColumnName ASC|DESC "); // @todo: uncomment as needed
1438: try {
1439: connection = DBUtils.getConnection();
1440: statement = connection.prepareStatement(sql.toString());
1441: resultSet = statement.executeQuery();
1442: while (resultSet.next()) {
1443: PostBean bean = new PostBean();
1444: bean.setPostID(resultSet.getInt("PostID"));
1445: bean.setParentPostID(resultSet.getInt("ParentPostID"));
1446: bean.setForumID(resultSet.getInt("ForumID"));
1447: bean.setThreadID(resultSet.getInt("ThreadID"));
1448: bean.setMemberID(resultSet.getInt("MemberID"));
1449: bean.setPostTopic(resultSet.getString("PostTopic"));
1450: bean.setPostBody(resultSet.getString("PostBody"));
1451: bean.setPostCreationDate(resultSet
1452: .getTimestamp("PostCreationDate"));
1453: bean.setPostLastEditDate(resultSet
1454: .getTimestamp("PostLastEditDate"));
1455: bean.setPostAttachCount(resultSet
1456: .getInt("PostAttachCount"));
1457: retValue.add(bean);
1458: }
1459: return retValue;
1460: } catch (SQLException sqle) {
1461: log.error("Sql Execution Error!", sqle);
1462: throw new DatabaseException(
1463: "Error executing SQL in PostDAOImplJDBC.getPosts.");
1464: } finally {
1465: DBUtils.closeResultSet(resultSet);
1466: DBUtils.closeStatement(statement);
1467: DBUtils.closeConnection(connection);
1468: }
1469: }
1470:
1471: public int getMaxPostID() throws DatabaseException {
1472:
1473: Connection connection = null;
1474: PreparedStatement statement = null;
1475: ResultSet resultSet = null;
1476: StringBuffer sql = new StringBuffer(512);
1477: sql.append("SELECT MAX(PostID)");
1478: sql.append(" FROM " + TABLE_NAME);
1479: try {
1480: connection = DBUtils.getConnection();
1481: statement = connection.prepareStatement(sql.toString());
1482: resultSet = statement.executeQuery();
1483: AssertionUtil.doAssert(resultSet.next(),
1484: "Assertion in PostDAOImplJDBC.getMaxPostID.");
1485: return resultSet.getInt(1);
1486: } catch (SQLException sqle) {
1487: log.error("Sql Execution Error!", sqle);
1488: throw new DatabaseException(
1489: "Error executing SQL in PostDAOImplJDBC.getMaxPostID.");
1490: } finally {
1491: DBUtils.closeResultSet(resultSet);
1492: DBUtils.closeStatement(statement);
1493: DBUtils.closeConnection(connection);
1494: }
1495: }
1496:
1497: /*
1498: * Included columns: PostID, ParentPostID, ForumID, ThreadID, MemberID,
1499: * MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate,
1500: * PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption,
1501: * PostOption, PostStatus, PostIcon, PostAttachCount
1502: * Excluded columns:
1503: */
1504: public Collection getPosts_fromIDRange(int fromID, int toID)
1505: throws IllegalArgumentException, DatabaseException {
1506:
1507: if (fromID < 0)
1508: throw new IllegalArgumentException(
1509: "The fromID < 0 is not allowed.");
1510: if (toID < fromID)
1511: throw new IllegalArgumentException(
1512: "toID < fromID is not allowed.");
1513:
1514: Connection connection = null;
1515: PreparedStatement statement = null;
1516: ResultSet resultSet = null;
1517: Collection retValue = new ArrayList();
1518: StringBuffer sql = new StringBuffer(512);
1519: sql
1520: .append("SELECT PostID, ParentPostID, ForumID, ThreadID, MemberID, MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate, PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption, PostOption, PostStatus, PostIcon, PostAttachCount");
1521: sql.append(" FROM " + TABLE_NAME);
1522: sql.append(" WHERE (PostID >= ?) AND (PostID <= ?)");
1523: sql.append(" ORDER BY PostID ASC ");
1524: try {
1525: connection = DBUtils.getConnection();
1526: //fix problem with oracle database when calling rebuild index [Vinaphone project]
1527: statement = connection.prepareStatement(sql.toString(),
1528: ResultSet.TYPE_SCROLL_INSENSITIVE,
1529: ResultSet.CONCUR_READ_ONLY);
1530: //statement = connection.prepareStatement(sql.toString());
1531: statement.setInt(1, fromID);
1532: statement.setInt(2, toID);
1533: resultSet = statement.executeQuery();
1534: while (resultSet.next()) {
1535: PostBean bean = new PostBean();
1536: bean.setPostID(resultSet.getInt("PostID"));
1537: bean.setParentPostID(resultSet.getInt("ParentPostID"));
1538: bean.setForumID(resultSet.getInt("ForumID"));
1539: bean.setThreadID(resultSet.getInt("ThreadID"));
1540: bean.setMemberID(resultSet.getInt("MemberID"));
1541: bean.setMemberName(resultSet.getString("MemberName"));
1542: bean.setLastEditMemberName(resultSet
1543: .getString("LastEditMemberName"));
1544: bean.setPostTopic(resultSet.getString("PostTopic"));
1545: bean.setPostBody(resultSet.getString("PostBody"));
1546: bean.setPostCreationDate(resultSet
1547: .getTimestamp("PostCreationDate"));
1548: bean.setPostLastEditDate(resultSet
1549: .getTimestamp("PostLastEditDate"));
1550: bean.setPostCreationIP(resultSet
1551: .getString("PostCreationIP"));
1552: bean.setPostLastEditIP(resultSet
1553: .getString("PostLastEditIP"));
1554: bean
1555: .setPostEditCount(resultSet
1556: .getInt("PostEditCount"));
1557: bean.setPostFormatOption(resultSet
1558: .getInt("PostFormatOption"));
1559: bean.setPostOption(resultSet.getInt("PostOption"));
1560: bean.setPostStatus(resultSet.getInt("PostStatus"));
1561: bean.setPostIcon(resultSet.getString("PostIcon"));
1562: bean.setPostAttachCount(resultSet
1563: .getInt("PostAttachCount"));
1564: retValue.add(bean);
1565: }
1566: return retValue;
1567: } catch (SQLException sqle) {
1568: log.error("Sql Execution Error!", sqle);
1569: throw new DatabaseException(
1570: "Error executing SQL in PostDAOImplJDBC.getPosts_fromIDRange.");
1571: } finally {
1572: DBUtils.closeResultSet(resultSet);
1573: DBUtils.closeStatement(statement);
1574: DBUtils.closeConnection(connection);
1575: }
1576: }
1577:
1578: /**
1579: * This is a special method. This method return a collection of ActiveMember
1580: * instead of a collection of PostBean
1581: */
1582: public Collection getMostActiveMembers(Timestamp since,
1583: int rowsToReturn) throws DatabaseException {
1584:
1585: Connection connection = null;
1586: PreparedStatement statement = null;
1587: ResultSet resultSet = null;
1588: Collection retValue = new ArrayList();
1589: StringBuffer sql = new StringBuffer(512);
1590: sql
1591: .append("SELECT MemberID, MemberName, COUNT(PostID) AS PostCount");// postgreSQL need AS
1592: sql.append(" FROM ").append(TABLE_NAME);
1593: sql.append(" WHERE PostCreationDate > ? AND PostStatus <> ")
1594: .append(PostBean.POST_STATUS_DISABLED);
1595: sql.append(" GROUP BY MemberID, MemberName");
1596: if (DBUtils.getDatabaseType() == DBUtils.DATABASE_FIREBIRD) {// should also interbase ???
1597: //sql.append(" ORDER BY 3 DESC");
1598: sql.append(" ORDER BY COUNT(PostID) DESC");
1599: } else {
1600: sql.append(" ORDER BY PostCount DESC");
1601: }
1602: try {
1603: connection = DBUtils.getConnection();
1604: statement = connection.prepareStatement(sql.toString());
1605: statement.setTimestamp(1, since);
1606: resultSet = statement.executeQuery();
1607: while (resultSet.next()) {
1608: ActiveMember member = new ActiveMember();
1609: member.setMemberID(resultSet.getInt("MemberID"));
1610: member.setMemberName(resultSet.getString("MemberName"));
1611: member.setLastPostCount(resultSet.getInt("PostCount"));
1612: retValue.add(member);
1613: if (retValue.size() == rowsToReturn)
1614: break;// Fix the Sybase bug
1615: }
1616: return retValue;
1617: } catch (SQLException sqle) {
1618: log.error("Sql Execution Error!", sqle);
1619: throw new DatabaseException(
1620: "Error executing SQL in PostDAOImplJDBC.getMostActiveMembers.");
1621: } finally {
1622: DBUtils.closeResultSet(resultSet);
1623: DBUtils.resetStatement(statement);
1624: DBUtils.closeStatement(statement);
1625: DBUtils.closeConnection(connection);
1626: }
1627: }
1628:
1629: public Collection getMostActiveThreads(Timestamp since,
1630: int rowsToReturn) throws DatabaseException {
1631:
1632: Connection connection = null;
1633: PreparedStatement statement = null;
1634: ResultSet resultSet = null;
1635: Collection retValue = new ArrayList();
1636: StringBuffer sql = new StringBuffer(512);
1637: sql
1638: .append("SELECT t.ThreadID, f.ForumID, t.MemberName, t.LastPostMemberName, t.ThreadCreationDate, ThreadLastPostDate, ThreadViewCount, ThreadReplyCount, ThreadType, ThreadPriority, ThreadTopic, ThreadAttachCount, ThreadHasPoll, ThreadIcon, COUNT(PostID) AS PostCount");// postgreSQL need AS
1639: sql.append(" FROM ").append(TABLE_NAME).append(" p , ").append(
1640: ThreadDAO.TABLE_NAME).append(" t,").append(
1641: ForumDAO.TABLE_NAME).append(" f");
1642: sql
1643: .append(
1644: " WHERE (t.ThreadID = p.ThreadID AND t.ThreadStatus <> ")
1645: .append(ThreadBean.THREAD_STATUS_DISABLED);
1646: sql.append(" AND p.ForumID = f.ForumID AND f.ForumStatus <> ")
1647: .append(ForumBean.FORUM_STATUS_DISABLED);
1648: sql.append(" AND PostCreationDate > ?)");
1649: sql
1650: .append(" GROUP BY t.ThreadID, f.ForumID, t.MemberName, t.LastPostMemberName, ThreadCreationDate, ThreadLastPostDate, ThreadViewCount, ThreadReplyCount, ThreadType, ThreadPriority, ThreadTopic, ThreadAttachCount, ThreadHasPoll, ThreadIcon");
1651: if (DBUtils.getDatabaseType() == DBUtils.DATABASE_FIREBIRD) {// should also interbase ???
1652: // 12 is the position of 'PostCount' in Select Query
1653: //sql.append(" ORDER BY 12 DESC, t.ThreadLastPostDate DESC");
1654: sql
1655: .append(" ORDER BY COUNT(PostID) DESC, t.ThreadLastPostDate DESC");
1656: } else {
1657: sql
1658: .append(" ORDER BY PostCount DESC, t.ThreadLastPostDate DESC");
1659: }
1660: try {
1661: connection = DBUtils.getConnection();
1662: statement = connection.prepareStatement(sql.toString());
1663: statement.setTimestamp(1, since);
1664: resultSet = statement.executeQuery();
1665: while (resultSet.next()) {
1666: ActiveThread thread = new ActiveThread();
1667: thread.setThreadID(resultSet.getInt("ThreadID"));
1668: thread.setThreadTopic(resultSet
1669: .getString("ThreadTopic"));
1670: thread.setForumID(resultSet.getInt("ForumID"));
1671: thread.setLastPostCount(resultSet.getInt("PostCount"));
1672: thread.setLastDate(resultSet
1673: .getTimestamp("ThreadLastPostDate"));
1674: thread.setAuthor(resultSet.getString("MemberName"));
1675: thread.setLastMember(resultSet
1676: .getString("LastPostMemberName"));
1677: thread.setThreadCreationDate(resultSet
1678: .getTimestamp("ThreadCreationDate"));
1679: thread.setThreadType(resultSet.getInt("ThreadType"));
1680: thread.setThreadPriority(resultSet
1681: .getInt("ThreadPriority"));
1682: thread
1683: .setViewCount(resultSet
1684: .getInt("ThreadViewCount"));
1685: thread.setPollCount(resultSet.getInt("ThreadHasPoll"));
1686: thread.setReplyCount(resultSet
1687: .getInt("ThreadReplyCount"));
1688: thread.setAttachCount(resultSet
1689: .getInt("ThreadAttachCount"));
1690: thread.setIcon(resultSet.getString("ThreadIcon"));
1691: retValue.add(thread);
1692: if (retValue.size() == rowsToReturn)
1693: break;// Fix the Sybase bug
1694: }
1695: return retValue;
1696: } catch (SQLException sqle) {
1697: log.error("Sql Execution Error!", sqle);
1698: throw new DatabaseException(
1699: "Error executing SQL in PostDAOImplJDBC.getMostActiveThreads.");
1700: } finally {
1701: DBUtils.closeResultSet(resultSet);
1702: DBUtils.resetStatement(statement);
1703: DBUtils.closeStatement(statement);
1704: DBUtils.closeConnection(connection);
1705: }
1706: }
1707:
1708: } // end of class PostDAOImplJDBC
|