0001: /*
0002: * Copyright (c) JForum Team
0003: * All rights reserved.
0004: *
0005: * Redistribution and use in source and binary forms,
0006: * with or without modification, are permitted provided
0007: * that the following conditions are met:
0008: *
0009: * 1) Redistributions of source code must retain the above
0010: * copyright notice, this list of conditions and the
0011: * following disclaimer.
0012: * 2) Redistributions in binary form must reproduce the
0013: * above copyright notice, this list of conditions and
0014: * the following disclaimer in the documentation and/or
0015: * other materials provided with the distribution.
0016: * 3) Neither the name of "Rafael Steil" nor
0017: * the names of its contributors may be used to endorse
0018: * or promote products derived from this software without
0019: * specific prior written permission.
0020: *
0021: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT
0022: * HOLDERS AND CONTRIBUTORS "AS IS" AND ANY
0023: * EXPRESS OR IMPLIED WARRANTIES, INCLUDING,
0024: * BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
0025: * MERCHANTABILITY AND FITNESS FOR A PARTICULAR
0026: * PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL
0027: * THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE
0028: * FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
0029: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES
0030: * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
0031: * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA,
0032: * OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
0033: * CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER
0034: * IN CONTRACT, STRICT LIABILITY, OR TORT
0035: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN
0036: * ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF
0037: * ADVISED OF THE POSSIBILITY OF SUCH DAMAGE
0038: *
0039: * This file creation date: Apr 6, 2003 / 2:38:28 PM
0040: * The JForum Project
0041: * http://www.jforum.net
0042: */
0043: package net.jforum.dao.generic;
0044:
0045: import java.sql.PreparedStatement;
0046: import java.sql.ResultSet;
0047: import java.sql.SQLException;
0048: import java.sql.Timestamp;
0049: import java.text.SimpleDateFormat;
0050: import java.util.ArrayList;
0051: import java.util.Collection;
0052: import java.util.Date;
0053: import java.util.HashMap;
0054: import java.util.Iterator;
0055: import java.util.List;
0056: import java.util.Map;
0057:
0058: import net.jforum.JForumExecutionContext;
0059: import net.jforum.SessionFacade;
0060: import net.jforum.dao.DataAccessDriver;
0061: import net.jforum.dao.ForumDAO;
0062: import net.jforum.dao.PollDAO;
0063: import net.jforum.dao.PostDAO;
0064: import net.jforum.dao.TopicDAO;
0065: import net.jforum.entities.KarmaStatus;
0066: import net.jforum.entities.Topic;
0067: import net.jforum.entities.User;
0068: import net.jforum.exceptions.DatabaseException;
0069: import net.jforum.repository.ForumRepository;
0070: import net.jforum.search.SearchArgs;
0071: import net.jforum.search.SearchResult;
0072: import net.jforum.util.DbUtils;
0073: import net.jforum.util.preferences.ConfigKeys;
0074: import net.jforum.util.preferences.SystemGlobals;
0075:
0076: /**
0077: * @author Rafael Steil
0078: * @version $Id: GenericTopicDAO.java,v 1.33 2007/09/12 14:43:15 rafaelsteil Exp $
0079: */
0080: public class GenericTopicDAO extends AutoKeys implements TopicDAO {
0081: /**
0082: * @see net.jforum.dao.TopicDAO#findTopicsByDateRange(net.jforum.search.SearchArgs)
0083: */
0084: public SearchResult findTopicsByDateRange(SearchArgs args) {
0085: SearchResult result = null;
0086:
0087: PreparedStatement p = null;
0088: ResultSet rs = null;
0089:
0090: try {
0091: p = JForumExecutionContext
0092: .getConnection()
0093: .prepareStatement(
0094: SystemGlobals
0095: .getSql("TopicModel.findTopicsByDateRange"));
0096:
0097: p.setTimestamp(1, new Timestamp(args.getFromDate()
0098: .getTime()));
0099: p
0100: .setTimestamp(2, new Timestamp(args.getToDate()
0101: .getTime()));
0102:
0103: rs = p.executeQuery();
0104: List l = new ArrayList();
0105:
0106: int counter = 0;
0107:
0108: while (rs.next()) {
0109: if (counter >= args.startFrom()
0110: && counter < args.startFrom()
0111: + args.fetchCount()) {
0112: l.add(new Integer(rs.getInt(1)));
0113: }
0114:
0115: counter++;
0116: }
0117:
0118: result = new SearchResult(this .newMessages(l), counter);
0119: } catch (Exception e) {
0120: throw new DatabaseException(e);
0121: } finally {
0122: DbUtils.close(rs, p);
0123: }
0124:
0125: return result;
0126: }
0127:
0128: /**
0129: * @see net.jforum.dao.TopicDAO#fixFirstLastPostId(int)
0130: */
0131: public void fixFirstLastPostId(int topicId) {
0132: PreparedStatement p = null;
0133: ResultSet rs = null;
0134: try {
0135: p = JForumExecutionContext
0136: .getConnection()
0137: .prepareStatement(
0138: SystemGlobals
0139: .getSql("TopicModel.getFirstLastPostId"));
0140: p.setInt(1, topicId);
0141:
0142: rs = p.executeQuery();
0143: if (rs.next()) {
0144: int first = rs.getInt("first_post_id");
0145: int last = rs.getInt("last_post_id");
0146:
0147: rs.close();
0148: rs = null;
0149: p.close();
0150: p = null;
0151:
0152: p = JForumExecutionContext
0153: .getConnection()
0154: .prepareStatement(
0155: SystemGlobals
0156: .getSql("TopicModel.fixFirstLastPostId"));
0157: p.setInt(1, first);
0158: p.setInt(2, last);
0159: p.setInt(3, topicId);
0160: p.executeUpdate();
0161: }
0162: } catch (SQLException e) {
0163: throw new DatabaseException(e);
0164: } finally {
0165: DbUtils.close(rs, p);
0166: }
0167: }
0168:
0169: /**
0170: * @see net.jforum.dao.TopicDAO#selectById(int)
0171: */
0172: public Topic selectById(int topicId) {
0173: PreparedStatement p = null;
0174: try {
0175: p = JForumExecutionContext.getConnection()
0176: .prepareStatement(
0177: SystemGlobals
0178: .getSql("TopicModel.selectById"));
0179: p.setInt(1, topicId);
0180:
0181: Topic t = new Topic();
0182: List l = this .fillTopicsData(p);
0183: p = null;
0184:
0185: if (l.size() > 0) {
0186: t = (Topic) l.get(0);
0187: }
0188:
0189: return t;
0190: } catch (SQLException e) {
0191: throw new DatabaseException(e);
0192: } finally {
0193: DbUtils.close(p);
0194: }
0195: }
0196:
0197: /**
0198: * @see net.jforum.dao.TopicDAO#selectRaw(int)
0199: */
0200: public Topic selectRaw(int topicId) {
0201: PreparedStatement p = null;
0202: ResultSet rs = null;
0203: try {
0204: p = JForumExecutionContext.getConnection()
0205: .prepareStatement(
0206: SystemGlobals
0207: .getSql("TopicModel.selectRaw"));
0208: p.setInt(1, topicId);
0209:
0210: Topic t = new Topic();
0211: rs = p.executeQuery();
0212: if (rs.next()) {
0213: t = this .getBaseTopicData(rs);
0214: }
0215:
0216: return t;
0217: } catch (SQLException e) {
0218: throw new DatabaseException(e);
0219: } finally {
0220: DbUtils.close(rs, p);
0221: }
0222: }
0223:
0224: /**
0225: * @see net.jforum.dao.TopicDAO#delete(net.jforum.entities.Topic)
0226: */
0227: public void delete(Topic topic, boolean fromModeration) {
0228: List l = new ArrayList();
0229: l.add(topic);
0230: this .deleteTopics(l, fromModeration);
0231: }
0232:
0233: public void deleteTopics(List topics, boolean fromModeration) {
0234: // Topic
0235: PreparedStatement p = null;
0236: try {
0237: p = JForumExecutionContext.getConnection()
0238: .prepareStatement(
0239: SystemGlobals.getSql("TopicModel.delete"));
0240:
0241: ForumDAO forumDao = DataAccessDriver.getInstance()
0242: .newForumDAO();
0243:
0244: PostDAO postDao = DataAccessDriver.getInstance()
0245: .newPostDAO();
0246: PollDAO pollDao = DataAccessDriver.getInstance()
0247: .newPollDAO();
0248:
0249: for (Iterator iter = topics.iterator(); iter.hasNext();) {
0250: Topic topic = (Topic) iter.next();
0251:
0252: // Remove watches
0253: this .removeSubscriptionByTopic(topic.getId());
0254:
0255: // Remove the messages
0256: postDao.deleteByTopic(topic.getId());
0257:
0258: // Remove the poll
0259: pollDao.deleteByTopicId(topic.getId());
0260:
0261: // Delete the topic itself
0262: p.setInt(1, topic.getId());
0263: p.executeUpdate();
0264:
0265: if (!fromModeration) {
0266: forumDao
0267: .decrementTotalTopics(topic.getForumId(), 1);
0268: }
0269: }
0270: } catch (SQLException e) {
0271: throw new DatabaseException(e);
0272: } finally {
0273: DbUtils.close(p);
0274: }
0275:
0276: }
0277:
0278: /**
0279: * @see net.jforum.dao.TopicDAO#deleteByForum(int)
0280: */
0281: public void deleteByForum(int forumId) {
0282: PreparedStatement p = null;
0283: ResultSet rs = null;
0284:
0285: try {
0286: p = JForumExecutionContext
0287: .getConnection()
0288: .prepareStatement(
0289: SystemGlobals
0290: .getSql("TopicModel.deleteByForum"));
0291: p.setInt(1, forumId);
0292:
0293: rs = p.executeQuery();
0294: List topics = new ArrayList();
0295:
0296: while (rs.next()) {
0297: Topic t = new Topic();
0298: t.setId(rs.getInt("topic_id"));
0299: t.setForumId(forumId);
0300:
0301: topics.add(t);
0302: }
0303:
0304: this .deleteTopics(topics, false);
0305: } catch (SQLException e) {
0306: throw new DatabaseException(e);
0307: } finally {
0308: DbUtils.close(rs, p);
0309: }
0310: }
0311:
0312: /**
0313: * @see net.jforum.dao.TopicDAO#update(net.jforum.entities.Topic)
0314: */
0315: public void update(Topic topic) {
0316: PreparedStatement p = null;
0317: try {
0318: p = JForumExecutionContext.getConnection()
0319: .prepareStatement(
0320: SystemGlobals.getSql("TopicModel.update"));
0321:
0322: p.setString(1, topic.getTitle());
0323: p.setInt(2, topic.getLastPostId());
0324: p.setInt(3, topic.getFirstPostId());
0325: p.setInt(4, topic.getType());
0326: p.setInt(5, topic.isModerated() ? 1 : 0);
0327: p.setInt(6, topic.getVoteId());
0328: p.setInt(7, topic.getId());
0329: p.executeUpdate();
0330: } catch (SQLException e) {
0331: throw new DatabaseException(e);
0332: } finally {
0333: DbUtils.close(p);
0334: }
0335:
0336: }
0337:
0338: /**
0339: * @see net.jforum.dao.TopicDAO#addNew(net.jforum.entities.Topic)
0340: */
0341: public int addNew(Topic topic) {
0342: PreparedStatement p = null;
0343:
0344: try {
0345: p = this .getStatementForAutoKeys("TopicModel.addNew");
0346:
0347: p.setInt(1, topic.getForumId());
0348: p.setString(2, topic.getTitle());
0349: p.setInt(3, topic.getPostedBy().getId());
0350: p.setTimestamp(4, new Timestamp(topic.getTime().getTime()));
0351: p.setInt(5, topic.getFirstPostId());
0352: p.setInt(6, topic.getLastPostId());
0353: p.setInt(7, topic.getType());
0354: p.setInt(8, topic.isModerated() ? 1 : 0);
0355:
0356: this .setAutoGeneratedKeysQuery(SystemGlobals
0357: .getSql("TopicModel.lastGeneratedTopicId"));
0358:
0359: int topicId = this .executeAutoKeysQuery(p);
0360:
0361: topic.setId(topicId);
0362:
0363: return topicId;
0364: } catch (SQLException e) {
0365: throw new DatabaseException(e);
0366: } finally {
0367: DbUtils.close(p);
0368: }
0369: }
0370:
0371: /**
0372: * @see net.jforum.dao.TopicDAO#incrementTotalViews(int)
0373: */
0374: public void incrementTotalViews(int topicId) {
0375: PreparedStatement p = null;
0376: try {
0377: p = JForumExecutionContext
0378: .getConnection()
0379: .prepareStatement(
0380: SystemGlobals
0381: .getSql("TopicModel.incrementTotalViews"));
0382: p.setInt(1, topicId);
0383: p.executeUpdate();
0384: } catch (SQLException e) {
0385: throw new DatabaseException(e);
0386: } finally {
0387: DbUtils.close(p);
0388: }
0389: }
0390:
0391: /**
0392: * @see net.jforum.dao.TopicDAO#incrementTotalReplies(int)
0393: */
0394: public void incrementTotalReplies(int topicId) {
0395: PreparedStatement p = null;
0396: try {
0397: p = JForumExecutionContext
0398: .getConnection()
0399: .prepareStatement(
0400: SystemGlobals
0401: .getSql("TopicModel.incrementTotalReplies"));
0402: p.setInt(1, topicId);
0403: p.executeUpdate();
0404: } catch (SQLException e) {
0405: throw new DatabaseException(e);
0406: } finally {
0407: DbUtils.close(p);
0408: }
0409: }
0410:
0411: /**
0412: * @see net.jforum.dao.TopicDAO#decrementTotalReplies(int)
0413: */
0414: public void decrementTotalReplies(int topicId) {
0415: PreparedStatement p = null;
0416: try {
0417: p = JForumExecutionContext
0418: .getConnection()
0419: .prepareStatement(
0420: SystemGlobals
0421: .getSql("TopicModel.decrementTotalReplies"));
0422: p.setInt(1, topicId);
0423: p.executeUpdate();
0424: } catch (SQLException e) {
0425: throw new DatabaseException(e);
0426: } finally {
0427: DbUtils.close(p);
0428: }
0429: }
0430:
0431: /**
0432: * @see net.jforum.dao.TopicDAO#setLastPostId(int, int)
0433: */
0434: public void setLastPostId(int topicId, int postId) {
0435: PreparedStatement p = null;
0436: try {
0437: p = JForumExecutionContext
0438: .getConnection()
0439: .prepareStatement(
0440: SystemGlobals
0441: .getSql("TopicModel.setLastPostId"));
0442: p.setInt(1, postId);
0443: p.setInt(2, topicId);
0444: p.executeUpdate();
0445: } catch (SQLException e) {
0446: throw new DatabaseException(e);
0447: } finally {
0448: DbUtils.close(p);
0449: }
0450: }
0451:
0452: /**
0453: * @see net.jforum.dao.TopicDAO#selectAllByForum(int)
0454: */
0455: public List selectAllByForum(int forumId) {
0456: return this .selectAllByForumByLimit(forumId, 0,
0457: Integer.MAX_VALUE);
0458: }
0459:
0460: /**
0461: * @see net.jforum.dao.TopicDAO#selectAllByForumByLimit(int, int, int)
0462: */
0463: public List selectAllByForumByLimit(int forumId, int startFrom,
0464: int count) {
0465: String sql = SystemGlobals
0466: .getSql("TopicModel.selectAllByForumByLimit");
0467:
0468: PreparedStatement p = null;
0469:
0470: try {
0471: p = JForumExecutionContext.getConnection()
0472: .prepareStatement(sql);
0473: p.setInt(1, forumId);
0474: p.setInt(2, forumId);
0475: p.setInt(3, startFrom);
0476: p.setInt(4, count);
0477:
0478: return this .fillTopicsData(p);
0479: } catch (SQLException e) {
0480: throw new DatabaseException(e);
0481: } finally {
0482: DbUtils.close(p);
0483: }
0484: }
0485:
0486: /**
0487: * @see net.jforum.dao.TopicDAO#selectByUserByLimit(int, int, int)
0488: */
0489: public List selectByUserByLimit(int userId, int startFrom, int count) {
0490: PreparedStatement p = null;
0491: try {
0492: p = JForumExecutionContext
0493: .getConnection()
0494: .prepareStatement(
0495: SystemGlobals
0496: .getSql(
0497: "TopicModel.selectByUserByLimit")
0498: .replaceAll(
0499: ":fids:",
0500: ForumRepository
0501: .getListAllowedForums()));
0502:
0503: p.setInt(1, userId);
0504: p.setInt(2, startFrom);
0505: p.setInt(3, count);
0506:
0507: List list = this .fillTopicsData(p);
0508: p = null;
0509: return list;
0510: } catch (SQLException e) {
0511: throw new DatabaseException(e);
0512: } finally {
0513: DbUtils.close(p);
0514: }
0515: }
0516:
0517: /**
0518: * @see net.jforum.dao.TopicDAO#countUserTopics(int)
0519: */
0520: public int countUserTopics(int userId) {
0521: int total = 0;
0522:
0523: PreparedStatement p = null;
0524: ResultSet rs = null;
0525: try {
0526: p = JForumExecutionContext
0527: .getConnection()
0528: .prepareStatement(
0529: SystemGlobals
0530: .getSql(
0531: "TopicModel.countUserTopics")
0532: .replaceAll(
0533: ":fids:",
0534: ForumRepository
0535: .getListAllowedForums()));
0536: p.setInt(1, userId);
0537:
0538: rs = p.executeQuery();
0539:
0540: if (rs.next()) {
0541: total = rs.getInt(1);
0542: }
0543:
0544: return total;
0545: } catch (SQLException e) {
0546: throw new DatabaseException(e);
0547: } finally {
0548: DbUtils.close(rs, p);
0549: }
0550: }
0551:
0552: protected Topic getBaseTopicData(ResultSet rs) throws SQLException {
0553: Topic t = new Topic();
0554:
0555: t.setTitle(rs.getString("topic_title"));
0556: t.setId(rs.getInt("topic_id"));
0557: t.setTime(new Date(rs.getTimestamp("topic_time").getTime()));
0558: t.setStatus(rs.getInt("topic_status"));
0559: t.setTotalViews(rs.getInt("topic_views"));
0560: t.setTotalReplies(rs.getInt("topic_replies"));
0561: t.setFirstPostId(rs.getInt("topic_first_post_id"));
0562: t.setLastPostId(rs.getInt("topic_last_post_id"));
0563: t.setType(rs.getInt("topic_type"));
0564: t.setForumId(rs.getInt("forum_id"));
0565: t.setModerated(rs.getInt("moderated") == 1);
0566: t.setVoteId(rs.getInt("topic_vote_id"));
0567: t.setMovedId(rs.getInt("topic_moved_id"));
0568:
0569: User user = new User();
0570: user.setId(rs.getInt("user_id"));
0571:
0572: t.setPostedBy(user);
0573:
0574: return t;
0575: }
0576:
0577: /**
0578: * @see net.jforum.dao.TopicDAO#autoSetLastPostId(int)
0579: */
0580: public int getMaxPostId(int topicId) {
0581: int id = -1;
0582:
0583: PreparedStatement p = null;
0584: ResultSet rs = null;
0585: try {
0586: p = JForumExecutionContext.getConnection()
0587: .prepareStatement(
0588: SystemGlobals
0589: .getSql("TopicModel.getMaxPostId"));
0590: p.setInt(1, topicId);
0591:
0592: rs = p.executeQuery();
0593: if (rs.next()) {
0594: id = rs.getInt("post_id");
0595: }
0596:
0597: return id;
0598: } catch (SQLException e) {
0599: throw new DatabaseException(e);
0600: } finally {
0601: DbUtils.close(rs, p);
0602: }
0603: }
0604:
0605: /**
0606: * @see net.jforum.dao.TopicDAO#getTotalPosts(int)
0607: */
0608: public int getTotalPosts(int topicId) {
0609: int total = 0;
0610:
0611: PreparedStatement p = null;
0612: ResultSet rs = null;
0613: try {
0614: p = JForumExecutionContext
0615: .getConnection()
0616: .prepareStatement(
0617: SystemGlobals
0618: .getSql("TopicModel.getTotalPosts"));
0619: p.setInt(1, topicId);
0620:
0621: rs = p.executeQuery();
0622: if (rs.next()) {
0623: total = rs.getInt("total");
0624: }
0625:
0626: return total;
0627: } catch (SQLException e) {
0628: throw new DatabaseException(e);
0629: } finally {
0630: DbUtils.close(rs, p);
0631: }
0632: }
0633:
0634: /**
0635: * @see net.jforum.dao.TopicDAO#notifyUsers(net.jforum.entities.Topic)
0636: */
0637: public List notifyUsers(Topic topic) {
0638: int posterId = SessionFacade.getUserSession().getUserId();
0639: int anonUser = SystemGlobals
0640: .getIntValue(ConfigKeys.ANONYMOUS_USER_ID);
0641:
0642: PreparedStatement stmt = null;
0643: ResultSet rs = null;
0644:
0645: try {
0646: stmt = JForumExecutionContext.getConnection()
0647: .prepareStatement(
0648: SystemGlobals
0649: .getSql("TopicModel.notifyUsers"));
0650:
0651: stmt.setInt(1, topic.getId());
0652: stmt.setInt(2, posterId); // don't notify the poster
0653: stmt.setInt(3, anonUser); // don't notify the anonimous user
0654:
0655: rs = stmt.executeQuery();
0656:
0657: List users = new ArrayList();
0658:
0659: while (rs.next()) {
0660: User user = new User();
0661:
0662: user.setId(rs.getInt("user_id"));
0663: user.setEmail(rs.getString("user_email"));
0664: user.setUsername(rs.getString("username"));
0665: user.setLang(rs.getString("user_lang"));
0666: user.setNotifyText(rs.getInt("user_notify_text") == 1);
0667:
0668: users.add(user);
0669: }
0670:
0671: rs.close();
0672: stmt.close();
0673:
0674: // Set read status to false
0675: stmt = JForumExecutionContext
0676: .getConnection()
0677: .prepareStatement(
0678: SystemGlobals
0679: .getSql("TopicModel.markAllAsUnread"));
0680: stmt.setInt(1, topic.getId());
0681: stmt.setInt(2, posterId); // don't notify the poster
0682: stmt.setInt(3, anonUser); // don't notify the anonimous user
0683:
0684: stmt.executeUpdate();
0685:
0686: return users;
0687: } catch (SQLException e) {
0688: throw new DatabaseException(e);
0689: } finally {
0690: DbUtils.close(rs, stmt);
0691: }
0692: }
0693:
0694: /**
0695: * @see net.jforum.dao.TopicDAO#subscribeUsers(int, java.util.List)
0696: */
0697: public void subscribeUsers(int topicId, List users) {
0698: PreparedStatement p = null;
0699:
0700: try {
0701: p = JForumExecutionContext
0702: .getConnection()
0703: .prepareStatement(
0704: SystemGlobals
0705: .getSql("TopicModel.subscribeUser"));
0706:
0707: p.setInt(1, topicId);
0708:
0709: for (Iterator iter = users.iterator(); iter.hasNext();) {
0710: int userId = ((User) iter.next()).getId();
0711:
0712: p.setInt(2, userId);
0713: p.executeUpdate();
0714: }
0715: } catch (SQLException e) {
0716: throw new DatabaseException(e);
0717: } finally {
0718: DbUtils.close(p);
0719: }
0720: }
0721:
0722: /**
0723: * @see net.jforum.dao.TopicDAO#subscribeUser(int, int)
0724: */
0725: public void subscribeUser(int topicId, int userId) {
0726: User user = new User();
0727: user.setId(userId);
0728:
0729: List l = new ArrayList();
0730: l.add(user);
0731:
0732: this .subscribeUsers(topicId, l);
0733: }
0734:
0735: /**
0736: * @see net.jforum.dao.TopicDAO#isUserSubscribing(int, int)
0737: */
0738: public boolean isUserSubscribed(int topicId, int userId) {
0739: PreparedStatement p = null;
0740: ResultSet rs = null;
0741:
0742: try {
0743: p = JForumExecutionContext
0744: .getConnection()
0745: .prepareStatement(
0746: SystemGlobals
0747: .getSql("TopicModel.isUserSubscribed"));
0748:
0749: p.setInt(1, topicId);
0750: p.setInt(2, userId);
0751:
0752: rs = p.executeQuery();
0753:
0754: return rs.next() && rs.getInt(1) > 0;
0755: } catch (SQLException e) {
0756: throw new DatabaseException(e);
0757: } finally {
0758: DbUtils.close(rs, p);
0759: }
0760: }
0761:
0762: /**
0763: * @see net.jforum.dao.TopicDAO#removeSubscription(int, int)
0764: */
0765: public void removeSubscription(int topicId, int userId) {
0766: PreparedStatement p = null;
0767: try {
0768: p = JForumExecutionContext
0769: .getConnection()
0770: .prepareStatement(
0771: SystemGlobals
0772: .getSql("TopicModel.removeSubscription"));
0773: p.setInt(1, topicId);
0774: p.setInt(2, userId);
0775:
0776: p.executeUpdate();
0777: } catch (SQLException e) {
0778: throw new DatabaseException(e);
0779: } finally {
0780: DbUtils.close(p);
0781: }
0782: }
0783:
0784: /**
0785: * @see net.jforum.dao.TopicDAO#removeSubscriptionByTopic(int)
0786: */
0787: public void removeSubscriptionByTopic(int topicId) {
0788: PreparedStatement p = null;
0789: try {
0790: p = JForumExecutionContext
0791: .getConnection()
0792: .prepareStatement(
0793: SystemGlobals
0794: .getSql("TopicModel.removeSubscriptionByTopic"));
0795: p.setInt(1, topicId);
0796:
0797: p.executeUpdate();
0798: } catch (SQLException e) {
0799: throw new DatabaseException(e);
0800: } finally {
0801: DbUtils.close(p);
0802: }
0803: }
0804:
0805: /**
0806: * @see net.jforum.dao.TopicDAO#updateReadStatus(int, int, boolean)
0807: */
0808: public void updateReadStatus(int topicId, int userId, boolean read) {
0809: if (this .isUserSubscribed(topicId, userId)) {
0810: PreparedStatement p = null;
0811: try {
0812: p = JForumExecutionContext
0813: .getConnection()
0814: .prepareStatement(
0815: SystemGlobals
0816: .getSql("TopicModel.updateReadStatus"));
0817: p.setInt(1, read ? 1 : 0);
0818: p.setInt(2, topicId);
0819: p.setInt(3, userId);
0820:
0821: p.executeUpdate();
0822: } catch (SQLException e) {
0823: throw new DatabaseException(e);
0824: } finally {
0825: DbUtils.close(p);
0826: }
0827: }
0828: }
0829:
0830: /**
0831: * @see net.jforum.dao.TopicDAO#lockUnlock(int[], int)
0832: */
0833: public void lockUnlock(int[] topicId, int status) {
0834: PreparedStatement p = null;
0835: try {
0836: p = JForumExecutionContext.getConnection()
0837: .prepareStatement(
0838: SystemGlobals
0839: .getSql("TopicModel.lockUnlock"));
0840: p.setInt(1, status);
0841:
0842: for (int i = 0; i < topicId.length; i++) {
0843: p.setInt(2, topicId[i]);
0844: p.executeUpdate();
0845: }
0846: } catch (SQLException e) {
0847: throw new DatabaseException(e);
0848: } finally {
0849: DbUtils.close(p);
0850: }
0851: }
0852:
0853: private List newMessages(List topicIds) {
0854: if (topicIds.size() == 0) {
0855: return new ArrayList();
0856: }
0857:
0858: PreparedStatement p = null;
0859:
0860: try {
0861: String sql = SystemGlobals
0862: .getSql("TopicModel.selectForNewMessages");
0863:
0864: StringBuffer sb = new StringBuffer();
0865:
0866: for (Iterator iter = topicIds.iterator(); iter.hasNext();) {
0867: sb.append(iter.next()).append(',');
0868: }
0869:
0870: sb.append("-1");
0871:
0872: sql = sql.replaceAll(":topicIds:", sb.toString());
0873:
0874: p = JForumExecutionContext.getConnection()
0875: .prepareStatement(sql);
0876:
0877: return this .fillTopicsData(p);
0878: } catch (SQLException e) {
0879: throw new DatabaseException(e);
0880: } finally {
0881: DbUtils.close(p);
0882: }
0883: }
0884:
0885: /**
0886: * Fills all topic data. The method will try to get all fields from the topics table, as well
0887: * information about the user who made the first and the last post in the topic. <br>
0888: * <b>The method <i>will</i> close the <i>PreparedStatement</i></b>
0889: *
0890: * @param p the PreparedStatement to execute
0891: * @return A list with all topics found
0892: * @throws SQLException
0893: */
0894: public List fillTopicsData(PreparedStatement p) {
0895: List l = new ArrayList();
0896: ResultSet rs = null;
0897:
0898: try {
0899: rs = p.executeQuery();
0900:
0901: SimpleDateFormat df = new SimpleDateFormat(SystemGlobals
0902: .getValue(ConfigKeys.DATE_TIME_FORMAT));
0903:
0904: StringBuffer sbFirst = new StringBuffer(128);
0905: StringBuffer sbLast = new StringBuffer(128);
0906:
0907: while (rs.next()) {
0908: Topic t = this .getBaseTopicData(rs);
0909:
0910: // Posted by
0911: User u = new User();
0912: u.setId(rs.getInt("user_id"));
0913: t.setPostedBy(u);
0914:
0915: // Last post by
0916: u = new User();
0917: u.setId(rs.getInt("last_user_id"));
0918: t.setLastPostBy(u);
0919:
0920: t.setHasAttach(rs.getInt("attach") > 0);
0921: t.setFirstPostTime(df.format(rs
0922: .getTimestamp("topic_time")));
0923: t.setLastPostTime(df.format(rs
0924: .getTimestamp("post_time")));
0925: t.setLastPostDate(new Date(rs.getTimestamp("post_time")
0926: .getTime()));
0927:
0928: l.add(t);
0929:
0930: sbFirst.append(rs.getInt("user_id")).append(',');
0931: sbLast.append(rs.getInt("last_user_id")).append(',');
0932: }
0933:
0934: rs.close();
0935: rs = null;
0936: p.close();
0937: p = null;
0938:
0939: // Users
0940: if (sbFirst.length() > 0) {
0941: sbLast.delete(sbLast.length() - 1, sbLast.length());
0942:
0943: String sql = SystemGlobals
0944: .getSql("TopicModel.getUserInformation");
0945: sql = sql.replaceAll("#ID#", sbFirst.toString()
0946: + sbLast.toString());
0947:
0948: Map users = new HashMap();
0949:
0950: p = JForumExecutionContext.getConnection()
0951: .prepareStatement(sql);
0952: rs = p.executeQuery();
0953:
0954: while (rs.next()) {
0955: users.put(new Integer(rs.getInt("user_id")), rs
0956: .getString("username"));
0957: }
0958:
0959: rs.close();
0960: rs = null;
0961: p.close();
0962: p = null;
0963:
0964: for (Iterator iter = l.iterator(); iter.hasNext();) {
0965: Topic t = (Topic) iter.next();
0966: t.getPostedBy().setUsername(
0967: (String) users.get(new Integer(t
0968: .getPostedBy().getId())));
0969: t.getLastPostBy().setUsername(
0970: (String) users.get(new Integer(t
0971: .getLastPostBy().getId())));
0972: }
0973: }
0974:
0975: return l;
0976: } catch (SQLException e) {
0977: throw new DatabaseException(e);
0978: } finally {
0979: DbUtils.close(rs, p);
0980: }
0981: }
0982:
0983: /**
0984: * @see net.jforum.dao.TopicDAO#selectRecentTopics(int)
0985: */
0986: public List selectRecentTopics(int limit) {
0987: PreparedStatement p = null;
0988: try {
0989: p = JForumExecutionContext
0990: .getConnection()
0991: .prepareStatement(
0992: SystemGlobals
0993: .getSql("TopicModel.selectRecentTopicsByLimit"));
0994: p.setInt(1, limit);
0995:
0996: List list = this .fillTopicsData(p);
0997: return list;
0998: } catch (SQLException e) {
0999: throw new DatabaseException(e);
1000: } finally {
1001: DbUtils.close(p);
1002: }
1003: }
1004:
1005: /**
1006: * @see net.jforum.dao.TopicDAO#selectHottestTopics(int)
1007: */
1008: public List selectHottestTopics(int limit) {
1009: PreparedStatement p = null;
1010: try {
1011: p = JForumExecutionContext
1012: .getConnection()
1013: .prepareStatement(
1014: SystemGlobals
1015: .getSql("TopicModel.selectHottestTopicsByLimit"));
1016: p.setInt(1, limit);
1017:
1018: List list = this .fillTopicsData(p);
1019: p = null;
1020: return list;
1021: } catch (SQLException e) {
1022: throw new DatabaseException(e);
1023: } finally {
1024: DbUtils.close(p);
1025: }
1026: }
1027:
1028: /**
1029: * @see net.jforum.dao.TopicDAO#setFirstPostId(int, int)
1030: */
1031: public void setFirstPostId(int topicId, int postId) {
1032: PreparedStatement p = null;
1033: try {
1034: p = JForumExecutionContext
1035: .getConnection()
1036: .prepareStatement(
1037: SystemGlobals
1038: .getSql("TopicModel.setFirstPostId"));
1039: p.setInt(1, postId);
1040: p.setInt(2, topicId);
1041: p.executeUpdate();
1042: } catch (SQLException e) {
1043: throw new DatabaseException(e);
1044: } finally {
1045: DbUtils.close(p);
1046: }
1047: }
1048:
1049: /**
1050: * @see net.jforum.dao.TopicDAO#getMinPostId(int)
1051: */
1052: public int getMinPostId(int topicId) {
1053: int id = -1;
1054:
1055: PreparedStatement p = null;
1056: try {
1057: p = JForumExecutionContext.getConnection()
1058: .prepareStatement(
1059: SystemGlobals
1060: .getSql("TopicModel.getMinPostId"));
1061: p.setInt(1, topicId);
1062:
1063: ResultSet rs = p.executeQuery();
1064: if (rs.next()) {
1065: id = rs.getInt("post_id");
1066: }
1067:
1068: return id;
1069: } catch (SQLException e) {
1070: throw new DatabaseException(e);
1071: } finally {
1072: DbUtils.close(p);
1073: }
1074: }
1075:
1076: /**
1077: * @see net.jforum.dao.TopicDAO#setModerationStatus(int, boolean)
1078: */
1079: public void setModerationStatus(int forumId, boolean status) {
1080: PreparedStatement p = null;
1081: try {
1082: p = JForumExecutionContext
1083: .getConnection()
1084: .prepareStatement(
1085: SystemGlobals
1086: .getSql("TopicModel.setModerationStatus"));
1087: p.setInt(1, status ? 1 : 0);
1088: p.setInt(2, forumId);
1089: p.executeUpdate();
1090: } catch (SQLException e) {
1091: throw new DatabaseException(e);
1092: } finally {
1093: DbUtils.close(p);
1094: }
1095: }
1096:
1097: /**
1098: * @see net.jforum.dao.TopicDAO#setModerationStatusByTopic(int, boolean)
1099: */
1100: public void setModerationStatusByTopic(int topicId, boolean status) {
1101: PreparedStatement p = null;
1102: try {
1103: p = JForumExecutionContext
1104: .getConnection()
1105: .prepareStatement(
1106: SystemGlobals
1107: .getSql("TopicModel.setModerationStatusByTopic"));
1108: p.setInt(1, status ? 1 : 0);
1109: p.setInt(2, topicId);
1110: p.executeUpdate();
1111: } catch (SQLException e) {
1112: throw new DatabaseException(e);
1113: } finally {
1114: DbUtils.close(p);
1115: }
1116: }
1117:
1118: /**
1119: * @see net.jforum.dao.TopicDAO#selectTopicTitlesByIds(java.util.Collection)
1120: */
1121: public List selectTopicTitlesByIds(Collection idList) {
1122: List l = new ArrayList();
1123: String sql = SystemGlobals
1124: .getSql("TopicModel.selectTopicTitlesByIds");
1125:
1126: StringBuffer sb = new StringBuffer(idList.size() * 2);
1127: for (Iterator iter = idList.iterator(); iter.hasNext();) {
1128: sb.append(iter.next()).append(",");
1129: }
1130:
1131: int len = sb.length();
1132: sql = sql.replaceAll(":ids:", len > 0 ? sb.toString()
1133: .substring(0, len - 1) : "0");
1134: PreparedStatement p = null;
1135: ResultSet rs = null;
1136: try {
1137: p = JForumExecutionContext.getConnection()
1138: .prepareStatement(sql);
1139:
1140: rs = p.executeQuery();
1141: while (rs.next()) {
1142: Map m = new HashMap();
1143: m.put("id", new Integer(rs.getInt("topic_id")));
1144: m.put("title", rs.getString("topic_title"));
1145:
1146: l.add(m);
1147: }
1148: return l;
1149: } catch (SQLException e) {
1150: throw new DatabaseException(e);
1151: } finally {
1152: DbUtils.close(rs, p);
1153: }
1154: }
1155:
1156: /**
1157: * @see net.jforum.model.UserModel#topicPosters(int)
1158: */
1159: public Map topicPosters(int topicId) {
1160: Map m = new HashMap();
1161:
1162: PreparedStatement p = null;
1163: ResultSet rs = null;
1164:
1165: try {
1166: StringBuffer sql = new StringBuffer(SystemGlobals
1167: .getSql("TopicModel.topicPosters"));
1168:
1169: p = JForumExecutionContext
1170: .getConnection()
1171: .prepareStatement(
1172: SystemGlobals
1173: .getSql("TopicModel.distinctPosters"));
1174: p.setInt(1, topicId);
1175:
1176: rs = p.executeQuery();
1177:
1178: StringBuffer sb = new StringBuffer();
1179:
1180: while (rs.next()) {
1181: sb.append(rs.getInt("user_id")).append(',');
1182: }
1183:
1184: rs.close();
1185: p.close();
1186:
1187: int index = sql.indexOf(":ids:");
1188: if (index > -1) {
1189: sql.replace(index, index + 5, sb.substring(0, sb
1190: .length() - 1));
1191: }
1192:
1193: p = JForumExecutionContext.getConnection()
1194: .prepareStatement(sql.toString());
1195: rs = p.executeQuery();
1196:
1197: while (rs.next()) {
1198: User u = new User();
1199:
1200: u.setId(rs.getInt("user_id"));
1201: u.setUsername(rs.getString("username"));
1202: u.setKarma(new KarmaStatus(u.getId(), rs
1203: .getDouble("user_karma")));
1204: u.setAvatar(rs.getString("user_avatar"));
1205: u.setAvatarEnabled(rs.getInt("user_allowavatar") == 1);
1206: u.setRegistrationDate(new Date(rs.getTimestamp(
1207: "user_regdate").getTime()));
1208: u.setTotalPosts(rs.getInt("user_posts"));
1209: u.setFrom(rs.getString("user_from"));
1210: u.setEmail(rs.getString("user_email"));
1211: u.setRankId(rs.getInt("rank_id"));
1212: u.setViewEmailEnabled(rs.getInt("user_viewemail") == 1);
1213: u.setIcq(rs.getString("user_icq"));
1214: u
1215: .setAttachSignatureEnabled(rs
1216: .getInt("user_attachsig") == 1);
1217: u.setMsnm(rs.getString("user_msnm"));
1218: u.setYim(rs.getString("user_yim"));
1219: u.setWebSite(rs.getString("user_website"));
1220: u.setAim(rs.getString("user_aim"));
1221: u.setSignature(rs.getString("user_sig"));
1222:
1223: m.put(new Integer(u.getId()), u);
1224: }
1225:
1226: return m;
1227: } catch (SQLException e) {
1228: throw new DatabaseException(e);
1229: } finally {
1230: DbUtils.close(rs, p);
1231: }
1232: }
1233: }
|