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