Source Code Cross Referenced for ThreadDAOImplJDBC.java in  » Forum » mvnforum-1.1 » com » mvnforum » db » jdbc » Java Source Code / Java DocumentationJava Source Code and Java Documentation

Java Source Code / Java Documentation
1. 6.0 JDK Core
2. 6.0 JDK Modules
3. 6.0 JDK Modules com.sun
4. 6.0 JDK Modules com.sun.java
5. 6.0 JDK Modules sun
6. 6.0 JDK Platform
7. Ajax
8. Apache Harmony Java SE
9. Aspect oriented
10. Authentication Authorization
11. Blogger System
12. Build
13. Byte Code
14. Cache
15. Chart
16. Chat
17. Code Analyzer
18. Collaboration
19. Content Management System
20. Database Client
21. Database DBMS
22. Database JDBC Connection Pool
23. Database ORM
24. Development
25. EJB Server geronimo
26. EJB Server GlassFish
27. EJB Server JBoss 4.2.1
28. EJB Server resin 3.1.5
29. ERP CRM Financial
30. ESB
31. Forum
32. GIS
33. Graphic Library
34. Groupware
35. HTML Parser
36. IDE
37. IDE Eclipse
38. IDE Netbeans
39. Installer
40. Internationalization Localization
41. Inversion of Control
42. Issue Tracking
43. J2EE
44. JBoss
45. JMS
46. JMX
47. Library
48. Mail Clients
49. Net
50. Parser
51. PDF
52. Portal
53. Profiler
54. Project Management
55. Report
56. RSS RDF
57. Rule Engine
58. Science
59. Scripting
60. Search Engine
61. Security
62. Sevlet Container
63. Source Control
64. Swing Library
65. Template Engine
66. Test Coverage
67. Testing
68. UML
69. Web Crawler
70. Web Framework
71. Web Mail
72. Web Server
73. Web Services
74. Web Services apache cxf 2.0.1
75. Web Services AXIS2
76. Wiki Engine
77. Workflow Engines
78. XML
79. XML UI
Java
Java Tutorial
Java Open Source
Jar File Download
Java Articles
Java Products
Java by API
Photoshop Tutorials
Maya Tutorials
Flash Tutorials
3ds-Max Tutorials
Illustrator Tutorials
GIMP Tutorials
C# / C Sharp
C# / CSharp Tutorial
C# / CSharp Open Source
ASP.Net
ASP.NET Tutorial
JavaScript DHTML
JavaScript Tutorial
JavaScript Reference
HTML / CSS
HTML CSS Reference
C / ANSI-C
C Tutorial
C++
C++ Tutorial
Ruby
PHP
Python
Python Tutorial
Python Open Source
SQL Server / T-SQL
SQL Server / T-SQL Tutorial
Oracle PL / SQL
Oracle PL/SQL Tutorial
PostgreSQL
SQL / MySQL
MySQL Tutorial
VB.Net
VB.Net Tutorial
Flash / Flex / ActionScript
VBA / Excel / Access / Word
XML
XML Tutorial
Microsoft Office PowerPoint 2007 Tutorial
Microsoft Office Excel 2007 Tutorial
Microsoft Office Word 2007 Tutorial
Java Source Code / Java Documentation » Forum » mvnforum 1.1 » com.mvnforum.db.jdbc 
Source Cross Referenced  Class Diagram Java Document (Java Doc) 


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
www.java2java.com | Contact Us
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.