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: * Created on 21/05/2004 - 14:19:11
040: * The JForum Project
041: * http://www.jforum.net
042: */
043: package net.jforum.dao.generic;
044:
045: import java.sql.Connection;
046: import java.sql.PreparedStatement;
047: import java.sql.ResultSet;
048: import java.sql.SQLException;
049: import java.util.Date;
050: import java.util.Iterator;
051: import java.util.List;
052:
053: import net.jforum.JForumExecutionContext;
054: import net.jforum.dao.PollDAO;
055: import net.jforum.entities.Poll;
056: import net.jforum.entities.PollOption;
057: import net.jforum.exceptions.DatabaseException;
058: import net.jforum.util.DbUtils;
059: import net.jforum.util.preferences.SystemGlobals;
060:
061: /**
062: * @author David Almilli
063: * @version $Id: GenericPollDAO.java,v 1.9 2007/08/01 22:30:03 rafaelsteil Exp $
064: */
065: public class GenericPollDAO extends AutoKeys implements PollDAO {
066: /**
067: * @see net.jforum.dao.PollDAO#addNew(net.jforum.entities.Poll)
068: */
069: public int addNew(Poll poll) {
070: this .addNewPoll(poll);
071: this .addNewPollOptions(poll.getId(), poll.getOptions());
072:
073: return poll.getId();
074: }
075:
076: protected void addNewPoll(Poll poll) {
077: PreparedStatement p = null;
078: try {
079: p = this .getStatementForAutoKeys("PollModel.addNewPoll");
080: p.setInt(1, poll.getTopicId());
081: p.setString(2, poll.getLabel());
082: p.setInt(3, poll.getLength());
083:
084: this .setAutoGeneratedKeysQuery(SystemGlobals
085: .getSql("PollModel.lastGeneratedPollId"));
086: int pollId = this .executeAutoKeysQuery(p);
087: poll.setId(pollId);
088: } catch (SQLException e) {
089: throw new DatabaseException(e);
090: } finally {
091: DbUtils.close(p);
092: }
093: }
094:
095: protected void addNewPollOptions(int pollId, List options) {
096: Connection connection = JForumExecutionContext.getConnection();
097:
098: PreparedStatement p = null;
099: ResultSet rs = null;
100: try {
101: p = connection.prepareStatement(SystemGlobals
102: .getSql("PollModel.selectMaxVoteId"));
103:
104: p.setInt(1, pollId);
105: rs = p.executeQuery();
106: rs.next();
107:
108: int optionId = rs.getInt(1);
109:
110: rs.close();
111: rs = null;
112: p.close();
113: p = null;
114:
115: p = connection.prepareStatement(SystemGlobals
116: .getSql("PollModel.addNewPollOption"));
117: for (Iterator iter = options.iterator(); iter.hasNext();) {
118: PollOption option = (PollOption) iter.next();
119:
120: p.setInt(1, pollId);
121: p.setInt(2, ++optionId);
122: p.setString(3, option.getText());
123:
124: p.executeUpdate();
125: }
126: } catch (SQLException e) {
127: throw new DatabaseException(e);
128: } finally {
129: DbUtils.close(rs, p);
130: }
131: }
132:
133: /**
134: * @see net.jforum.dao.PollDAO#selectById(int)
135: */
136: public Poll selectById(int pollId) {
137: PreparedStatement p = null;
138: PreparedStatement o = null;
139: ResultSet ors = null;
140: ResultSet prs = null;
141: try {
142: p = JForumExecutionContext.getConnection()
143: .prepareStatement(
144: SystemGlobals
145: .getSql("PollModel.selectById"));
146: p.setInt(1, pollId);
147: prs = p.executeQuery();
148:
149: Poll poll = null;
150: if (prs.next()) {
151: poll = this .makePoll(prs);
152:
153: o = JForumExecutionContext
154: .getConnection()
155: .prepareStatement(
156: SystemGlobals
157: .getSql("PollModel.selectOptionsByPollId"));
158: o.setInt(1, pollId);
159: ors = o.executeQuery();
160:
161: while (ors.next()) {
162: poll.addOption(this .makePollOption(ors));
163: }
164: }
165:
166: return poll;
167: } catch (SQLException e) {
168: throw new DatabaseException(e);
169: } finally {
170: DbUtils.close(prs, p);
171: DbUtils.close(ors, o);
172: }
173: }
174:
175: protected Poll makePoll(ResultSet rs) throws SQLException {
176: Poll poll = new Poll();
177: poll.setId(rs.getInt("vote_id"));
178: poll.setTopicId(rs.getInt("topic_id"));
179: poll.setLabel(rs.getString("vote_text"));
180: poll.setStartTime(new Date(rs.getTimestamp("vote_start")
181: .getTime()));
182: poll.setLength(rs.getInt("vote_length"));
183:
184: return poll;
185: }
186:
187: protected PollOption makePollOption(ResultSet rs)
188: throws SQLException {
189: PollOption option = new PollOption();
190: option.setPollId(rs.getInt("vote_id"));
191: option.setId(rs.getInt("vote_option_id"));
192: option.setText(rs.getString("vote_option_text"));
193: option.setVoteCount(rs.getInt("vote_result"));
194:
195: return option;
196: }
197:
198: /**
199: * @see net.jforum.dao.PollDAO#voteOnPoll(int, int, int, java.lang.String)
200: */
201: public void voteOnPoll(int pollId, int optionId, int userId,
202: String ipAddress) {
203: Connection connection = JForumExecutionContext.getConnection();
204:
205: PreparedStatement v = null;
206: PreparedStatement p = null;
207: try {
208: p = connection.prepareStatement(SystemGlobals
209: .getSql("PollModel.incrementVoteCount"));
210: v = connection.prepareStatement(SystemGlobals
211: .getSql("PollModel.addNewVoter"));
212:
213: p.setInt(1, pollId);
214: p.setInt(2, optionId);
215:
216: v.setInt(1, pollId);
217: v.setInt(2, userId);
218: v.setString(3, ipAddress);
219:
220: p.executeUpdate();
221: v.executeUpdate();
222: } catch (SQLException e) {
223: throw new DatabaseException(e);
224: } finally {
225: DbUtils.close(p);
226: DbUtils.close(v);
227: }
228: }
229:
230: /**
231: * @see net.jforum.dao.PollDAO#hasVotedOnPoll(int, int)
232: */
233: public boolean hasUserVotedOnPoll(int pollId, int userId) {
234: PreparedStatement p = null;
235: ResultSet rs = null;
236: try {
237: p = JForumExecutionContext.getConnection()
238: .prepareStatement(
239: SystemGlobals
240: .getSql("PollModel.selectVoter"));
241: p.setInt(1, pollId);
242: p.setInt(2, userId);
243:
244: rs = p.executeQuery();
245:
246: return rs.next();
247: } catch (SQLException e) {
248: throw new DatabaseException(e);
249: } finally {
250: DbUtils.close(rs, p);
251: }
252: }
253:
254: /**
255: * Tells if the anonymous user has already voted on the given poll from the given IP
256: *
257: * @param pollId
258: * the poll id that is being checked
259: * @param ipAddress
260: * the IP address of the anonymoususer to check the vote for
261: * @return true if the user has already voted on the given poll
262: */
263: public boolean hasUserVotedOnPoll(int pollId, String ipAddress) {
264: PreparedStatement p = null;
265: ResultSet rs = null;
266: try {
267: p = JForumExecutionContext
268: .getConnection()
269: .prepareStatement(
270: SystemGlobals
271: .getSql("PollModel.selectVoterByIP"));
272: p.setInt(1, pollId);
273: p.setString(2, ipAddress);
274:
275: rs = p.executeQuery();
276:
277: return rs.next();
278: } catch (SQLException e) {
279: throw new DatabaseException(e);
280: } finally {
281: DbUtils.close(rs, p);
282: }
283: }
284:
285: /**
286: * @see net.jforum.dao.PollDAO#delete(int)
287: */
288: public void deleteByTopicId(int topicId) {
289: // first, lookup the poll id, then delete it
290: PreparedStatement p = null;
291: ResultSet rs = null;
292: try {
293: p = JForumExecutionContext
294: .getConnection()
295: .prepareStatement(
296: SystemGlobals
297: .getSql("PollModel.selectPollByTopicId"));
298:
299: p.setInt(1, topicId);
300:
301: rs = p.executeQuery();
302:
303: int pollId = 0;
304: if (rs.next()) {
305: pollId = rs.getInt("vote_id");
306: }
307:
308: if (pollId != 0) {
309: delete(pollId);
310: }
311: } catch (SQLException e) {
312: throw new DatabaseException(e);
313: } finally {
314: DbUtils.close(rs, p);
315: }
316: }
317:
318: /**
319: * @see net.jforum.dao.PollDAO#delete(int)
320: */
321: public void delete(int pollId) {
322: this .deletePollVotes(pollId);
323: this .deleteAllPollOptions(pollId);
324: this .deletePoll(pollId);
325: }
326:
327: protected void deletePoll(int pollId) {
328: PreparedStatement poll = null;
329: try {
330: poll = JForumExecutionContext.getConnection()
331: .prepareStatement(
332: SystemGlobals
333: .getSql("PollModel.deletePoll"));
334: poll.setInt(1, pollId);
335: poll.executeUpdate();
336: } catch (SQLException e) {
337: throw new DatabaseException(e);
338: } finally {
339: DbUtils.close(poll);
340: }
341: }
342:
343: protected void deletePollVotes(int pollId) {
344: PreparedStatement poll = null;
345: try {
346: poll = JForumExecutionContext
347: .getConnection()
348: .prepareStatement(
349: SystemGlobals
350: .getSql("PollModel.deletePollVoters"));
351: poll.setInt(1, pollId);
352: poll.executeUpdate();
353: } catch (SQLException e) {
354: throw new DatabaseException(e);
355: } finally {
356: DbUtils.close(poll);
357: }
358: }
359:
360: protected void deleteAllPollOptions(int pollId) {
361: PreparedStatement poll = null;
362: try {
363: poll = JForumExecutionContext
364: .getConnection()
365: .prepareStatement(
366: SystemGlobals
367: .getSql("PollModel.deleteAllPollOptions"));
368:
369: poll.setInt(1, pollId);
370: poll.executeUpdate();
371: } catch (SQLException e) {
372: throw new DatabaseException(e);
373: } finally {
374: DbUtils.close(poll);
375: }
376: }
377:
378: protected void deletePollOptions(int pollId, List deleted)
379: throws SQLException {
380: Connection connection = JForumExecutionContext.getConnection();
381:
382: PreparedStatement options = null;
383: try {
384: options = connection.prepareStatement(SystemGlobals
385: .getSql("PollModel.deletePollOption"));
386:
387: for (Iterator iter = deleted.iterator(); iter.hasNext();) {
388: PollOption o = (PollOption) iter.next();
389:
390: // Option
391: options.setInt(1, pollId);
392: options.setInt(2, o.getId());
393: options.executeUpdate();
394: }
395: } finally {
396: DbUtils.close(options);
397: }
398: }
399:
400: protected void updatePollOptions(int pollId, List options)
401: throws SQLException {
402: PreparedStatement p = null;
403: try {
404: p = JForumExecutionContext
405: .getConnection()
406: .prepareStatement(
407: SystemGlobals
408: .getSql("PollModel.updatePollOption"));
409:
410: for (Iterator iter = options.iterator(); iter.hasNext();) {
411: PollOption o = (PollOption) iter.next();
412:
413: p.setString(1, o.getText());
414: p.setInt(2, o.getId());
415: p.setInt(3, pollId);
416:
417: p.executeUpdate();
418: }
419: } finally {
420: DbUtils.close(p);
421: }
422: }
423:
424: /**
425: * @see net.jforum.dao.PollDAO#update(net.jforum.entities.Poll)
426: */
427: public void update(Poll poll) {
428: try {
429: this .updatePoll(poll);
430:
431: if (poll.getChanges() != null) {
432: this .deletePollOptions(poll.getId(), poll.getChanges()
433: .getDeletedOptions());
434: this .updatePollOptions(poll.getId(), poll.getChanges()
435: .getChangedOptions());
436: this .addNewPollOptions(poll.getId(), poll.getChanges()
437: .getNewOptions());
438: }
439: } catch (SQLException e) {
440: throw new DatabaseException(e);
441: }
442: }
443:
444: protected void updatePoll(Poll poll) throws SQLException {
445: PreparedStatement p = null;
446: try {
447: p = JForumExecutionContext.getConnection()
448: .prepareStatement(
449: SystemGlobals
450: .getSql("PollModel.updatePoll"));
451:
452: p.setString(1, poll.getLabel());
453: p.setInt(2, poll.getLength());
454: p.setInt(3, poll.getId());
455:
456: p.executeUpdate();
457: } finally {
458: DbUtils.close(p);
459: }
460: }
461: }
|