ThreadDAOImplJDBC


1   /*
2    * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/ThreadDAOImplJDBC.java,v 1.32 2006/04/14 17:05:26 minhnn Exp $
3    * $Author: minhnn $
4    * $Revision: 1.32 $
5    * $Date: 2006/04/14 17:05:26 $
6    *
7    * ====================================================================
8    *
9    * Copyright (C) 2002-2006 by MyVietnam.net
10   *
11   * All copyright notices regarding mvnForum MUST remain 
12   * intact in the scripts and in the outputted HTML.
13   * The "powered by" text/logo with a link back to
14   * http://www.mvnForum.com and http://www.MyVietnam.net in 
15   * the footer of the pages MUST remain visible when the pages
16   * are viewed on the internet or intranet.
17   *
18   * This program is free software; you can redistribute it and/or modify
19   * it under the terms of the GNU General Public License as published by
20   * the Free Software Foundation; either version 2 of the License, or
21   * any later version.
22   *
23   * This program is distributed in the hope that it will be useful,
24   * but WITHOUT ANY WARRANTY; without even the implied warranty of
25   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
26   * GNU General Public License for more details.
27   *
28   * You should have received a copy of the GNU General Public License
29   * along with this program; if not, write to the Free Software
30   * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
31   *
32   * Support can be obtained from support forums at:
33   * http://www.mvnForum.com/mvnforum/index
34   *
35   * Correspondence and Marketing Questions can be sent to:
36   * info at MyVietnam net
37   *
38   * @author: Minh Nguyen  
39   * @author: Mai  Nguyen  
40   */
41  package com.mvnforum.db.jdbc;
42  
43  import java.io.StringReader  ;
44  import java.sql.*;
45  import java.util.*;
46  
47  import com.mvnforum.db.*;
48  import net.myvietnam.mvncore.db.DBUtils;
49  import net.myvietnam.mvncore.exception.*;
50  import org.apache.commons.logging.Log;
51  import org.apache.commons.logging.LogFactory;
52  
53  public class ThreadDAOImplJDBC implements ThreadDAO {
54  
55      private static Log log = LogFactory.getLog(ThreadDAOImplJDBC.class);
56  
57      // this variable will support caching if cache for this class is needed
58      private static boolean m_dirty = true;
59  
60      public ThreadDAOImplJDBC() {
61      }
62  
63      protected static boolean isDirty() {
64          return m_dirty;
65      }
66  
67      protected static void setDirty(boolean dirty) {
68          m_dirty = dirty;
69      }
70  
71      public void findByPrimaryKey(int threadID)
72          throws ObjectNotFoundException, DatabaseException {
73  
74          Connection connection = null;
75          PreparedStatement statement = null;
76          ResultSet resultSet = null;
77          StringBuffer   sql = new StringBuffer  (512);
78          sql.append("SELECT ThreadID");
79          sql.append(" FROM " + TABLE_NAME);
80          sql.append(" WHERE ThreadID = ?");
81          try {
82              connection = DBUtils.getConnection();
83              statement = connection.prepareStatement(sql.toString());
84              statement.setInt(1, threadID);
85              resultSet = statement.executeQuery();
86              if (!resultSet.next()) {
87                  throw new ObjectNotFoundException("Cannot find the primary key (" + threadID + ") in table 'Thread'.");
88              }
89          } catch(SQLException sqle) {
90              log.error("Sql Execution Error!", sqle);
91              throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.findByPrimaryKey.");
92          } finally {
93              DBUtils.closeResultSet(resultSet);
94              DBUtils.closeStatement(statement);
95              DBUtils.closeConnection(connection);
96          }
97      }
98  
99      /*
100      * Included columns: ForumID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody,
101      *                   ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType,
102      *                   ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount,
103      *                   ThreadIcon, ThreadDuration
104      * Excluded columns: ThreadID
105      */
106     private void create(int forumID, String   memberName, String   lastPostMemberName,
107                         String   threadTopic, String   threadBody, int threadVoteCount,
108                         int threadVoteTotalStars, Timestamp threadCreationDate, Timestamp threadLastPostDate,
109                         int threadType, int threadOption, int threadStatus,
110                         int threadHasPoll, int threadViewCount, int threadReplyCount,
111                         String   threadIcon, int threadDuration, int threadAttachCount)
112         throws CreateException, DatabaseException, ForeignKeyNotFoundException {
113 
114         ThreadBean.validateThreadStatus(threadStatus);
115 
116         try {
117             // @todo: modify the parameter list as needed
118             // You may have to regenerate this method if the needed columns dont have attribute 'include'
119             DAOFactory.getForumDAO().findByPrimaryKey(forumID);
120         } catch(ObjectNotFoundException e) {
121             throw new ForeignKeyNotFoundException("Foreign key refers to table 'Forum' does not exist. Cannot create new Thread.");
122         }
123 
124         /* Here we allow memberName to be empty or null, which means
125            unknown user or guest created the thread. */
126         if ((memberName!=null) && (memberName.length()>0)) {
127             try {
128                 // @todo: modify the parameter list as needed
129                 // You may have to regenerate this method if the needed columns dont have attribute 'include'
130                 DAOFactory.getMemberDAO().findByAlternateKey_MemberName(memberName);
131             } catch(ObjectNotFoundException e) {
132                 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Member' does not exist. Cannot create new Post.");
133             }
134         } else {
135             memberName=""; // This is needed, otherwise we will get 'null' in the sql query, instead of ''
136         }
137 
138         //we also allow guests to send posts (if admin allows that)
139         if ((lastPostMemberName!=null) && (lastPostMemberName.length()>0)) {
140             try {
141                 DAOFactory.getMemberDAO().findByAlternateKey_MemberName(lastPostMemberName);
142             } catch(ObjectNotFoundException e) {
143                 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Member' does not exist. Cannot create table 'Thread'.");
144             }
145         } else {
146             lastPostMemberName=""; //so we don't get 'null' in sql query
147         }
148 
149         Connection connection = null;
150         PreparedStatement statement = null;
151         StringBuffer   sql = new StringBuffer  (512);
152         sql.append("INSERT INTO " + TABLE_NAME + " (ForumID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration, ThreadAttachCount)");
153         sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
154         try {
155             connection = DBUtils.getConnection();
156             statement = connection.prepareStatement(sql.toString());
157 
158             statement.setInt(1, forumID);
159             statement.setString(2, memberName);
160             statement.setString(3, lastPostMemberName);
161             statement.setString(4, threadTopic);
162             if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
163                 statement.setCharacterStream(5, new StringReader  (threadBody), threadBody.length());
164             } else {
165                 statement.setString(5, threadBody);
166             }
167             statement.setInt(6, threadVoteCount);
168             statement.setInt(7, threadVoteTotalStars);
169             statement.setTimestamp(8, threadCreationDate);
170             statement.setTimestamp(9, threadLastPostDate);
171             statement.setInt(10, threadType);
172             statement.setInt(11, threadOption);
173             statement.setInt(12, threadStatus);
174             statement.setInt(13, threadHasPoll);
175             statement.setInt(14, threadViewCount);
176             statement.setInt(15, threadReplyCount);
177             statement.setString(16, threadIcon);
178             statement.setInt(17, threadDuration);
179             statement.setInt(18, threadAttachCount);
180 
181             if (statement.executeUpdate() != 1) {
182                 throw new CreateException("Error adding a row into table 'Thread'.");
183             }
184             m_dirty = true;
185         } catch(SQLException sqle) {
186             log.error("Sql Execution Error!", sqle);
187             throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.create.");
188         } finally {
189             DBUtils.closeStatement(statement);
190             DBUtils.closeConnection(connection);
191         }
192     }
193 
194     public int createThread(int forumID, String   memberName, String   lastPostMemberName,
195                         String   threadTopic, String   threadBody, int threadVoteCount,
196                         int threadVoteTotalStars, Timestamp threadCreationDate, Timestamp threadLastPostDate,
197                         int threadType, int threadOption, int threadStatus,
198                         int threadHasPoll, int threadViewCount, int threadReplyCount,
199                         String   threadIcon, int threadDuration, int threadAttachCount)
200                         throws ObjectNotFoundException, CreateException, DatabaseException, ForeignKeyNotFoundException {
201 
202         create(forumID, memberName, lastPostMemberName, threadTopic, threadBody, threadVoteCount, threadVoteTotalStars, threadCreationDate, threadLastPostDate, threadType, threadOption, threadStatus, threadHasPoll, threadViewCount, threadReplyCount, threadIcon, threadDuration, threadAttachCount);
203         int threadID = 0;
204         try {
205             threadID = findThreadID(forumID, memberName, threadCreationDate);
206         } catch (ObjectNotFoundException ex) {
207             // Hack the Oracle 9i problem
208             Timestamp roundTimestamp = new Timestamp((threadCreationDate.getTime()/1000)*1000);
209             threadID = findThreadID(forumID, memberName, roundTimestamp);
210         }
211         return threadID;
212     }
213 
214     public void delete(int threadID)
215         throws DatabaseException, ObjectNotFoundException {
216 
217         Connection connection = null;
218         PreparedStatement statement = null;
219         StringBuffer   sql = new StringBuffer  (512);
220         sql.append("DELETE FROM " + TABLE_NAME);
221         sql.append(" WHERE ThreadID = ?");
222 
223         try {
224             connection = DBUtils.getConnection();
225             statement = connection.prepareStatement(sql.toString());
226             statement.setInt(1, threadID);
227             if (statement.executeUpdate() != 1) {
228                 throw new ObjectNotFoundException("Cannot delete a row in table Thread where primary key = (" + threadID + ").");
229             }
230             m_dirty = true;
231         } catch(SQLException sqle) {
232             log.error("Sql Execution Error!", sqle);
233             throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.delete.");
234         } finally {
235             DBUtils.closeStatement(statement);
236             DBUtils.closeConnection(connection);
237         }
238     }
239 
240     public void delete_inForum(int forumID)
241         throws DatabaseException {
242 
243         Connection connection = null;
244         PreparedStatement statement = null;
245         StringBuffer   sql = new StringBuffer  (512);
246         sql.append("DELETE FROM " + TABLE_NAME);
247         sql.append(" WHERE ForumID = ?");
248 
249         try {
250             connection = DBUtils.getConnection();
251             statement = connection.prepareStatement(sql.toString());
252             statement.setInt(1, forumID);
253 
254             statement.executeUpdate();
255             m_dirty = true;
256         } catch(SQLException sqle) {
257             log.error("Sql Execution Error!", sqle);
258             throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.delete_inForum.");
259         } finally {
260             DBUtils.closeStatement(statement);
261             DBUtils.closeConnection(connection);
262         }
263     }
264 
265     /*
266      * Included columns: ThreadTopic, ThreadBody, ThreadIcon
267      * Excluded columns: ThreadID, ForumID, MemberName, LastPostMemberName, ThreadVoteCount,
268      *                   ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadOption,
269      *                   ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount,
270      *                   ThreadDuration
271      */
272     public void updateTopic_Body_Icon(int threadID, // primary key
273                         String   threadTopic, String   threadBody, String   threadIcon)
274         throws ObjectNotFoundException, DatabaseException {
275 
276         Connection connection = null;
277         PreparedStatement statement = null;
278         StringBuffer   sql = new StringBuffer  (512);
279         sql.append("UPDATE " + TABLE_NAME + " SET ThreadTopic = ?, ThreadBody = ?, ThreadIcon = ?");
280         sql.append(" WHERE ThreadID = ?");
281         try {
282             connection = DBUtils.getConnection();
283             statement = connection.prepareStatement(sql.toString());
284 
285             // // column(s) to update
286             statement.setString(1, threadTopic);
287             if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
288                 statement.setCharacterStream(2, new StringReader  (threadBody), threadBody.length());
289             } else {
290                 statement.setString(2, threadBody);
291             }
292             statement.setString(3, threadIcon);
293 
294             // primary key column(s)
295             statement.setInt(4, threadID);
296 
297             if (statement.executeUpdate() != 1) {
298                 throw new ObjectNotFoundException("Cannot update table Thread where primary key = (" + threadID + ").");
299             }
300             m_dirty = true;
301         } catch(SQLException sqle) {
302             log.error("Sql Execution Error!", sqle);
303             throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.updateTopic_Body_Icon.");
304         } finally {
305             DBUtils.closeStatement(statement);
306             DBUtils.closeConnection(connection);
307         }
308     }
309 
310     public void updateThreadAttachCount(int threadID,
311                                         int attachCount)
312         throws ObjectNotFoundException, DatabaseException {
313 
314         Connection connection = null;
315         PreparedStatement statement = null;
316         StringBuffer   sql = new StringBuffer  (512);
317         sql.append("UPDATE " + TABLE_NAME + " SET ThreadAttachCount = ?");
318         sql.append(" WHERE ThreadID = ?");
319         try {
320             connection = DBUtils.getConnection();
321             statement = connection.prepareStatement(sql.toString());
322 
323             // // column(s) to update
324             statement.setInt(1, attachCount);
325 
326             // primary key column(s)
327             statement.setInt(2, threadID);
328 
329             if (statement.executeUpdate() != 1) {
330                 throw new ObjectNotFoundException("Cannot update ThreadAttachCount in table Thread where primary key = (" + threadID + ").");
331             }
332             m_dirty = true;
333         } catch(SQLException sqle) {
334             log.error("Sql Execution Error!", sqle);
335             throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.updateThreadAttachCount.");
336         } finally {
337             DBUtils.closeStatement(statement);
338             DBUtils.closeConnection(connection);
339         }
340     }
341 
342     /**
343      * This method should be call only when we can make sure that threadID is in database
344      */
345     public void increaseReplyCount(int threadID)
346         throws DatabaseException, ObjectNotFoundException {
347 
348         Connection connection = null;
349         PreparedStatement statement = null;
350         String   sql = "UPDATE " + TABLE_NAME + " SET ThreadReplyCount = ThreadReplyCount + 1 WHERE ThreadID = ?";
351         try {
352             connection = DBUtils.getConnection();
353             statement = connection.prepareStatement(sql);
354             statement.setInt(1, threadID);
355             if (statement.executeUpdate() != 1) {
356                 throw new ObjectNotFoundException("Cannot update the ThreadReplyCount in table Thread. Please contact Web site Administrator.");
357             }
358             //@todo: coi lai cho nay
359             // ATTENTION !!!
360             setDirty(true);
361         } catch (SQLException sqle) {
362             log.error("Sql Execution Error!", sqle);
363             throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.increaseReplyCount.");
364         } finally {
365             DBUtils.closeStatement(statement);
366             DBUtils.closeConnection(connection);
367         }
368     }
369 
370     /*
371      * Included columns: LastPostMemberName
372      * Excluded columns: ThreadID, ForumID, MemberName, ThreadTopic, ThreadBody,
373      *                   ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType,
374      *                   ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount,
375      *                   ThreadIcon, ThreadDuration
376      */
377     public void updateLastPostMemberName(int threadID, // primary key
378                                          String   lastPostMemberName)
379         throws ObjectNotFoundException, DatabaseException, ForeignKeyNotFoundException {
380 
381         //we also allow guests to send posts (if admin allows that)
382         if ((lastPostMemberName!=null) && (lastPostMemberName.length()>0)) {
383             try {
384                 // @todo: modify the parameter list as needed
385                 // If this method does not change the foreign key columns, you can comment this block of code.
386                 DAOFactory.getMemberDAO().findByAlternateKey_MemberName(lastPostMemberName);
387             } catch(ObjectNotFoundException e) {
388                 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Member' does not exist. Cannot update table 'Thread'.");
389             }
390         } else {
391             lastPostMemberName=""; //so we don't get 'null' in sql query
392         }
393 
394         Connection connection = null;
395         PreparedStatement statement = null;
396         StringBuffer   sql = new StringBuffer  (512);
397         sql.append("UPDATE " + TABLE_NAME + " SET LastPostMemberName = ?");
398         sql.append(" WHERE ThreadID = ?");
399         try {
400             connection = DBUtils.getConnection();
401             statement = connection.prepareStatement(sql.toString());
402 
403             // // column(s) to update
404             statement.setString(1, lastPostMemberName);
405 
406             // primary key column(s)
407             statement.setInt(2, threadID);
408 
409             if (statement.executeUpdate() != 1) {
410                 // Some drivers dont update database if it detect old and new data are the same
411                 // @todo: should check driver, not check database
412                 // Currently there is only one driver: Caucho MySql driver
413                 if ( DBUtils.getDatabaseType() != DBUtils.DATABASE_MYSQL ) {
414                     throw new ObjectNotFoundException("Cannot update table Thread where primary key = (" + threadID + ").");
415                 } else {
416                     log.warn("WARNING: By pass the check for Caucho MySql driver.");
417                 }
418             }
419             setDirty(true);
420         } catch(SQLException sqle) {
421             log.error("Sql Execution Error!", sqle);
422             throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.updateLastPostMemberName.");
423         } finally {
424             DBUtils.closeStatement(statement);
425             DBUtils.closeConnection(connection);
426         }
427     }
428 
429     /*
430      * Included columns: ThreadLastPostDate
431      * Excluded columns: ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic,
432      *                   ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadType,
433      *                   ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount,
434      *                   ThreadIcon, ThreadDuration
435      */
436     public void updateLastPostDate(int threadID, // primary key
437                         Timestamp threadLastPostDate)
438         throws ObjectNotFoundException, DatabaseException {
439 
440         Connection connection = null;
441         PreparedStatement statement = null;
442         StringBuffer   sql = new StringBuffer  (512);
443         sql.append("UPDATE " + TABLE_NAME + " SET ThreadLastPostDate = ?");
444         sql.append(" WHERE ThreadID = ?");
445         try {
446             connection = DBUtils.getConnection();
447             statement = connection.prepareStatement(sql.toString());
448 
449             // // column(s) to update
450             statement.setTimestamp(1, threadLastPostDate);
451 
452             // primary key column(s)
453             statement.setInt(2, threadID);
454 
455             if (statement.executeUpdate() != 1) {
456                 throw new ObjectNotFoundException("Cannot update table Thread where primary key = (" + threadID + ").");
457             }
458             m_dirty = true;
459         } catch(SQLException sqle) {
460             log.error("Sql Execution Error!", sqle);
461             throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.updateLastPostDate.");
462         } finally {
463             DBUtils.closeStatement(statement);
464             DBUtils.closeConnection(connection);
465         }
466     }
467 
468     /*
469      * Included columns: ForumID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody,
470      *                   ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType,
471      *                   ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount,
472      *                   ThreadIcon, ThreadDuration
473      * Excluded columns: ThreadID
474      */
475     public ThreadBean getThread(int threadID)
476         throws ObjectNotFoundException, DatabaseException {
477 
478         Connection connection = null;
479         PreparedStatement statement = null;
480         ResultSet resultSet = null;
481         StringBuffer   sql = new StringBuffer  (512);
482         sql.append("SELECT ForumID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration");
483         sql.append(" FROM " + TABLE_NAME);
484         sql.append(" WHERE ThreadID = ?");
485         try {
486             connection = DBUtils.getConnection();
487             statement = connection.prepareStatement(sql.toString());
488             statement.setInt(1, threadID);
489             resultSet = statement.executeQuery();
490             if(!resultSet.next()) {
491                 throw new ObjectNotFoundException("Cannot find the row in table Thread where primary key = (" + threadID + ").");
492             }
493 
494             ThreadBean bean = new ThreadBean();
495             // @todo: uncomment the following line(s) as needed
496             bean.setThreadID(threadID);
497             bean.setForumID(resultSet.getInt("ForumID"));
498             bean.setMemberName(resultSet.getString("MemberName"));
499             bean.setLastPostMemberName(resultSet.getString("LastPostMemberName"));
500             bean.setThreadTopic(resultSet.getString("ThreadTopic"));
501             bean.setThreadBody(resultSet.getString("ThreadBody"));
502             bean.setThreadVoteCount(resultSet.getInt("ThreadVoteCount"));
503             bean.setThreadVoteTotalStars(resultSet.getInt("ThreadVoteTotalStars"));
504             bean.setThreadCreationDate(resultSet.getTimestamp("ThreadCreationDate"));
505             bean.setThreadLastPostDate(resultSet.getTimestamp("ThreadLastPostDate"));
506             bean.setThreadType(resultSet.getInt("ThreadType"));
507             bean.setThreadOption(resultSet.getInt("ThreadOption"));
508             bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
509             bean.setThreadHasPoll(resultSet.getInt("ThreadHasPoll"));
510             bean.setThreadViewCount(resultSet.getInt("ThreadViewCount"));
511             bean.setThreadReplyCount(resultSet.getInt("ThreadReplyCount"));
512             bean.setThreadIcon(resultSet.getString("ThreadIcon"));
513             bean.setThreadDuration(resultSet.getInt("ThreadDuration"));
514             return bean;
515         } catch(SQLException sqle) {
516             log.error("Sql Execution Error!", sqle);
517             throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getThread(pk).");
518         } finally {
519             DBUtils.closeResultSet(resultSet);
520             DBUtils.closeStatement(statement);
521             DBUtils.closeConnection(connection);
522         }
523     }
524 
525     /**
526      * Note: this is a customized method
527      */
528     public int getNumberOfEnableThreads_inForum(int forumID)
529         throws AssertionException, DatabaseException {
530 
531         return getNumberOfBeans_inForum(forumID, true);
532     }
533 
534     public int getNumberOfDisableThreads_inForum(int forumID)
535         throws AssertionException, DatabaseException {
536 
537         return getNumberOfBeans_inForum(forumID, false);
538     }
539 
540     private int getNumberOfBeans_inForum(int forumID, boolean enable)
541         throws AssertionException, DatabaseException {
542 
543         Connection connection = null;
544         PreparedStatement statement = null;
545         ResultSet resultSet = null;
546         StringBuffer   sql = new StringBuffer  (512);
547         sql.append("SELECT Count(*)");
548         sql.append(" FROM " + TABLE_NAME);
549         sql.append(" WHERE ForumID = ?");
550         if (enable) {
551             sql.append(" AND ThreadStatus <> 1 ");
552         } else {//disable
553             sql.append(" AND ThreadStatus = 1 ");
554         }
555         try {
556             connection = DBUtils.getConnection();
557             statement = connection.prepareStatement(sql.toString());
558             statement.setInt(1, forumID);
559             resultSet = statement.executeQuery();
560             if (!resultSet.next()) {
561                 throw new AssertionException("Assertion in ThreadDAOImplJDBC.getNumberOfBeans_inForum.");
562             }
563             return resultSet.getInt(1);
564         } catch(SQLException sqle) {
565             log.error("Sql Execution Error!", sqle);
566             throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getNumberOfBeans_inForum.");
567         } finally {
568             DBUtils.closeResultSet(resultSet);
569             DBUtils.closeStatement(statement);
570             DBUtils.closeConnection(connection);
571         }
572     }
573 
574     public int getNumberOfNormalEnableThreads_inForum(int forumID)
575         throws AssertionException, DatabaseException {
576 
577         Connection connection = null;
578         PreparedStatement statement = null;
579         ResultSet resultSet = null;
580         StringBuffer   sql = new StringBuffer  (512);
581         sql.append("SELECT Count(*)");
582         sql.append(" FROM " + TABLE_NAME);
583         sql.append(" WHERE ForumID = ? AND ThreadType = ").append(ThreadBean.THREAD_TYPE_DEFAULT);
584         sql.append(" AND ThreadStatus <> 1 ");//mean enable thread
585         try {
586             connection = DBUtils.getConnection();
587             statement = connection.prepareStatement(sql.toString());
588             statement.setInt(1, forumID);
589             resultSet = statement.executeQuery();
590             if (!resultSet.next()) {
591                 throw new AssertionException("Assertion in ThreadDAOImplJDBC.getNumberOfNormalEnableThreads_inForum.");
592             }
593             return resultSet.getInt(1);
594         } catch(SQLException sqle) {
595             log.error("Sql Execution Error!", sqle);
596             throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getNumberOfNormalEnableThreads_inForum.");
597         } finally {
598             DBUtils.closeResultSet(resultSet);
599             DBUtils.closeStatement(statement);
600             DBUtils.closeConnection(connection);
601         }
602     }
603 
604     public int getNumberOfEnableThreads()
605         throws AssertionException, DatabaseException {
606 
607         return getNumberOfBeans(true);
608     }
609 
610     public int getNumberOfDisableThreads()
611         throws AssertionException, DatabaseException {
612 
613         return getNumberOfBeans(false);
614     }
615 
616     private int getNumberOfBeans(boolean enable)
617         throws AssertionException, DatabaseException {
618 
619         Connection connection = null;
620         PreparedStatement statement = null;
621         ResultSet resultSet = null;
622         StringBuffer   sql = new StringBuffer  (512);
623         sql.append("SELECT Count(*)");
624         sql.append(" FROM " + TABLE_NAME);
625         if (enable) {
626             sql.append(" WHERE ThreadStatus <> 1 ");
627         } else {//disable
628             sql.append(" WHERE ThreadStatus = 1 ");
629         }
630         try {
631             connection = DBUtils.getConnection();
632             statement = connection.prepareStatement(sql.toString());
633             resultSet = statement.executeQuery();
634             if (!resultSet.next()) {
635                 throw new AssertionException("Assertion in ThreadDAOImplJDBC.getNumberOfBeans.");
636             }
637             return resultSet.getInt(1);
638         } catch(SQLException sqle) {
639             log.error("Sql Execution Error!", sqle);
640             throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getNumberOfBeans.");
641         } finally {
642             DBUtils.closeResultSet(resultSet);
643             DBUtils.closeStatement(statement);
644             DBUtils.closeConnection(connection);
645         }
646     }
647 
648 
649 
650     /************************************************
651      * Customized methods come below
652      ************************************************/
653 
654 
655     /**
656      * This is a customized method
657      */
658     private int findThreadID(int forumID, String   memberName, Timestamp threadCreationDate)
659         throws ObjectNotFoundException, DatabaseException {
660 
661         Connection connection = null;
662         PreparedStatement statement = null;
663         ResultSet resultSet = null;
664         StringBuffer   sql = new StringBuffer  (512);
665         sql.append("SELECT ThreadID");
666         sql.append(" FROM " + TABLE_NAME);
667         sql.append(" WHERE ForumID = ? AND MemberName = ? AND ThreadCreationDate = ? ");
668         try {
669             connection = DBUtils.getConnection();
670             statement = connection.prepareStatement(sql.toString());
671             statement.setInt(1, forumID);
672             statement.setString(2, memberName);
673             statement.setTimestamp(3, threadCreationDate);
674             resultSet = statement.executeQuery();
675             if(!resultSet.next()) {
676                 throw new ObjectNotFoundException("Cannot find the ThreadID in table Thread.");
677             }
678 
679             return resultSet.getInt("ThreadID");
680         } catch(SQLException sqle) {
681             log.error("Sql Execution Error!", sqle);
682             throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.findThreadID.");
683         } finally {
684             DBUtils.closeResultSet(resultSet);
685             DBUtils.closeStatement(statement);
686             DBUtils.closeConnection(connection);
687         }
688     }
689 
690     /*
691      * Included columns: ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic,
692      *                   ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate,
693      *                   ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount,
694      *                   ThreadReplyCount, ThreadIcon, ThreadDuration
695      * Excluded columns:
696      */
697     public Collection getThreads_inFavorite_inMember(int memberID)
698         throws DatabaseException {
699 
700         Connection connection = null;
701         PreparedStatement statement = null;
702         ResultSet resultSet = null;
703         Collection retValue = new ArrayList();
704         StringBuffer   sql = new StringBuffer  (512);
705         sql.append("SELECT thread.ThreadID, thread.ForumID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration, ThreadAttachCount");
706         sql.append(" FROM " + TABLE_NAME + " thread, " + FavoriteThreadDAO.TABLE_NAME + " favorite ");
707         sql.append(" WHERE thread.ThreadID = favorite.ThreadID AND favorite.MemberID = ? ");
708         //sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
709         try {
710             connection = DBUtils.getConnection();
711             statement = connection.prepareStatement(sql.toString());
712             statement.setInt(1, memberID);
713             resultSet = statement.executeQuery();
714             while (resultSet.next()) {
715                 ThreadBean bean = new ThreadBean();
716                 bean.setThreadID(resultSet.getInt("ThreadID"));
717                 bean.setForumID(resultSet.getInt("ForumID"));
718                 bean.setMemberName(resultSet.getString("MemberName"));
719                 bean.setLastPostMemberName(resultSet.getString("LastPostMemberName"));
720                 bean.setThreadTopic(resultSet.getString("ThreadTopic"));
721                 bean.setThreadBody(resultSet.getString("ThreadBody"));
722                 bean.setThreadVoteCount(resultSet.getInt("ThreadVoteCount"));
723                 bean.setThreadVoteTotalStars(resultSet.getInt("ThreadVoteTotalStars"));
724                 bean.setThreadCreationDate(resultSet.getTimestamp("ThreadCreationDate"));
725                 bean.setThreadLastPostDate(resultSet.getTimestamp("ThreadLastPostDate"));
726                 bean.setThreadType(resultSet.getInt("ThreadType"));
727                 bean.setThreadOption(resultSet.getInt("ThreadOption"));
728                 bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
729                 bean.setThreadHasPoll(resultSet.getInt("ThreadHasPoll"));
730                 bean.setThreadViewCount(resultSet.getInt("ThreadViewCount"));
731                 bean.setThreadReplyCount(resultSet.getInt("ThreadReplyCount"));
732                 bean.setThreadIcon(resultSet.getString("ThreadIcon"));
733                 bean.setThreadDuration(resultSet.getInt("ThreadDuration"));
734                 bean.setThreadAttachCount(resultSet.getInt("ThreadAttachCount"));
735                 retValue.add(bean);
736             }
737             return retValue;
738         } catch(SQLException sqle) {
739             log.error("Sql Execution Error!", sqle);
740             throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getThreads_inFavorite_inMember.");
741         } finally {
742             DBUtils.closeResultSet(resultSet);
743             DBUtils.closeStatement(statement);
744             DBUtils.closeConnection(connection);
745         }
746     }
747 
748     public Collection getEnableThreads_withSortSupport_limit(int offset, int rowsToReturn, String   sort, String   order)
749         throws IllegalArgumentException  , DatabaseException {
750 
751         if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
752             return getBeans_withSortSupport_limit_mysql(offset, rowsToReturn, sort, order, true);
753         } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
754             return getBeans_withSortSupport_limit_noscroll(offset, rowsToReturn, sort, order, true);
755         }
756         return getBeans_withSortSupport_limit_general(offset, rowsToReturn, sort, order, true);
757     }
758 
759     public Collection getDisableBeans_withSortSupport_limit(int offset, int rowsToReturn, String   sort, String   order)
760         throws IllegalArgumentException  , DatabaseException {
761 
762         if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
763             return getBeans_withSortSupport_limit_mysql(offset, rowsToReturn, sort, order, false);
764         } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
765             return getBeans_withSortSupport_limit_noscroll(offset, rowsToReturn, sort, order, false);
766         }
767         return getBeans_withSortSupport_limit_general(offset, rowsToReturn, sort, order, false);
768     }
769 
770     /*
771      * Included columns: ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic,
772      *                   ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate,
773      *                   ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount,
774      *                   ThreadReplyCount, ThreadIcon, ThreadDuration
775      * Excluded columns:
776      */
777     private Collection getBeans_withSortSupport_limit_mysql(int offset, int rowsToReturn, String   sort, String   order, boolean enable)
778         throws IllegalArgumentException  , DatabaseException {
779 
780         if (offset < 0) throw new IllegalArgumentException  ("The offset < 0 is not allowed.");
781         if (rowsToReturn <= 0) throw new IllegalArgumentException  ("The rowsToReturn <= 0 is not allowed.");
782 
783         if ((!sort.equals("ThreadLastPostDate")) &&
784             (!sort.equals("ThreadCreationDate")) &&
785             (!sort.equals("MemberName")) &&
786             (!sort.equals("ThreadReplyCount")) &&
787             (!sort.equals("ForumID")) &&
788             (!sort.equals("ThreadViewCount")) ) {
789             throw new IllegalArgumentException  ("Cannot sort, reason: dont understand the criteria '" + sort + "'.");
790         }
791 
792         if ((!order.equals("ASC")) &&
793             (!order.equals("DESC")) ) {
794             throw new IllegalArgumentException  ("Cannot sort, reason: dont understand the order '" + order + "'.");
795         }
796 
797         Connection connection = null;
798         PreparedStatement statement = null;
799         ResultSet resultSet = null;
800         Collection retValue = new ArrayList();
801         StringBuffer   sql = new StringBuffer  (512);
802         sql.append("SELECT ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration, ThreadAttachCount");
803         sql.append(" FROM " + TABLE_NAME);
804         if (enable) {
805             sql.append(" WHERE ThreadStatus <> 1 ");
806         } else {// disable
807             sql.append(" WHERE ThreadStatus = 1 ");
808         }
809         sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
810         sql.append(" LIMIT ?, ?");
811         try {
812             connection = DBUtils.getConnection();
813             statement = connection.prepareStatement(sql.toString());
814             statement.setInt(1, offset);
815             statement.setInt(2, rowsToReturn);
816             resultSet = statement.executeQuery();
817             while (resultSet.next()) {
818                 ThreadBean bean = new ThreadBean();
819                 bean.setThreadID(resultSet.getInt("ThreadID"));
820                 bean.setForumID(resultSet.getInt("ForumID"));
821                 bean.setMemberName(resultSet.getString("MemberName"));
822                 bean.setLastPostMemberName(resultSet.getString("LastPostMemberName"));
823                 bean.setThreadTopic(resultSet.getString("ThreadTopic"));
824                 bean.setThreadBody(resultSet.getString("ThreadBody"));
825                 bean.setThreadVoteCount(resultSet.getInt("ThreadVoteCount"));
826                 bean.setThreadVoteTotalStars(resultSet.getInt("ThreadVoteTotalStars"));
827                 bean.setThreadCreationDate(resultSet.getTimestamp("ThreadCreationDate"));
828                 bean.setThreadLastPostDate(resultSet.getTimestamp("ThreadLastPostDate"));
829                 bean.setThreadType(resultSet.getInt("ThreadType"));
830                 bean.setThreadOption(resultSet.getInt("ThreadOption"));
831                 bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
832                 bean.setThreadHasPoll(resultSet.getInt("ThreadHasPoll"));
833                 bean.setThreadViewCount(resultSet.getInt("ThreadViewCount"));
834                 bean.setThreadReplyCount(resultSet.getInt("ThreadReplyCount"));
835                 bean.setThreadIcon(resultSet.getString("ThreadIcon"));
836                 bean.setThreadDuration(resultSet.getInt("ThreadDuration"));
837                 bean.setThreadAttachCount(resultSet.getInt("ThreadAttachCount"));
838                 retValue.add(bean);
839             }
840             return retValue;
841         } catch(SQLException sqle) {
842             log.error("Sql Execution Error!", sqle);
843             throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getBeans_withSortSupport_limit_mysql.");
844         } finally {
845             DBUtils.closeResultSet(resultSet);
846             DBUtils.closeStatement(statement);
847             DBUtils.closeConnection(connection);
848         }
849     }
850 
851     /*
852      * Included columns: ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic,
853      *                   ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate,
854      *                   ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount,
855      *                   ThreadReplyCount, ThreadIcon, ThreadDuration
856      * Excluded columns:
857      */
858     private Collection getBeans_withSortSupport_limit_noscroll(int offset, int rowsToReturn, String   sort, String   order, boolean enable)
859         throws IllegalArgumentException  , DatabaseException {
860 
861         if (offset < 0) throw new IllegalArgumentException  ("The offset < 0 is not allowed.");
862         if (rowsToReturn <= 0) throw new IllegalArgumentException  ("The rowsToReturn <= 0 is not allowed.");
863 
864         if ((!sort.equals("ThreadLastPostDate")) &&
865             (!sort.equals("ThreadCreationDate")) &&
866             (!sort.equals("MemberName")) &&
867             (!sort.equals("ThreadReplyCount")) &&
868             (!sort.equals("ForumID")) &&
869             (!sort.equals("ThreadViewCount")) ) {
870             throw new IllegalArgumentException  ("Cannot sort, reason: dont understand the criteria '" + sort + "'.");
871         }
872 
873         if ((!order.equals("ASC")) &&
874             (!order.equals("DESC")) ) {
875             throw new IllegalArgumentException  ("Cannot sort, reason: dont understand the order '" + order + "'.");
876         }
877 
878         Connection connection = null;
879         PreparedStatement statement = null;
880         ResultSet resultSet = null;
881         Collection retValue = new ArrayList();
882         StringBuffer   sql = new StringBuffer  (512);
883         sql.append("SELECT ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration, ThreadAttachCount");
884         sql.append(" FROM " + TABLE_NAME);
885         if (enable) {
886             sql.append(" WHERE ThreadStatus <> 1 ");
887         } else {// disable
888             sql.append(" WHERE ThreadStatus = 1 ");
889         }
890         sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
891         try {
892             connection = DBUtils.getConnection();
893             statement = connection.prepareStatement(sql.toString());
894             statement.setMaxRows(offset + rowsToReturn);
895             resultSet = statement.executeQuery();
896             int rowIndex = -1;
897             while (resultSet.next()) {
898                 rowIndex++;
899                 if (rowIndex < offset) continue;
900                 ThreadBean bean = new ThreadBean();
901                 bean.setThreadID(resultSet.getInt("ThreadID"));
902                 bean.setForumID(resultSet.getInt("ForumID"));
903                 bean.setMemberName(resultSet.getString("MemberName"));
904                 bean.setLastPostMemberName(resultSet.getString("LastPostMemberName"));
905                 bean.setThreadTopic(resultSet.getString("ThreadTopic"));
906                 bean.setThreadBody(resultSet.getString("ThreadBody"));
907                 bean.setThreadVoteCount(resultSet.getInt("ThreadVoteCount"));
908                 bean.setThreadVoteTotalStars(resultSet.getInt("ThreadVoteTotalStars"));
909                 bean.setThreadCreationDate(resultSet.getTimestamp("ThreadCreationDate"));
910                 bean.setThreadLastPostDate(resultSet.getTimestamp("ThreadLastPostDate"));
911                 bean.setThreadType(resultSet.getInt("ThreadType"));
912                 bean.setThreadOption(resultSet.getInt("ThreadOption"));
913                 bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
914                 bean.setThreadHasPoll(resultSet.getInt("ThreadHasPoll"));
915                 bean.setThreadViewCount(resultSet.getInt("ThreadViewCount"));
916                 bean.setThreadReplyCount(resultSet.getInt("ThreadReplyCount"));
917                 bean.setThreadIcon(resultSet.getString("ThreadIcon"));
918                 bean.setThreadDuration(resultSet.getInt("ThreadDuration"));
919                 bean.setThreadAttachCount(resultSet.getInt("ThreadAttachCount"));
920                 retValue.add(bean);
921                 if (retValue.size() == rowsToReturn) break;// Fix the Sybase bug
922             }
923             return retValue;
924         } catch(SQLException sqle) {
925             log.error("Sql Execution Error!", sqle);
926             throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getBeans_withSortSupport_limit_noscroll.");
927         } finally {
928             DBUtils.closeResultSet(resultSet);
929             DBUtils.resetStatement(statement);
930             DBUtils.closeStatement(statement);
931             DBUtils.closeConnection(connection);
932         }
933     }
934 
935     /*
936      * Included columns: ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic,
937      *                   ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate,
938      *                   ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount,
939      *                   ThreadReplyCount, ThreadIcon, ThreadDuration
940      * Excluded columns:
941      */
942     private Collection getBeans_withSortSupport_limit_general(int offset, int rowsToReturn, String   sort, String   order, boolean enable)
943         throws IllegalArgumentException  , DatabaseException {
944 
945         if (offset < 0) throw new IllegalArgumentException  ("The offset < 0 is not allowed.");
946         if (rowsToReturn <= 0) throw new IllegalArgumentException  ("The rowsToReturn <= 0 is not allowed.");
947 
948         if ((!sort.equals("ThreadLastPostDate")) &&
949             (!sort.equals("ThreadCreationDate")) &&
950             (!sort.equals("MemberName")) &&
951             (!sort.equals("ThreadReplyCount")) &&
952             (!sort.equals("ForumID")) &&
953             (!sort.equals("ThreadViewCount")) ) {
954             throw new IllegalArgumentException  ("Cannot sort, reason: dont understand the criteria '" + sort + "'.");
955         }
956 
957         if ((!order.equals("ASC")) &&
958             (!order.equals("DESC")) ) {
959             throw new IllegalArgumentException  ("Cannot sort, reason: dont understand the order '" + order + "'.");
960         }
961 
962         Connection connection = null;
963         PreparedStatement statement = null;
964         ResultSet resultSet = null;
965         Collection retValue = new ArrayList();
966         StringBuffer   sql = new StringBuffer  (512);
967         sql.append("SELECT ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration, ThreadAttachCount");
968         sql.append(" FROM " + TABLE_NAME);
969         if (enable) {
970             sql.append(" WHERE ThreadStatus <> 1 ");
971         } else {// disable
972             sql.append(" WHERE ThreadStatus = 1 ");
973         }
974         sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
975         try {
976             connection = DBUtils.getConnection();
977             statement = connection.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
978             statement.setMaxRows(offset + rowsToReturn);
979             try {
980                 statement.setFetchSize(rowsToReturn);
981             } catch (SQLException sqle) {
982                 //do nothing, postgreSQL doesnt support this method
983             }
984             resultSet = statement.executeQuery();
985             boolean loop = resultSet.absolute(offset + 1);// the absolute method begin with 1 instead of 0 as in the LIMIT clause
986             while (loop) {
987                 ThreadBean bean = new ThreadBean();
988                 bean.setThreadID(resultSet.getInt("ThreadID"));
989                 bean.setForumID(resultSet.getInt("ForumID"));
990                 bean.setMemberName(resultSet.getString("MemberName"));
991                 bean.setLastPostMemberName(resultSet.getString("LastPostMemberName"));
992                 bean.setThreadTopic(resultSet.getString("ThreadTopic"));
993                 bean.setThreadBody(resultSet.getString("ThreadBody"));
994                 bean.setThreadVoteCount(resultSet.getInt("ThreadVoteCount"));
995                 bean.setThreadVoteTotalStars(resultSet.getInt("ThreadVoteTotalStars"));
996                 bean.setThreadCreationDate(resultSet.getTimestamp("ThreadCreationDate"));
997                 bean.setThreadLastPostDate(resultSet.getTimestamp("ThreadLastPostDate"));
998                 bean.setThreadType(resultSet.getInt("ThreadType"));
999                 bean.setThreadOption(resultSet.getInt("ThreadOption"));
1000                bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
1001                bean.setThreadHasPoll(resultSet.getInt("ThreadHasPoll"));
1002                bean.setThreadViewCount(resultSet.getInt("ThreadViewCount"));
1003                bean.setThreadReplyCount(resultSet.getInt("ThreadReplyCount"));
1004                bean.setThreadIcon(resultSet.getString("ThreadIcon"));
1005                bean.setThreadDuration(resultSet.getInt("ThreadDuration"));
1006                bean.setThreadAttachCount(resultSet.getInt("ThreadAttachCount"));
1007                retValue.add(bean);
1008                if (retValue.size() == rowsToReturn) break;// Fix the Sybase bug
1009                loop = resultSet.next();
1010            }//while
1011            return retValue;
1012        } catch(SQLException sqle) {
1013            log.error("Sql Execution Error!", sqle);
1014            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getBeans_withSortSupport_limit_general.");
1015        } finally {
1016            DBUtils.closeResultSet(resultSet);
1017            DBUtils.resetStatement(statement);
1018            DBUtils.closeStatement(statement);
1019            DBUtils.closeConnection(connection);
1020        }
1021    }
1022
1023    public Collection getNormalEnableThreads_inForum_withSortSupport_limit(int forumID, int offset, int rowsToReturn, String   sort, String   order)
1024        throws IllegalArgumentException  , DatabaseException {
1025
1026        if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
1027            return getBeans_inForum_withSortSupport_limit_mysql(forumID, offset, rowsToReturn, sort, order, true, true);
1028        } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
1029            return getBeans_inForum_withSortSupport_limit_noscroll(forumID, offset, rowsToReturn, sort, order, true, true);
1030        }
1031        return getBeans_inForum_withSortSupport_limit_general(forumID, offset, rowsToReturn, sort, order, true, true);
1032    }
1033
1034    public Collection getAllEnableThreads_inForum_withSortSupport_limit(int forumID, int offset, int rowsToReturn, String   sort, String   order)
1035        throws IllegalArgumentException  , DatabaseException {
1036
1037        if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
1038            return getBeans_inForum_withSortSupport_limit_mysql(forumID, offset, rowsToReturn, sort, order, true, false);
1039        } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
1040            return getBeans_inForum_withSortSupport_limit_noscroll(forumID, offset, rowsToReturn, sort, order, true, false);
1041        }
1042        return getBeans_inForum_withSortSupport_limit_general(forumID, offset, rowsToReturn, sort, order, true, false);
1043    }
1044
1045    public Collection getDisableThreads_inForum_withSortSupport_limit(int forumID, int offset, int rowsToReturn, String   sort, String   order)
1046        throws IllegalArgumentException  , DatabaseException {
1047
1048        if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
1049            return getBeans_inForum_withSortSupport_limit_mysql(forumID, offset, rowsToReturn, sort, order, false, false);
1050        } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
1051            return getBeans_inForum_withSortSupport_limit_noscroll(forumID, offset, rowsToReturn, sort, order, false, false);
1052        }
1053        return getBeans_inForum_withSortSupport_limit_general(forumID, offset, rowsToReturn, sort, order, false, false);
1054    }
1055
1056    /*
1057     * Included columns: ThreadID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody,
1058     *                   ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType,
1059     *                   ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount,
1060     *                   ThreadIcon, ThreadDuration
1061     * Excluded columns: ForumID
1062     */
1063    /**
1064     * Note: This is a customized method
1065     */
1066    private Collection getBeans_inForum_withSortSupport_limit_mysql(int forumID, int offset, int rowsToReturn, String   sort, String   order, boolean enable, boolean onlyNormalThread)
1067        throws IllegalArgumentException  , DatabaseException {
1068
1069        if (offset < 0) throw new IllegalArgumentException  ("The offset < 0 is not allowed.");
1070        if (rowsToReturn <= 0) throw new IllegalArgumentException  ("The rowsToReturn <= 0 is not allowed.");
1071
1072        if ((!sort.equals("ThreadLastPostDate")) &&
1073            (!sort.equals("ThreadCreationDate")) &&
1074            (!sort.equals("MemberName")) &&
1075            (!sort.equals("ThreadReplyCount")) &&
1076            (!sort.equals("ThreadViewCount")) ) {
1077            throw new IllegalArgumentException  ("Cannot sort, reason: dont understand the criteria '" + sort + "'.");
1078        }
1079
1080        if ((!order.equals("ASC")) &&
1081            (!order.equals("DESC")) ) {
1082            throw new IllegalArgumentException  ("Cannot sort, reason: dont understand the order '" + order + "'.");
1083        }
1084
1085        Connection connection = null;
1086        PreparedStatement statement = null;
1087        ResultSet resultSet = null;
1088        Collection retValue = new ArrayList();
1089        StringBuffer   sql = new StringBuffer  (512);
1090        sql.append("SELECT ThreadID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration, ThreadAttachCount");
1091        sql.append(" FROM " + TABLE_NAME);
1092        sql.append(" WHERE ForumID = ? ");
1093        if (onlyNormalThread) {
1094            sql.append(" AND ThreadType = ").append(ThreadBean.THREAD_TYPE_DEFAULT);
1095        }
1096        if (enable) {
1097            sql.append(" AND ThreadStatus <> 1 ");
1098        } else {//disable
1099            sql.append(" AND ThreadStatus = 1 ");
1100        }
1101        sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
1102        sql.append(" LIMIT ?, ?");
1103        try {
1104            connection = DBUtils.getConnection();
1105            statement = connection.prepareStatement(sql.toString());
1106            statement.setInt(1, forumID);
1107            statement.setInt(2, offset);
1108            statement.setInt(3, rowsToReturn);
1109            resultSet = statement.executeQuery();
1110            while (resultSet.next()) {
1111                ThreadBean bean = new ThreadBean();
1112                bean.setThreadID(resultSet.getInt("ThreadID"));
1113                bean.setForumID(forumID);
1114                bean.setMemberName(resultSet.getString("MemberName"));
1115                bean.setLastPostMemberName(resultSet.getString("LastPostMemberName"));
1116                bean.setThreadTopic(resultSet.getString("ThreadTopic"));
1117                bean.setThreadBody(resultSet.getString("ThreadBody"));
1118                bean.setThreadVoteCount(resultSet.getInt("ThreadVoteCount"));
1119                bean.setThreadVoteTotalStars(resultSet.getInt("ThreadVoteTotalStars"));
1120                bean.setThreadCreationDate(resultSet.getTimestamp("ThreadCreationDate"));
1121                bean.setThreadLastPostDate(resultSet.getTimestamp("ThreadLastPostDate"));
1122                bean.setThreadType(resultSet.getInt("ThreadType"));
1123                bean.setThreadOption(resultSet.getInt("ThreadOption"));
1124                bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
1125                bean.setThreadHasPoll(resultSet.getInt("ThreadHasPoll"));
1126                bean.setThreadViewCount(resultSet.getInt("ThreadViewCount"));
1127                bean.setThreadReplyCount(resultSet.getInt("ThreadReplyCount"));
1128                bean.setThreadIcon(resultSet.getString("ThreadIcon"));
1129                bean.setThreadDuration(resultSet.getInt("ThreadDuration"));
1130                bean.setThreadAttachCount(resultSet.getInt("ThreadAttachCount"));
1131                retValue.add(bean);
1132            }
1133            return retValue;
1134        } catch(SQLException sqle) {
1135            log.error("Sql Execution Error!", sqle);
1136            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getBeans_inForum_withSortSupport_limit_mysql.");
1137        } finally {
1138            DBUtils.closeResultSet(resultSet);
1139            DBUtils.closeStatement(statement);
1140            DBUtils.closeConnection(connection);
1141        }
1142    }
1143
1144    /*
1145     * Included columns: ThreadID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody,
1146     *                   ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType,
1147     *                   ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount,
1148     *                   ThreadIcon, ThreadDuration
1149     * Excluded columns: ForumID
1150     */
1151    /**
1152     * Note: This is a customized method
1153     */
1154    private Collection getBeans_inForum_withSortSupport_limit_noscroll(int forumID, int offset, int rowsToReturn, String   sort, String   order, boolean enable, boolean onlyNormalThread)
1155        throws IllegalArgumentException  , DatabaseException {
1156
1157        if (offset < 0) throw new IllegalArgumentException  ("The offset < 0 is not allowed.");
1158        if (rowsToReturn <= 0) throw new IllegalArgumentException  ("The rowsToReturn <= 0 is not allowed.");
1159
1160        if ((!sort.equals("ThreadLastPostDate")) &&
1161            (!sort.equals("ThreadCreationDate")) &&
1162            (!sort.equals("MemberName")) &&
1163            (!sort.equals("ThreadReplyCount")) &&
1164            (!sort.equals("ThreadViewCount")) ) {
1165            throw new IllegalArgumentException  ("Cannot sort, reason: dont understand the criteria '" + sort + "'.");
1166        }
1167
1168        if ((!order.equals("ASC")) &&
1169            (!order.equals("DESC")) ) {
1170            throw new IllegalArgumentException  ("Cannot sort, reason: dont understand the order '" + order + "'.");
1171        }
1172
1173        Connection connection = null;
1174        PreparedStatement statement = null;
1175        ResultSet resultSet = null;
1176        Collection retValue = new ArrayList();
1177        StringBuffer   sql = new StringBuffer  (512);
1178        sql.append("SELECT ThreadID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration, ThreadAttachCount");
1179        sql.append(" FROM " + TABLE_NAME);
1180        sql.append(" WHERE ForumID = ? ");
1181        if (onlyNormalThread) {
1182            sql.append(" AND ThreadType = ").append(ThreadBean.THREAD_TYPE_DEFAULT);
1183        }
1184        if (enable) {
1185            sql.append(" AND ThreadStatus <> 1 ");
1186        } else {//disable
1187            sql.append(" AND ThreadStatus = 1 ");
1188        }
1189        sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
1190        try {
1191            connection = DBUtils.getConnection();
1192            statement = connection.prepareStatement(sql.toString());
1193            statement.setMaxRows(offset + rowsToReturn);
1194            statement.setInt(1, forumID);
1195            resultSet = statement.executeQuery();
1196            int rowIndex = -1;
1197            while (resultSet.next()) {
1198                rowIndex++;
1199                if (rowIndex < offset) continue;
1200                ThreadBean bean = new ThreadBean();
1201                bean.setThreadID(resultSet.getInt("ThreadID"));
1202                bean.setForumID(forumID);
1203                bean.setMemberName(resultSet.getString("MemberName"));
1204                bean.setLastPostMemberName(resultSet.getString("LastPostMemberName"));
1205                bean.setThreadTopic(resultSet.getString("ThreadTopic"));
1206                bean.setThreadBody(resultSet.getString("ThreadBody"));
1207                bean.setThreadVoteCount(resultSet.getInt("ThreadVoteCount"));
1208                bean.setThreadVoteTotalStars(resultSet.getInt("ThreadVoteTotalStars"));
1209                bean.setThreadCreationDate(resultSet.getTimestamp("ThreadCreationDate"));
1210                bean.setThreadLastPostDate(resultSet.getTimestamp("ThreadLastPostDate"));
1211                bean.setThreadType(resultSet.getInt("ThreadType"));
1212                bean.setThreadOption(resultSet.getInt("ThreadOption"));
1213                bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
1214                bean.setThreadHasPoll(resultSet.getInt("ThreadHasPoll"));
1215                bean.setThreadViewCount(resultSet.getInt("ThreadViewCount"));
1216                bean.setThreadReplyCount(resultSet.getInt("ThreadReplyCount"));
1217                bean.setThreadIcon(resultSet.getString("ThreadIcon"));
1218                bean.setThreadDuration(resultSet.getInt("ThreadDuration"));
1219                bean.setThreadAttachCount(resultSet.getInt("ThreadAttachCount"));
1220                retValue.add(bean);
1221                if (retValue.size() == rowsToReturn) break;// Fix the Sybase bug
1222            }
1223            return retValue;
1224        } catch(SQLException sqle) {
1225            log.error("Sql Execution Error!", sqle);
1226            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getBeans_inForum_withSortSupport_limit_noscroll.");
1227        } finally {
1228            DBUtils.closeResultSet(resultSet);
1229            DBUtils.resetStatement(statement);
1230            DBUtils.closeStatement(statement);
1231            DBUtils.closeConnection(connection);
1232        }
1233    }
1234
1235    /*
1236     * Included columns: ThreadID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody,
1237     *                   ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType,
1238     *                   ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount,
1239     *                   ThreadIcon, ThreadDuration
1240     * Excluded columns: ForumID
1241     */
1242    /**
1243     * Note: This is a customized method
1244     */
1245    private Collection getBeans_inForum_withSortSupport_limit_general(int forumID, int offset, int rowsToReturn, String   sort, String   order, boolean enable, boolean onlyNormalThread)
1246        throws IllegalArgumentException  , DatabaseException {
1247
1248        if (offset < 0) throw new IllegalArgumentException  ("The offset < 0 is not allowed.");
1249        if (rowsToReturn <= 0) throw new IllegalArgumentException  ("The rowsToReturn <= 0 is not allowed.");
1250
1251        if ((!sort.equals("ThreadLastPostDate")) &&
1252            (!sort.equals("ThreadCreationDate")) &&
1253            (!sort.equals("MemberName")) &&
1254            (!sort.equals("ThreadReplyCount")) &&
1255            (!sort.equals("ThreadViewCount")) ) {
1256            throw new IllegalArgumentException  ("Cannot sort, reason: dont understand the criteria '" + sort + "'.");
1257        }
1258
1259        if ((!order.equals("ASC")) &&
1260            (!order.equals("DESC")) ) {
1261            throw new IllegalArgumentException  ("Cannot sort, reason: dont understand the order '" + order + "'.");
1262        }
1263
1264        Connection connection = null;
1265        PreparedStatement statement = null;
1266        ResultSet resultSet = null;
1267        Collection retValue = new ArrayList();
1268        StringBuffer   sql = new StringBuffer  (512);
1269        sql.append("SELECT ThreadID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration, ThreadAttachCount");
1270        sql.append(" FROM " + TABLE_NAME);
1271        sql.append(" WHERE ForumID = ? ");
1272        if (onlyNormalThread) {
1273            sql.append(" AND ThreadType = ").append(ThreadBean.THREAD_TYPE_DEFAULT);
1274        }
1275        if (enable) {
1276            sql.append(" AND ThreadStatus <> 1 ");
1277        } else {//disable
1278            sql.append(" AND ThreadStatus = 1 ");
1279        }
1280        sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
1281        try {
1282            connection = DBUtils.getConnection();
1283            statement = connection.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
1284            statement.setInt(1, forumID);
1285            statement.setMaxRows(offset + rowsToReturn);
1286            try {
1287                statement.setFetchSize(rowsToReturn);
1288            } catch (SQLException sqle) {
1289                //do nothing, postgreSQL doesnt support this method
1290            }
1291            resultSet = statement.executeQuery();
1292            boolean loop = resultSet.absolute(offset + 1);// the absolute method begin with 1 instead of 0 as in the LIMIT clause
1293            while (loop) {
1294                ThreadBean bean = new ThreadBean();
1295                bean.setThreadID(resultSet.getInt("ThreadID"));
1296                bean.setForumID(forumID);
1297                bean.setMemberName(resultSet.getString("MemberName"));
1298                bean.setLastPostMemberName(resultSet.getString("LastPostMemberName"));
1299                bean.setThreadTopic(resultSet.getString("ThreadTopic"));
1300                bean.setThreadBody(resultSet.getString("ThreadBody"));
1301                bean.setThreadVoteCount(resultSet.getInt("ThreadVoteCount"));
1302                bean.setThreadVoteTotalStars(resultSet.getInt("ThreadVoteTotalStars"));
1303                bean.setThreadCreationDate(resultSet.getTimestamp("ThreadCreationDate"));
1304                bean.setThreadLastPostDate(resultSet.getTimestamp("ThreadLastPostDate"));
1305                bean.setThreadType(resultSet.getInt("ThreadType"));
1306                bean.setThreadOption(resultSet.getInt("ThreadOption"));
1307                bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
1308                bean.setThreadHasPoll(resultSet.getInt("ThreadHasPoll"));
1309                bean.setThreadViewCount(resultSet.getInt("ThreadViewCount"));
1310                bean.setThreadReplyCount(resultSet.getInt("ThreadReplyCount"));
1311                bean.setThreadIcon(resultSet.getString("ThreadIcon"));
1312                bean.setThreadDuration(resultSet.getInt("ThreadDuration"));
1313                bean.setThreadAttachCount(resultSet.getInt("ThreadAttachCount"));
1314                retValue.add(bean);
1315                if (retValue.size() == rowsToReturn) break;// Fix the Sybase bug
1316                loop = resultSet.next();
1317            }//while
1318            return retValue;
1319        } catch(SQLException sqle) {
1320            log.error("Sql Execution Error!", sqle);
1321            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getBeans_inForum_withSortSupport_limit_general.");
1322        } finally {
1323            DBUtils.closeResultSet(resultSet);
1324            DBUtils.resetStatement(statement);
1325            DBUtils.closeStatement(statement);
1326            DBUtils.closeConnection(connection);
1327        }
1328    }
1329
1330    /*
1331     * Included columns: ForumID
1332     */
1333    public void updateForumID(int threadID, // primary key
1334                               int forumID)
1335        throws ObjectNotFoundException, DatabaseException, ForeignKeyNotFoundException {
1336
1337        try {
1338            // @todo: modify the parameter list as needed
1339            // If this method does not change the foreign key columns, you can comment this block of code.
1340            DAOFactory.getForumDAO().findByPrimaryKey(forumID);
1341        } catch(ObjectNotFoundException e) {
1342            throw new ForeignKeyNotFoundException("Foreign key refers to table 'Forum' does not exist. Cannot update table 'Thread'.");
1343        }
1344
1345        Connection connection = null;
1346        PreparedStatement statement = null;
1347        StringBuffer   sql = new StringBuffer  (512);
1348        sql.append("UPDATE " + TABLE_NAME + " SET ForumID = ?");
1349        sql.append(" WHERE ThreadID = ?");
1350        try {
1351            connection = DBUtils.getConnection();
1352            statement = connection.prepareStatement(sql.toString());
1353
1354            // // column(s) to update
1355            statement.setInt(1, forumID);
1356
1357            // primary key column(s)
1358            statement.setInt(2, threadID);
1359
1360            if (statement.executeUpdate() != 1) {
1361                throw new ObjectNotFoundException("Cannot update table Thread where primary key = (" + threadID + ").");
1362            }
1363            m_dirty = true;
1364        } catch(SQLException sqle) {
1365            log.error("Sql Execution Error!", sqle);
1366            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.updateForumID.");
1367        } finally {
1368            DBUtils.closeStatement(statement);
1369            DBUtils.closeConnection(connection);
1370        }
1371    }
1372
1373    /*
1374     * Included columns: ThreadStatus
1375     */
1376    public void updateThreadStatus(int threadID, // primary key
1377                                   int threadStatus)
1378        throws ObjectNotFoundException, DatabaseException {
1379
1380        ThreadBean.validateThreadStatus(threadStatus);
1381
1382        Connection connection = null;
1383        PreparedStatement statement = null;
1384        StringBuffer   sql = new StringBuffer  (512);
1385        sql.append("UPDATE " + TABLE_NAME + " SET ThreadStatus = ?");
1386        sql.append(" WHERE ThreadID = ?");
1387        try {
1388            connection = DBUtils.getConnection();
1389            statement = connection.prepareStatement(sql.toString());
1390
1391            // // column(s) to update
1392            statement.setInt(1, threadStatus);
1393
1394            // primary key column(s)
1395            statement.setInt(2, threadID);
1396
1397            if (statement.executeUpdate() != 1) {
1398                throw new ObjectNotFoundException("Cannot update table Thread (ThreadStatus) where primary key = (" + threadID + ").");
1399            }
1400            m_dirty = true;
1401        } catch(SQLException sqle) {
1402            log.error("Sql Execution Error!", sqle);
1403            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.updateThreadStatus.");
1404        } finally {
1405            DBUtils.closeStatement(statement);
1406            DBUtils.closeConnection(connection);
1407        }
1408    }
1409
1410    /*
1411     * Included columns: ThreadType
1412     */
1413    public void updateThreadType(int threadID, // primary key
1414                                 int threadType)
1415        throws ObjectNotFoundException, DatabaseException {
1416
1417        ThreadBean.validateThreadType(threadType);
1418
1419        Connection connection = null;
1420        PreparedStatement statement = null;
1421        StringBuffer   sql = new StringBuffer  (512);
1422        sql.append("UPDATE " + TABLE_NAME + " SET ThreadType = ?");
1423        sql.append(" WHERE ThreadID = ?");
1424        try {
1425            connection = DBUtils.getConnection();
1426            statement = connection.prepareStatement(sql.toString());
1427
1428            // // column(s) to update
1429            statement.setInt(1, threadType);
1430
1431            // primary key column(s)
1432            statement.setInt(2, threadID);
1433
1434            if (statement.executeUpdate() != 1) {
1435                throw new ObjectNotFoundException("Cannot update table Thread (ThreadType) where primary key = (" + threadID + ").");
1436            }
1437            m_dirty = true;
1438        } catch(SQLException sqle) {
1439            log.error("Sql Execution Error!", sqle);
1440            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.updateThreadType.");
1441        } finally {
1442            DBUtils.closeStatement(statement);
1443            DBUtils.closeConnection(connection);
1444        }
1445    }
1446
1447    /**
1448     * This method should be call only when we can make sure that threadID is in database
1449     */
1450    public void increaseViewCount(int threadID)
1451        throws DatabaseException, ObjectNotFoundException {
1452
1453        Connection connection = null;
1454        PreparedStatement statement = null;
1455        String   sql = "UPDATE " + TABLE_NAME + " SET ThreadViewCount = ThreadViewCount + 1 WHERE ThreadID = ?";
1456        try {
1457            connection = DBUtils.getConnection();
1458            statement = connection.prepareStatement(sql);
1459            statement.setInt(1, threadID);
1460            if (statement.executeUpdate() != 1) {
1461                throw new ObjectNotFoundException("Cannot update the ThreadViewCount in table Thread. Please contact Web site Administrator.");
1462            }
1463            //@todo: coi lai cho nay
1464            // ATTENTION !!!
1465            setDirty(true);
1466        } catch (SQLException sqle) {
1467            log.error("Sql Execution Error!", sqle);
1468            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.increaseViewCount.");
1469        } finally {
1470            DBUtils.closeStatement(statement);
1471            DBUtils.closeConnection(connection);
1472        }
1473    }
1474
1475    public void updateReplyCount(int threadID, // primary key
1476                                 int threadReplyCount)
1477        throws IllegalArgumentException  , DatabaseException, ObjectNotFoundException {
1478
1479        if (threadReplyCount < 0) {
1480            throw new IllegalArgumentException  ("Cannot update a negative reply count.");
1481        }
1482
1483        Connection connection = null;
1484        PreparedStatement statement = null;
1485        StringBuffer   sql = new StringBuffer  (512);
1486        sql.append("UPDATE " + TABLE_NAME + " SET ThreadReplyCount = ?");
1487        sql.append(" WHERE ThreadID = ?");
1488        try {
1489            connection = DBUtils.getConnection();
1490            statement = connection.prepareStatement(sql.toString());
1491
1492            // // column(s) to update
1493            statement.setInt(1, threadReplyCount);
1494
1495            // primary key column(s)
1496            statement.setInt(2, threadID);
1497
1498            if (statement.executeUpdate() != 1) {
1499                throw new ObjectNotFoundException("Cannot update table Thread where primary key = (" + threadID + ").");
1500            }
1501            setDirty(true);
1502        } catch(SQLException sqle) {
1503            log.error("Sql Execution Error!", sqle);
1504            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.updateReplyCount.");
1505        } finally {
1506            DBUtils.closeStatement(statement);
1507            DBUtils.closeConnection(connection);
1508        }
1509    }
1510
1511    public int getPreviousEnableThread(int forumID, int threadID)
1512        throws DatabaseException, AssertionException {
1513
1514        Connection connection = null;
1515        PreparedStatement statement = null;
1516        ResultSet resultSet = null;
1517        String   sql = "SELECT MAX(ThreadID) FROM " + TABLE_NAME + " WHERE ThreadID < ? AND ForumID = ? AND ThreadStatus <> 1 ";
1518        try {
1519            connection = DBUtils.getConnection();
1520            statement = connection.prepareStatement(sql);
1521            statement.setInt(1, threadID);
1522            statement.setInt(2, forumID);
1523            resultSet = statement.executeQuery();
1524            if(!resultSet.next()) {
1525                /** @todo I am sure that this will not happen in MySql, should check other DBMS */
1526                throw new AssertionException("Cannot get the previous thread of the thread you requested: ThreadID = " + threadID);
1527            }
1528            return resultSet.getInt(1);
1529        } catch(SQLException sqle) {
1530            log.error("Sql Execution Error!", sqle);
1531            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getPreviousEnableThread.");
1532        } finally {
1533            DBUtils.closeResultSet(resultSet);
1534            DBUtils.closeStatement(statement);
1535            DBUtils.closeConnection(connection);
1536        }
1537    }
1538
1539    public int getNextEnableThread(int forumID, int threadID)
1540        throws DatabaseException, AssertionException {
1541
1542        Connection connection = null;
1543        PreparedStatement statement = null;
1544        ResultSet resultSet = null;
1545        String   sql = "SELECT MIN(ThreadID) FROM " + TABLE_NAME + " WHERE ThreadID > ? AND ForumID = ? AND ThreadStatus <> 1 ";
1546        try {
1547            connection = DBUtils.getConnection();
1548            statement = connection.prepareStatement(sql);
1549            statement.setInt(1, threadID);
1550            statement.setInt(2, forumID);
1551            resultSet = statement.executeQuery();
1552            if(!resultSet.next()) {
1553                /** @todo I am sure that this will not happen in MySql, should check other DBMS */
1554                throw new AssertionException("Cannot get the next thread of the thread you requested: ThreadID = " + threadID);
1555            }
1556            return resultSet.getInt(1);
1557        } catch(SQLException sqle) {
1558            log.error("Sql Execution Error!", sqle);
1559            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getNextEnableThread.");
1560        } finally {
1561            DBUtils.closeResultSet(resultSet);
1562            DBUtils.closeStatement(statement);
1563            DBUtils.closeConnection(connection);
1564        }
1565    }
1566
1567    /*
1568     * Included columns: ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic,
1569     *                   ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate,
1570     *                   ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount,
1571     *                   ThreadReplyCount, ThreadIcon, ThreadDuration
1572     * Excluded columns:
1573     */
1574    public Collection getEnableThreads_inGlobal(Timestamp sinceDate)
1575        throws DatabaseException {
1576
1577        Connection connection = null;
1578        PreparedStatement statement = null;
1579        ResultSet resultSet = null;
1580        Collection retValue = new ArrayList();
1581        StringBuffer   sql = new StringBuffer  (512);
1582        sql.append("SELECT ThreadID, thread.ForumID, MemberName, thread.LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration");
1583        sql.append(" FROM " + TABLE_NAME + " thread, " + ForumDAO.TABLE_NAME + " forum ");
1584        sql.append(" WHERE thread.ThreadStatus <> 1 AND thread.ForumID = forum.ForumID AND ThreadLastPostDate > ? ");
1585        sql.append(" ORDER BY forum.CategoryID ASC, thread.ForumID ASC ");
1586        try {
1587            connection = DBUtils.getConnection();
1588            statement = connection.prepareStatement(sql.toString());
1589            statement.setTimestamp(1, sinceDate);
1590            resultSet = statement.executeQuery();
1591            while (resultSet.next()) {
1592                ThreadBean bean = new ThreadBean();
1593                bean.setThreadID(resultSet.getInt("ThreadID"));
1594                bean.setForumID(resultSet.getInt("ForumID"));
1595                bean.setMemberName(resultSet.getString("MemberName"));
1596                bean.setLastPostMemberName(resultSet.getString("LastPostMemberName"));
1597                bean.setThreadTopic(resultSet.getString("ThreadTopic"));
1598                bean.setThreadBody(resultSet.getString("ThreadBody"));
1599                bean.setThreadVoteCount(resultSet.getInt("ThreadVoteCount"));
1600                bean.setThreadVoteTotalStars(resultSet.getInt("ThreadVoteTotalStars"));
1601                bean.setThreadCreationDate(resultSet.getTimestamp("ThreadCreationDate"));
1602                bean.setThreadLastPostDate(resultSet.getTimestamp("ThreadLastPostDate"));
1603                bean.setThreadType(resultSet.getInt("ThreadType"));
1604                bean.setThreadOption(resultSet.getInt("ThreadOption"));
1605                bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
1606                bean.setThreadHasPoll(resultSet.getInt("ThreadHasPoll"));
1607                bean.setThreadViewCount(resultSet.getInt("ThreadViewCount"));
1608                bean.setThreadReplyCount(resultSet.getInt("ThreadReplyCount"));
1609                bean.setThreadIcon(resultSet.getString("ThreadIcon"));
1610                bean.setThreadDuration(resultSet.getInt("ThreadDuration"));
1611                retValue.add(bean);
1612            }
1613            return retValue;
1614        } catch(SQLException sqle) {
1615            log.error("Sql Execution Error!", sqle);
1616            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getEnableThreads_inGlobal.");
1617        } finally {
1618            DBUtils.closeResultSet(resultSet);
1619            DBUtils.closeStatement(statement);
1620            DBUtils.closeConnection(connection);
1621        }
1622    }
1623
1624    /*
1625     * Included columns: ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic,
1626     *                   ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate,
1627     *                   ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount,
1628     *                   ThreadReplyCount, ThreadIcon, ThreadDuration
1629     * Excluded columns:
1630     */
1631    public Collection getEnableThreads_inCategory(int categoryID, Timestamp sinceDate)
1632        throws DatabaseException {
1633
1634        Connection connection = null;
1635        PreparedStatement statement = null;
1636        ResultSet resultSet = null;
1637        Collection retValue = new ArrayList();
1638        StringBuffer   sql = new StringBuffer  (512);
1639        sql.append("SELECT ThreadID, thread.ForumID, MemberName, thread.LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration");
1640        sql.append(" FROM " + TABLE_NAME + " thread, " + ForumDAO.TABLE_NAME + " forum ");
1641        sql.append(" WHERE thread.ThreadStatus <> 1 AND thread.ForumID = forum.ForumID AND forum.CategoryID = ? AND ThreadLastPostDate > ? ");
1642        sql.append(" ORDER BY thread.ForumID ASC ");
1643        try {
1644            connection = DBUtils.getConnection();
1645            statement = connection.prepareStatement(sql.toString());
1646            statement.setInt(1, categoryID);
1647            statement.setTimestamp(2, sinceDate);
1648            resultSet = statement.executeQuery();
1649            while (resultSet.next()) {
1650                ThreadBean bean = new ThreadBean();
1651                bean.setThreadID(resultSet.getInt("ThreadID"));
1652                bean.setForumID(resultSet.getInt("ForumID"));
1653                bean.setMemberName(resultSet.getString("MemberName"));
1654                bean.setLastPostMemberName(resultSet.getString("LastPostMemberName"));
1655                bean.setThreadTopic(resultSet.getString("ThreadTopic"));
1656                bean.setThreadBody(resultSet.getString("ThreadBody"));
1657                bean.setThreadVoteCount(resultSet.getInt("ThreadVoteCount"));
1658                bean.setThreadVoteTotalStars(resultSet.getInt("ThreadVoteTotalStars"));
1659                bean.setThreadCreationDate(resultSet.getTimestamp("ThreadCreationDate"));
1660                bean.setThreadLastPostDate(resultSet.getTimestamp("ThreadLastPostDate"));
1661                bean.setThreadType(resultSet.getInt("ThreadType"));
1662                bean.setThreadOption(resultSet.getInt("ThreadOption"));
1663                bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
1664                bean.setThreadHasPoll(resultSet.getInt("ThreadHasPoll"));
1665                bean.setThreadViewCount(resultSet.getInt("ThreadViewCount"));
1666                bean.setThreadReplyCount(resultSet.getInt("ThreadReplyCount"));
1667                bean.setThreadIcon(resultSet.getString("ThreadIcon"));
1668                bean.setThreadDuration(resultSet.getInt("ThreadDuration"));
1669                retValue.add(bean);
1670            }
1671            return retValue;
1672        } catch(SQLException sqle) {
1673            log.error("Sql Execution Error!", sqle);
1674            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getEnableThreads_inCategory.");
1675        } finally {
1676            DBUtils.closeResultSet(resultSet);
1677            DBUtils.closeStatement(statement);
1678            DBUtils.closeConnection(connection);
1679        }
1680    }
1681
1682    /*
1683     * Included columns: ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic,
1684     *                   ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate,
1685     *                   ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount,
1686     *                   ThreadReplyCount, ThreadIcon, ThreadDuration
1687     * Excluded columns:
1688     */
1689    public Collection getEnableThreads_inForum(int forumID, Timestamp sinceDate)
1690        throws DatabaseException {
1691
1692        Connection connection = null;
1693        PreparedStatement statement = null;
1694        ResultSet resultSet = null;
1695        Collection retValue = new ArrayList();
1696        StringBuffer   sql = new StringBuffer  (512);
1697        sql.append("SELECT ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration");
1698        sql.append(" FROM " + TABLE_NAME);
1699        sql.append(" WHERE ThreadStatus <> 1 AND ForumID = ? AND ThreadLastPostDate > ? ");
1700        //sql.append(" ORDER BY ColumnName ASC|DESC "); // @todo: uncomment as needed
1701        try {
1702            connection = DBUtils.getConnection();
1703            statement = connection.prepareStatement(sql.toString());
1704            statement.setInt(1, forumID);
1705            statement.setTimestamp(2, sinceDate);
1706            resultSet = statement.executeQuery();
1707            while (resultSet.next()) {
1708                ThreadBean bean = new ThreadBean();
1709                bean.setThreadID(resultSet.getInt("ThreadID"));
1710                bean.setForumID(resultSet.getInt("ForumID"));
1711                bean.setMemberName(resultSet.getString("MemberName"));
1712                bean.setLastPostMemberName(resultSet.getString("LastPostMemberName"));
1713                bean.setThreadTopic(resultSet.getString("ThreadTopic"));
1714                bean.setThreadBody(resultSet.getString("ThreadBody"));
1715                bean.setThreadVoteCount(resultSet.getInt("ThreadVoteCount"));
1716                bean.setThreadVoteTotalStars(resultSet.getInt("ThreadVoteTotalStars"));
1717                bean.setThreadCreationDate(resultSet.getTimestamp("ThreadCreationDate"));
1718                bean.setThreadLastPostDate(resultSet.getTimestamp("ThreadLastPostDate"));
1719                bean.setThreadType(resultSet.getInt("ThreadType"));
1720                bean.setThreadOption(resultSet.getInt("ThreadOption"));
1721                bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
1722                bean.setThreadHasPoll(resultSet.getInt("ThreadHasPoll"));
1723                bean.setThreadViewCount(resultSet.getInt("ThreadViewCount"));
1724                bean.setThreadReplyCount(resultSet.getInt("ThreadReplyCount"));
1725                bean.setThreadIcon(resultSet.getString("ThreadIcon"));
1726                bean.setThreadDuration(resultSet.getInt("ThreadDuration"));
1727                retValue.add(bean);
1728            }
1729            return retValue;
1730        } catch(SQLException sqle) {
1731            log.error("Sql Execution Error!", sqle);
1732            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getEnableThreads_inForum.");
1733        } finally {
1734            DBUtils.closeResultSet(resultSet);
1735            DBUtils.closeStatement(statement);
1736            DBUtils.closeConnection(connection);
1737        }
1738    }
1739
1740    /*
1741     * Included columns: ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic,
1742     *                   ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate,
1743     *                   ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount,
1744     *                   ThreadReplyCount, ThreadIcon, ThreadDuration
1745     * Excluded columns:
1746     */
1747    public Collection getEnableThreads_inThread(int threadID, Timestamp sinceDate)
1748        throws DatabaseException {
1749
1750        Connection connection = null;
1751        PreparedStatement statement = null;
1752        ResultSet resultSet = null;
1753        Collection retValue = new ArrayList();
1754        StringBuffer   sql = new StringBuffer  (512);
1755        sql.append("SELECT ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration");
1756        sql.append(" FROM " + TABLE_NAME);
1757        sql.append(" WHERE ThreadStatus <> 1 AND ThreadID = ? AND ThreadLastPostDate > ? ");
1758        //sql.append(" ORDER BY ColumnName ASC|DESC "); // @todo: uncomment as needed
1759        try {
1760            connection = DBUtils.getConnection();
1761            statement = connection.prepareStatement(sql.toString());
1762            statement.setInt(1, threadID);
1763            statement.setTimestamp(2, sinceDate);
1764            resultSet = statement.executeQuery();
1765            while (resultSet.next()) {
1766                ThreadBean bean = new ThreadBean();
1767                bean.setThreadID(resultSet.getInt("ThreadID"));
1768                bean.setForumID(resultSet.getInt("ForumID"));
1769                bean.setMemberName(resultSet.getString("MemberName"));
1770                bean.setLastPostMemberName(resultSet.getString("LastPostMemberName"));
1771                bean.setThreadTopic(resultSet.getString("ThreadTopic"));
1772                bean.setThreadBody(resultSet.getString("ThreadBody"));
1773                bean.setThreadVoteCount(resultSet.getInt("ThreadVoteCount"));
1774                bean.setThreadVoteTotalStars(resultSet.getInt("ThreadVoteTotalStars"));
1775                bean.setThreadCreationDate(resultSet.getTimestamp("ThreadCreationDate"));
1776                bean.setThreadLastPostDate(resultSet.getTimestamp("ThreadLastPostDate"));
1777                bean.setThreadType(resultSet.getInt("ThreadType"));
1778                bean.setThreadOption(resultSet.getInt("ThreadOption"));
1779                bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
1780                bean.setThreadHasPoll(resultSet.getInt("ThreadHasPoll"));
1781                bean.setThreadViewCount(resultSet.getInt("ThreadViewCount"));
1782                bean.setThreadReplyCount(resultSet.getInt("ThreadReplyCount"));
1783                bean.setThreadIcon(resultSet.getString("ThreadIcon"));
1784                bean.setThreadDuration(resultSet.getInt("ThreadDuration"));
1785                retValue.add(bean);
1786            }
1787            return retValue;
1788        } catch(SQLException sqle) {
1789            log.error("Sql Execution Error!", sqle);
1790            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getEnableThreads_inThread.");
1791        } finally {
1792            DBUtils.closeResultSet(resultSet);
1793            DBUtils.closeStatement(statement);
1794            DBUtils.closeConnection(connection);
1795        }
1796    }
1797
1798    public int getNumberOfEnableThreadsWithPendingPosts()
1799        throws AssertionException, DatabaseException {
1800
1801        Connection connection = null;
1802        PreparedStatement statement = null;
1803        ResultSet resultSet = null;
1804        StringBuffer   sql = new StringBuffer  (512);
1805        sql.append("SELECT Count(DISTINCT thread.ThreadID)");
1806        sql.append(" FROM " + TABLE_NAME + " thread, " + PostDAO.TABLE_NAME + " post ");
1807        sql.append(" WHERE post.ThreadID = thread.ThreadID AND PostStatus = 1 AND ThreadStatus <> 1");
1808        try {
1809            connection = DBUtils.getConnection();
1810            statement = connection.prepareStatement(sql.toString());
1811            resultSet = statement.executeQuery();
1812            if (!resultSet.next()) {
1813                throw new AssertionException("Assertion in ThreadDAOImplJDBC.getNumberOfEnableThreadsWithPendingPosts.");
1814            }
1815            return resultSet.getInt(1);
1816        } catch(SQLException sqle) {
1817            log.error("Sql Execution Error!", sqle);
1818            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getNumberOfEnableThreadsWithPendingPosts.");
1819        } finally {
1820            DBUtils.closeResultSet(resultSet);
1821            DBUtils.closeStatement(statement);
1822            DBUtils.closeConnection(connection);
1823        }
1824    }
1825
1826    public int getNumberOfEnableThreadsWithPendingPosts_inForum(int forumID)
1827        throws AssertionException, DatabaseException {
1828
1829        Connection connection = null;
1830        PreparedStatement statement = null;
1831        ResultSet resultSet = null;
1832        StringBuffer   sql = new StringBuffer  (512);
1833        sql.append("SELECT Count(DISTINCT thread.ThreadID)");
1834        sql.append(" FROM " + TABLE_NAME + " thread, " + PostDAO.TABLE_NAME + " post ");
1835        sql.append(" WHERE post.ThreadID = thread.ThreadID AND PostStatus = 1 AND ThreadStatus <> 1 AND thread.ForumID = ?");
1836        try {
1837            connection = DBUtils.getConnection();
1838            statement = connection.prepareStatement(sql.toString());
1839            statement.setInt(1, forumID);
1840            resultSet = statement.executeQuery();
1841            if (!resultSet.next()) {
1842                throw new AssertionException("Assertion in ThreadDAOImplJDBC.getNumberOfEnableThreadsWithPendingPosts_inForum.");
1843            }
1844            return resultSet.getInt(1);
1845        } catch(SQLException sqle) {
1846            log.error("Sql Execution Error!", sqle);
1847            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getNumberOfEnableThreadsWithPendingPosts_inForum.");
1848        } finally {
1849            DBUtils.closeResultSet(resultSet);
1850            DBUtils.closeStatement(statement);
1851            DBUtils.closeConnection(connection);
1852        }
1853    }
1854
1855    /**
1856     * This method is used to get enable threads that having at least one pending post (for moderation)
1857     *
1858     * Note: current implementation use the NOSCROLL method which is quite slow
1859     *
1860     * @param offset
1861     * @param rowsToReturn
1862     * @param sort
1863     * @param order
1864     * @return
1865     * @throws java.lang.IllegalArgumentException if the arguments are not valid
1866     * @throws DatabaseException
1867     */
1868    public Collection getEnableThreadsWithPendingPosts_withSortSupport_limit(int offset, int rowsToReturn, String   sort, String   order)
1869        throws IllegalArgumentException  , DatabaseException, ObjectNotFoundException {
1870
1871        return getEnableThreadsWithPendingPosts_withSortSupport_limit_noscroll(-1/*mean all forums*/, offset, rowsToReturn, sort, order);
1872    }
1873
1874    /**
1875     * This method is used to get enable threads that having at least one pending post (for moderation)
1876     *
1877     * Note: current implementation use the NOSCROLL method which is quite slow
1878     * NOTE: This method is a hack for Oracle because of the error "Not a Group By expression"
1879     *       and when add to the group by clause, then it cannot group by a LONG column
1880     *
1881     * @param offset
1882     * @param rowsToReturn
1883     * @param sort
1884     * @param order
1885     * @return
1886     * @throws java.lang.IllegalArgumentException if the arguments are not valid
1887     * @throws DatabaseException
1888     */
1889    public Collection getEnableThreadsWithPendingPosts_inForum_withSortSupport_limit(int forumID, int offset, int rowsToReturn, String   sort, String   order)
1890        throws IllegalArgumentException  , DatabaseException, ObjectNotFoundException {
1891
1892        return getEnableThreadsWithPendingPosts_withSortSupport_limit_noscroll(forumID, offset, rowsToReturn, sort, order);
1893    }
1894
1895    /**
1896     *
1897     * @param forumID the forumID to get threads, or -1 mean get threads in all forums
1898     */
1899    private Collection getEnableThreadsWithPendingPosts_withSortSupport_limit_noscroll(int forumID, int offset, int rowsToReturn, String   sort, String   order)
1900        throws IllegalArgumentException  , DatabaseException, ObjectNotFoundException {
1901
1902        if (offset < 0) throw new IllegalArgumentException  ("The offset < 0 is not allowed.");
1903        if (rowsToReturn <= 0) throw new IllegalArgumentException  ("The rowsToReturn <= 0 is not allowed.");
1904
1905        if ((!sort.equals("ThreadLastPostDate")) &&
1906            (!sort.equals("ThreadCreationDate")) &&
1907            (!sort.equals("MemberName")) &&
1908            (!sort.equals("ThreadReplyCount")) &&
1909            (!sort.equals("ThreadViewCount")) &&
1910            (!sort.equals("ForumID")) &&
1911            (!sort.equals("ThreadPendingPostCount")) ) {//ThreadPendingPostCount get from GROUP BY clause
1912            throw new IllegalArgumentException  ("Cannot sort, reason: dont understand the criteria '" + sort + "'.");
1913        }
1914
1915        if ((!order.equals("ASC")) &&
1916            (!order.equals("DESC")) ) {
1917            throw new IllegalArgumentException  ("Cannot sort, reason: dont understand the order '" + order + "'.");
1918        }
1919
1920        Connection connection = null;
1921        PreparedStatement statement = null;
1922        ResultSet resultSet = null;
1923        Collection retValue = new ArrayList();
1924        StringBuffer   sql = new StringBuffer  (512);
1925        sql.append("SELECT thread.ThreadID, thread.ForumID, thread.MemberName, ThreadCreationDate, ThreadLastPostDate, ThreadViewCount, ThreadReplyCount, COUNT(thread.ThreadID) AS ThreadPendingPostCount");
1926        sql.append(" FROM " + TABLE_NAME + " thread, " + PostDAO.TABLE_NAME + " post ");
1927        // PostStatus = 1 means pending(disabled) posts
1928        // ThreadStatus <> 1 means enable threads
1929        sql.append(" WHERE post.ThreadID = thread.ThreadID AND PostStatus = 1 AND ThreadStatus <> 1");
1930        if (forumID != -1) {
1931            sql.append(" AND thread.ForumID = ?");
1932        }
1933        sql.append(" GROUP BY thread.ThreadID, thread.ForumID, thread.MemberName, ThreadCreationDate, ThreadLastPostDate, ThreadViewCount, ThreadReplyCount");
1934        if (sort.equals("ThreadPendingPostCount")) {
1935            sql.append(" ORDER BY ThreadPendingPostCount " + order);// ColumnName, ASC|DESC
1936        } else {
1937            sql.append(" ORDER BY thread." + sort + " " + order);// ColumnName, ASC|DESC
1938        }
1939        try {
1940            connection = DBUtils.getConnection();
1941            statement = connection.prepareStatement(sql.toString());
1942            if (forumID != -1) {
1943                statement.setInt(1, forumID);
1944            }
1945            statement.setMaxRows(offset + rowsToReturn);
1946            resultSet = statement.executeQuery();
1947            int rowIndex = -1;
1948            while (resultSet.next()) {
1949                rowIndex++;
1950                if (rowIndex < offset) continue;
1951                ThreadBean bean = new ThreadBean();
1952                bean.setThreadID(resultSet.getInt("ThreadID"));
1953                bean.setForumID(resultSet.getInt("ForumID"));
1954                bean.setMemberName(resultSet.getString("MemberName"));
1955                bean.setThreadCreationDate(resultSet.getTimestamp("ThreadCreationDate"));
1956                bean.setThreadLastPostDate(resultSet.getTimestamp("ThreadLastPostDate"));
1957                bean.setThreadViewCount(resultSet.getInt("ThreadViewCount"));
1958                bean.setThreadReplyCount(resultSet.getInt("ThreadReplyCount"));
1959                bean.setThreadPendingPostCount(resultSet.getInt("ThreadPendingPostCount"));
1960                retValue.add(bean);
1961                if (retValue.size() == rowsToReturn) break;// Fix the Sybase bug
1962            }
1963        } catch(SQLException sqle) {
1964            log.error("Sql Execution Error!", sqle);
1965            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getEnableThreadsHavingPendingPosts_withSortSupport_limit_noscroll.");
1966        } finally {
1967            DBUtils.closeResultSet(resultSet);
1968            DBUtils.resetStatement(statement);
1969            DBUtils.closeStatement(statement);
1970            DBUtils.closeConnection(connection);
1971        }
1972
1973        ArrayList newThreadBeans = new ArrayList();
1974        for (Iterator iter = retValue.iterator(); iter.hasNext(); ) {
1975            ThreadBean threadBean = (ThreadBean)iter.next();
1976            ThreadBean fullThreadBean = getThread(threadBean.getThreadID());
1977            fullThreadBean.setThreadPendingPostCount(threadBean.getThreadPendingPostCount());
1978            newThreadBeans.add(fullThreadBean);
1979        }
1980        return newThreadBeans;
1981    }
1982
1983    private Collection getEnableThreads_inType_inForum(int forumID, int threadType)
1984        throws DatabaseException {
1985
1986        Connection connection = null;
1987        PreparedStatement statement = null;
1988        ResultSet resultSet = null;
1989        Collection retValue = new ArrayList();
1990        StringBuffer   sql = new StringBuffer  (512);
1991        sql.append("SELECT ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration, ThreadAttachCount");
1992        sql.append(" FROM ").append(TABLE_NAME);
1993        final int NO_FORUM = -1;
1994        boolean isGlobalAnnoucement = (forumID == NO_FORUM && threadType == ThreadBean.THREAD_TYPE_GLOBAL_ANNOUNCEMENT);
1995        if (isGlobalAnnoucement) {
1996            sql.append(" WHERE ThreadType = ? ");
1997        } else {
1998            sql.append(" WHERE ForumID = ? AND ThreadType = ?");
1999        }
2000        sql.append(" AND ThreadStatus <> ").append(ThreadBean.THREAD_STATUS_DISABLED);
2001        sql.append(" ORDER BY ThreadLastPostDate DESC");
2002        // log.debug("SQL:: " + sql);
2003        try {
2004            connection = DBUtils.getConnection();
2005            statement = connection.prepareStatement(sql.toString());
2006            if (isGlobalAnnoucement) {
2007                statement.setInt(1, threadType);
2008            } else {
2009                statement.setInt(1, forumID);
2010                statement.setInt(2, threadType);
2011            }
2012
2013            resultSet = statement.executeQuery();
2014            while (resultSet.next()) {
2015                ThreadBean bean = new ThreadBean();
2016                bean.setThreadID(resultSet.getInt("ThreadID"));
2017                bean.setForumID(resultSet.getInt("ForumID"));
2018                bean.setMemberName(resultSet.getString("MemberName"));
2019                bean.setLastPostMemberName(resultSet.getString("LastPostMemberName"));
2020                bean.setThreadTopic(resultSet.getString("ThreadTopic"));
2021                bean.setThreadBody(resultSet.getString("ThreadBody"));
2022                bean.setThreadVoteCount(resultSet.getInt("ThreadVoteCount"));
2023                bean.setThreadVoteTotalStars(resultSet.getInt("ThreadVoteTotalStars"));
2024                bean.setThreadCreationDate(resultSet.getTimestamp("ThreadCreationDate"));
2025                bean.setThreadLastPostDate(resultSet.getTimestamp("ThreadLastPostDate"));
2026                bean.setThreadType(resultSet.getInt("ThreadType"));
2027                bean.setThreadOption(resultSet.getInt("ThreadOption"));
2028                bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
2029                bean.setThreadHasPoll(resultSet.getInt("ThreadHasPoll"));
2030                bean.setThreadViewCount(resultSet.getInt("ThreadViewCount"));
2031                bean.setThreadReplyCount(resultSet.getInt("ThreadReplyCount"));
2032                bean.setThreadIcon(resultSet.getString("ThreadIcon"));
2033                bean.setThreadDuration(resultSet.getInt("ThreadDuration"));
2034                bean.setThreadAttachCount(resultSet.getInt("ThreadAttachCount"));
2035                retValue.add(bean);
2036            }
2037        } catch(SQLException sqle) {
2038            log.error("Sql Execution Error!", sqle);
2039            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getEnableThreads_inType_inForum.");
2040        } finally {
2041            DBUtils.closeResultSet(resultSet);
2042            DBUtils.resetStatement(statement);
2043            DBUtils.closeStatement(statement);
2044            DBUtils.closeConnection(connection);
2045        }
2046        return retValue;
2047    }
2048
2049    public Collection getEnableStickies_inForum(int forumID) throws DatabaseException {
2050        return this.getEnableThreads_inType_inForum(forumID, ThreadBean.THREAD_TYPE_STICKY);
2051    }
2052
2053    public Collection getEnableForumAnnouncements_inForum(int forumID) throws DatabaseException {
2054        return this.getEnableThreads_inType_inForum(forumID, ThreadBean.THREAD_TYPE_FORUM_ANNOUNCEMENT);
2055    }
2056
2057    public Collection getEnableGlobalAnnouncements() throws DatabaseException {
2058        return this.getEnableThreads_inType_inForum(-1 /* not belongs to any forum */, ThreadBean.THREAD_TYPE_GLOBAL_ANNOUNCEMENT);
2059    }
2060}// end of class ThreadDAOImplJDBC
2061
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags