|
1
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.common.ActiveMember;
48 import com.mvnforum.common.ActiveThread;
49 import com.mvnforum.db.*;
50 import net.myvietnam.mvncore.db.DBUtils;
51 import net.myvietnam.mvncore.exception.*;
52 import org.apache.commons.logging.Log;
53 import org.apache.commons.logging.LogFactory;
54
55 public class PostDAOImplJDBC implements PostDAO {
56
57 private static Log log = LogFactory.getLog(PostDAOImplJDBC.class);
58
59 private static boolean m_dirty = true;
61
62 public PostDAOImplJDBC() {
63 }
64
65 protected static boolean isDirty() {
66 return m_dirty;
67 }
68
69 protected static void setDirty(boolean dirty) {
70 m_dirty = dirty;
71 }
72
73 public void findByPrimaryKey(int postID)
74 throws ObjectNotFoundException, DatabaseException {
75
76 Connection connection = null;
77 PreparedStatement statement = null;
78 ResultSet resultSet = null;
79 StringBuffer sql = new StringBuffer (512);
80 sql.append("SELECT PostID");
81 sql.append(" FROM " + TABLE_NAME);
82 sql.append(" WHERE PostID = ?");
83 try {
84 connection = DBUtils.getConnection();
85 statement = connection.prepareStatement(sql.toString());
86 statement.setInt(1, postID);
87 resultSet = statement.executeQuery();
88 if (!resultSet.next()) {
89 throw new ObjectNotFoundException("Cannot find the primary key (" + postID + ") in table 'Post'.");
90 }
91 } catch(SQLException sqle) {
92 log.error("Sql Execution Error!", sqle);
93 throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.findByPrimaryKey.");
94 } finally {
95 DBUtils.closeResultSet(resultSet);
96 DBUtils.closeStatement(statement);
97 DBUtils.closeConnection(connection);
98 }
99 }
100
101
108 public void create(int parentPostID, int forumID, int threadID,
109 int memberID, String memberName, String lastEditMemberName,
110 String postTopic, String postBody, Timestamp postCreationDate,
111 Timestamp postLastEditDate, String postCreationIP, String postLastEditIP,
112 int postEditCount, int postFormatOption, int postOption,
113 int postStatus, String postIcon, int postAttachCount)
114 throws CreateException, DatabaseException, ForeignKeyNotFoundException {
115
116 try {
117 DAOFactory.getForumDAO().findByPrimaryKey(forumID);
120 } catch(ObjectNotFoundException e) {
121 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Forum' does not exist. Cannot create new Post.");
122 }
123
124 if (memberID != 0) {
126 try {
127 DAOFactory.getMemberDAO().findByPrimaryKey2(memberID, memberName);
130 } catch(ObjectNotFoundException e) {
131 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Member' does not exist. Cannot create new Post.");
132 }
133 }
134
135 try {
136 DAOFactory.getThreadDAO().findByPrimaryKey(threadID);
139 } catch(ObjectNotFoundException e) {
140 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Thread' does not exist. Cannot create new Post.");
141 }
142
143 if ((memberName!=null) && (memberName.length()>0)) {
145 try {
146 DAOFactory.getMemberDAO().findByAlternateKey_MemberName(memberName);
149 } catch(ObjectNotFoundException e) {
150 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Member' does not exist. Cannot create new Post.");
151 }
152 } else {
153 memberName = "";
155 }
156
157 if ((lastEditMemberName!=null) && (lastEditMemberName.length()>0)) {
158 try {
159 DAOFactory.getMemberDAO().findByAlternateKey_MemberName(lastEditMemberName);
160 } catch(ObjectNotFoundException e) {
161 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Member' does not exist. Cannot create table 'Post'.");
162 }
163 } else {
164 lastEditMemberName=""; }
166
167 try {
168 if (parentPostID != 0) {
171 findByPrimaryKey(parentPostID);
172 }
173 } catch(ObjectNotFoundException e) {
174 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Post' does not exist. Cannot create new Post.");
175 }
176
177 Connection connection = null;
178 PreparedStatement statement = null;
179 StringBuffer sql = new StringBuffer (512);
180 sql.append("INSERT INTO " + TABLE_NAME + " (ParentPostID, ForumID, ThreadID, MemberID, MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate, PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption, PostOption, PostStatus, PostIcon, PostAttachCount)");
181 sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
182 try {
183 connection = DBUtils.getConnection();
184 statement = connection.prepareStatement(sql.toString());
185
186 statement.setInt(1, parentPostID);
187 statement.setInt(2, forumID);
188 statement.setInt(3, threadID);
189 statement.setInt(4, memberID);
190 statement.setString(5, memberName);
191 statement.setString(6, lastEditMemberName);
192 statement.setString(7, postTopic);
193 if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
194 statement.setCharacterStream(8, new StringReader (postBody), postBody.length());
195 } else {
196 statement.setString(8, postBody);
197 }
198 statement.setTimestamp(9, postCreationDate);
199 statement.setTimestamp(10, postLastEditDate);
200 statement.setString(11, postCreationIP);
201 statement.setString(12, postLastEditIP);
202 statement.setInt(13, postEditCount);
203 statement.setInt(14, postFormatOption);
204 statement.setInt(15, postOption);
205 statement.setInt(16, postStatus);
206 statement.setString(17, postIcon);
207 statement.setInt(18, postAttachCount);
208
209 if (statement.executeUpdate() != 1) {
210 throw new CreateException("Error adding a row into table 'Post'.");
211 }
212 m_dirty = true;
213 } catch(SQLException sqle) {
214 log.error("Sql Execution Error!", sqle);
215 throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.create.");
216 } finally {
217 DBUtils.closeStatement(statement);
218 DBUtils.closeConnection(connection);
219 }
220 }
221
222 public int createPost(int parentPostID, int forumID, int threadID,
223 int memberID, String memberName, String lastEditMemberName,
224 String postTopic, String postBody, Timestamp postCreationDate,
225 Timestamp postLastEditDate, String postCreationIP, String postLastEditIP,
226 int postEditCount, int postFormatOption, int postOption,
227 int postStatus, String postIcon, int postAttachCount)
228 throws CreateException, DatabaseException, ForeignKeyNotFoundException {
229
230 create(
231 parentPostID, forumID, threadID, memberID, memberName, lastEditMemberName,
232 postTopic, postBody, postCreationDate, postLastEditDate,
233 postCreationIP, postLastEditIP, postEditCount, postFormatOption,
234 postOption, postStatus, postIcon, postAttachCount);
235
236 int postID = 0;
237 try {
238 postID = findPostID(forumID, memberName, postCreationDate);
239 } catch (ObjectNotFoundException ex) {
240 Timestamp roundTimestamp = new Timestamp((postCreationDate.getTime()/1000)*1000);
242 try {
243 postID = findPostID(forumID, memberName, roundTimestamp);
244 } catch (ObjectNotFoundException e) {
245 throw new CreateException("Cannot find the PostID in table Post.");
246 }
247 }
248 return postID;
249 }
250
251 public void delete(int postID)
252 throws DatabaseException, ObjectNotFoundException {
253
254 Connection connection = null;
255 PreparedStatement statement = null;
256 StringBuffer sql = new StringBuffer (512);
257 sql.append("DELETE FROM " + TABLE_NAME);
258 sql.append(" WHERE PostID = ?");
259
260 try {
261 connection = DBUtils.getConnection();
262 statement = connection.prepareStatement(sql.toString());
263 statement.setInt(1, postID);
264 if (statement.executeUpdate() != 1) {
265 throw new ObjectNotFoundException("Cannot delete a row in table Post where PostID = (" + postID + ").");
266 }
267 m_dirty = true;
268 } catch(SQLException sqle) {
269 log.error("Sql Execution Error!", sqle);
270 throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.delete.");
271 } finally {
272 DBUtils.closeStatement(statement);
273 DBUtils.closeConnection(connection);
274 }
275 }
276
277 public void delete_inThread(int threadID)
278 throws DatabaseException {
279
280 Connection connection = null;
281 PreparedStatement statement = null;
282 StringBuffer sql = new StringBuffer (512);
283 sql.append("DELETE FROM " + TABLE_NAME);
284 sql.append(" WHERE ThreadID = ?");
285
286 try {
287 connection = DBUtils.getConnection();
288 statement = connection.prepareStatement(sql.toString());
289 statement.setInt(1, threadID);
290
291 statement.executeUpdate();
292
293 m_dirty = true;
294 } catch(SQLException sqle) {
295 log.error("Sql Execution Error!", sqle);
296 throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.delete_inThread.");
297 } finally {
298 DBUtils.closeStatement(statement);
299 DBUtils.closeConnection(connection);
300 }
301 }
302
303 public void delete_inForum(int forumID)
304 throws DatabaseException {
305
306 Connection connection = null;
307 PreparedStatement statement = null;
308 StringBuffer sql = new StringBuffer (512);
309 sql.append("DELETE FROM " + TABLE_NAME);
310 sql.append(" WHERE ForumID = ?");
311
312 try {
313 connection = DBUtils.getConnection();
314 statement = connection.prepareStatement(sql.toString());
315 statement.setInt(1, forumID);
316 statement.executeUpdate();
317 m_dirty = true;
318 } catch(SQLException sqle) {
319 log.error("Sql Execution Error!", sqle);
320 throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.delete_inForum.");
321 } finally {
322 DBUtils.closeStatement(statement);
323 DBUtils.closeConnection(connection);
324 }
325 }
326
327
333 public void update(int postID, String lastEditMemberName, String postTopic, String postBody,
335 Timestamp postLastEditDate, String postLastEditIP, int postFormatOption,
336 int postOption, int postStatus, String postIcon)
337 throws ObjectNotFoundException, DatabaseException, ForeignKeyNotFoundException {
338
339 if ((lastEditMemberName!=null) && (lastEditMemberName.length()>0)) {
341 try {
342 DAOFactory.getMemberDAO().findByAlternateKey_MemberName(lastEditMemberName);
345 } catch(ObjectNotFoundException e) {
346 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Member' does not exist. Cannot update table 'Post'.");
347 }
348 } else {
349 lastEditMemberName=""; }
351
352 Connection connection = null;
353 PreparedStatement statement = null;
354 StringBuffer sql = new StringBuffer (512);
355 sql.append("UPDATE " + TABLE_NAME + " SET LastEditMemberName = ?, PostTopic = ?, PostBody = ?, PostLastEditDate = ?, PostLastEditIP = ?, PostFormatOption = ?, PostOption = ?, PostStatus = ?, PostIcon = ?");
356 sql.append(" WHERE PostID = ?");
357 try {
358 connection = DBUtils.getConnection();
359 statement = connection.prepareStatement(sql.toString());
360
361 statement.setString(1, lastEditMemberName);
363 statement.setString(2, postTopic);
364 if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
365 statement.setCharacterStream(3, new StringReader (postBody), postBody.length());
366 } else {
367 statement.setString(3, postBody);
368 }
369 statement.setTimestamp(4, postLastEditDate);
370 statement.setString(5, postLastEditIP);
371 statement.setInt(6, postFormatOption);
372 statement.setInt(7, postOption);
373 statement.setInt(8, postStatus);
374 statement.setString(9, postIcon);
375
376 statement.setInt(10, postID);
378
379 if (statement.executeUpdate() != 1) {
380 throw new ObjectNotFoundException("Cannot update table Post where primary key = (" + postID + ").");
381 }
382 m_dirty = true;
383 } catch(SQLException sqle) {
384 log.error("Sql Execution Error!", sqle);
385 throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.update.");
386 } finally {
387 DBUtils.closeStatement(statement);
388 DBUtils.closeConnection(connection);
389 }
390 }
391
392
399 public void updateAttachCount(int postID, int postAttachCount)
401 throws ObjectNotFoundException, DatabaseException {
402
403 Connection connection = null;
404 PreparedStatement statement = null;
405 StringBuffer sql = new StringBuffer (512);
406 sql.append("UPDATE " + TABLE_NAME + " SET PostAttachCount = ?");
407 sql.append(" WHERE PostID = ?");
408 try {
409 connection = DBUtils.getConnection();
410 statement = connection.prepareStatement(sql.toString());
411
412 statement.setInt(1, postAttachCount);
414
415 statement.setInt(2, postID);
417
418 if (statement.executeUpdate() != 1) {
419 throw new ObjectNotFoundException("Cannot update AttachCount in table Post where primary key = (" + postID + ").");
420 }
421 m_dirty = true;
422 } catch(SQLException sqle) {
423 log.error("Sql Execution Error!", sqle);
424 throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.updateAttachCount.");
425 } finally {
426 DBUtils.closeStatement(statement);
427 DBUtils.closeConnection(connection);
428 }
429 }
430
431
438 public void updateStatus(int postID, int postStatus)
440 throws ObjectNotFoundException, DatabaseException {
441
442 Connection connection = null;
443 PreparedStatement statement = null;
444 StringBuffer sql = new StringBuffer (512);
445 sql.append("UPDATE " + TABLE_NAME + " SET PostStatus = ?");
446 sql.append(" WHERE PostID = ?");
447 try {
448 connection = DBUtils.getConnection();
449 statement = connection.prepareStatement(sql.toString());
450
451 statement.setInt(1, postStatus);
453
454 statement.setInt(2, postID);
456
457 if (statement.executeUpdate() != 1) {
458 throw new ObjectNotFoundException("Cannot update PostStatus in table Post where primary key = (" + postID + ").");
459 }
460 m_dirty = true;
461 } catch(SQLException sqle) {
462 log.error("Sql Execution Error!", sqle);
463 throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.updateStatus.");
464 } finally {
465 DBUtils.closeStatement(statement);
466 DBUtils.closeConnection(connection);
467 }
468 }
469
470
473 public void update_ForumID_inThread(int threadID, int forumID)
474 throws DatabaseException, ForeignKeyNotFoundException {
475
476 try {
477 DAOFactory.getForumDAO().findByPrimaryKey(forumID);
480 } catch(ObjectNotFoundException e) {
481 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Forum' does not exist. Cannot update table 'Post'.");
482 }
483
484 Connection connection = null;
485 PreparedStatement statement = null;
486 StringBuffer sql = new StringBuffer (512);
487 sql.append("UPDATE " + TABLE_NAME + " SET ForumID = ?");
488 sql.append(" WHERE ThreadID = ?");
489 try {
490 connection = DBUtils.getConnection();
491 statement = connection.prepareStatement(sql.toString());
492
493 statement.setInt(1, forumID);
495
496 statement.setInt(2, threadID);
498
499 statement.executeUpdate();
500 m_dirty = true;
501 } catch(SQLException sqle) {
502 log.error("Sql Execution Error!", sqle);
503 throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.update_ForumID_inThread.");
504 } finally {
505 DBUtils.closeStatement(statement);
506 DBUtils.closeConnection(connection);
507 }
508 }
509
510 private int findPostID(int forumID, String memberName, Timestamp postCreationDate)
511 throws ObjectNotFoundException, DatabaseException {
512
513 Connection connection = null;
514 PreparedStatement statement = null;
515 ResultSet resultSet = null;
516 StringBuffer sql = new StringBuffer (512);
517 sql.append("SELECT PostID");
518 sql.append(" FROM " + TABLE_NAME);
519 sql.append(" WHERE ForumID = ? AND MemberName = ? AND PostCreationDate = ? ");
520 try {
521 connection = DBUtils.getConnection();
522 statement = connection.prepareStatement(sql.toString());
523 statement.setInt(1, forumID);
524 statement.setString(2, memberName);
525 statement.setTimestamp(3, postCreationDate);
526 resultSet = statement.executeQuery();
527 if(!resultSet.next()) {
528 throw new ObjectNotFoundException("Cannot find the PostID in table Post.");
529 }
530
531 return resultSet.getInt("PostID");
532 } catch(SQLException sqle) {
533 log.error("Sql Execution Error!", sqle);
534 throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.findPostID.");
535 } finally {
536 DBUtils.closeResultSet(resultSet);
537 DBUtils.closeStatement(statement);
538 DBUtils.closeConnection(connection);
539 }
540 }
541
542
549 public PostBean getPost(int postID)
550 throws ObjectNotFoundException, DatabaseException {
551
552 Connection connection = null;
553 PreparedStatement statement = null;
554 ResultSet resultSet = null;
555 StringBuffer sql = new StringBuffer (512);
556 sql.append("SELECT ParentPostID, ForumID, ThreadID, MemberID, MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate, PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption, PostOption, PostStatus, PostIcon, PostAttachCount");
557 sql.append(" FROM " + TABLE_NAME);
558 sql.append(" WHERE PostID = ?");
559 try {
560 connection = DBUtils.getConnection();
561 statement = connection.prepareStatement(sql.toString());
562 statement.setInt(1, postID);
563 resultSet = statement.executeQuery();
564 if(!resultSet.next()) {
565 throw new ObjectNotFoundException("Cannot find the row in table Post where primary key = (" + postID + ").");
566 }
567
568 PostBean bean = new PostBean();
569 bean.setPostID(postID);
571 bean.setParentPostID(resultSet.getInt("ParentPostID"));
572 bean.setForumID(resultSet.getInt("ForumID"));
573 bean.setThreadID(resultSet.getInt("ThreadID"));
574 bean.setMemberID(resultSet.getInt("MemberID"));
575 bean.setMemberName(resultSet.getString("MemberName"));
576 bean.setLastEditMemberName(resultSet.getString("LastEditMemberName"));
577 bean.setPostTopic(resultSet.getString("PostTopic"));
578 bean.setPostBody(resultSet.getString("PostBody"));
579 bean.setPostCreationDate(resultSet.getTimestamp("PostCreationDate"));
580 bean.setPostLastEditDate(resultSet.getTimestamp("PostLastEditDate"));
581 bean.setPostCreationIP(resultSet.getString("PostCreationIP"));
582 bean.setPostLastEditIP(resultSet.getString("PostLastEditIP"));
583 bean.setPostEditCount(resultSet.getInt("PostEditCount"));
584 bean.setPostFormatOption(resultSet.getInt("PostFormatOption"));
585 bean.setPostOption(resultSet.getInt("PostOption"));
586 bean.setPostStatus(resultSet.getInt("PostStatus"));
587 bean.setPostIcon(resultSet.getString("PostIcon"));
588 bean.setPostAttachCount(resultSet.getInt("PostAttachCount"));
589 return bean;
590 } catch(SQLException sqle) {
591 log.error("Sql Execution Error!", sqle);
592 throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.getPost(pk).");
593 } finally {
594 DBUtils.closeResultSet(resultSet);
595 DBUtils.closeStatement(statement);
596 DBUtils.closeConnection(connection);
597 }
598 }
599
600 public PostBean getFirstPost_inThread(int threadID)
601 throws ObjectNotFoundException, DatabaseException {
602
603 Collection enablePostBeans = getEnablePosts_inThread_limit(threadID, 0, 1);
606 Iterator iter = enablePostBeans.iterator();
607 if (iter.hasNext()) {
608 PostBean postBean = (PostBean)iter.next();
609 return postBean;
610 }
611 throw new ObjectNotFoundException("Cannot find the first post in thread = " + threadID);
612 }
613
614 public Collection getEnablePosts_inThread_limit(int threadID, int offset, int rowsToReturn)
615 throws IllegalArgumentException , DatabaseException {
616 if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
617 return getBeans_inThread_limit_mysql(threadID, offset, rowsToReturn, true);
618 } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
619 return getBeans_inThread_limit_noscroll(threadID, offset, rowsToReturn, true);
620 }
621 return getBeans_inThread_limit_general(threadID, offset, rowsToReturn, true);
622 }
623
624 public Collection getDisablePosts_inThread_limit(int threadID, int offset, int rowsToReturn)
625 throws IllegalArgumentException , DatabaseException {
626 if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
627 return getBeans_inThread_limit_mysql(threadID, offset, rowsToReturn, false);
628 } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
629 return getBeans_inThread_limit_noscroll(threadID, offset, rowsToReturn, false);
630 }
631 return getBeans_inThread_limit_general(threadID, offset, rowsToReturn, false);
632 }
633
634
641 private Collection getBeans_inThread_limit_mysql(int threadID, int offset, int rowsToReturn, boolean enable)
642 throws IllegalArgumentException , DatabaseException {
643 if (offset < 0) throw new IllegalArgumentException ("The offset < 0 is not allowed.");
644 if (rowsToReturn <= 0) throw new IllegalArgumentException ("The rowsToReturn <= 0 is not allowed.");
645
646 Connection connection = null;
647 PreparedStatement statement = null;
648 ResultSet resultSet = null;
649 Collection retValue = new ArrayList();
650 StringBuffer sql = new StringBuffer (512);
651 sql.append("SELECT PostID, ParentPostID, ForumID, ThreadID, MemberID, MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate, PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption, PostOption, PostStatus, PostIcon, PostAttachCount");
652 sql.append(" FROM " + TABLE_NAME);
653 sql.append(" WHERE ThreadID = ?");
654 if (enable) {
655 sql.append(" AND PostStatus <> 1 ");
656 } else { sql.append(" AND PostStatus = 1 ");
658 }
659 sql.append(" ORDER BY PostID ASC ");
660 sql.append(" LIMIT ?, ?");
661 try {
662 connection = DBUtils.getConnection();
663 statement = connection.prepareStatement(sql.toString());
664 statement.setInt(1, threadID);
665 statement.setInt(2, offset);
666 statement.setInt(3, rowsToReturn);
667 resultSet = statement.executeQuery();
668 while (resultSet.next()) {
669 PostBean bean = new PostBean();
670 bean.setPostID(resultSet.getInt("PostID"));
671 bean.setParentPostID(resultSet.getInt("ParentPostID"));
672 bean.setForumID(resultSet.getInt("ForumID"));
673 bean.setThreadID(resultSet.getInt("ThreadID"));
674 bean.setMemberID(resultSet.getInt("MemberID"));
675 bean.setMemberName(resultSet.getString("MemberName"));
676 bean.setLastEditMemberName(resultSet.getString("LastEditMemberName"));
677 bean.setPostTopic(resultSet.getString("PostTopic"));
678 bean.setPostBody(resultSet.getString("PostBody"));
679 bean.setPostCreationDate(resultSet.getTimestamp("PostCreationDate"));
680 bean.setPostLastEditDate(resultSet.getTimestamp("PostLastEditDate"));
681 bean.setPostCreationIP(resultSet.getString("PostCreationIP"));
682 bean.setPostLastEditIP(resultSet.getString("PostLastEditIP"));
683 bean.setPostEditCount(resultSet.getInt("PostEditCount"));
684 bean.setPostFormatOption(resultSet.getInt("PostFormatOption"));
685 bean.setPostOption(resultSet.getInt("PostOption"));
686 bean.setPostStatus(resultSet.getInt("PostStatus"));
687 bean.setPostIcon(resultSet.getString("PostIcon"));
688 bean.setPostAttachCount(resultSet.getInt("PostAttachCount"));
689 retValue.add(bean);
690 }
691 return retValue;
692 } catch(SQLException sqle) {
693 log.error("Sql Execution Error!", sqle);
694 throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.getBeans_inThread_limit_mysql.");
695 } finally {
696 DBUtils.closeResultSet(resultSet);
697 DBUtils.closeStatement(statement);
698 DBUtils.closeConnection(connection);
699 }
700 }
701
702
709 private Collection getBeans_inThread_limit_noscroll(int threadID, int offset, int rowsToReturn, boolean enable)
710 throws IllegalArgumentException , DatabaseException {
711 if (offset < 0) throw new IllegalArgumentException ("The offset < 0 is not allowed.");
712 if (rowsToReturn <= 0) throw new IllegalArgumentException ("The rowsToReturn <= 0 is not allowed.");
713
714 Connection connection = null;
715 PreparedStatement statement = null;
716 ResultSet resultSet = null;
717 Collection retValue = new ArrayList();
718 StringBuffer sql = new StringBuffer (512);
719 sql.append("SELECT PostID, ParentPostID, ForumID, ThreadID, MemberID, MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate, PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption, PostOption, PostStatus, PostIcon, PostAttachCount");
720 sql.append(" FROM " + TABLE_NAME);
721 sql.append(" WHERE ThreadID = ?");
722 if (enable) {
723 sql.append(" AND PostStatus <> 1 ");
724 } else { sql.append(" AND PostStatus = 1 ");
726 }
727 sql.append(" ORDER BY PostID ASC ");
728 try {
729 connection = DBUtils.getConnection();
730 statement = connection.prepareStatement(sql.toString());
731 statement.setMaxRows(offset + rowsToReturn);
732 statement.setInt(1, threadID);
733 resultSet = statement.executeQuery();
734 int rowIndex = -1;
735 while (resultSet.next()) {
736 rowIndex++;
737 if (rowIndex < offset) continue;
738 PostBean bean = new PostBean();
739 bean.setPostID(resultSet.getInt("PostID"));
740 bean.setParentPostID(resultSet.getInt("ParentPostID"));
741 bean.setForumID(resultSet.getInt("ForumID"));
742 bean.setThreadID(resultSet.getInt("ThreadID"));
743 bean.setMemberID(resultSet.getInt("MemberID"));
744 bean.setMemberName(resultSet.getString("MemberName"));
745 bean.setLastEditMemberName(resultSet.getString("LastEditMemberName"));
746 bean.setPostTopic(resultSet.getString("PostTopic"));
747 bean.setPostBody(resultSet.getString("PostBody"));
748 bean.setPostCreationDate(resultSet.getTimestamp("PostCreationDate"));
749 bean.setPostLastEditDate(resultSet.getTimestamp("PostLastEditDate"));
750 bean.setPostCreationIP(resultSet.getString("PostCreationIP"));
751 bean.setPostLastEditIP(resultSet.getString("PostLastEditIP"));
752 bean.setPostEditCount(resultSet.getInt("PostEditCount"));
753 bean.setPostFormatOption(resultSet.getInt("PostFormatOption"));
754 bean.setPostOption(resultSet.getInt("PostOption"));
755 bean.setPostStatus(resultSet.getInt("PostStatus"));
756 bean.setPostIcon(resultSet.getString("PostIcon"));
757 bean.setPostAttachCount(resultSet.getInt("PostAttachCount"));
758 retValue.add(bean);
759 if (retValue.size() == rowsToReturn) break; }
761 return retValue;
762 } catch(SQLException sqle) {
763 log.error("Sql Execution Error!", sqle);
764 throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.getBeans_inThread_limit_noscroll.");
765 } finally {
766 DBUtils.closeResultSet(resultSet);
767 DBUtils.resetStatement(statement);
768 DBUtils.closeStatement(statement);
769 DBUtils.closeConnection(connection);
770 }
771 }
772
773
780 private Collection getBeans_inThread_limit_general(int threadID, int offset, int rowsToReturn, boolean enable)
781 throws IllegalArgumentException , DatabaseException {
782 if (offset < 0) throw new IllegalArgumentException ("The offset < 0 is not allowed.");
783 if (rowsToReturn <= 0) throw new IllegalArgumentException ("The rowsToReturn <= 0 is not allowed.");
784
785 Connection connection = null;
786 PreparedStatement statement = null;
787 ResultSet resultSet = null;
788 Collection retValue = new ArrayList();
789 StringBuffer sql = new StringBuffer (512);
790 sql.append("SELECT PostID, ParentPostID, ForumID, ThreadID, MemberID, MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate, PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption, PostOption, PostStatus, PostIcon, PostAttachCount");
791 sql.append(" FROM " + TABLE_NAME);
792 sql.append(" WHERE ThreadID = ?");
793 if (enable) {
794 sql.append(" AND PostStatus <> 1 ");
795 } else { sql.append(" AND PostStatus = 1 ");
797 }
798 sql.append(" ORDER BY PostID ASC ");
799 try {
800 connection = DBUtils.getConnection();
801 statement = connection.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
802 statement.setMaxRows(offset + rowsToReturn);
803 try {
804 statement.setFetchSize(rowsToReturn);
805 } catch (SQLException sqle) {
806 }
808
809 statement.setInt(1, threadID);
810 resultSet = statement.executeQuery();
811 boolean loop = resultSet.absolute(offset + 1); while (loop) {
813 PostBean bean = new PostBean();
814 bean.setPostID(resultSet.getInt("PostID"));
815 bean.setParentPostID(resultSet.getInt("ParentPostID"));
816 bean.setForumID(resultSet.getInt("ForumID"));
817 bean.setThreadID(resultSet.getInt("ThreadID"));
818 bean.setMemberID(resultSet.getInt("MemberID"));
819 bean.setMemberName(resultSet.getString("MemberName"));
820 bean.setLastEditMemberName(resultSet.getString("LastEditMemberName"));
821 bean.setPostTopic(resultSet.getString("PostTopic"));
822 bean.setPostBody(resultSet.getString("PostBody"));
823 bean.setPostCreationDate(resultSet.getTimestamp("PostCreationDate"));
824 bean.setPostLastEditDate(resultSet.getTimestamp("PostLastEditDate"));
825 bean.setPostCreationIP(resultSet.getString("PostCreationIP"));
826 bean.setPostLastEditIP(resultSet.getString("PostLastEditIP"));
827 bean.setPostEditCount(resultSet.getInt("PostEditCount"));
828 bean.setPostFormatOption(resultSet.getInt("PostFormatOption"));
829 bean.setPostOption(resultSet.getInt("PostOption"));
830 bean.setPostStatus(resultSet.getInt("PostStatus"));
831 bean.setPostIcon(resultSet.getString("PostIcon"));
832 bean.setPostAttachCount(resultSet.getInt("PostAttachCount"));
833 retValue.add(bean);
834 if (retValue.size() == rowsToReturn) break; loop = resultSet.next();
836 } return retValue;
838 } catch(SQLException sqle) {
839 log.error("Sql Execution Error!", sqle);
840 throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.getBeans_inThread_limit_general.");
841 } finally {
842 DBUtils.closeResultSet(resultSet);
843 DBUtils.resetStatement(statement);
844 DBUtils.closeStatement(statement);
845 DBUtils.closeConnection(connection);
846 }
847 }
848
849 public int getNumberOfPosts_inMember(int memberID)
850 throws AssertionException, DatabaseException {
851
852 Connection connection = null;
853 PreparedStatement statement = null;
854 ResultSet resultSet = null;
855 StringBuffer sql = new StringBuffer (512);
856 sql.append("SELECT Count(*)");
857 sql.append(" FROM " + TABLE_NAME);
858 sql.append(" WHERE MemberID = ?");
859 try {
860 connection = DBUtils.getConnection();
861 statement = connection.prepareStatement(sql.toString());
862
863 statement.setInt(1, memberID);
864
865 resultSet = statement.executeQuery();
866 if (!resultSet.next()) {
867 throw new AssertionException("Assertion in PostDAOImplJDBC.getNumberOfPosts_inMember.");
868 }
869 return resultSet.getInt(1);
870 } catch(SQLException sqle) {
871 log.error("Sql Execution Error!", sqle);
872 throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.getNumberOfPosts_inMember(memberID).");
873 } finally {
874 DBUtils.closeResultSet(resultSet);
875 DBUtils.closeStatement(statement);
876 DBUtils.closeConnection(connection);
877 }
878 }
879
880 public int getNumberOfEnablePosts_inForum(int forumID)
881 throws AssertionException, DatabaseException {
882
883 return getNumberOfPosts_inForum(forumID, true);
884 }
885
886 public int getNumberOfDisablePosts_inForum(int forumID)
887 throws AssertionException, DatabaseException {
888
889 return getNumberOfPosts_inForum(forumID, false);
890 }
891
892 public int getNumberOfPosts_inForum(int forumID, boolean enable)
893 throws AssertionException, DatabaseException {
894
895 Connection connection = null;
896 PreparedStatement statement = null;
897 ResultSet resultSet = null;
898 StringBuffer sql = new StringBuffer (512);
899 sql.append("SELECT Count(*)");
900 sql.append(" FROM " + TABLE_NAME);
901 sql.append(" WHERE ForumID = ? ");
902 if (enable) {
903 sql.append(" AND PostStatus <> 1 ");
904 } else { sql.append(" AND PostStatus = 1 ");
906 }
907 try {
908 connection = DBUtils.getConnection();
909 statement = connection.prepareStatement(sql.toString());
910
911 statement.setInt(1, forumID);
912
913 resultSet = statement.executeQuery();
914 if (!resultSet.next()) {
915 throw new AssertionException("Assertion in PostDAOImplJDBC.getNumberOfEnablePosts_inForum.");
916 }
917 return resultSet.getInt(1);
918 } catch(SQLException sqle) {
919 log.error("Sql Execution Error!", sqle);
920 throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.getNumberOfPosts_inForum(forumID).");
921 } finally {
922 DBUtils.closeResultSet(resultSet);
923 DBUtils.closeStatement(statement);
924 DBUtils.closeConnection(connection);
925 }
926 }
927
928 public int getNumberOfEnablePosts_inThread(int threadID)
929 throws AssertionException, DatabaseException {
930
931 return getNumberOfPosts_inThread(threadID, true);
932 }
933
934 public int getNumberOfDisablePosts_inThread(int threadID)
935 throws AssertionException, DatabaseException {
936
937 return getNumberOfPosts_inThread(threadID, false);
938 }
939
940 public int getNumberOfPosts_inThread(int threadID, boolean enable)
941 throws AssertionException, DatabaseException {
942
943 Connection connection = null;
944 PreparedStatement statement = null;
945 ResultSet resultSet = null;
946 StringBuffer sql = new StringBuffer (512);
947 sql.append("SELECT Count(*)");
948 sql.append(" FROM " + TABLE_NAME);
949 sql.append(" WHERE ThreadID = ?");
950 if (enable) {
951 sql.append(" AND PostStatus <> 1 ");
952 } else { sql.append(" AND PostStatus = 1 ");
954 }
955 try {
956 connection = DBUtils.getConnection();
957 statement = connection.prepareStatement(sql.toString());
958
959 statement.setInt(1, threadID);
960
961 resultSet = statement.executeQuery();
962 if (!resultSet.next()) {
963 throw new AssertionException("Assertion in PostDAOImplJDBC.getNumberOfPosts_inThread.");
964 }
965 return resultSet.getInt(1);
966 } catch(SQLException sqle) {
967 log.error("Sql Execution Error!", sqle);
968 throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.getNumberOfPosts_inThread(threadID).");
969 } finally {
970 DBUtils.closeResultSet(resultSet);
971 DBUtils.closeStatement(statement);
972 DBUtils.closeConnection(connection);
973 }
974 }
975
976 public void updateParentPostID(int oldParentPostID, int newParentPostID)
977 throws ObjectNotFoundException, DatabaseException {
978
979 Connection connection = null;
980 PreparedStatement statement = null;
981 StringBuffer sql = new StringBuffer (512);
982 sql.append("UPDATE " + TABLE_NAME + " SET ParentPostID = ?");
983 sql.append(" WHERE ParentPostID = ?");
984 try {
985 connection = DBUtils.getConnection();
986 statement = connection.prepareStatement(sql.toString());
987
988 statement.setInt(1, newParentPostID);
990
991 statement.setInt(2, oldParentPostID);
993
994 if (statement.executeUpdate() != 1) {
995 throw new ObjectNotFoundException("No row is updated in table Post where ParentPostID = (" + oldParentPostID + ").");
996 }
997 setDirty(true);
998 } catch(SQLException sqle) {
999 log.error("Sql Execution Error!", sqle);
1000 throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.updateParentPostID.");
1001 } finally {
1002 DBUtils.closeStatement(statement);
1003 DBUtils.closeConnection(connection);
1004 }
1005 }
1006
1007
1010 public void increaseEditCount(int postID)
1011 throws DatabaseException, ObjectNotFoundException {
1012
1013 Connection connection = null;
1014 PreparedStatement statement = null;
1015 String sql = "UPDATE " + TABLE_NAME + " SET PostEditCount = PostEditCount + 1 WHERE PostID = ?";
1016 try {
1017 connection = DBUtils.getConnection();
1018 statement = connection.prepareStatement(sql);
1019 statement.setInt(1, postID);
1020 if (statement.executeUpdate() != 1) {
1021 throw new ObjectNotFoundException("Cannot update the PostEditCount in table Post. Please contact Web site Administrator.");
1022 }
1023 setDirty(true);
1026 } catch (SQLException sqle) {
1027 log.error("Sql Execution Error!", sqle);
1028 throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.increaseEditCount.");
1029 } finally {
1030 DBUtils.closeStatement(statement);
1031 DBUtils.closeConnection(connection);
1032 }
1033 }
1034
1035
1042 public Collection getLastEnablePosts_inThread_limit(int threadID, int rowsToReturn)
1043 throws IllegalArgumentException , DatabaseException {
1044 if (rowsToReturn <= 0) throw new IllegalArgumentException ("The rowsToReturn <= 0 is not allowed.");
1045
1046 Connection connection = null;
1047 PreparedStatement statement = null;
1048 ResultSet resultSet = null;
1049 Collection retValue = new ArrayList();
1050 StringBuffer sql = new StringBuffer (512);
1051 sql.append("SELECT PostID, ParentPostID, ForumID, ThreadID, MemberID, MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate, PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption, PostOption, PostStatus, PostIcon, PostAttachCount");
1052 sql.append(" FROM " + TABLE_NAME);
1053 sql.append(" WHERE ThreadID = ? AND PostStatus = 0");
1054 sql.append(" ORDER BY PostCreationDate DESC ");
1055 try {
1056 connection = DBUtils.getConnection();
1057 statement = connection.prepareStatement(sql.toString());
1058 statement.setMaxRows(rowsToReturn);
1059 try {
1060 statement.setFetchSize(rowsToReturn);
1061 } catch (SQLException sqle) {
1062 }
1064
1065 statement.setInt(1, threadID);
1066 resultSet = statement.executeQuery();
1067 while (resultSet.next()) {
1068 PostBean bean = new PostBean();
1069 bean.setPostID(resultSet.getInt("PostID"));
1070 bean.setParentPostID(resultSet.getInt("ParentPostID"));
1071 bean.setForumID(resultSet.getInt("ForumID"));
1072 bean.setThreadID(resultSet.getInt("ThreadID"));
1073 bean.setMemberID(resultSet.getInt("MemberID"));
1074 bean.setMemberName(resultSet.getString("MemberName"));
1075 bean.setLastEditMemberName(resultSet.getString("LastEditMemberName"));
1076 bean.setPostTopic(resultSet.getString("PostTopic"));
1077 bean.setPostBody(resultSet.getString("PostBody"));
1078 bean.setPostCreationDate(resultSet.getTimestamp("PostCreationDate"));
1079 bean.setPostLastEditDate(resultSet.getTimestamp("PostLastEditDate"));
1080 bean.setPostCreationIP(resultSet.getString("PostCreationIP"));
1081 bean.setPostLastEditIP(resultSet.getString("PostLastEditIP"));
1082 bean.setPostEditCount(resultSet.getInt("PostEditCount"));
1083 bean.setPostFormatOption(resultSet.getInt("PostFormatOption"));
1084 bean.setPostOption(resultSet.getInt("PostOption"));
1085 bean.setPostStatus(resultSet.getInt("PostStatus"));
1086 bean.setPostIcon(resultSet.getString("PostIcon"));
1087 bean.setPostAttachCount(resultSet.getInt("PostAttachCount"));
1088 retValue.add(bean);
1089 if (retValue.size() == rowsToReturn) break; }
1091 return retValue;
1092 } catch(SQLException sqle) {
1093 log.error("Sql Execution Error!", sqle);
1094 throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.getLastEnablePosts_inThread_limit.");
1095 } finally {
1096 DBUtils.closeResultSet(resultSet);
1097 DBUtils.resetStatement(statement);
1098 DBUtils.closeStatement(statement);
1099 DBUtils.closeConnection(connection);
1100 }
1101 }
1102
1103
1110 public Collection getLastEnablePosts_inForum_limit(int forumID, int rowsToReturn)
1111 throws IllegalArgumentException , DatabaseException {
1112 if (rowsToReturn <= 0) throw new IllegalArgumentException ("The rowsToReturn <= 0 is not allowed.");
1113
1114 Connection connection = null;
1115 PreparedStatement statement = null;
1116 ResultSet resultSet = null;
1117 Collection retValue = new ArrayList();
1118 StringBuffer sql = new StringBuffer (512);
1119 sql.append("SELECT PostID, ParentPostID, ForumID, ThreadID, MemberID, MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate, PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption, PostOption, PostStatus, PostIcon, PostAttachCount");
1120 sql.append(" FROM " + TABLE_NAME);
1121 sql.append(" WHERE ForumID = ? AND PostStatus = 0"); sql.append(" ORDER BY PostCreationDate DESC ");
1123 try {
1124 connection = DBUtils.getConnection();
1125 statement = connection.prepareStatement(sql.toString());
1126 statement.setMaxRows(rowsToReturn);
1127 try {
1128 statement.setFetchSize(rowsToReturn);
1129 } catch (SQLException sqle) {
1130 }
1132
1133 statement.setInt(1, forumID);
1134 resultSet = statement.executeQuery();
1135 while (resultSet.next()) {
1136 PostBean bean = new PostBean();
1137 bean.setPostID(resultSet.getInt("PostID"));
1138 bean.setParentPostID(resultSet.getInt("ParentPostID"));
1139 bean.setForumID(resultSet.getInt("ForumID"));
1140 bean.setThreadID(resultSet.getInt("ThreadID"));
1141 bean.setMemberID(resultSet.getInt("MemberID"));
1142 bean.setMemberName(resultSet.getString("MemberName"));
1143 bean.setLastEditMemberName(resultSet.getString("LastEditMemberName"));
1144 bean.setPostTopic(resultSet.getString("PostTopic"));
1145 bean.setPostBody(resultSet.getString("PostBody"));
1146 bean.setPostCreationDate(resultSet.getTimestamp("PostCreationDate"));
1147 bean.setPostLastEditDate(resultSet.getTimestamp("PostLastEditDate"));
1148 bean.setPostCreationIP(resultSet.getString("PostCreationIP"));
1149 bean.setPostLastEditIP(resultSet.getString("PostLastEditIP"));
1150 bean.setPostEditCount(resultSet.getInt("PostEditCount"));
1151 bean.setPostFormatOption(resultSet.getInt("PostFormatOption"));
1152 bean.setPostOption(resultSet.getInt("PostOption"));
1153 bean.setPostStatus(resultSet.getInt("PostStatus"));
1154 bean.setPostIcon(resultSet.getString("PostIcon"));
1155 bean.setPostAttachCount(resultSet.getInt("PostAttachCount"));
1156 retValue.add(bean);
1157 if (retValue.size() == rowsToReturn) break; }
1159 return retValue;
1160 } catch(SQLException sqle) {
1161 log.error("Sql Execution Error!", sqle);
1162 throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.getLastEnablePosts_inForum_limit.");
1163 } finally {
1164 DBUtils.closeResultSet(resultSet);
1165 DBUtils.resetStatement(statement);
1166 DBUtils.closeStatement(statement);
1167 DBUtils.closeConnection(connection);
1168 }
1169 }
1170
1171 public int getNumberOfPosts()
1172 throws AssertionException, DatabaseException {
1173
1174 Connection connection = null;
1175 PreparedStatement statement = null;
1176 ResultSet resultSet = null;
1177 StringBuffer sql = new StringBuffer (512);
1178 sql.append("SELECT Count(*)");
1179 sql.append(" FROM " + TABLE_NAME);
1180 try {
1182 connection = DBUtils.getConnection();
1183 statement = connection.prepareStatement(sql.toString());
1184 resultSet = statement.executeQuery();
1185 if (!resultSet.next()) {
1186 throw new AssertionException("Assertion in PostDAOImplJDBC.getNumberOfPosts.");
1187 }
1188 return resultSet.getInt(1);
1189 } catch(SQLException sqle) {
1190 log.error("Sql Execution Error!", sqle);
1191 throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.getNumberOfPosts.");
1192 } finally {
1193 DBUtils.closeResultSet(resultSet);
1194 DBUtils.closeStatement(statement);
1195 DBUtils.closeConnection(connection);
1196 }
1197 }
1198
1199
1205 public Collection getPosts()
1206 throws DatabaseException {
1207
1208 Connection connection = null;
1209 PreparedStatement statement = null;
1210 ResultSet resultSet = null;
1211 Collection retValue = new ArrayList();
1212 StringBuffer sql = new StringBuffer (512);
1213 sql.append("SELECT PostID, ParentPostID, ForumID, ThreadID, MemberID, PostTopic, PostBody, PostCreationDate, PostLastEditDate, PostAttachCount");
1214 sql.append(" FROM " + TABLE_NAME);
1215 try {
1218 connection = DBUtils.getConnection();
1219 statement = connection.prepareStatement(sql.toString());
1220 resultSet = statement.executeQuery();
1221 while (resultSet.next()) {
1222 PostBean bean = new PostBean();
1223 bean.setPostID(resultSet.getInt("PostID"));
1224 bean.setParentPostID(resultSet.getInt("ParentPostID"));
1225 bean.setForumID(resultSet.getInt("ForumID"));
1226 bean.setThreadID(resultSet.getInt("ThreadID"));
1227 bean.setMemberID(resultSet.getInt("MemberID"));
1228 bean.setPostTopic(resultSet.getString("PostTopic"));
1229 bean.setPostBody(resultSet.getString("PostBody"));
1230 bean.setPostCreationDate(resultSet.getTimestamp("PostCreationDate"));
1231 bean.setPostLastEditDate(resultSet.getTimestamp("PostLastEditDate"));
1232 bean.setPostAttachCount(resultSet.getInt("PostAttachCount"));
1233 retValue.add(bean);
1234 }
1235 return retValue;
1236 } catch(SQLException sqle) {
1237 log.error("Sql Execution Error!", sqle);
1238 throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.getPosts.");
1239 } finally {
1240 DBUtils.closeResultSet(resultSet);
1241 DBUtils.closeStatement(statement);
1242 DBUtils.closeConnection(connection);
1243 }
1244 }
1245
1246 public int getMaxPostID()
1247 throws AssertionException, DatabaseException {
1248
1249 Connection connection = null;
1250 PreparedStatement statement = null;
1251 ResultSet resultSet = null;
1252 StringBuffer sql = new StringBuffer (512);
1253 sql.append("SELECT MAX(PostID)");
1254 sql.append(" FROM " + TABLE_NAME);
1255 try {
1256 connection = DBUtils.getConnection();
1257 statement = connection.prepareStatement(sql.toString());
1258 resultSet = statement.executeQuery();
1259 if (!resultSet.next()) {
1260 throw new AssertionException("Assertion in PostDAOImplJDBC.getMaxPostID.");
1261 }
1262 return resultSet.getInt(1);
1263 } catch(SQLException sqle) {
1264 log.error("Sql Execution Error!", sqle);
1265 throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.getMaxPostID.");
1266 } finally {
1267 DBUtils.closeResultSet(resultSet);
1268 DBUtils.closeStatement(statement);
1269 DBUtils.closeConnection(connection);
1270 }
1271 }
1272
1273
1280 public Collection getPosts_fromIDRange(int fromID, int toID)
1281 throws IllegalArgumentException , DatabaseException {
1282
1283 if (fromID < 0) throw new IllegalArgumentException ("The fromID < 0 is not allowed.");
1284 if (toID < fromID) throw new IllegalArgumentException ("toID < fromID is not allowed.");
1285
1286 Connection connection = null;
1287 PreparedStatement statement = null;
1288 ResultSet resultSet = null;
1289 Collection retValue = new ArrayList();
1290 StringBuffer sql = new StringBuffer (512);
1291 sql.append("SELECT PostID, ParentPostID, ForumID, ThreadID, MemberID, MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate, PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption, PostOption, PostStatus, PostIcon, PostAttachCount");
1292 sql.append(" FROM " + TABLE_NAME);
1293 sql.append(" WHERE (PostID >= ?) AND (PostID <= ?)");
1294 sql.append(" ORDER BY PostID ASC ");
1295 try {
1296 connection = DBUtils.getConnection();
1297 statement = connection.prepareStatement(sql.toString());
1298 statement.setInt(1, fromID);
1299 statement.setInt(2, toID);
1300 resultSet = statement.executeQuery();
1301 while (resultSet.next()) {
1302 PostBean bean = new PostBean();
1303 bean.setPostID(resultSet.getInt("PostID"));
1304 bean.setParentPostID(resultSet.getInt("ParentPostID"));
1305 bean.setForumID(resultSet.getInt("ForumID"));
1306 bean.setThreadID(resultSet.getInt("ThreadID"));
1307 bean.setMemberID(resultSet.getInt("MemberID"));
1308 bean.setMemberName(resultSet.getString("MemberName"));
1309 bean.setLastEditMemberName(resultSet.getString("LastEditMemberName"));
1310 bean.setPostTopic(resultSet.getString("PostTopic"));
1311 bean.setPostBody(resultSet.getString("PostBody"));
1312 bean.setPostCreationDate(resultSet.getTimestamp("PostCreationDate"));
1313 bean.setPostLastEditDate(resultSet.getTimestamp("PostLastEditDate"));
1314 bean.setPostCreationIP(resultSet.getString("PostCreationIP"));
1315 bean.setPostLastEditIP(resultSet.getString("PostLastEditIP"));
1316 bean.setPostEditCount(resultSet.getInt("PostEditCount"));
1317 bean.setPostFormatOption(resultSet.getInt("PostFormatOption"));
1318 bean.setPostOption(resultSet.getInt("PostOption"));
1319 bean.setPostStatus(resultSet.getInt("PostStatus"));
1320 bean.setPostIcon(resultSet.getString("PostIcon"));
1321 bean.setPostAttachCount(resultSet.getInt("PostAttachCount"));
1322 retValue.add(bean);
1323 }
1324 return retValue;
1325 } catch(SQLException sqle) {
1326 log.error("Sql Execution Error!", sqle);
1327 throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.getPosts_fromIDRange.");
1328 } finally {
1329 DBUtils.closeResultSet(resultSet);
1330 DBUtils.closeStatement(statement);
1331 DBUtils.closeConnection(connection);
1332 }
1333 }
1334
1335
1339 public Collection getMostActiveMembers(Timestamp since, int rowsToReturn)
1340 throws DatabaseException {
1341
1342 Connection connection = null;
1343 PreparedStatement statement = null;
1344 ResultSet resultSet = null;
1345 Collection retValue = new ArrayList();
1346 StringBuffer sql = new StringBuffer (512);
1347 sql.append("SELECT MemberID , MemberName, COUNT(PostID) AS PostCount"); sql.append(" FROM ").append(TABLE_NAME);
1349 sql.append(" WHERE PostCreationDate > ? AND PostStatus <> ").append(PostBean.POST_STATUS_DISABLED);
1350 sql.append(" GROUP BY MemberID, MemberName");
1351 if (DBUtils.getDatabaseType() == DBUtils.DATABASE_FIREBIRD) { sql.append(" ORDER BY COUNT(PostID) DESC");
1354 } else {
1355 sql.append(" ORDER BY PostCount DESC");
1356 }
1357 try {
1358 connection = DBUtils.getConnection();
1359 statement = connection.prepareStatement(sql.toString());
1360 statement.setTimestamp(1, since);
1361 resultSet = statement.executeQuery();
1362 while (resultSet.next()) {
1363 ActiveMember member = new ActiveMember();
1364 member.setMemberID(resultSet.getInt("MemberID"));
1365 member.setMemberName(resultSet.getString("MemberName"));
1366 member.setLastPostCount(resultSet.getInt("PostCount"));
1367 retValue.add(member);
1368 if (retValue.size() == rowsToReturn) break; }
1370 return retValue;
1371 } catch(SQLException sqle) {
1372 log.error("Sql Execution Error!", sqle);
1373 throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.getMostActiveMembers.");
1374 } finally {
1375 DBUtils.closeResultSet(resultSet);
1376 DBUtils.resetStatement(statement);
1377 DBUtils.closeStatement(statement);
1378 DBUtils.closeConnection(connection);
1379 }
1380 }
1381
1382 public Collection getMostActiveThreads(Timestamp since, int rowsToReturn)
1383 throws DatabaseException {
1384
1385 Connection connection = null;
1386 PreparedStatement statement = null;
1387 ResultSet resultSet = null;
1388 Collection retValue = new ArrayList();
1389 StringBuffer sql = new StringBuffer (512);
1390 sql.append("SELECT t.ThreadID , f.ForumID, t.MemberName, t.LastPostMemberName, ThreadLastPostDate, ThreadViewCount, ThreadReplyCount, ThreadType, ThreadTopic, ThreadAttachCount, ThreadIcon, COUNT(PostID) AS PostCount"); sql.append(" FROM ").append(TABLE_NAME).append(" p , ").append(ThreadDAO.TABLE_NAME).append(" t,").append(ForumDAO.TABLE_NAME).append(" f");
1392 sql.append(" WHERE (t.ThreadID = p.ThreadID AND t.ThreadStatus <> ").append(ThreadBean.THREAD_STATUS_DISABLED);
1393 sql.append(" AND p.ForumID = f.ForumID AND f.ForumStatus <> ").append(ForumBean.FORUM_STATUS_DISABLED);
1394 sql.append(" AND PostCreationDate > ?)");
1395 sql.append(" GROUP BY t.ThreadID, f.ForumID, t.MemberName, t.LastPostMemberName, ThreadLastPostDate, ThreadViewCount, ThreadReplyCount, ThreadType, ThreadTopic, ThreadAttachCount, ThreadIcon");
1396 if (DBUtils.getDatabaseType() == DBUtils.DATABASE_FIREBIRD) { sql.append(" ORDER BY COUNT(PostID) DESC, t.ThreadLastPostDate DESC");
1400 } else {
1401 sql.append(" ORDER BY PostCount DESC, t.ThreadLastPostDate DESC");
1402 }
1403 try {
1404 connection = DBUtils.getConnection();
1405 statement = connection.prepareStatement(sql.toString());
1406 statement.setTimestamp(1, since);
1407 resultSet = statement.executeQuery();
1408 while (resultSet.next()) {
1409 ActiveThread thread = new ActiveThread();
1410 thread.setThreadID(resultSet.getInt("ThreadID"));
1411 thread.setThreadTopic(resultSet.getString("ThreadTopic"));
1412 thread.setForumID(resultSet.getInt("ForumID"));
1413 thread.setLastPostCount(resultSet.getInt("PostCount"));
1414 thread.setLastDate(resultSet.getTimestamp("ThreadLastPostDate"));
1415 thread.setAuthor(resultSet.getString("MemberName"));
1416 thread.setLastMember(resultSet.getString("LastPostMemberName"));
1417 thread.setThreadType(resultSet.getInt("ThreadType"));
1418 thread.setViewCount(resultSet.getInt("ThreadViewCount"));
1419 thread.setReplyCount(resultSet.getInt("ThreadReplyCount"));
1420 thread.setAttachCount(resultSet.getInt("ThreadAttachCount"));
1421 thread.setIcon(resultSet.getString("ThreadIcon"));
1422 retValue.add(thread);
1423 if (retValue.size() == rowsToReturn) break; }
1425 return retValue;
1426 } catch(SQLException sqle) {
1427 log.error("Sql Execution Error!", sqle);
1428 throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.getMostActiveThreads.");
1429 } finally {
1430 DBUtils.closeResultSet(resultSet);
1431 DBUtils.resetStatement(statement);
1432 DBUtils.closeStatement(statement);
1433 DBUtils.closeConnection(connection);
1434 }
1435 }
1436
1437} |
Popular Tags |
Java API By Example, From Geeks To Geeks.