001: /*
002: * Copyright (c) JForum Team
003: * All rights reserved.
004: *
005: * Redistribution and use in source and binary forms,
006: * with or without modification, are permitted provided
007: * that the following conditions are met:
008: *
009: * 1) Redistributions of source code must retain the above
010: * copyright notice, this list of conditions and the
011: * following disclaimer.
012: * 2) Redistributions in binary form must reproduce the
013: * above copyright notice, this list of conditions and
014: * the following disclaimer in the documentation and/or
015: * other materials provided with the distribution.
016: * 3) Neither the name of "Rafael Steil" nor
017: * the names of its contributors may be used to endorse
018: * or promote products derived from this software without
019: * specific prior written permission.
020: *
021: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT
022: * HOLDERS AND CONTRIBUTORS "AS IS" AND ANY
023: * EXPRESS OR IMPLIED WARRANTIES, INCLUDING,
024: * BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
025: * MERCHANTABILITY AND FITNESS FOR A PARTICULAR
026: * PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL
027: * THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE
028: * FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
029: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES
030: * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
031: * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA,
032: * OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
033: * CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER
034: * IN CONTRACT, STRICT LIABILITY, OR TORT
035: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN
036: * ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF
037: * ADVISED OF THE POSSIBILITY OF SUCH DAMAGE
038: *
039: * This file creation date: Mar 28, 2003 / 22:57:43 PM
040: * The JForum Project
041: * http://www.jforum.net
042: */
043: package net.jforum.dao.generic;
044:
045: import java.sql.PreparedStatement;
046: import java.sql.ResultSet;
047: import java.sql.SQLException;
048: import java.sql.Timestamp;
049: import java.text.SimpleDateFormat;
050: import java.util.ArrayList;
051: import java.util.Date;
052: import java.util.Iterator;
053: import java.util.List;
054:
055: import net.jforum.JForumExecutionContext;
056: import net.jforum.dao.DataAccessDriver;
057: import net.jforum.entities.Post;
058: import net.jforum.exceptions.DatabaseException;
059: import net.jforum.repository.ForumRepository;
060: import net.jforum.search.SearchFacade;
061: import net.jforum.util.DbUtils;
062: import net.jforum.util.preferences.ConfigKeys;
063: import net.jforum.util.preferences.SystemGlobals;
064:
065: /**
066: * @author Rafael Steil
067: * @author Vanessa Sabino
068: * @version $Id: GenericPostDAO.java,v 1.29 2007/09/12 14:43:15 rafaelsteil Exp $
069: */
070: public class GenericPostDAO extends AutoKeys implements
071: net.jforum.dao.PostDAO {
072: /**
073: * @see net.jforum.dao.PostDAO#selectById(int)
074: */
075: public Post selectById(int postId) {
076: PreparedStatement p = null;
077: ResultSet rs = null;
078: try {
079: p = JForumExecutionContext.getConnection()
080: .prepareStatement(
081: SystemGlobals
082: .getSql("PostModel.selectById"));
083: p.setInt(1, postId);
084:
085: rs = p.executeQuery();
086:
087: Post post = new Post();
088:
089: if (rs.next()) {
090: post = this .makePost(rs);
091: }
092:
093: return post;
094: } catch (SQLException e) {
095: throw new DatabaseException(e);
096: } finally {
097: DbUtils.close(rs, p);
098: }
099: }
100:
101: protected Post makePost(ResultSet rs) throws SQLException {
102: Post post = new Post();
103: post.setId(rs.getInt("post_id"));
104: post.setTopicId(rs.getInt("topic_id"));
105: post.setForumId(rs.getInt("forum_id"));
106: post.setUserId(rs.getInt("user_id"));
107:
108: Timestamp postTime = rs.getTimestamp("post_time");
109: post.setTime(new Date(postTime.getTime()));
110: post.setUserIp(rs.getString("poster_ip"));
111: post.setBbCodeEnabled(rs.getInt("enable_bbcode") > 0);
112: post.setHtmlEnabled(rs.getInt("enable_html") > 0);
113: post.setSmiliesEnabled(rs.getInt("enable_smilies") > 0);
114: post.setSignatureEnabled(rs.getInt("enable_sig") > 0);
115: post.setEditCount(rs.getInt("post_edit_count"));
116:
117: Timestamp editTime = rs.getTimestamp("post_edit_time");
118: post
119: .setEditTime(editTime != null ? new Date(editTime
120: .getTime()) : null);
121:
122: post.setSubject(rs.getString("post_subject"));
123: post.setText(this .getPostTextFromResultSet(rs));
124: post.setPostUsername(rs.getString("username"));
125: post.hasAttachments(rs.getInt("attach") > 0);
126: post.setModerate(rs.getInt("need_moderate") == 1);
127:
128: SimpleDateFormat df = new SimpleDateFormat(SystemGlobals
129: .getValue(ConfigKeys.DATE_TIME_FORMAT));
130: post.setFormatedTime(df.format(postTime));
131:
132: post.setKarma(DataAccessDriver.getInstance().newKarmaDAO()
133: .getPostKarma(post.getId()));
134:
135: return post;
136: }
137:
138: /**
139: * Utility method to read the post text fromt the result set. This method may be useful when
140: * using some "non-standart" way to store text, like oracle does when using (c|b)lob
141: *
142: * @param rs The resultset to fetch data from
143: * @return The post text string
144: * @throws SQLException
145: */
146: protected String getPostTextFromResultSet(ResultSet rs)
147: throws SQLException {
148: return rs.getString("post_text");
149: }
150:
151: /**
152: * @see net.jforum.dao.PostDAO#delete(Post)
153: */
154: public void delete(Post post) {
155: List l = new ArrayList();
156: l.add(post);
157: this .removePosts(l);
158: }
159:
160: private void removePosts(List posts) {
161: PreparedStatement post = null;
162: PreparedStatement text = null;
163:
164: try {
165: post = JForumExecutionContext.getConnection()
166: .prepareStatement(
167: SystemGlobals
168: .getSql("PostModel.deletePost"));
169:
170: text = JForumExecutionContext
171: .getConnection()
172: .prepareStatement(
173: SystemGlobals
174: .getSql("PostModel.deletePostText"));
175:
176: for (Iterator iter = posts.iterator(); iter.hasNext();) {
177: Post p = (Post) iter.next();
178:
179: post.setInt(1, p.getId());
180: text.setInt(1, p.getId());
181:
182: text.executeUpdate();
183: post.executeUpdate();
184:
185: SearchFacade.delete(p);
186: }
187: } catch (SQLException e) {
188: throw new DatabaseException(e);
189: } finally {
190: DbUtils.close(post);
191: DbUtils.close(text);
192: }
193: }
194:
195: /**
196: * @see net.jforum.model.PostModel#deleteByTopic(int)
197: */
198: public void deleteByTopic(int topicId) {
199: PreparedStatement p = null;
200: ResultSet rs = null;
201:
202: try {
203: p = JForumExecutionContext.getConnection()
204: .prepareStatement(
205: SystemGlobals
206: .getSql("PostModel.deleteByTopic"));
207: p.setInt(1, topicId);
208: rs = p.executeQuery();
209:
210: List posts = new ArrayList();
211:
212: while (rs.next()) {
213: Post post = new Post();
214: post.setId(rs.getInt("post_id"));
215: post.setUserId(rs.getInt("user_id"));
216:
217: posts.add(post);
218: }
219:
220: this .removePosts(posts);
221: } catch (SQLException e) {
222: throw new DatabaseException(e);
223: } finally {
224: DbUtils.close(rs, p);
225: }
226: }
227:
228: /**
229: * @see net.jforum.dao.PostDAO#update(net.jforum.entities.Post)
230: */
231: public void update(Post post) {
232: this .updatePostsTable(post);
233: this .updatePostsTextTable(post);
234:
235: SearchFacade.update(post);
236: }
237:
238: protected void updatePostsTextTable(Post post) {
239: PreparedStatement p = null;
240:
241: try {
242: p = JForumExecutionContext
243: .getConnection()
244: .prepareStatement(
245: SystemGlobals
246: .getSql("PostModel.updatePostText"));
247: p.setString(1, post.getText());
248: p.setString(2, post.getSubject());
249: p.setInt(3, post.getId());
250:
251: p.executeUpdate();
252: } catch (SQLException e) {
253: throw new DatabaseException(e);
254: } finally {
255: DbUtils.close(p);
256: }
257: }
258:
259: protected void updatePostsTable(Post post) {
260: PreparedStatement p = null;
261:
262: try {
263: p = JForumExecutionContext.getConnection()
264: .prepareStatement(
265: SystemGlobals
266: .getSql("PostModel.updatePost"));
267: p.setInt(1, post.getTopicId());
268: p.setInt(2, post.getForumId());
269: p.setInt(3, post.isBbCodeEnabled() ? 1 : 0);
270: p.setInt(4, post.isHtmlEnabled() ? 1 : 0);
271: p.setInt(5, post.isSmiliesEnabled() ? 1 : 0);
272: p.setInt(6, post.isSignatureEnabled() ? 1 : 0);
273: p
274: .setTimestamp(7, new Timestamp(System
275: .currentTimeMillis()));
276: p.setInt(8, post.getEditCount() + 1);
277: p.setString(9, post.getUserIp());
278: p.setInt(10, post.getId());
279:
280: p.executeUpdate();
281: } catch (SQLException e) {
282: throw new DatabaseException(e);
283: } finally {
284: DbUtils.close(p);
285: }
286: }
287:
288: /**
289: * @see net.jforum.dao.PostDAO#addNew(net.jforum.entities.Post)
290: */
291: public int addNew(Post post) {
292: try {
293: this .addNewPost(post);
294: this .addNewPostText(post);
295:
296: // Search
297: SearchFacade.create(post);
298:
299: return post.getId();
300: } catch (Exception e) {
301: throw new DatabaseException(e);
302: }
303: }
304:
305: protected void addNewPostText(Post post) throws Exception {
306: PreparedStatement p = null;
307: try {
308: p = JForumExecutionContext
309: .getConnection()
310: .prepareStatement(
311: SystemGlobals
312: .getSql("PostModel.addNewPostText"));
313: p.setInt(1, post.getId());
314: p.setString(2, post.getText());
315: p.setString(3, post.getSubject());
316: p.executeUpdate();
317: } finally {
318: DbUtils.close(p);
319: }
320: }
321:
322: protected void addNewPost(Post post) {
323: PreparedStatement p = null;
324: try {
325: p = this .getStatementForAutoKeys("PostModel.addNewPost");
326:
327: p.setInt(1, post.getTopicId());
328: p.setInt(2, post.getForumId());
329: p.setLong(3, post.getUserId());
330: p.setTimestamp(4, new Timestamp(post.getTime().getTime()));
331: p.setString(5, post.getUserIp());
332: p.setInt(6, post.isBbCodeEnabled() ? 1 : 0);
333: p.setInt(7, post.isHtmlEnabled() ? 1 : 0);
334: p.setInt(8, post.isSmiliesEnabled() ? 1 : 0);
335: p.setInt(9, post.isSignatureEnabled() ? 1 : 0);
336: p.setInt(10, post.isModerationNeeded() ? 1 : 0);
337:
338: this .setAutoGeneratedKeysQuery(SystemGlobals
339: .getSql("PostModel.lastGeneratedPostId"));
340: int postId = this .executeAutoKeysQuery(p);
341: post.setId(postId);
342: } catch (SQLException e) {
343: throw new DatabaseException(e);
344: } finally {
345: DbUtils.close(p);
346: }
347: }
348:
349: /**
350: * @see net.jforum.dao.PostDAO#selectAllBytTopic(int)
351: */
352: public List selectAllByTopic(int topicId) {
353: return this .selectAllByTopicByLimit(topicId, 0,
354: Integer.MAX_VALUE - 1);
355: }
356:
357: /**
358: * @see net.jforum.dao.PostDAO#selectAllBytTopicByLimit(int, int, int)
359: */
360: public List selectAllByTopicByLimit(int topicId, int startFrom,
361: int count) {
362: List l = new ArrayList();
363:
364: String sql = SystemGlobals
365: .getSql("PostModel.selectAllByTopicByLimit");
366:
367: PreparedStatement p = null;
368: ResultSet rs = null;
369:
370: try {
371: p = JForumExecutionContext.getConnection()
372: .prepareStatement(sql);
373: p.setInt(1, topicId);
374: p.setInt(2, startFrom);
375: p.setInt(3, count);
376:
377: rs = p.executeQuery();
378:
379: while (rs.next()) {
380: l.add(this .makePost(rs));
381: }
382:
383: return l;
384: } catch (SQLException e) {
385: throw new DatabaseException(e);
386: } finally {
387: DbUtils.close(rs, p);
388: }
389: }
390:
391: /**
392: * @see net.jforum.dao.PostDAO#selectByUserByLimit(int, int, int)
393: */
394: public List selectByUserByLimit(int userId, int startFrom, int count) {
395: String sql = SystemGlobals
396: .getSql("PostModel.selectByUserByLimit");
397: sql = sql.replaceAll(":fids:", ForumRepository
398: .getListAllowedForums());
399:
400: PreparedStatement p = null;
401: ResultSet rs = null;
402: try {
403: p = JForumExecutionContext.getConnection()
404: .prepareStatement(sql);
405:
406: p.setInt(1, userId);
407: p.setInt(2, startFrom);
408: p.setInt(3, count);
409:
410: rs = p.executeQuery();
411: List l = new ArrayList();
412:
413: while (rs.next()) {
414: l.add(this .makePost(rs));
415: }
416:
417: return l;
418: } catch (SQLException e) {
419: throw new DatabaseException(e);
420: } finally {
421: DbUtils.close(rs, p);
422: }
423: }
424:
425: public int countUserPosts(int userId) {
426: int total = 0;
427:
428: PreparedStatement p = null;
429: ResultSet rs = null;
430: try {
431: p = JForumExecutionContext
432: .getConnection()
433: .prepareStatement(
434: SystemGlobals
435: .getSql("PostModel.countUserPosts")
436: .replaceAll(
437: ":fids:",
438: ForumRepository
439: .getListAllowedForums()));
440: p.setInt(1, userId);
441:
442: rs = p.executeQuery();
443:
444: if (rs.next()) {
445: total = rs.getInt(1);
446: }
447:
448: return total;
449: } catch (SQLException e) {
450: throw new DatabaseException(e);
451: } finally {
452: DbUtils.close(rs, p);
453: }
454: }
455:
456: /**
457: * @see net.jforum.model.PostModel#countPreviousPosts(int)
458: */
459: public int countPreviousPosts(int postId) {
460: int total = 0;
461:
462: PreparedStatement p = null;
463: ResultSet rs = null;
464: try {
465: p = JForumExecutionContext
466: .getConnection()
467: .prepareStatement(
468: SystemGlobals
469: .getSql("PostModel.countPreviousPosts"));
470: p.setInt(1, postId);
471: p.setInt(2, postId);
472:
473: rs = p.executeQuery();
474:
475: if (rs.next()) {
476: total = rs.getInt(1);
477: }
478:
479: return total;
480: } catch (SQLException e) {
481: throw new DatabaseException(e);
482: } finally {
483: DbUtils.close(rs, p);
484: }
485: }
486:
487: public List selectLatestByForumForRSS(int forumId, int limit) {
488: List l = new ArrayList();
489:
490: PreparedStatement p = null;
491: ResultSet rs = null;
492:
493: try {
494: p = JForumExecutionContext
495: .getConnection()
496: .prepareStatement(
497: SystemGlobals
498: .getSql("PostModel.selectLatestByForumForRSS"));
499: p.setInt(1, forumId);
500: p.setInt(2, limit);
501:
502: rs = p.executeQuery();
503:
504: while (rs.next()) {
505: Post post = this .buildPostForRSS(rs);
506: l.add(post);
507: }
508:
509: } catch (SQLException e) {
510: throw new DatabaseException(e);
511: } finally {
512: DbUtils.close(rs, p);
513: }
514:
515: return l;
516: }
517:
518: public List selectHotForRSS(int limit) {
519: List l = new ArrayList();
520:
521: PreparedStatement p = null;
522: ResultSet rs = null;
523:
524: try {
525: p = JForumExecutionContext
526: .getConnection()
527: .prepareStatement(
528: SystemGlobals
529: .getSql("PostModel.selectHotForRSS"));
530: p.setInt(1, limit);
531:
532: rs = p.executeQuery();
533:
534: while (rs.next()) {
535: Post post = this .buildPostForRSS(rs);
536: l.add(post);
537: }
538:
539: } catch (SQLException e) {
540: throw new DatabaseException(e);
541: } finally {
542: DbUtils.close(rs, p);
543: }
544:
545: return l;
546: }
547:
548: private Post buildPostForRSS(ResultSet rs) throws SQLException {
549: Post post = new Post();
550:
551: post.setId(rs.getInt("post_id"));
552: post.setSubject(rs.getString("subject"));
553: post.setText(rs.getString("post_text"));
554: post.setTopicId(rs.getInt("topic_id"));
555: post.setForumId(rs.getInt("forum_id"));
556: post.setUserId(rs.getInt("user_id"));
557: post.setPostUsername(rs.getString("username"));
558: post.setTime(new Date(rs.getTimestamp("post_time").getTime()));
559:
560: return post;
561: }
562: }
|