|
1
41 package com.mvnforum.db.jdbc;
42
43 import java.io.StringReader  ;
44 import java.sql.*;
45 import java.util.ArrayList ;
46 import java.util.Collection ;
47
48 import com.mvnforum.db.*;
49 import net.myvietnam.mvncore.db.DBUtils;
50 import net.myvietnam.mvncore.exception.*;
51 import org.apache.commons.logging.Log;
52 import org.apache.commons.logging.LogFactory;
53
54 public class ForumDAOImplJDBC implements ForumDAO {
55
56 private static Log log = LogFactory.getLog(ForumDAOImplJDBC.class);
57
58 private static boolean m_dirty = true;
60
61 public ForumDAOImplJDBC() {
62 }
63
64 public static boolean isDirty() {
65 return m_dirty;
66 }
67
68 public static void setDirty(boolean dirty) {
69 m_dirty = dirty;
70 }
71
72 public void findByPrimaryKey(int forumID)
73 throws ObjectNotFoundException, DatabaseException {
74
75 Connection connection = null;
76 PreparedStatement statement = null;
77 ResultSet resultSet = null;
78 StringBuffer sql = new StringBuffer (512);
79 sql.append("SELECT ForumID");
80 sql.append(" FROM " + TABLE_NAME);
81 sql.append(" WHERE ForumID = ?");
82 try {
83 connection = DBUtils.getConnection();
84 statement = connection.prepareStatement(sql.toString());
85 statement.setInt(1, forumID);
86 resultSet = statement.executeQuery();
87 if (!resultSet.next()) {
88 throw new ObjectNotFoundException("Cannot find the primary key (" + forumID + ") in table 'Forum'.");
89 }
90 } catch(SQLException sqle) {
91 log.error("Sql Execution Error!", sqle);
92 throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.findByPrimaryKey.");
93 } finally {
94 DBUtils.closeResultSet(resultSet);
95 DBUtils.closeStatement(statement);
96 DBUtils.closeConnection(connection);
97 }
98 }
99
100 public void findByAlternateKey_ForumName_CategoryID(String forumName, int categoryID)
101 throws ObjectNotFoundException, DatabaseException {
102
103 Connection connection = null;
104 PreparedStatement statement = null;
105 ResultSet resultSet = null;
106 StringBuffer sql = new StringBuffer (512);
107 sql.append("SELECT ForumName, CategoryID");
108 sql.append(" FROM " + TABLE_NAME);
109 sql.append(" WHERE ForumName = ? AND CategoryID = ?");
110 try {
111 connection = DBUtils.getConnection();
112 statement = connection.prepareStatement(sql.toString());
113 statement.setString(1, forumName);
114 statement.setInt(2, categoryID);
115 resultSet = statement.executeQuery();
116 if (!resultSet.next()) {
117 throw new ObjectNotFoundException("Cannot find the alternate key [ForumName, CategoryID] (" + forumName + ", " + categoryID + ") in table 'Forum'.");
118 }
119 } catch(SQLException sqle) {
120 log.error("Sql Execution Error!", sqle);
121 throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.findByAlternateKey_ForumName_CategoryID.");
122 } finally {
123 DBUtils.closeResultSet(resultSet);
124 DBUtils.closeStatement(statement);
125 DBUtils.closeConnection(connection);
126 }
127 }
128
129
136 public void create(int categoryID, String lastPostMemberName, String forumName,
137 String forumDesc, Timestamp forumCreationDate, Timestamp forumModifiedDate,
138 Timestamp forumLastPostDate, int forumOrder, int forumType,
139 int forumFormatOption, int forumOption, int forumStatus,
140 int forumModerationMode, String forumPassword, int forumThreadCount,
141 int forumPostCount)
142 throws CreateException, DatabaseException, DuplicateKeyException, ForeignKeyNotFoundException {
143
144 ForumBean.validateForumType(forumType);
146 ForumBean.validateForumFormatOption(forumFormatOption);
147 ForumBean.validateForumOption(forumOption);
148 ForumBean.validateForumStatus(forumStatus);
149 ForumBean.validateForumModerationMode(forumModerationMode);
150
151 try {
154 findByAlternateKey_ForumName_CategoryID(forumName, categoryID);
156 throw new DuplicateKeyException("Alternate key already exists. Cannot create new Forum with the same [ForumName, CategoryID] (" + forumName + ", " + categoryID + ").");
158 } catch(ObjectNotFoundException e) {
159 }
161
162 try {
163 DAOFactory.getCategoryDAO().findByPrimaryKey(categoryID);
166 } catch(ObjectNotFoundException e) {
167 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Category' does not exist. Cannot create new Forum.");
168 }
169
170 Connection connection = null;
171 PreparedStatement statement = null;
172 StringBuffer sql = new StringBuffer (512);
173 sql.append("INSERT INTO " + TABLE_NAME + " (CategoryID, LastPostMemberName, ForumName, ForumDesc, ForumCreationDate, ForumModifiedDate, ForumLastPostDate, ForumOrder, ForumType, ForumFormatOption, ForumOption, ForumStatus, ForumModerationMode, ForumPassword, ForumThreadCount, ForumPostCount)");
174 sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
175 try {
176 connection = DBUtils.getConnection();
177 statement = connection.prepareStatement(sql.toString());
178
179 statement.setInt(1, categoryID);
180 statement.setString(2, lastPostMemberName);
181 statement.setString(3, forumName);
182 if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
183 statement.setCharacterStream(4, new StringReader (forumDesc), forumDesc.length());
184 } else {
185 statement.setString(4, forumDesc);
186 }
187 statement.setTimestamp(5, forumCreationDate);
188 statement.setTimestamp(6, forumModifiedDate);
189 statement.setTimestamp(7, forumLastPostDate);
190 statement.setInt(8, forumOrder);
191 statement.setInt(9, forumType);
192 statement.setInt(10, forumFormatOption);
193 statement.setInt(11, forumOption);
194 statement.setInt(12, forumStatus);
195 statement.setInt(13, forumModerationMode);
196 statement.setString(14, forumPassword);
197 statement.setInt(15, forumThreadCount);
198 statement.setInt(16, forumPostCount);
199
200 if (statement.executeUpdate() != 1) {
201 throw new CreateException("Error adding a row into table 'Forum'.");
202 }
203 m_dirty = true;
204 } catch(SQLException sqle) {
205 log.error("Sql Execution Error!", sqle);
206 throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.create.");
207 } finally {
208 DBUtils.closeStatement(statement);
209 DBUtils.closeConnection(connection);
210 }
211 }
212
213
220 public int createForum(int categoryID, String lastPostMemberName, String forumName,
221 String forumDesc, Timestamp forumCreationDate, Timestamp forumModifiedDate,
222 Timestamp forumLastPostDate, int forumOrder, int forumType,
223 int forumFormatOption, int forumOption, int forumStatus,
224 int forumModerationMode, String forumPassword, int forumThreadCount,
225 int forumPostCount)
226 throws CreateException, DatabaseException, DuplicateKeyException, ForeignKeyNotFoundException {
227
228 create(categoryID, lastPostMemberName, forumName, forumDesc, forumCreationDate, forumModifiedDate, forumLastPostDate, forumOrder,
229 forumType, forumFormatOption, forumOption, forumStatus, forumModerationMode, forumPassword, forumThreadCount, forumPostCount);
230
231 ForumBean forumBean = null;
232 try {
233 forumBean = getBean_byAlternateKey_ForumName_CategoryID(forumName, categoryID);
234 } catch (ObjectNotFoundException ex) {
235 log.error("Cannot find ForumID after creating forum.", ex);
236 }
237 return forumBean.getForumID();
238 }
239
240 public void delete(int forumID)
241 throws DatabaseException, ObjectNotFoundException {
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 if (statement.executeUpdate() != 1) {
254 throw new ObjectNotFoundException("Cannot delete a row in table Forum where primary key = (" + forumID + ").");
255 }
256 m_dirty = true;
257 } catch(SQLException sqle) {
258 log.error("Sql Execution Error!", sqle);
259 throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.delete.");
260 } finally {
261 DBUtils.closeStatement(statement);
262 DBUtils.closeConnection(connection);
263 }
264 }
265
266
272 public void update(int forumID, int categoryID, String forumName, String forumDesc,
274 Timestamp forumModifiedDate, int forumOrder, int forumType,
275 int forumFormatOption, int forumOption, int forumStatus,
276 int forumModerationMode)
277 throws ObjectNotFoundException, DatabaseException, DuplicateKeyException, ForeignKeyNotFoundException, IllegalArgumentException {
278
279 ForumBean.validateForumType(forumType);
281 ForumBean.validateForumFormatOption(forumFormatOption);
282 ForumBean.validateForumOption(forumOption);
283 ForumBean.validateForumStatus(forumStatus);
284 ForumBean.validateForumModerationMode(forumModerationMode);
285
286 ForumBean bean = getForum(forumID);
287
288 if ( !forumName.equals(bean.getForumName()) ||
289 (categoryID != bean.getCategoryID()) ) {
290 try {
292 findByAlternateKey_ForumName_CategoryID(forumName, categoryID);
293 throw new DuplicateKeyException("Alternate key [ForumName, CategoryID] (" + forumName + ", " + categoryID + ")already exists. Cannot update Forum.");
294 } catch(ObjectNotFoundException e) {
295 }
297 }
298
299 try {
300 DAOFactory.getCategoryDAO().findByPrimaryKey(categoryID);
303 } catch(ObjectNotFoundException e) {
304 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Category' does not exist. Cannot update table 'Forum'.");
305 }
306
307 Connection connection = null;
308 PreparedStatement statement = null;
309 StringBuffer sql = new StringBuffer (512);
310 sql.append("UPDATE " + TABLE_NAME + " SET CategoryID = ?, ForumName = ?, ForumDesc = ?, ForumModifiedDate = ?, ForumOrder = ?, ForumType = ?, ForumFormatOption = ?, ForumOption = ?, ForumStatus = ?, ForumModerationMode = ?");
311 sql.append(" WHERE ForumID = ?");
312 try {
313 connection = DBUtils.getConnection();
314 statement = connection.prepareStatement(sql.toString());
315
316 statement.setInt(1, categoryID);
318 statement.setString(2, forumName);
319 if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
320 statement.setCharacterStream(3, new StringReader (forumDesc), forumDesc.length());
321 } else {
322 statement.setString(3, forumDesc);
323 }
324 statement.setTimestamp(4, forumModifiedDate);
325 statement.setInt(5, forumOrder);
326 statement.setInt(6, forumType);
327 statement.setInt(7, forumFormatOption);
328 statement.setInt(8, forumOption);
329 statement.setInt(9, forumStatus);
330 statement.setInt(10, forumModerationMode);
331
332 statement.setInt(11, forumID);
334
335 if (statement.executeUpdate() != 1) {
336 throw new ObjectNotFoundException("Cannot update table Forum where primary key = (" + forumID + ").");
337 }
338 m_dirty = true;
339 } catch(SQLException sqle) {
340 log.error("Sql Execution Error!", sqle);
341 throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.update.");
342 } finally {
343 DBUtils.closeStatement(statement);
344 DBUtils.closeConnection(connection);
345 }
346 }
347
348
355 public void updateLastPostMemberName(int forumID, String lastPostMemberName)
357 throws ObjectNotFoundException, DatabaseException, ForeignKeyNotFoundException {
358
359 if ((lastPostMemberName!=null) && (lastPostMemberName.length()>0)) {
361 try {
362 DAOFactory.getMemberDAO().findByAlternateKey_MemberName(lastPostMemberName);
365 } catch(ObjectNotFoundException e) {
366 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Member' does not exist. Cannot update table 'Forum'.");
367 }
368 } else lastPostMemberName="";
370 Connection connection = null;
371 PreparedStatement statement = null;
372 StringBuffer sql = new StringBuffer (512);
373 sql.append("UPDATE " + TABLE_NAME + " SET LastPostMemberName = ?");
374 sql.append(" WHERE ForumID = ?");
375 try {
376 connection = DBUtils.getConnection();
377 statement = connection.prepareStatement(sql.toString());
378
379 statement.setString(1, lastPostMemberName);
381
382 statement.setInt(2, forumID);
384
385 if (statement.executeUpdate() != 1) {
386 if ( DBUtils.getDatabaseType() != DBUtils.DATABASE_MYSQL ) {
390 throw new ObjectNotFoundException("Cannot update table Forum where primary key = (" + forumID + ").");
391 } else {
392 log.warn("WARNING: By pass the check for Caucho MySql driver.");
393 }
394 }
395 setDirty(true);
398 } catch(SQLException sqle) {
399 log.error("Sql Execution Error!", sqle);
400 throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.updateLastPostMemberName.");
401 } finally {
402 DBUtils.closeStatement(statement);
403 DBUtils.closeConnection(connection);
404 }
405 }
406
407
414 public void updateLastPostDate(int forumID, Timestamp forumLastPostDate)
416 throws ObjectNotFoundException, DatabaseException {
417
418 Connection connection = null;
419 PreparedStatement statement = null;
420 StringBuffer sql = new StringBuffer (512);
421 sql.append("UPDATE " + TABLE_NAME + " SET ForumLastPostDate = ?");
422 sql.append(" WHERE ForumID = ?");
423 try {
424 connection = DBUtils.getConnection();
425 statement = connection.prepareStatement(sql.toString());
426
427 statement.setTimestamp(1, forumLastPostDate);
429
430 statement.setInt(2, forumID);
432
433 if (statement.executeUpdate() != 1) {
434 throw new ObjectNotFoundException("Cannot update table Forum where primary key = (" + forumID + ").");
435 }
436 m_dirty = true;
437 } catch(SQLException sqle) {
438 log.error("Sql Execution Error!", sqle);
439 throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.updateLastPostDate.");
440 } finally {
441 DBUtils.closeStatement(statement);
442 DBUtils.closeConnection(connection);
443 }
444 }
445
446
452 public void updateStatistics(int forumID, int forumThreadCount, int forumPostCount)
454 throws ObjectNotFoundException, DatabaseException {
455
456 Connection connection = null;
457 PreparedStatement statement = null;
458 StringBuffer sql = new StringBuffer (512);
459 sql.append("UPDATE " + TABLE_NAME + " SET ForumThreadCount = ?, ForumPostCount = ?");
460 sql.append(" WHERE ForumID = ?");
461 try {
462 connection = DBUtils.getConnection();
463 statement = connection.prepareStatement(sql.toString());
464
465 statement.setInt(1, forumThreadCount);
467 statement.setInt(2, forumPostCount);
468
469 statement.setInt(3, forumID);
471
472 if (statement.executeUpdate() != 1) {
473 throw new ObjectNotFoundException("Cannot update table Forum where primary key = (" + forumID + ").");
474 }
475 m_dirty = true;
476 } catch(SQLException sqle) {
477 log.error("Sql Execution Error!", sqle);
478 throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.updateStatistics.");
479 } finally {
480 DBUtils.closeStatement(statement);
481 DBUtils.closeConnection(connection);
482 }
483 }
484
485
488 public void increasePostCount(int forumID)
489 throws DatabaseException, ObjectNotFoundException {
490
491 Connection connection = null;
492 PreparedStatement statement = null;
493 String sql = "UPDATE " + TABLE_NAME + " SET ForumPostCount = ForumPostCount + 1 WHERE ForumID = ?";
494 try {
495 connection = DBUtils.getConnection();
496 statement = connection.prepareStatement(sql);
497 statement.setInt(1, forumID);
498 if (statement.executeUpdate() != 1) {
499 throw new ObjectNotFoundException("Cannot update the ForumPostCount in table Forum. Please contact Web site Administrator.");
500 }
501 setDirty(true);
504 } catch (SQLException sqle) {
505 log.error("Sql Execution Error!", sqle);
506 throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.increasePostCount.");
507 } finally {
508 DBUtils.closeStatement(statement);
509 DBUtils.closeConnection(connection);
510 }
511 }
512
513
516 public void increaseThreadCount(int forumID)
517 throws DatabaseException, ObjectNotFoundException {
518
519 Connection connection = null;
520 PreparedStatement statement = null;
521 String sql = "UPDATE " + TABLE_NAME + " SET ForumThreadCount = ForumThreadCount + 1 WHERE ForumID = ?";
522 try {
523 connection = DBUtils.getConnection();
524 statement = connection.prepareStatement(sql);
525 statement.setInt(1, forumID);
526 if (statement.executeUpdate() != 1) {
527 throw new ObjectNotFoundException("Cannot update the ForumThreadCount in table Forum. Please contact Web site Administrator.");
528 }
529 setDirty(true);
530 } catch (SQLException sqle) {
531 log.error("Sql Execution Error!", sqle);
532 throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.increaseThreadCount.");
533 } finally {
534 DBUtils.closeStatement(statement);
535 DBUtils.closeConnection(connection);
536 }
537 }
538
539
542 public void decreaseThreadCount(int forumID)
543 throws DatabaseException, ObjectNotFoundException {
544
545 Connection connection = null;
546 PreparedStatement statement = null;
547 String sql = "UPDATE " + TABLE_NAME + " SET ForumThreadCount = ForumThreadCount - 1 WHERE ForumID = ?";
548 try {
549 connection = DBUtils.getConnection();
550 statement = connection.prepareStatement(sql);
551 statement.setInt(1, forumID);
552 if (statement.executeUpdate() != 1) {
553 throw new ObjectNotFoundException("Cannot update the ForumThreadCount in table Forum. Please contact Web site Administrator.");
554 }
555 setDirty(true);
556 } catch (SQLException sqle) {
557 log.error("Sql Execution Error!", sqle);
558 throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.decreaseThreadCount.");
559 } finally {
560 DBUtils.closeStatement(statement);
561 DBUtils.closeConnection(connection);
562 }
563 }
564
565
572 public ForumBean getForum(int forumID)
573 throws ObjectNotFoundException, DatabaseException {
574
575 Connection connection = null;
576 PreparedStatement statement = null;
577 ResultSet resultSet = null;
578 StringBuffer sql = new StringBuffer (512);
579 sql.append("SELECT CategoryID, LastPostMemberName, ForumName, ForumDesc, ForumCreationDate, ForumModifiedDate, ForumLastPostDate, ForumOrder, ForumType, ForumFormatOption, ForumOption, ForumStatus, ForumModerationMode, ForumPassword, ForumThreadCount, ForumPostCount");
580 sql.append(" FROM " + TABLE_NAME);
581 sql.append(" WHERE ForumID = ?");
582 try {
583 connection = DBUtils.getConnection();
584 statement = connection.prepareStatement(sql.toString());
585 statement.setInt(1, forumID);
586 resultSet = statement.executeQuery();
587 if(!resultSet.next()) {
588 throw new ObjectNotFoundException("Cannot find the row in table Forum where primary key = (" + forumID + ").");
589 }
590
591 ForumBean bean = new ForumBean();
592 bean.setForumID(forumID);
594 bean.setCategoryID(resultSet.getInt("CategoryID"));
595 bean.setLastPostMemberName(resultSet.getString("LastPostMemberName"));
596 bean.setForumName(resultSet.getString("ForumName"));
597 bean.setForumDesc(resultSet.getString("ForumDesc"));
598 bean.setForumCreationDate(resultSet.getTimestamp("ForumCreationDate"));
599 bean.setForumModifiedDate(resultSet.getTimestamp("ForumModifiedDate"));
600 bean.setForumLastPostDate(resultSet.getTimestamp("ForumLastPostDate"));
601 bean.setForumOrder(resultSet.getInt("ForumOrder"));
602 bean.setForumType(resultSet.getInt("ForumType"));
603 bean.setForumFormatOption(resultSet.getInt("ForumFormatOption"));
604 bean.setForumOption(resultSet.getInt("ForumOption"));
605 bean.setForumStatus(resultSet.getInt("ForumStatus"));
606 bean.setForumModerationMode(resultSet.getInt("ForumModerationMode"));
607 bean.setForumPassword(resultSet.getString("ForumPassword"));
608 bean.setForumThreadCount(resultSet.getInt("ForumThreadCount"));
609 bean.setForumPostCount(resultSet.getInt("ForumPostCount"));
610 return bean;
611 } catch(SQLException sqle) {
612 log.error("Sql Execution Error!", sqle);
613 throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.getForum(pk).");
614 } finally {
615 DBUtils.closeResultSet(resultSet);
616 DBUtils.closeStatement(statement);
617 DBUtils.closeConnection(connection);
618 }
619 }
620
621
628 public Collection getForums()
629 throws DatabaseException {
630
631 Connection connection = null;
632 PreparedStatement statement = null;
633 ResultSet resultSet = null;
634 Collection retValue = new ArrayList ();
635 StringBuffer sql = new StringBuffer (512);
636 sql.append("SELECT ForumID, CategoryID, LastPostMemberName, ForumName, ForumDesc, ForumCreationDate, ForumModifiedDate, ForumLastPostDate, ForumOrder, ForumType, ForumFormatOption, ForumOption, ForumStatus, ForumModerationMode, ForumPassword, ForumThreadCount, ForumPostCount");
637 sql.append(" FROM " + TABLE_NAME);
638 sql.append(" ORDER BY CategoryID ASC, ForumOrder ASC "); try {
641 connection = DBUtils.getConnection();
642 statement = connection.prepareStatement(sql.toString());
643 resultSet = statement.executeQuery();
644 while (resultSet.next()) {
645 ForumBean bean = new ForumBean();
646 bean.setForumID(resultSet.getInt("ForumID"));
647 bean.setCategoryID(resultSet.getInt("CategoryID"));
648 bean.setLastPostMemberName(resultSet.getString("LastPostMemberName"));
649 bean.setForumName(resultSet.getString("ForumName"));
650 bean.setForumDesc(resultSet.getString("ForumDesc"));
651 bean.setForumCreationDate(resultSet.getTimestamp("ForumCreationDate"));
652 bean.setForumModifiedDate(resultSet.getTimestamp("ForumModifiedDate"));
653 bean.setForumLastPostDate(resultSet.getTimestamp("ForumLastPostDate"));
654 bean.setForumOrder(resultSet.getInt("ForumOrder"));
655 bean.setForumType(resultSet.getInt("ForumType"));
656 bean.setForumFormatOption(resultSet.getInt("ForumFormatOption"));
657 bean.setForumOption(resultSet.getInt("ForumOption"));
658 bean.setForumStatus(resultSet.getInt("ForumStatus"));
659 bean.setForumModerationMode(resultSet.getInt("ForumModerationMode"));
660 bean.setForumPassword(resultSet.getString("ForumPassword"));
661 bean.setForumThreadCount(resultSet.getInt("ForumThreadCount"));
662 bean.setForumPostCount(resultSet.getInt("ForumPostCount"));
663 retValue.add(bean);
664 }
665 return retValue;
666 } catch(SQLException sqle) {
667 log.error("Sql Execution Error!", sqle);
668 throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.getForums.");
669 } finally {
670 DBUtils.closeResultSet(resultSet);
671 DBUtils.closeStatement(statement);
672 DBUtils.closeConnection(connection);
673 }
674 }
675
676
683 public Collection getForums_inCategory(int categoryID)
684 throws DatabaseException {
685
686 Connection connection = null;
687 PreparedStatement statement = null;
688 ResultSet resultSet = null;
689 Collection retValue = new ArrayList ();
690 StringBuffer sql = new StringBuffer (512);
691 sql.append("SELECT ForumID, CategoryID, LastPostMemberName, ForumName, ForumDesc, ForumCreationDate, ForumModifiedDate, ForumLastPostDate, ForumOrder, ForumType, ForumFormatOption, ForumOption, ForumStatus, ForumModerationMode, ForumPassword, ForumThreadCount, ForumPostCount");
692 sql.append(" FROM " + TABLE_NAME);
693 sql.append(" WHERE CategoryID = ?");
694 sql.append(" ORDER BY CategoryID ASC, ForumOrder ASC "); try {
696 connection = DBUtils.getConnection();
697 statement = connection.prepareStatement(sql.toString());
698 statement.setInt(1, categoryID);
699 resultSet = statement.executeQuery();
700 while (resultSet.next()) {
701 ForumBean bean = new ForumBean();
702 bean.setForumID(resultSet.getInt("ForumID"));
703 bean.setCategoryID(resultSet.getInt("CategoryID"));
704 bean.setLastPostMemberName(resultSet.getString("LastPostMemberName"));
705 bean.setForumName(resultSet.getString("ForumName"));
706 bean.setForumDesc(resultSet.getString("ForumDesc"));
707 bean.setForumCreationDate(resultSet.getTimestamp("ForumCreationDate"));
708 bean.setForumModifiedDate(resultSet.getTimestamp("ForumModifiedDate"));
709 bean.setForumLastPostDate(resultSet.getTimestamp("ForumLastPostDate"));
710 bean.setForumOrder(resultSet.getInt("ForumOrder"));
711 bean.setForumType(resultSet.getInt("ForumType"));
712 bean.setForumFormatOption(resultSet.getInt("ForumFormatOption"));
713 bean.setForumOption(resultSet.getInt("ForumOption"));
714 bean.setForumStatus(resultSet.getInt("ForumStatus"));
715 bean.setForumModerationMode(resultSet.getInt("ForumModerationMode"));
716 bean.setForumPassword(resultSet.getString("ForumPassword"));
717 bean.setForumThreadCount(resultSet.getInt("ForumThreadCount"));
718 bean.setForumPostCount(resultSet.getInt("ForumPostCount"));
719 retValue.add(bean);
720 }
721 return retValue;
722 } catch(SQLException sqle) {
723 log.error("Sql Execution Error!", sqle);
724 throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.getForums_inCategory.");
725 } finally {
726 DBUtils.closeResultSet(resultSet);
727 DBUtils.closeStatement(statement);
728 DBUtils.closeConnection(connection);
729 }
730 }
731
732
735
736
739 public void decreaseForumOrder(int forumID, Timestamp forumModifiedDate)
740 throws DatabaseException, ObjectNotFoundException {
741
742 Connection connection = null;
743 PreparedStatement statement = null;
744 String sql = "UPDATE " + TABLE_NAME + " SET ForumOrder = ForumOrder - 1, ForumModifiedDate = ? WHERE ForumID = ?";
745 try {
746 connection = DBUtils.getConnection();
747 statement = connection.prepareStatement(sql);
748 statement.setTimestamp(1, forumModifiedDate);
749 statement.setInt(2, forumID);
750 if (statement.executeUpdate() != 1) {
751 throw new ObjectNotFoundException("Cannot update the ForumOrder in table Forum. Please contact Web site Administrator.");
752 }
753 setDirty(true);
756 } catch (SQLException sqle) {
757 log.error("Sql Execution Error!", sqle);
758 throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.decreaseForumOrder.");
759 } finally {
760 DBUtils.closeStatement(statement);
761 DBUtils.closeConnection(connection);
762 }
763 }
764
765
768 public void increaseForumOrder(int forumID, Timestamp forumModifiedDate)
769 throws DatabaseException, ObjectNotFoundException {
770
771 Connection connection = null;
772 PreparedStatement statement = null;
773 String sql = "UPDATE " + TABLE_NAME + " SET ForumOrder = ForumOrder + 1, ForumModifiedDate = ? WHERE ForumID = ?";
774 try {
775 connection = DBUtils.getConnection();
776 statement = connection.prepareStatement(sql);
777 statement.setTimestamp(1, forumModifiedDate);
778 statement.setInt(2, forumID);
779 if (statement.executeUpdate() != 1) {
780 throw new ObjectNotFoundException("Cannot update the ForumOrder in table Forum. Please contact Web site Administrator.");
781 }
782 setDirty(true);
785 } catch (SQLException sqle) {
786 log.error("Sql Execution Error!", sqle);
787 throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.increaseForumOrder.");
788 } finally {
789 DBUtils.closeStatement(statement);
790 DBUtils.closeConnection(connection);
791 }
792 }
793
794
801 protected static ForumBean getBean_byAlternateKey_ForumName_CategoryID(String forumName, int categoryID)
802 throws ObjectNotFoundException, DatabaseException {
803
804 Connection connection = null;
805 PreparedStatement statement = null;
806 ResultSet resultSet = null;
807 StringBuffer sql = new StringBuffer (512);
808 sql.append("SELECT ForumID, CategoryID, LastPostMemberName, ForumName, ForumDesc, ForumCreationDate, ForumModifiedDate, ForumLastPostDate, ForumOrder, ForumType, ForumFormatOption, ForumOption, ForumStatus, ForumModerationMode, ForumPassword, ForumThreadCount, ForumPostCount");
809 sql.append(" FROM " + TABLE_NAME);
810 sql.append(" WHERE ForumName = ? AND CategoryID = ?");
811 try {
812 connection = DBUtils.getConnection();
813 statement = connection.prepareStatement(sql.toString());
814 statement.setString(1, forumName);
815 statement.setInt(2, categoryID);
816 resultSet = statement.executeQuery();
817 if(!resultSet.next()) {
818 throw new ObjectNotFoundException("Cannot find the row in table Forum where alternate key [ForumName, CategoryID] = (" + forumName + ", " + categoryID + ").");
819 }
820
821 ForumBean bean = new ForumBean();
822 bean.setForumID(resultSet.getInt("ForumID"));
826 bean.setCategoryID(resultSet.getInt("CategoryID"));
827 bean.setLastPostMemberName(resultSet.getString("LastPostMemberName"));
828 bean.setForumName(resultSet.getString("ForumName"));
829 bean.setForumDesc(resultSet.getString("ForumDesc"));
830 bean.setForumCreationDate(resultSet.getTimestamp("ForumCreationDate"));
831 bean.setForumModifiedDate(resultSet.getTimestamp("ForumModifiedDate"));
832 bean.setForumLastPostDate(resultSet.getTimestamp("ForumLastPostDate"));
833 bean.setForumOrder(resultSet.getInt("ForumOrder"));
834 bean.setForumType(resultSet.getInt("ForumType"));
835 bean.setForumFormatOption(resultSet.getInt("ForumFormatOption"));
836 bean.setForumOption(resultSet.getInt("ForumOption"));
837 bean.setForumStatus(resultSet.getInt("ForumStatus"));
838 bean.setForumModerationMode(resultSet.getInt("ForumModerationMode"));
839 bean.setForumPassword(resultSet.getString("ForumPassword"));
840 bean.setForumThreadCount(resultSet.getInt("ForumThreadCount"));
841 bean.setForumPostCount(resultSet.getInt("ForumPostCount"));
842 return bean;
843 } catch(SQLException sqle) {
844 log.error("Sql Execution Error!", sqle);
845 throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.getBean_byAlternateKey_ForumName_CategoryID(ak).");
846 } finally {
847 DBUtils.closeResultSet(resultSet);
848 DBUtils.closeStatement(statement);
849 DBUtils.closeConnection(connection);
850 }
851 }
852
853 } |
Popular Tags |
Java API By Example, From Geeks To Geeks.