|
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 MessageDAOImplJDBC implements MessageDAO {
55
56 private static Log log = LogFactory.getLog(MessageDAOImplJDBC.class);
57
58 private static boolean m_dirty = true;
60
61 public MessageDAOImplJDBC() {
62 }
63
64 protected static boolean isDirty() {
65 return m_dirty;
66 }
67
68 protected static void setDirty(boolean dirty) {
69 m_dirty = dirty;
70 }
71
72 private int findMessageID(int memberID, int messageSenderID, Timestamp messageCreationDate)
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 MessageID");
80 sql.append(" FROM " + TABLE_NAME);
81 sql.append(" WHERE MemberID = ? AND MessageSenderID = ? AND MessageCreationDate = ? ");
82 sql.append(" ORDER BY MessageID DESC");
83 try {
84 connection = DBUtils.getConnection();
85 statement = connection.prepareStatement(sql.toString());
86 statement.setInt(1, memberID);
87 statement.setInt(2, messageSenderID);
88 statement.setTimestamp(3, messageCreationDate);
89 resultSet = statement.executeQuery();
90 if(!resultSet.next()) {
91 throw new ObjectNotFoundException("Cannot find the Message in table Message.");
92 }
93
94 return resultSet.getInt("MessageID");
95 } catch(SQLException sqle) {
96 log.error("Sql Execution Error!", sqle);
97 throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.findMessageID.");
98 } finally {
99 DBUtils.closeResultSet(resultSet);
100 DBUtils.closeStatement(statement);
101 DBUtils.closeConnection(connection);
102 }
103 }
104
105 public void findByPrimaryKey(int messageID)
106 throws ObjectNotFoundException, DatabaseException {
107
108 Connection connection = null;
109 PreparedStatement statement = null;
110 ResultSet resultSet = null;
111 StringBuffer sql = new StringBuffer (512);
112 sql.append("SELECT MessageID");
113 sql.append(" FROM " + TABLE_NAME);
114 sql.append(" WHERE MessageID = ?");
115 try {
116 connection = DBUtils.getConnection();
117 statement = connection.prepareStatement(sql.toString());
118 statement.setInt(1, messageID);
119 resultSet = statement.executeQuery();
120 if (!resultSet.next()) {
121 throw new ObjectNotFoundException("Cannot find the primary key (" + messageID + ") in table 'Message'.");
122 }
123 } catch(SQLException sqle) {
124 log.error("Sql Execution Error!", sqle);
125 throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.findByPrimaryKey.");
126 } finally {
127 DBUtils.closeResultSet(resultSet);
128 DBUtils.closeStatement(statement);
129 DBUtils.closeConnection(connection);
130 }
131 }
132
133
140 public int create(String folderName, int memberID, int messageSenderID,
141 String messageSenderName, String messageToList, String messageCcList,
142 String messageBccList, String messageTopic, String messageBody,
143 int messageType, int messageOption, int messageStatus,
144 int messageReadStatus, int messageNotify, String messageIcon,
145 int messageAttachCount, String messageIP, Timestamp messageCreationDate)
146 throws CreateException, DatabaseException, ForeignKeyNotFoundException {
147
148 int messageID = 0;
149
150 try {
151 DAOFactory.getMessageFolderDAO().findByPrimaryKey(folderName, memberID);
154 } catch(ObjectNotFoundException e) {
155 throw new ForeignKeyNotFoundException("Foreign key refers to table 'MessageFolder' does not exist. Cannot create new Message.");
156 }
157
158 try {
159 DAOFactory.getMemberDAO().findByPrimaryKey(memberID);
162 } catch(ObjectNotFoundException e) {
163 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Member' does not exist. Cannot create new Message.");
164 }
165
166 try {
167 DAOFactory.getMemberDAO().findByPrimaryKey2(messageSenderID, messageSenderName);
170 } catch(ObjectNotFoundException e) {
171 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Member' does not exist. Cannot create new Message.");
172 }
173
174 Connection connection = null;
175 PreparedStatement statement = null;
176 StringBuffer sql = new StringBuffer (512);
177 sql.append("INSERT INTO " + TABLE_NAME + " (FolderName, MemberID, MessageSenderID, MessageSenderName, MessageToList, MessageCcList, MessageBccList, MessageTopic, MessageBody, MessageType, MessageOption, MessageStatus, MessageReadStatus, MessageNotify, MessageIcon, MessageAttachCount, MessageIP, MessageCreationDate)");
178 sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
179 try {
180 connection = DBUtils.getConnection();
181 statement = connection.prepareStatement(sql.toString());
182
183 statement.setString(1, folderName);
184 statement.setInt(2, memberID);
185 statement.setInt(3, messageSenderID);
186 statement.setString(4, messageSenderName);
187 statement.setString(5, messageToList);
188 statement.setString(6, messageCcList);
189 statement.setString(7, messageBccList);
190 statement.setString(8, messageTopic);
191 if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
192 statement.setCharacterStream(9, new StringReader (messageBody), messageBody.length());
193 } else {
194 statement.setString(9, messageBody);
195 }
196 statement.setInt(10, messageType);
197 statement.setInt(11, messageOption);
198 statement.setInt(12, messageStatus);
199 statement.setInt(13, messageReadStatus);
200 statement.setInt(14, messageNotify);
201 statement.setString(15, messageIcon);
202 statement.setInt(16, messageAttachCount);
203 statement.setString(17, messageIP);
204 statement.setTimestamp(18, messageCreationDate);
205
206 if (statement.executeUpdate() != 1) {
207 throw new CreateException("Error adding a row into table 'Message'.");
208 }
209 m_dirty = true;
210 try {
212 messageID = findMessageID(memberID, messageSenderID, messageCreationDate);
213 } catch (ObjectNotFoundException onfe) {
214 Timestamp roundTimestamp = new Timestamp((messageCreationDate.getTime()/1000)*1000);
216 try {
217 messageID = findMessageID(memberID, messageSenderID, roundTimestamp);
218 } catch (ObjectNotFoundException e) {
219 throw new CreateException("Cannot find the Message in table Message.");
220 }
221 }
222 } catch(SQLException sqle) {
223 log.error("Sql Execution Error!", sqle);
224 throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.create.");
225 } finally {
226 DBUtils.closeStatement(statement);
227 DBUtils.closeConnection(connection);
228 }
229 return messageID;
230 }
231
232 public Collection getAllMessages_inMember_inFolder_withSortSupport_limit(int memberID, String folderName, int offset, int rowsToReturn, String sort, String order)
233 throws IllegalArgumentException , DatabaseException {
234
235 if (offset < 0) throw new IllegalArgumentException ("The offset < 0 is not allowed.");
236 if (rowsToReturn <= 0) throw new IllegalArgumentException ("The rowsToReturn <= 0 is not allowed.");
237
238 if ((!sort.equals("MessageSenderName")) &&
239 (!sort.equals("MessageTopic")) &&
240 (!sort.equals("MessageReadStatus")) &&
241 (!sort.equals("MessageAttachCount")) &&
242 (!sort.equals("MessageCreationDate")) ) {
243 throw new IllegalArgumentException ("Cannot sort, reason: dont understand the criteria '" + sort + "'.");
244 }
245
246 if ((!order.equals("ASC")) &&
247 (!order.equals("DESC")) ) {
248 throw new IllegalArgumentException ("Cannot sort, reason: dont understand the order '" + order + "'.");
249 }
250
251 if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
252 return getBeans_inMember_inFolder_withSortSupport_limit_mysql(memberID, folderName, offset, rowsToReturn, sort, order, false);
253 } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
254 return getBeans_inMember_inFolder_withSortSupport_limit_noscroll(memberID, folderName, offset, rowsToReturn, sort, order, false);
255 }
256 return getBeans_inMember_inFolder_withSortSupport_limit_general(memberID, folderName, offset, rowsToReturn, sort, order, false);
257 }
258
259 public Collection getNonPublicMessages_inMember_inFolder_withSortSupport_limit(int memberID, String folderName, int offset, int rowsToReturn, String sort, String order)
260 throws IllegalArgumentException , DatabaseException {
261
262 if (offset < 0) throw new IllegalArgumentException ("The offset < 0 is not allowed.");
263 if (rowsToReturn <= 0) throw new IllegalArgumentException ("The rowsToReturn <= 0 is not allowed.");
264
265 if ((!sort.equals("MessageSenderName")) &&
266 (!sort.equals("MessageTopic")) &&
267 (!sort.equals("MessageReadStatus")) &&
268 (!sort.equals("MessageAttachCount")) &&
269 (!sort.equals("MessageCreationDate")) ) {
270 throw new IllegalArgumentException ("Cannot sort, reason: dont understand the criteria '" + sort + "'.");
271 }
272
273 if ((!order.equals("ASC")) &&
274 (!order.equals("DESC")) ) {
275 throw new IllegalArgumentException ("Cannot sort, reason: dont understand the order '" + order + "'.");
276 }
277
278 if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
279 return getBeans_inMember_inFolder_withSortSupport_limit_mysql(memberID, folderName, offset, rowsToReturn, sort, order, true);
280 } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
281 return getBeans_inMember_inFolder_withSortSupport_limit_noscroll(memberID, folderName, offset, rowsToReturn, sort, order, true);
282 }
283 return getBeans_inMember_inFolder_withSortSupport_limit_general(memberID, folderName, offset, rowsToReturn, sort, order, true);
284 }
285
286
293 public Collection getBeans_inMember_inFolder_withSortSupport_limit_mysql(int memberID, String folderName, int offset, int rowsToReturn, String sort, String order, boolean onlyNonPublic)
294 throws DatabaseException {
295
296
299 Connection connection = null;
300 PreparedStatement statement = null;
301 ResultSet resultSet = null;
302 Collection retValue = new ArrayList ();
303 StringBuffer sql = new StringBuffer (512);
304 sql.append("SELECT MessageID, FolderName, MemberID, MessageSenderID, MessageSenderName, MessageToList, MessageCcList, MessageBccList, MessageTopic, MessageBody, MessageType, MessageOption, MessageStatus, MessageReadStatus, MessageNotify, MessageIcon, MessageAttachCount, MessageIP, MessageCreationDate");
305 sql.append(" FROM " + TABLE_NAME);
306 sql.append(" WHERE MemberID = ?");
307 sql.append(" AND FolderName = ?");
308 if (onlyNonPublic) {
309 sql.append(" AND MessageType <> " + MessageBean.MESSAGE_TYPE_PUBLIC);
310 }
311 sql.append(" ORDER BY " + sort + " " + order); sql.append(" LIMIT ?, ?");
313 try {
314 connection = DBUtils.getConnection();
315 statement = connection.prepareStatement(sql.toString());
316 statement.setInt(1, memberID);
317 statement.setString(2, folderName);
318 statement.setInt(3, offset);
319 statement.setInt(4, rowsToReturn);
320 resultSet = statement.executeQuery();
321 while (resultSet.next()) {
322 MessageBean bean = new MessageBean();
323 bean.setMessageID(resultSet.getInt("MessageID"));
324 bean.setFolderName(resultSet.getString("FolderName"));
325 bean.setMemberID(resultSet.getInt("MemberID"));
326 bean.setMessageSenderID(resultSet.getInt("MessageSenderID"));
327 bean.setMessageSenderName(resultSet.getString("MessageSenderName"));
328 bean.setMessageToList(resultSet.getString("MessageToList"));
329 bean.setMessageCcList(resultSet.getString("MessageCcList"));
330 bean.setMessageBccList(resultSet.getString("MessageBccList"));
331 bean.setMessageTopic(resultSet.getString("MessageTopic"));
332 bean.setMessageBody(resultSet.getString("MessageBody"));
333 bean.setMessageType(resultSet.getInt("MessageType"));
334 bean.setMessageOption(resultSet.getInt("MessageOption"));
335 bean.setMessageStatus(resultSet.getInt("MessageStatus"));
336 bean.setMessageReadStatus(resultSet.getInt("MessageReadStatus"));
337 bean.setMessageNotify(resultSet.getInt("MessageNotify"));
338 bean.setMessageIcon(resultSet.getString("MessageIcon"));
339 bean.setMessageAttachCount(resultSet.getInt("MessageAttachCount"));
340 bean.setMessageIP(resultSet.getString("MessageIP"));
341 bean.setMessageCreationDate(resultSet.getTimestamp("MessageCreationDate"));
342 retValue.add(bean);
343 }
344 return retValue;
345 } catch(SQLException sqle) {
346 log.error("Sql Execution Error!", sqle);
347 throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.getBeans_inMember_inFolder_withSortSupport_limit_mysql.");
348 } finally {
349 DBUtils.closeResultSet(resultSet);
350 DBUtils.closeStatement(statement);
351 DBUtils.closeConnection(connection);
352 }
353 }
354
355
362 public Collection getBeans_inMember_inFolder_withSortSupport_limit_noscroll(int memberID, String folderName, int offset, int rowsToReturn, String sort, String order, boolean onlyNonPublic)
363 throws DatabaseException {
364
365
368 Connection connection = null;
369 PreparedStatement statement = null;
370 ResultSet resultSet = null;
371 Collection retValue = new ArrayList ();
372 StringBuffer sql = new StringBuffer (512);
373 sql.append("SELECT MessageID, FolderName, MemberID, MessageSenderID, MessageSenderName, MessageToList, MessageCcList, MessageBccList, MessageTopic, MessageBody, MessageType, MessageOption, MessageStatus, MessageReadStatus, MessageNotify, MessageIcon, MessageAttachCount, MessageIP, MessageCreationDate");
374 sql.append(" FROM " + TABLE_NAME);
375 sql.append(" WHERE MemberID = ?");
376 sql.append(" AND FolderName = ?");
377 if (onlyNonPublic) {
378 sql.append(" AND MessageType <> " + MessageBean.MESSAGE_TYPE_PUBLIC);
379 }
380 sql.append(" ORDER BY " + sort + " " + order); try {
382 connection = DBUtils.getConnection();
383 statement = connection.prepareStatement(sql.toString());
384 statement.setMaxRows(offset + rowsToReturn);
385 statement.setInt(1, memberID);
386 statement.setString(2, folderName);
387 resultSet = statement.executeQuery();
388 int rowIndex = -1;
389 while (resultSet.next()) {
390 rowIndex++;
391 if (rowIndex < offset) continue;
392 MessageBean bean = new MessageBean();
393 bean.setMessageID(resultSet.getInt("MessageID"));
394 bean.setFolderName(resultSet.getString("FolderName"));
395 bean.setMemberID(resultSet.getInt("MemberID"));
396 bean.setMessageSenderID(resultSet.getInt("MessageSenderID"));
397 bean.setMessageSenderName(resultSet.getString("MessageSenderName"));
398 bean.setMessageToList(resultSet.getString("MessageToList"));
399 bean.setMessageCcList(resultSet.getString("MessageCcList"));
400 bean.setMessageBccList(resultSet.getString("MessageBccList"));
401 bean.setMessageTopic(resultSet.getString("MessageTopic"));
402 bean.setMessageBody(resultSet.getString("MessageBody"));
403 bean.setMessageType(resultSet.getInt("MessageType"));
404 bean.setMessageOption(resultSet.getInt("MessageOption"));
405 bean.setMessageStatus(resultSet.getInt("MessageStatus"));
406 bean.setMessageReadStatus(resultSet.getInt("MessageReadStatus"));
407 bean.setMessageNotify(resultSet.getInt("MessageNotify"));
408 bean.setMessageIcon(resultSet.getString("MessageIcon"));
409 bean.setMessageAttachCount(resultSet.getInt("MessageAttachCount"));
410 bean.setMessageIP(resultSet.getString("MessageIP"));
411 bean.setMessageCreationDate(resultSet.getTimestamp("MessageCreationDate"));
412 retValue.add(bean);
413 if (retValue.size() == rowsToReturn) break; }
415 return retValue;
416 } catch(SQLException sqle) {
417 log.error("Sql Execution Error!", sqle);
418 throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.getBeans_inMember_inFolder_withSortSupport_limit_noscroll.");
419 } finally {
420 DBUtils.closeResultSet(resultSet);
421 DBUtils.resetStatement(statement);
422 DBUtils.closeStatement(statement);
423 DBUtils.closeConnection(connection);
424 }
425 }
426
427
434 public Collection getBeans_inMember_inFolder_withSortSupport_limit_general(int memberID, String folderName, int offset, int rowsToReturn, String sort, String order, boolean onlyNonPublic)
435 throws DatabaseException {
436
437
440 Connection connection = null;
441 PreparedStatement statement = null;
442 ResultSet resultSet = null;
443 Collection retValue = new ArrayList ();
444 StringBuffer sql = new StringBuffer (512);
445 sql.append("SELECT MessageID, FolderName, MemberID, MessageSenderID, MessageSenderName, MessageToList, MessageCcList, MessageBccList, MessageTopic, MessageBody, MessageType, MessageOption, MessageStatus, MessageReadStatus, MessageNotify, MessageIcon, MessageAttachCount, MessageIP, MessageCreationDate");
446 sql.append(" FROM " + TABLE_NAME);
447 sql.append(" WHERE MemberID = ?");
448 sql.append(" AND FolderName = ?");
449 if (onlyNonPublic) {
450 sql.append(" AND MessageType <> " + MessageBean.MESSAGE_TYPE_PUBLIC);
451 }
452 sql.append(" ORDER BY " + sort + " " + order); try {
454 connection = DBUtils.getConnection();
455 statement = connection.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
456 statement.setInt(1, memberID);
457 statement.setString(2, folderName);
458 statement.setMaxRows(offset + rowsToReturn);
459 try {
460 statement.setFetchSize(rowsToReturn);
461 } catch (SQLException sqle) {
462 }
464 resultSet = statement.executeQuery();
465 boolean loop = resultSet.absolute(offset + 1); while (loop) {
467 MessageBean bean = new MessageBean();
468 bean.setMessageID(resultSet.getInt("MessageID"));
469 bean.setFolderName(resultSet.getString("FolderName"));
470 bean.setMemberID(resultSet.getInt("MemberID"));
471 bean.setMessageSenderID(resultSet.getInt("MessageSenderID"));
472 bean.setMessageSenderName(resultSet.getString("MessageSenderName"));
473 bean.setMessageToList(resultSet.getString("MessageToList"));
474 bean.setMessageCcList(resultSet.getString("MessageCcList"));
475 bean.setMessageBccList(resultSet.getString("MessageBccList"));
476 bean.setMessageTopic(resultSet.getString("MessageTopic"));
477 bean.setMessageBody(resultSet.getString("MessageBody"));
478 bean.setMessageType(resultSet.getInt("MessageType"));
479 bean.setMessageOption(resultSet.getInt("MessageOption"));
480 bean.setMessageStatus(resultSet.getInt("MessageStatus"));
481 bean.setMessageReadStatus(resultSet.getInt("MessageReadStatus"));
482 bean.setMessageNotify(resultSet.getInt("MessageNotify"));
483 bean.setMessageIcon(resultSet.getString("MessageIcon"));
484 bean.setMessageAttachCount(resultSet.getInt("MessageAttachCount"));
485 bean.setMessageIP(resultSet.getString("MessageIP"));
486 bean.setMessageCreationDate(resultSet.getTimestamp("MessageCreationDate"));
487 retValue.add(bean);
488 if (retValue.size() == rowsToReturn) break; loop = resultSet.next();
490 }
491 return retValue;
492 } catch(SQLException sqle) {
493 log.error("Sql Execution Error!", sqle);
494 throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.getBeans_inMember_inFolder_withSortSupport_limit_general.");
495 } finally {
496 DBUtils.closeResultSet(resultSet);
497 DBUtils.resetStatement(statement);
498 DBUtils.closeStatement(statement);
499 DBUtils.closeConnection(connection);
500 }
501 }
502
503
510 public Collection getPublicMessages()
511 throws DatabaseException {
512
513
516 Connection connection = null;
517 Statement statement = null;
518 ResultSet resultSet = null;
519 Collection retValue = new ArrayList ();
520 StringBuffer sql = new StringBuffer (512);
521 sql.append("SELECT MessageID, FolderName, MemberID, MessageSenderID, MessageSenderName, MessageToList, MessageCcList, MessageBccList, MessageTopic, MessageBody, MessageType, MessageOption, MessageStatus, MessageReadStatus, MessageNotify, MessageIcon, MessageAttachCount, MessageIP, MessageCreationDate");
522 sql.append(" FROM " + TABLE_NAME);
523 sql.append(" WHERE MessageType = " + MessageBean.MESSAGE_TYPE_PUBLIC);
524 sql.append(" ORDER BY MessageCreationDate DESC");
525 try {
526 connection = DBUtils.getConnection();
527 statement = connection.createStatement();
528 resultSet = statement.executeQuery(sql.toString());
529 while (resultSet.next()) {
530 MessageBean bean = new MessageBean();
531 bean.setMessageID(resultSet.getInt("MessageID"));
532 bean.setFolderName(resultSet.getString("FolderName"));
533 bean.setMemberID(resultSet.getInt("MemberID"));
534 bean.setMessageSenderID(resultSet.getInt("MessageSenderID"));
535 bean.setMessageSenderName(resultSet.getString("MessageSenderName"));
536 bean.setMessageToList(resultSet.getString("MessageToList"));
537 bean.setMessageCcList(resultSet.getString("MessageCcList"));
538 bean.setMessageBccList(resultSet.getString("MessageBccList"));
539 bean.setMessageTopic(resultSet.getString("MessageTopic"));
540 bean.setMessageBody(resultSet.getString("MessageBody"));
541 bean.setMessageType(resultSet.getInt("MessageType"));
542 bean.setMessageOption(resultSet.getInt("MessageOption"));
543 bean.setMessageStatus(resultSet.getInt("MessageStatus"));
544 bean.setMessageReadStatus(resultSet.getInt("MessageReadStatus"));
545 bean.setMessageNotify(resultSet.getInt("MessageNotify"));
546 bean.setMessageIcon(resultSet.getString("MessageIcon"));
547 bean.setMessageAttachCount(resultSet.getInt("MessageAttachCount"));
548 bean.setMessageIP(resultSet.getString("MessageIP"));
549 bean.setMessageCreationDate(resultSet.getTimestamp("MessageCreationDate"));
550 retValue.add(bean);
551 }
552 return retValue;
553 } catch(SQLException sqle) {
554 log.error("Sql Execution Error!", sqle);
555 throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.getPublicMessages.");
556 } finally {
557 DBUtils.closeResultSet(resultSet);
558 DBUtils.resetStatement(statement);
559 DBUtils.closeStatement(statement);
560 DBUtils.closeConnection(connection);
561 }
562 }
563
564
571 public MessageBean getMessage(int messageID)
572 throws ObjectNotFoundException, DatabaseException {
573
574 Connection connection = null;
575 PreparedStatement statement = null;
576 ResultSet resultSet = null;
577 StringBuffer sql = new StringBuffer (512);
578 sql.append("SELECT MessageID, FolderName, MemberID, MessageSenderID, MessageSenderName, MessageToList, MessageCcList, MessageBccList, MessageTopic, MessageBody, MessageType, MessageOption, MessageStatus, MessageReadStatus, MessageNotify, MessageIcon, MessageAttachCount, MessageIP, MessageCreationDate");
579 sql.append(" FROM " + TABLE_NAME);
580 sql.append(" WHERE MessageID = ?");
581 try {
582 connection = DBUtils.getConnection();
583 statement = connection.prepareStatement(sql.toString());
584 statement.setInt(1, messageID);
585 resultSet = statement.executeQuery();
586 if(!resultSet.next()) {
587 throw new ObjectNotFoundException("Cannot find the row in table Message where primary key = (" + messageID + ").");
588 }
589
590 MessageBean bean = new MessageBean();
591 bean.setMessageID(resultSet.getInt("MessageID"));
594 bean.setFolderName(resultSet.getString("FolderName"));
595 bean.setMemberID(resultSet.getInt("MemberID"));
596 bean.setMessageSenderID(resultSet.getInt("MessageSenderID"));
597 bean.setMessageSenderName(resultSet.getString("MessageSenderName"));
598 bean.setMessageToList(resultSet.getString("MessageToList"));
599 bean.setMessageCcList(resultSet.getString("MessageCcList"));
600 bean.setMessageBccList(resultSet.getString("MessageBccList"));
601 bean.setMessageTopic(resultSet.getString("MessageTopic"));
602 bean.setMessageBody(resultSet.getString("MessageBody"));
603 bean.setMessageType(resultSet.getInt("MessageType"));
604 bean.setMessageOption(resultSet.getInt("MessageOption"));
605 bean.setMessageStatus(resultSet.getInt("MessageStatus"));
606 bean.setMessageReadStatus(resultSet.getInt("MessageReadStatus"));
607 bean.setMessageNotify(resultSet.getInt("MessageNotify"));
608 bean.setMessageIcon(resultSet.getString("MessageIcon"));
609 bean.setMessageAttachCount(resultSet.getInt("MessageAttachCount"));
610 bean.setMessageIP(resultSet.getString("MessageIP"));
611 bean.setMessageCreationDate(resultSet.getTimestamp("MessageCreationDate"));
612 return bean;
613 } catch(SQLException sqle) {
614 log.error("Sql Execution Error!", sqle);
615 throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.getMessage(pk).");
616 } finally {
617 DBUtils.closeResultSet(resultSet);
618 DBUtils.closeStatement(statement);
619 DBUtils.closeConnection(connection);
620 }
621 }
622
623 public int getNumberOfNonPublicMessages_inMember(int memberID)
624 throws AssertionException, DatabaseException {
625
626 Connection connection = null;
627 PreparedStatement statement = null;
628 ResultSet resultSet = null;
629 StringBuffer sql = new StringBuffer (512);
630 sql.append("SELECT Count(*)");
631 sql.append(" FROM " + TABLE_NAME);
632 sql.append(" WHERE MemberID = ?");
633 boolean onlyNonPublic = true;
634 if (onlyNonPublic) {
635 sql.append(" AND MessageType <> " + MessageBean.MESSAGE_TYPE_PUBLIC);
636 }
637 try {
638 connection = DBUtils.getConnection();
639 statement = connection.prepareStatement(sql.toString());
640 statement.setInt(1, memberID);
641 resultSet = statement.executeQuery();
642 if (!resultSet.next()) {
643 throw new AssertionException("Assertion in MessageDAOImplJDBC.getNumberOfMessages_inMember.");
644 }
645 return resultSet.getInt(1);
646 } catch(SQLException sqle) {
647 log.error("Sql Execution Error!", sqle);
648 throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.getNumberOfMessages_inMember.");
649 } finally {
650 DBUtils.closeResultSet(resultSet);
651 DBUtils.closeStatement(statement);
652 DBUtils.closeConnection(connection);
653 }
654 }
655
656 public void updateMessageReadStatus(int messageID, int memberID, int messageReadStatus)
658 throws ObjectNotFoundException, DatabaseException {
659
660 Connection connection = null;
661 PreparedStatement statement = null;
662 StringBuffer sql = new StringBuffer (512);
663 sql.append("UPDATE " + TABLE_NAME + " SET MessageReadStatus = ?");
664 sql.append(" WHERE MessageID = ?");
665 sql.append(" AND MemberID = ?");
666 try {
667 connection = DBUtils.getConnection();
668 statement = connection.prepareStatement(sql.toString());
669
670 statement.setInt(1, messageReadStatus);
672 statement.setInt(2, messageID);
674
675 statement.setInt(3, memberID);
676
677 if (statement.executeUpdate() != 1) {
678 throw new ObjectNotFoundException("Cannot update table Message where primary key = (" + messageID + ") and MemberID = " + memberID + ".");
679 }
680 m_dirty = true;
681 } catch(SQLException sqle) {
682 log.error("Sql Execution Error!", sqle);
683 throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.updateMessageReadStatus.");
684 } finally {
685 DBUtils.closeStatement(statement);
686 DBUtils.closeConnection(connection);
687 }
688 }
689
690 public void updateAttachCount(int messageID, int messageAttachCount)
692 throws ObjectNotFoundException, DatabaseException {
693
694 Connection connection = null;
695 PreparedStatement statement = null;
696 StringBuffer sql = new StringBuffer (512);
697 sql.append("UPDATE " + TABLE_NAME + " SET MessageAttachCount = ?");
698 sql.append(" WHERE MessageID = ?");
699 try {
700 connection = DBUtils.getConnection();
701 statement = connection.prepareStatement(sql.toString());
702
703 statement.setInt(1, messageAttachCount);
705
706 statement.setInt(2, messageID);
708
709 if (statement.executeUpdate() != 1) {
710 throw new ObjectNotFoundException("Cannot update AttachCount in table Message where primary key = (" + messageID + ").");
711 }
712 m_dirty = true;
713 } catch(SQLException sqle) {
714 log.error("Sql Execution Error!", sqle);
715 throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.updateAttachCount.");
716 } finally {
717 DBUtils.closeStatement(statement);
718 DBUtils.closeConnection(connection);
719 }
720 }
721
722 public void updateFolderName(int messageID, int memberID, String folderName)
724 throws ObjectNotFoundException, DatabaseException {
725
726 Connection connection = null;
727 PreparedStatement statement = null;
728 StringBuffer sql = new StringBuffer (512);
729 sql.append("UPDATE " + TABLE_NAME + " SET FolderName = ?");
730 sql.append(" WHERE MessageID = ? AND MemberID= ?");
731 try {
732 connection = DBUtils.getConnection();
733 statement = connection.prepareStatement(sql.toString());
734
735 statement.setString(1, folderName);
737 statement.setInt(2, messageID);
739 statement.setInt(3, memberID);
740
741 if (statement.executeUpdate() != 1) {
742 throw new ObjectNotFoundException("Cannot update table Message where primary key = (" + messageID + ").");
743 }
744 m_dirty = true;
745 } catch(SQLException sqle) {
746 log.error("Sql Execution Error!", sqle);
747 throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.updateFolderName.");
748 } finally {
749 DBUtils.closeStatement(statement);
750 DBUtils.closeConnection(connection);
751 }
752 }
753
754 public void deleteMessage(int messageID, int memberID)
755 throws DatabaseException, ObjectNotFoundException {
756
757 Connection connection = null;
758 PreparedStatement statement = null;
759 StringBuffer sql = new StringBuffer (512);
760 sql.append("DELETE FROM " + TABLE_NAME);
761 sql.append(" WHERE MessageID = ?");
762 sql.append(" AND MemberID = ?");
763
764 try {
765 connection = DBUtils.getConnection();
766 statement = connection.prepareStatement(sql.toString());
767 statement.setInt(1, messageID);
768 statement.setInt(2, memberID);
769 if (statement.executeUpdate() != 1) {
770 throw new ObjectNotFoundException("Cannot delete a row in table Message where primary key = (" + messageID + ") and MemberID = " + memberID + ".");
771 }
772 m_dirty = true;
773 } catch(SQLException sqle) {
774 log.error("Sql Execution Error!", sqle);
775 throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.deleteMessage.");
776 } finally {
777 DBUtils.closeStatement(statement);
778 DBUtils.closeConnection(connection);
779 }
780 }
781
782 public void deleteSenderMessages(int senderID)
783 throws DatabaseException {
784
785 Connection connection = null;
786 PreparedStatement statement = null;
787 StringBuffer sql = new StringBuffer (512);
788 sql.append("DELETE FROM " + TABLE_NAME);
789 sql.append(" WHERE MessageSenderID = ?");
790
791 try {
792 connection = DBUtils.getConnection();
793 statement = connection.prepareStatement(sql.toString());
794 statement.setInt(1, senderID);
795
796 statement.executeUpdate();
797 m_dirty = true;
798 } catch(SQLException sqle) {
799 log.error("Sql Execution Error!", sqle);
800 throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.deleteSenderMessages.");
801 } finally {
802 DBUtils.closeStatement(statement);
803 DBUtils.closeConnection(connection);
804 }
805 }
806
807 public void deleteMessages_inFolderName_inMember(String folderName, int memberID)
808 throws DatabaseException {
809
810 Connection connection = null;
811 PreparedStatement statement = null;
812 StringBuffer sql = new StringBuffer (512);
813 sql.append("DELETE FROM " + TABLE_NAME);
814 sql.append(" WHERE FolderName = ?");
815 sql.append(" AND MemberID = ?");
816
817 try {
818 connection = DBUtils.getConnection();
819 statement = connection.prepareStatement(sql.toString());
820 statement.setString(1, folderName);
821 statement.setInt(2, memberID);
822
823 statement.executeUpdate();
824
825 m_dirty = true;
826 } catch(SQLException sqle) {
827 log.error("Sql Execution Error!", sqle);
828 throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.deleteMessages_inFolderName_inMember.");
829 } finally {
830 DBUtils.closeStatement(statement);
831 DBUtils.closeConnection(connection);
832 }
833
834 }
835
836 public int getNumberOfNonPublicMessages_inMember_inFolder(int memberID, String folderName)
837 throws AssertionException, DatabaseException {
838 return getNumberOfMessages_inMember_inFolder(memberID, folderName, false, true);
840 }
841
842 public int getNumberOfUnreadNonPublicMessages_inMember_inFolder(int memberID, String folderName)
843 throws AssertionException, DatabaseException {
844 return getNumberOfMessages_inMember_inFolder(memberID, folderName, true, true);
846 }
847
848 public int getNumberOfAllMessages_inMember_inFolder(int memberID, String folderName)
849 throws AssertionException, DatabaseException {
850 return getNumberOfMessages_inMember_inFolder(memberID, folderName, false, false);
852 }
853
854 public int getNumberOfUnreadAllMessages_inMember_inFolder(int memberID, String folderName)
855 throws AssertionException, DatabaseException {
856 return getNumberOfMessages_inMember_inFolder(memberID, folderName, true, false);
858 }
859
860 private int getNumberOfMessages_inMember_inFolder(int memberID, String folderName, boolean unread, boolean onlyNonPublic)
863 throws AssertionException, DatabaseException {
864
865 Connection connection = null;
866 PreparedStatement statement = null;
867 ResultSet resultSet = null;
868 StringBuffer sql = new StringBuffer (512);
869 sql.append("SELECT Count(*)");
870 sql.append(" FROM " + TABLE_NAME);
871 sql.append(" WHERE FolderName = ? AND MemberID = ? ");
872 if (unread) {
873 sql.append(" AND MessageReadStatus = 0");
874 }
875 if (onlyNonPublic) {
876 sql.append(" AND MessageType <> " + MessageBean.MESSAGE_TYPE_PUBLIC);
877 }
878 try {
879 connection = DBUtils.getConnection();
880 statement = connection.prepareStatement(sql.toString());
881 statement.setString(1, folderName);
882 statement.setInt(2, memberID);
883 resultSet = statement.executeQuery();
884 if (!resultSet.next()) {
885 throw new AssertionException("Assertion in MessageDAOImplJDBC.getNumberOfMessages_inMember_inFolder(flagUnread, onlyNonPublic).");
886 }
887 return resultSet.getInt(1);
888 } catch(SQLException sqle) {
889 log.error("Sql Execution Error!", sqle);
890 throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.getNumberOfMessages_inMember_inFolder(flagUnread, onlyNonPublic).");
891 } finally {
892 DBUtils.closeResultSet(resultSet);
893 DBUtils.closeStatement(statement);
894 DBUtils.closeConnection(connection);
895 }
896 }
897 }
898 |
Popular Tags |
Java API By Example, From Geeks To Geeks.