0001: /*
0002: * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/MessageDAOImplJDBC.java,v 1.44 2008/01/15 11:17:54 minhnn Exp $
0003: * $Author: minhnn $
0004: * $Revision: 1.44 $
0005: * $Date: 2008/01/15 11:17:54 $
0006: *
0007: * ====================================================================
0008: *
0009: * Copyright (C) 2002-2007 by MyVietnam.net
0010: *
0011: * All copyright notices regarding mvnForum MUST remain
0012: * intact in the scripts and in the outputted HTML.
0013: * The "powered by" text/logo with a link back to
0014: * http://www.mvnForum.com and http://www.MyVietnam.net in
0015: * the footer of the pages MUST remain visible when the pages
0016: * are viewed on the internet or intranet.
0017: *
0018: * This program is free software; you can redistribute it and/or modify
0019: * it under the terms of the GNU General Public License as published by
0020: * the Free Software Foundation; either version 2 of the License, or
0021: * any later version.
0022: *
0023: * This program is distributed in the hope that it will be useful,
0024: * but WITHOUT ANY WARRANTY; without even the implied warranty of
0025: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
0026: * GNU General Public License for more details.
0027: *
0028: * You should have received a copy of the GNU General Public License
0029: * along with this program; if not, write to the Free Software
0030: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
0031: *
0032: * Support can be obtained from support forums at:
0033: * http://www.mvnForum.com/mvnforum/index
0034: *
0035: * Correspondence and Marketing Questions can be sent to:
0036: * info at MyVietnam net
0037: *
0038: * @author: Minh Nguyen
0039: * @author: Mai Nguyen
0040: */
0041: package com.mvnforum.db.jdbc;
0042:
0043: import java.io.StringReader;
0044: import java.sql.*;
0045: import java.util.ArrayList;
0046: import java.util.Collection;
0047:
0048: import com.mvnforum.db.*;
0049: import net.myvietnam.mvncore.db.DBUtils;
0050: import net.myvietnam.mvncore.exception.*;
0051: import net.myvietnam.mvncore.util.AssertionUtil;
0052:
0053: import org.apache.commons.logging.Log;
0054: import org.apache.commons.logging.LogFactory;
0055:
0056: public class MessageDAOImplJDBC implements MessageDAO {
0057:
0058: private static Log log = LogFactory
0059: .getLog(MessageDAOImplJDBC.class);
0060:
0061: //this variable will support caching if cache for this class is needed
0062: private static boolean m_dirty = true;
0063:
0064: public MessageDAOImplJDBC() {
0065: }
0066:
0067: protected static boolean isDirty() {
0068: return m_dirty;
0069: }
0070:
0071: protected static void setDirty(boolean dirty) {
0072: m_dirty = dirty;
0073: }
0074:
0075: private int findMessageID(int memberID, int messageSenderID,
0076: Timestamp messageCreationDate)
0077: throws ObjectNotFoundException, DatabaseException {
0078:
0079: Connection connection = null;
0080: PreparedStatement statement = null;
0081: ResultSet resultSet = null;
0082: StringBuffer sql = new StringBuffer(512);
0083: sql.append("SELECT MessageID");
0084: sql.append(" FROM " + TABLE_NAME);
0085: sql
0086: .append(" WHERE MemberID = ? AND MessageSenderID = ? AND MessageCreationDate = ? ");
0087: sql.append(" ORDER BY MessageID DESC");
0088: try {
0089: connection = DBUtils.getConnection();
0090: statement = connection.prepareStatement(sql.toString());
0091: statement.setInt(1, memberID);
0092: statement.setInt(2, messageSenderID);
0093: statement.setTimestamp(3, messageCreationDate);
0094: resultSet = statement.executeQuery();
0095: if (!resultSet.next()) {
0096: throw new ObjectNotFoundException(
0097: "Cannot find the Message in table Message.");
0098: }
0099:
0100: return resultSet.getInt("MessageID");
0101: } catch (SQLException sqle) {
0102: log.error("Sql Execution Error!", sqle);
0103: throw new DatabaseException(
0104: "Error executing SQL in MessageDAOImplJDBC.findMessageID.");
0105: } finally {
0106: DBUtils.closeResultSet(resultSet);
0107: DBUtils.closeStatement(statement);
0108: DBUtils.closeConnection(connection);
0109: }
0110: }
0111:
0112: public void findByPrimaryKey(int messageID)
0113: throws ObjectNotFoundException, DatabaseException {
0114:
0115: Connection connection = null;
0116: PreparedStatement statement = null;
0117: ResultSet resultSet = null;
0118: StringBuffer sql = new StringBuffer(512);
0119: sql.append("SELECT MessageID");
0120: sql.append(" FROM " + TABLE_NAME);
0121: sql.append(" WHERE MessageID = ?");
0122: try {
0123: connection = DBUtils.getConnection();
0124: statement = connection.prepareStatement(sql.toString());
0125: statement.setInt(1, messageID);
0126: resultSet = statement.executeQuery();
0127: if (!resultSet.next()) {
0128: throw new ObjectNotFoundException(
0129: "Cannot find the primary key (" + messageID
0130: + ") in table 'Message'.");
0131: }
0132: } catch (SQLException sqle) {
0133: log.error("Sql Execution Error!", sqle);
0134: throw new DatabaseException(
0135: "Error executing SQL in MessageDAOImplJDBC.findByPrimaryKey.");
0136: } finally {
0137: DBUtils.closeResultSet(resultSet);
0138: DBUtils.closeStatement(statement);
0139: DBUtils.closeConnection(connection);
0140: }
0141: }
0142:
0143: /*
0144: * Included columns: FolderName, MemberID, MessageSenderID, MessageSenderName, MessageToList,
0145: * MessageCcList, MessageBccList, MessageTopic, MessageBody, MessageType,
0146: * MessageOption, MessageStatus, MessageReadStatus, MessageNotify, MessageIcon,
0147: * MessageAttachCount, MessageIP, MessageCreationDate
0148: * Excluded columns: MessageID
0149: */
0150: public int create(String folderName, int memberID,
0151: int messageSenderID, String messageSenderName,
0152: String messageToList, String messageCcList,
0153: String messageBccList, String messageTopic,
0154: String messageBody, int messageType, int messageOption,
0155: int messageStatus, int messageReadStatus,
0156: int messageNotify, String messageIcon,
0157: int messageAttachCount, String messageIP,
0158: Timestamp messageCreationDate) throws CreateException,
0159: DatabaseException, ForeignKeyNotFoundException {
0160:
0161: int messageID = 0;
0162:
0163: try {
0164: // @todo: modify the parameter list as needed
0165: // You may have to regenerate this method if the needed columns dont have attribute 'include'
0166: DAOFactory.getMessageFolderDAO().findByPrimaryKey(
0167: folderName, memberID);
0168: } catch (ObjectNotFoundException e) {
0169: throw new ForeignKeyNotFoundException(
0170: "Foreign key refers to table 'MessageFolder' does not exist. Cannot create new Message.");
0171: }
0172:
0173: try {
0174: // @todo: modify the parameter list as needed
0175: // You may have to regenerate this method if the needed columns dont have attribute 'include'
0176: DAOFactory.getMemberDAO().findByPrimaryKey(memberID);
0177: } catch (ObjectNotFoundException e) {
0178: throw new ForeignKeyNotFoundException(
0179: "Foreign key refers to table 'Member' does not exist. Cannot create new Message.");
0180: }
0181:
0182: try {
0183: // @todo: modify the parameter list as needed
0184: // You may have to regenerate this method if the needed columns dont have attribute 'include'
0185: DAOFactory.getMemberDAO().findByPrimaryKey2(
0186: messageSenderID, messageSenderName);
0187: } catch (ObjectNotFoundException e) {
0188: throw new ForeignKeyNotFoundException(
0189: "Foreign key refers to table 'Member' does not exist. Cannot create new Message.");
0190: }
0191:
0192: Connection connection = null;
0193: PreparedStatement statement = null;
0194: StringBuffer sql = new StringBuffer(512);
0195: sql
0196: .append("INSERT INTO "
0197: + TABLE_NAME
0198: + " (FolderName, MemberID, MessageSenderID, MessageSenderName, MessageToList, MessageCcList, MessageBccList, MessageTopic, MessageBody, MessageType, MessageOption, MessageStatus, MessageReadStatus, MessageNotify, MessageIcon, MessageAttachCount, MessageIP, MessageCreationDate)");
0199: sql
0200: .append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
0201: try {
0202: connection = DBUtils.getConnection();
0203: statement = connection.prepareStatement(sql.toString());
0204:
0205: statement.setString(1, folderName);
0206: statement.setInt(2, memberID);
0207: statement.setInt(3, messageSenderID);
0208: statement.setString(4, messageSenderName);
0209: statement.setString(5, messageToList);
0210: statement.setString(6, messageCcList);
0211: statement.setString(7, messageBccList);
0212: statement.setString(8, messageTopic);
0213: if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
0214: statement.setCharacterStream(9, new StringReader(
0215: messageBody), messageBody.length());
0216: } else {
0217: statement.setString(9, messageBody);
0218: }
0219: statement.setInt(10, messageType);
0220: statement.setInt(11, messageOption);
0221: statement.setInt(12, messageStatus);
0222: statement.setInt(13, messageReadStatus);
0223: statement.setInt(14, messageNotify);
0224: statement.setString(15, messageIcon);
0225: statement.setInt(16, messageAttachCount);
0226: statement.setString(17, messageIP);
0227: statement.setTimestamp(18, messageCreationDate);
0228:
0229: if (statement.executeUpdate() != 1) {
0230: throw new CreateException(
0231: "Error adding a row into table 'Message'.");
0232: }
0233: m_dirty = true;
0234: // Search returned MessageID here
0235: try {
0236: messageID = findMessageID(memberID, messageSenderID,
0237: messageCreationDate);
0238: } catch (ObjectNotFoundException onfe) {
0239: // Hack the Oracle 9i problem.
0240: Timestamp roundTimestamp = new Timestamp(
0241: (messageCreationDate.getTime() / 1000) * 1000);
0242: try {
0243: messageID = findMessageID(memberID,
0244: messageSenderID, roundTimestamp);
0245: } catch (ObjectNotFoundException e) {
0246: throw new CreateException(
0247: "Cannot find the Message in table Message.");
0248: }
0249: }
0250: } catch (SQLException sqle) {
0251: log.error("Sql Execution Error!", sqle);
0252: throw new DatabaseException(
0253: "Error executing SQL in MessageDAOImplJDBC.create.");
0254: } finally {
0255: DBUtils.closeStatement(statement);
0256: DBUtils.closeConnection(connection);
0257: }
0258: return messageID;
0259: }
0260:
0261: public Collection getAllMessages_inMember_inFolder_withSortSupport_limit(
0262: int memberID, String folderName, int offset,
0263: int rowsToReturn, String sort, String order)
0264: throws IllegalArgumentException, DatabaseException {
0265:
0266: if (offset < 0)
0267: throw new IllegalArgumentException(
0268: "The offset < 0 is not allowed.");
0269: if (rowsToReturn <= 0)
0270: throw new IllegalArgumentException(
0271: "The rowsToReturn <= 0 is not allowed.");
0272:
0273: if ((!sort.equals("MessageSenderName"))
0274: && (!sort.equals("MessageTopic"))
0275: && (!sort.equals("MessageReadStatus"))
0276: && (!sort.equals("MessageAttachCount"))
0277: && (!sort.equals("MessageCreationDate"))) {
0278: throw new IllegalArgumentException(
0279: "Cannot sort, reason: dont understand the criteria '"
0280: + sort + "'.");
0281: }
0282:
0283: if ((!order.equals("ASC")) && (!order.equals("DESC"))) {
0284: throw new IllegalArgumentException(
0285: "Cannot sort, reason: dont understand the order '"
0286: + order + "'.");
0287: }
0288:
0289: if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
0290: return getBeans_inMember_inFolder_withSortSupport_limit_mysql(
0291: memberID, folderName, offset, rowsToReturn, sort,
0292: order, false);
0293: } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
0294: return getBeans_inMember_inFolder_withSortSupport_limit_noscroll(
0295: memberID, folderName, offset, rowsToReturn, sort,
0296: order, false);
0297: }
0298: return getBeans_inMember_inFolder_withSortSupport_limit_general(
0299: memberID, folderName, offset, rowsToReturn, sort,
0300: order, false);
0301: }
0302:
0303: public Collection getNonPublicMessages_inMember_inFolder_withSortSupport_limit(
0304: int memberID, String folderName, int offset,
0305: int rowsToReturn, String sort, String order)
0306: throws IllegalArgumentException, DatabaseException {
0307:
0308: if (offset < 0)
0309: throw new IllegalArgumentException(
0310: "The offset < 0 is not allowed.");
0311: if (rowsToReturn <= 0)
0312: throw new IllegalArgumentException(
0313: "The rowsToReturn <= 0 is not allowed.");
0314:
0315: if ((!sort.equals("MessageSenderName"))
0316: && (!sort.equals("MessageTopic"))
0317: && (!sort.equals("MessageReadStatus"))
0318: && (!sort.equals("MessageAttachCount"))
0319: && (!sort.equals("MessageCreationDate"))) {
0320: throw new IllegalArgumentException(
0321: "Cannot sort, reason: dont understand the criteria '"
0322: + sort + "'.");
0323: }
0324:
0325: if ((!order.equals("ASC")) && (!order.equals("DESC"))) {
0326: throw new IllegalArgumentException(
0327: "Cannot sort, reason: dont understand the order '"
0328: + order + "'.");
0329: }
0330:
0331: if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
0332: return getBeans_inMember_inFolder_withSortSupport_limit_mysql(
0333: memberID, folderName, offset, rowsToReturn, sort,
0334: order, true);
0335: } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
0336: return getBeans_inMember_inFolder_withSortSupport_limit_noscroll(
0337: memberID, folderName, offset, rowsToReturn, sort,
0338: order, true);
0339: }
0340: return getBeans_inMember_inFolder_withSortSupport_limit_general(
0341: memberID, folderName, offset, rowsToReturn, sort,
0342: order, true);
0343: }
0344:
0345: /*
0346: * Included columns: MessageID, FolderName, MemberID, MessageSenderID, MessageSenderName,
0347: * MessageToList, MessageCcList, MessageBccList, MessageTopic, MessageBody,
0348: * MessageType, MessageOption, MessageStatus, MessageReadStatus, MessageNotify,
0349: * MessageIcon, MessageAttachCount, MessageIP, MessageCreationDate
0350: * Excluded columns:
0351: */
0352: public Collection getBeans_inMember_inFolder_withSortSupport_limit_mysql(
0353: int memberID, String folderName, int offset,
0354: int rowsToReturn, String sort, String order,
0355: boolean onlyNonPublic) throws DatabaseException {
0356:
0357: // IMPORTANT NOTE: the checking of parameters is moved to method getBeans_inMember_inFolder_withSortSupport_limit
0358: // IF THERE ARE ANY CHANGES HERE, PLEASE MOVE BACK THE CHECKING OF PARAMETERS
0359:
0360: Connection connection = null;
0361: PreparedStatement statement = null;
0362: ResultSet resultSet = null;
0363: Collection retValue = new ArrayList();
0364: StringBuffer sql = new StringBuffer(512);
0365: sql
0366: .append("SELECT MessageID, FolderName, MemberID, MessageSenderID, MessageSenderName, MessageToList, MessageCcList, MessageBccList, MessageTopic, MessageBody, MessageType, MessageOption, MessageStatus, MessageReadStatus, MessageNotify, MessageIcon, MessageAttachCount, MessageIP, MessageCreationDate");
0367: sql.append(" FROM " + TABLE_NAME);
0368: sql.append(" WHERE MemberID = ?");
0369: sql.append(" AND FolderName = ?");
0370: if (onlyNonPublic) {
0371: sql.append(" AND MessageType <> "
0372: + MessageBean.MESSAGE_TYPE_PUBLIC);
0373: }
0374: sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
0375: sql.append(" LIMIT ?, ?");
0376: try {
0377: connection = DBUtils.getConnection();
0378: statement = connection.prepareStatement(sql.toString());
0379: statement.setInt(1, memberID);
0380: statement.setString(2, folderName);
0381: statement.setInt(3, offset);
0382: statement.setInt(4, rowsToReturn);
0383: resultSet = statement.executeQuery();
0384: while (resultSet.next()) {
0385: MessageBean bean = new MessageBean();
0386: bean.setMessageID(resultSet.getInt("MessageID"));
0387: bean.setFolderName(resultSet.getString("FolderName"));
0388: bean.setMemberID(resultSet.getInt("MemberID"));
0389: bean.setMessageSenderID(resultSet
0390: .getInt("MessageSenderID"));
0391: bean.setMessageSenderName(resultSet
0392: .getString("MessageSenderName"));
0393: bean.setMessageToList(resultSet
0394: .getString("MessageToList"));
0395: bean.setMessageCcList(resultSet
0396: .getString("MessageCcList"));
0397: bean.setMessageBccList(resultSet
0398: .getString("MessageBccList"));
0399: bean.setMessageTopic(resultSet
0400: .getString("MessageTopic"));
0401: bean.setMessageBody(resultSet.getString("MessageBody"));
0402: bean.setMessageType(resultSet.getInt("MessageType"));
0403: bean
0404: .setMessageOption(resultSet
0405: .getInt("MessageOption"));
0406: bean
0407: .setMessageStatus(resultSet
0408: .getInt("MessageStatus"));
0409: bean.setMessageReadStatus(resultSet
0410: .getInt("MessageReadStatus"));
0411: bean
0412: .setMessageNotify(resultSet
0413: .getInt("MessageNotify"));
0414: bean.setMessageIcon(resultSet.getString("MessageIcon"));
0415: bean.setMessageAttachCount(resultSet
0416: .getInt("MessageAttachCount"));
0417: bean.setMessageIP(resultSet.getString("MessageIP"));
0418: bean.setMessageCreationDate(resultSet
0419: .getTimestamp("MessageCreationDate"));
0420: retValue.add(bean);
0421: }
0422: return retValue;
0423: } catch (SQLException sqle) {
0424: log.error("Sql Execution Error!", sqle);
0425: throw new DatabaseException(
0426: "Error executing SQL in MessageDAOImplJDBC.getBeans_inMember_inFolder_withSortSupport_limit_mysql.");
0427: } finally {
0428: DBUtils.closeResultSet(resultSet);
0429: DBUtils.closeStatement(statement);
0430: DBUtils.closeConnection(connection);
0431: }
0432: }
0433:
0434: /*
0435: * Included columns: MessageID, FolderName, MemberID, MessageSenderID, MessageSenderName,
0436: * MessageToList, MessageCcList, MessageBccList, MessageTopic, MessageBody,
0437: * MessageType, MessageOption, MessageStatus, MessageReadStatus, MessageNotify,
0438: * MessageIcon, MessageAttachCount, MessageIP, MessageCreationDate
0439: * Excluded columns:
0440: */
0441: public Collection getBeans_inMember_inFolder_withSortSupport_limit_noscroll(
0442: int memberID, String folderName, int offset,
0443: int rowsToReturn, String sort, String order,
0444: boolean onlyNonPublic) throws DatabaseException {
0445:
0446: // IMPORTANT NOTE: the checking of parameters is moved to method getBeans_inMember_inFolder_withSortSupport_limit
0447: // IF THERE ARE ANY CHANGES HERE, PLEASE MOVE BACK THE CHECKING OF PARAMETERS
0448:
0449: Connection connection = null;
0450: PreparedStatement statement = null;
0451: ResultSet resultSet = null;
0452: Collection retValue = new ArrayList();
0453: StringBuffer sql = new StringBuffer(512);
0454: sql
0455: .append("SELECT MessageID, FolderName, MemberID, MessageSenderID, MessageSenderName, MessageToList, MessageCcList, MessageBccList, MessageTopic, MessageBody, MessageType, MessageOption, MessageStatus, MessageReadStatus, MessageNotify, MessageIcon, MessageAttachCount, MessageIP, MessageCreationDate");
0456: sql.append(" FROM " + TABLE_NAME);
0457: sql.append(" WHERE MemberID = ?");
0458: sql.append(" AND FolderName = ?");
0459: if (onlyNonPublic) {
0460: sql.append(" AND MessageType <> "
0461: + MessageBean.MESSAGE_TYPE_PUBLIC);
0462: }
0463: sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
0464: try {
0465: connection = DBUtils.getConnection();
0466: statement = connection.prepareStatement(sql.toString());
0467: statement.setMaxRows(offset + rowsToReturn);
0468: statement.setInt(1, memberID);
0469: statement.setString(2, folderName);
0470: resultSet = statement.executeQuery();
0471: int rowIndex = -1;
0472: while (resultSet.next()) {
0473: rowIndex++;
0474: if (rowIndex < offset)
0475: continue;
0476: MessageBean bean = new MessageBean();
0477: bean.setMessageID(resultSet.getInt("MessageID"));
0478: bean.setFolderName(resultSet.getString("FolderName"));
0479: bean.setMemberID(resultSet.getInt("MemberID"));
0480: bean.setMessageSenderID(resultSet
0481: .getInt("MessageSenderID"));
0482: bean.setMessageSenderName(resultSet
0483: .getString("MessageSenderName"));
0484: bean.setMessageToList(resultSet
0485: .getString("MessageToList"));
0486: bean.setMessageCcList(resultSet
0487: .getString("MessageCcList"));
0488: bean.setMessageBccList(resultSet
0489: .getString("MessageBccList"));
0490: bean.setMessageTopic(resultSet
0491: .getString("MessageTopic"));
0492: bean.setMessageBody(resultSet.getString("MessageBody"));
0493: bean.setMessageType(resultSet.getInt("MessageType"));
0494: bean
0495: .setMessageOption(resultSet
0496: .getInt("MessageOption"));
0497: bean
0498: .setMessageStatus(resultSet
0499: .getInt("MessageStatus"));
0500: bean.setMessageReadStatus(resultSet
0501: .getInt("MessageReadStatus"));
0502: bean
0503: .setMessageNotify(resultSet
0504: .getInt("MessageNotify"));
0505: bean.setMessageIcon(resultSet.getString("MessageIcon"));
0506: bean.setMessageAttachCount(resultSet
0507: .getInt("MessageAttachCount"));
0508: bean.setMessageIP(resultSet.getString("MessageIP"));
0509: bean.setMessageCreationDate(resultSet
0510: .getTimestamp("MessageCreationDate"));
0511: retValue.add(bean);
0512: if (retValue.size() == rowsToReturn)
0513: break;// Fix the Sybase bug
0514: }
0515: return retValue;
0516: } catch (SQLException sqle) {
0517: log.error("Sql Execution Error!", sqle);
0518: throw new DatabaseException(
0519: "Error executing SQL in MessageDAOImplJDBC.getBeans_inMember_inFolder_withSortSupport_limit_noscroll.");
0520: } finally {
0521: DBUtils.closeResultSet(resultSet);
0522: DBUtils.resetStatement(statement);
0523: DBUtils.closeStatement(statement);
0524: DBUtils.closeConnection(connection);
0525: }
0526: }
0527:
0528: /*
0529: * Included columns: MessageID, FolderName, MemberID, MessageSenderID, MessageSenderName,
0530: * MessageToList, MessageCcList, MessageBccList, MessageTopic, MessageBody,
0531: * MessageType, MessageOption, MessageStatus, MessageReadStatus, MessageNotify,
0532: * MessageIcon, MessageAttachCount, MessageIP, MessageCreationDate
0533: * Excluded columns:
0534: */
0535: public Collection getBeans_inMember_inFolder_withSortSupport_limit_general(
0536: int memberID, String folderName, int offset,
0537: int rowsToReturn, String sort, String order,
0538: boolean onlyNonPublic) throws DatabaseException {
0539:
0540: // IMPORTANT NOTE: the checking of parameters is moved to method getBeans_inMember_inFolder_withSortSupport_limit
0541: // IF THERE ARE ANY CHANGES HERE, PLEASE MOVE BACK THE CHECKING OF PARAMETERS
0542:
0543: Connection connection = null;
0544: PreparedStatement statement = null;
0545: ResultSet resultSet = null;
0546: Collection retValue = new ArrayList();
0547: StringBuffer sql = new StringBuffer(512);
0548: sql
0549: .append("SELECT MessageID, FolderName, MemberID, MessageSenderID, MessageSenderName, MessageToList, MessageCcList, MessageBccList, MessageTopic, MessageBody, MessageType, MessageOption, MessageStatus, MessageReadStatus, MessageNotify, MessageIcon, MessageAttachCount, MessageIP, MessageCreationDate");
0550: sql.append(" FROM " + TABLE_NAME);
0551: sql.append(" WHERE MemberID = ?");
0552: sql.append(" AND FolderName = ?");
0553: if (onlyNonPublic) {
0554: sql.append(" AND MessageType <> "
0555: + MessageBean.MESSAGE_TYPE_PUBLIC);
0556: }
0557: sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
0558: try {
0559: connection = DBUtils.getConnection();
0560: statement = connection.prepareStatement(sql.toString(),
0561: ResultSet.TYPE_SCROLL_INSENSITIVE,
0562: ResultSet.CONCUR_READ_ONLY);
0563: statement.setInt(1, memberID);
0564: statement.setString(2, folderName);
0565: statement.setMaxRows(offset + rowsToReturn);
0566: try {
0567: statement.setFetchSize(Math.min(rowsToReturn,
0568: DBUtils.MAX_FETCH_SIZE));
0569: } catch (SQLException sqle) {
0570: //do nothing, postgreSQL does not support this method
0571: }
0572: resultSet = statement.executeQuery();
0573: boolean loop = resultSet.absolute(offset + 1);// the absolute method begin with 1 instead of 0 as in the LIMIT clause
0574: while (loop) {
0575: MessageBean bean = new MessageBean();
0576: bean.setMessageID(resultSet.getInt("MessageID"));
0577: bean.setFolderName(resultSet.getString("FolderName"));
0578: bean.setMemberID(resultSet.getInt("MemberID"));
0579: bean.setMessageSenderID(resultSet
0580: .getInt("MessageSenderID"));
0581: bean.setMessageSenderName(resultSet
0582: .getString("MessageSenderName"));
0583: bean.setMessageToList(resultSet
0584: .getString("MessageToList"));
0585: bean.setMessageCcList(resultSet
0586: .getString("MessageCcList"));
0587: bean.setMessageBccList(resultSet
0588: .getString("MessageBccList"));
0589: bean.setMessageTopic(resultSet
0590: .getString("MessageTopic"));
0591: bean.setMessageBody(resultSet.getString("MessageBody"));
0592: bean.setMessageType(resultSet.getInt("MessageType"));
0593: bean
0594: .setMessageOption(resultSet
0595: .getInt("MessageOption"));
0596: bean
0597: .setMessageStatus(resultSet
0598: .getInt("MessageStatus"));
0599: bean.setMessageReadStatus(resultSet
0600: .getInt("MessageReadStatus"));
0601: bean
0602: .setMessageNotify(resultSet
0603: .getInt("MessageNotify"));
0604: bean.setMessageIcon(resultSet.getString("MessageIcon"));
0605: bean.setMessageAttachCount(resultSet
0606: .getInt("MessageAttachCount"));
0607: bean.setMessageIP(resultSet.getString("MessageIP"));
0608: bean.setMessageCreationDate(resultSet
0609: .getTimestamp("MessageCreationDate"));
0610: retValue.add(bean);
0611: if (retValue.size() == rowsToReturn)
0612: break;// Fix the Sybase bug
0613: loop = resultSet.next();
0614: }
0615: return retValue;
0616: } catch (SQLException sqle) {
0617: log.error("Sql Execution Error!", sqle);
0618: throw new DatabaseException(
0619: "Error executing SQL in MessageDAOImplJDBC.getBeans_inMember_inFolder_withSortSupport_limit_general.");
0620: } finally {
0621: DBUtils.closeResultSet(resultSet);
0622: DBUtils.resetStatement(statement);
0623: DBUtils.closeStatement(statement);
0624: DBUtils.closeConnection(connection);
0625: }
0626: }
0627:
0628: /*
0629: * Included columns: MessageID, FolderName, MemberID, MessageSenderID, MessageSenderName,
0630: * MessageToList, MessageCcList, MessageBccList, MessageTopic, MessageBody,
0631: * MessageType, MessageOption, MessageStatus, MessageReadStatus, MessageNotify,
0632: * MessageIcon, MessageAttachCount, MessageIP, MessageCreationDate
0633: * Excluded columns:
0634: */
0635: public Collection getPublicMessages() throws DatabaseException {
0636:
0637: // IMPORTANT NOTE: the checking of parameters is moved to method getBeans_inMember_inFolder_withSortSupport_limit
0638: // IF THERE ARE ANY CHANGES HERE, PLEASE MOVE BACK THE CHECKING OF PARAMETERS
0639:
0640: Connection connection = null;
0641: Statement statement = null;
0642: ResultSet resultSet = null;
0643: Collection retValue = new ArrayList();
0644: StringBuffer sql = new StringBuffer(512);
0645: sql
0646: .append("SELECT MessageID, FolderName, MemberID, MessageSenderID, MessageSenderName, MessageToList, MessageCcList, MessageBccList, MessageTopic, MessageBody, MessageType, MessageOption, MessageStatus, MessageReadStatus, MessageNotify, MessageIcon, MessageAttachCount, MessageIP, MessageCreationDate");
0647: sql.append(" FROM " + TABLE_NAME);
0648: sql.append(" WHERE MessageType = "
0649: + MessageBean.MESSAGE_TYPE_PUBLIC);
0650: sql.append(" ORDER BY MessageCreationDate DESC");
0651: try {
0652: connection = DBUtils.getConnection();
0653: statement = connection.createStatement();
0654: resultSet = statement.executeQuery(sql.toString());
0655: while (resultSet.next()) {
0656: MessageBean bean = new MessageBean();
0657: bean.setMessageID(resultSet.getInt("MessageID"));
0658: bean.setFolderName(resultSet.getString("FolderName"));
0659: bean.setMemberID(resultSet.getInt("MemberID"));
0660: bean.setMessageSenderID(resultSet
0661: .getInt("MessageSenderID"));
0662: bean.setMessageSenderName(resultSet
0663: .getString("MessageSenderName"));
0664: bean.setMessageToList(resultSet
0665: .getString("MessageToList"));
0666: bean.setMessageCcList(resultSet
0667: .getString("MessageCcList"));
0668: bean.setMessageBccList(resultSet
0669: .getString("MessageBccList"));
0670: bean.setMessageTopic(resultSet
0671: .getString("MessageTopic"));
0672: bean.setMessageBody(resultSet.getString("MessageBody"));
0673: bean.setMessageType(resultSet.getInt("MessageType"));
0674: bean
0675: .setMessageOption(resultSet
0676: .getInt("MessageOption"));
0677: bean
0678: .setMessageStatus(resultSet
0679: .getInt("MessageStatus"));
0680: bean.setMessageReadStatus(resultSet
0681: .getInt("MessageReadStatus"));
0682: bean
0683: .setMessageNotify(resultSet
0684: .getInt("MessageNotify"));
0685: bean.setMessageIcon(resultSet.getString("MessageIcon"));
0686: bean.setMessageAttachCount(resultSet
0687: .getInt("MessageAttachCount"));
0688: bean.setMessageIP(resultSet.getString("MessageIP"));
0689: bean.setMessageCreationDate(resultSet
0690: .getTimestamp("MessageCreationDate"));
0691: retValue.add(bean);
0692: }
0693: return retValue;
0694: } catch (SQLException sqle) {
0695: log.error("Sql Execution Error!", sqle);
0696: throw new DatabaseException(
0697: "Error executing SQL in MessageDAOImplJDBC.getPublicMessages.");
0698: } finally {
0699: DBUtils.closeResultSet(resultSet);
0700: DBUtils.resetStatement(statement);
0701: DBUtils.closeStatement(statement);
0702: DBUtils.closeConnection(connection);
0703: }
0704: }
0705:
0706: /*
0707: * Included columns: MessageID, FolderName, MemberID, MessageSenderID, MessageSenderName,
0708: * MessageToList, MessageCcList, MessageBccList, MessageTopic, MessageBody,
0709: * MessageType, MessageOption, MessageStatus, MessageReadStatus, MessageNotify,
0710: * MessageIcon, MessageAttachCount, MessageIP, MessageCreationDate
0711: * Excluded columns:
0712: */
0713: public MessageBean getMessage(int messageID)
0714: throws ObjectNotFoundException, DatabaseException {
0715:
0716: Connection connection = null;
0717: PreparedStatement statement = null;
0718: ResultSet resultSet = null;
0719: StringBuffer sql = new StringBuffer(512);
0720: sql
0721: .append("SELECT MessageID, FolderName, MemberID, MessageSenderID, MessageSenderName, MessageToList, MessageCcList, MessageBccList, MessageTopic, MessageBody, MessageType, MessageOption, MessageStatus, MessageReadStatus, MessageNotify, MessageIcon, MessageAttachCount, MessageIP, MessageCreationDate");
0722: sql.append(" FROM " + TABLE_NAME);
0723: sql.append(" WHERE MessageID = ?");
0724: try {
0725: connection = DBUtils.getConnection();
0726: statement = connection.prepareStatement(sql.toString());
0727: statement.setInt(1, messageID);
0728: resultSet = statement.executeQuery();
0729: if (!resultSet.next()) {
0730: throw new ObjectNotFoundException(
0731: "Cannot find the row in table Message where primary key = ("
0732: + messageID + ").");
0733: }
0734:
0735: MessageBean bean = new MessageBean();
0736: // @todo: uncomment the following line(s) as needed
0737: //bean.setMessageID(messageID);
0738: bean.setMessageID(resultSet.getInt("MessageID"));
0739: bean.setFolderName(resultSet.getString("FolderName"));
0740: bean.setMemberID(resultSet.getInt("MemberID"));
0741: bean
0742: .setMessageSenderID(resultSet
0743: .getInt("MessageSenderID"));
0744: bean.setMessageSenderName(resultSet
0745: .getString("MessageSenderName"));
0746: bean.setMessageToList(resultSet.getString("MessageToList"));
0747: bean.setMessageCcList(resultSet.getString("MessageCcList"));
0748: bean.setMessageBccList(resultSet
0749: .getString("MessageBccList"));
0750: bean.setMessageTopic(resultSet.getString("MessageTopic"));
0751: bean.setMessageBody(resultSet.getString("MessageBody"));
0752: bean.setMessageType(resultSet.getInt("MessageType"));
0753: bean.setMessageOption(resultSet.getInt("MessageOption"));
0754: bean.setMessageStatus(resultSet.getInt("MessageStatus"));
0755: bean.setMessageReadStatus(resultSet
0756: .getInt("MessageReadStatus"));
0757: bean.setMessageNotify(resultSet.getInt("MessageNotify"));
0758: bean.setMessageIcon(resultSet.getString("MessageIcon"));
0759: bean.setMessageAttachCount(resultSet
0760: .getInt("MessageAttachCount"));
0761: bean.setMessageIP(resultSet.getString("MessageIP"));
0762: bean.setMessageCreationDate(resultSet
0763: .getTimestamp("MessageCreationDate"));
0764: return bean;
0765: } catch (SQLException sqle) {
0766: log.error("Sql Execution Error!", sqle);
0767: throw new DatabaseException(
0768: "Error executing SQL in MessageDAOImplJDBC.getMessage(pk).");
0769: } finally {
0770: DBUtils.closeResultSet(resultSet);
0771: DBUtils.closeStatement(statement);
0772: DBUtils.closeConnection(connection);
0773: }
0774: }
0775:
0776: public int getNumberOfNonPublicMessages_inMember(int memberID)
0777: throws DatabaseException {
0778:
0779: Connection connection = null;
0780: PreparedStatement statement = null;
0781: ResultSet resultSet = null;
0782: StringBuffer sql = new StringBuffer(512);
0783: sql.append("SELECT Count(*)");
0784: sql.append(" FROM " + TABLE_NAME);
0785: sql.append(" WHERE MemberID = ?");
0786: boolean onlyNonPublic = true;
0787: if (onlyNonPublic) {
0788: sql.append(" AND MessageType <> "
0789: + MessageBean.MESSAGE_TYPE_PUBLIC);
0790: }
0791: try {
0792: connection = DBUtils.getConnection();
0793: statement = connection.prepareStatement(sql.toString());
0794: statement.setInt(1, memberID);
0795: resultSet = statement.executeQuery();
0796: AssertionUtil
0797: .doAssert(resultSet.next(),
0798: "Assertion in MessageDAOImplJDBC.getNumberOfMessages_inMember.");
0799: return resultSet.getInt(1);
0800: } catch (SQLException sqle) {
0801: log.error("Sql Execution Error!", sqle);
0802: throw new DatabaseException(
0803: "Error executing SQL in MessageDAOImplJDBC.getNumberOfMessages_inMember.");
0804: } finally {
0805: DBUtils.closeResultSet(resultSet);
0806: DBUtils.closeStatement(statement);
0807: DBUtils.closeConnection(connection);
0808: }
0809: }
0810:
0811: public void updateMessageReadStatus(int messageID, // primary key
0812: int memberID, int messageReadStatus)
0813: throws ObjectNotFoundException, DatabaseException {
0814:
0815: Connection connection = null;
0816: PreparedStatement statement = null;
0817: StringBuffer sql = new StringBuffer(512);
0818: sql.append("UPDATE " + TABLE_NAME
0819: + " SET MessageReadStatus = ?");
0820: sql.append(" WHERE MessageID = ?");
0821: sql.append(" AND MemberID = ?");
0822: try {
0823: connection = DBUtils.getConnection();
0824: statement = connection.prepareStatement(sql.toString());
0825:
0826: // // column(s) to update
0827: statement.setInt(1, messageReadStatus);
0828: // primary key column(s)
0829: statement.setInt(2, messageID);
0830:
0831: statement.setInt(3, memberID);
0832:
0833: if (statement.executeUpdate() != 1) {
0834: throw new ObjectNotFoundException(
0835: "Cannot update table Message where primary key = ("
0836: + messageID + ") and MemberID = "
0837: + memberID + ".");
0838: }
0839: m_dirty = true;
0840: } catch (SQLException sqle) {
0841: log.error("Sql Execution Error!", sqle);
0842: throw new DatabaseException(
0843: "Error executing SQL in MessageDAOImplJDBC.updateMessageReadStatus.");
0844: } finally {
0845: DBUtils.closeStatement(statement);
0846: DBUtils.closeConnection(connection);
0847: }
0848: }
0849:
0850: //@todo: should we update also based on MemberID ???
0851: public void updateAttachCount(int messageID, int messageAttachCount)
0852: throws ObjectNotFoundException, DatabaseException {
0853:
0854: Connection connection = null;
0855: PreparedStatement statement = null;
0856: StringBuffer sql = new StringBuffer(512);
0857: sql.append("UPDATE " + TABLE_NAME
0858: + " SET MessageAttachCount = ?");
0859: sql.append(" WHERE MessageID = ?");
0860: try {
0861: connection = DBUtils.getConnection();
0862: statement = connection.prepareStatement(sql.toString());
0863:
0864: // // column(s) to update
0865: statement.setInt(1, messageAttachCount);
0866:
0867: // primary key column(s)
0868: statement.setInt(2, messageID);
0869:
0870: if (statement.executeUpdate() != 1) {
0871: throw new ObjectNotFoundException(
0872: "Cannot update AttachCount in table Message where primary key = ("
0873: + messageID + ").");
0874: }
0875: m_dirty = true;
0876: } catch (SQLException sqle) {
0877: log.error("Sql Execution Error!", sqle);
0878: throw new DatabaseException(
0879: "Error executing SQL in MessageDAOImplJDBC.updateAttachCount.");
0880: } finally {
0881: DBUtils.closeStatement(statement);
0882: DBUtils.closeConnection(connection);
0883: }
0884: }
0885:
0886: public void updateFolderName(int messageID, // primary key
0887: int memberID, String folderName)
0888: throws ObjectNotFoundException, DatabaseException {
0889:
0890: Connection connection = null;
0891: PreparedStatement statement = null;
0892: StringBuffer sql = new StringBuffer(512);
0893: sql.append("UPDATE " + TABLE_NAME + " SET FolderName = ?");
0894: sql.append(" WHERE MessageID = ? AND MemberID= ?");
0895: try {
0896: connection = DBUtils.getConnection();
0897: statement = connection.prepareStatement(sql.toString());
0898:
0899: // // column(s) to update
0900: statement.setString(1, folderName);
0901: // primary key column(s)
0902: statement.setInt(2, messageID);
0903: statement.setInt(3, memberID);
0904:
0905: if (statement.executeUpdate() != 1) {
0906: throw new ObjectNotFoundException(
0907: "Cannot update table Message where primary key = ("
0908: + messageID + ").");
0909: }
0910: m_dirty = true;
0911: } catch (SQLException sqle) {
0912: log.error("Sql Execution Error!", sqle);
0913: throw new DatabaseException(
0914: "Error executing SQL in MessageDAOImplJDBC.updateFolderName.");
0915: } finally {
0916: DBUtils.closeStatement(statement);
0917: DBUtils.closeConnection(connection);
0918: }
0919: }
0920:
0921: public void deleteMessage(int messageID, int memberID)
0922: throws DatabaseException, ObjectNotFoundException {
0923:
0924: Connection connection = null;
0925: PreparedStatement statement = null;
0926: StringBuffer sql = new StringBuffer(512);
0927: sql.append("DELETE FROM " + TABLE_NAME);
0928: sql.append(" WHERE MessageID = ?");
0929: sql.append(" AND MemberID = ?");
0930:
0931: try {
0932: connection = DBUtils.getConnection();
0933: statement = connection.prepareStatement(sql.toString());
0934: statement.setInt(1, messageID);
0935: statement.setInt(2, memberID);
0936: if (statement.executeUpdate() != 1) {
0937: throw new ObjectNotFoundException(
0938: "Cannot delete a row in table Message where primary key = ("
0939: + messageID + ") and MemberID = "
0940: + memberID + ".");
0941: }
0942: m_dirty = true;
0943: } catch (SQLException sqle) {
0944: log.error("Sql Execution Error!", sqle);
0945: throw new DatabaseException(
0946: "Error executing SQL in MessageDAOImplJDBC.deleteMessage.");
0947: } finally {
0948: DBUtils.closeStatement(statement);
0949: DBUtils.closeConnection(connection);
0950: }
0951: }
0952:
0953: public void deleteSenderMessages(int senderID)
0954: throws DatabaseException {
0955:
0956: Connection connection = null;
0957: PreparedStatement statement = null;
0958: StringBuffer sql = new StringBuffer(512);
0959: sql.append("DELETE FROM " + TABLE_NAME);
0960: sql.append(" WHERE MessageSenderID = ?");
0961:
0962: try {
0963: connection = DBUtils.getConnection();
0964: statement = connection.prepareStatement(sql.toString());
0965: statement.setInt(1, senderID);
0966:
0967: statement.executeUpdate();
0968: m_dirty = true;
0969: } catch (SQLException sqle) {
0970: log.error("Sql Execution Error!", sqle);
0971: throw new DatabaseException(
0972: "Error executing SQL in MessageDAOImplJDBC.deleteSenderMessages.");
0973: } finally {
0974: DBUtils.closeStatement(statement);
0975: DBUtils.closeConnection(connection);
0976: }
0977: }
0978:
0979: public void deleteMessages_inFolderName_inMember(String folderName,
0980: int memberID) throws DatabaseException {
0981:
0982: Connection connection = null;
0983: PreparedStatement statement = null;
0984: StringBuffer sql = new StringBuffer(512);
0985: sql.append("DELETE FROM " + TABLE_NAME);
0986: sql.append(" WHERE FolderName = ?");
0987: sql.append(" AND MemberID = ?");
0988:
0989: try {
0990: connection = DBUtils.getConnection();
0991: statement = connection.prepareStatement(sql.toString());
0992: statement.setString(1, folderName);
0993: statement.setInt(2, memberID);
0994:
0995: statement.executeUpdate();
0996:
0997: m_dirty = true;
0998: } catch (SQLException sqle) {
0999: log.error("Sql Execution Error!", sqle);
1000: throw new DatabaseException(
1001: "Error executing SQL in MessageDAOImplJDBC.deleteMessages_inFolderName_inMember.");
1002: } finally {
1003: DBUtils.closeStatement(statement);
1004: DBUtils.closeConnection(connection);
1005: }
1006:
1007: }
1008:
1009: public int getNumberOfNonPublicMessages_inMember_inFolder(
1010: int memberID, String folderName) throws DatabaseException {
1011: // get the number of message in folder "folderName" and belong to "memberID"
1012: return getNumberOfMessages_inMember_inFolder(memberID,
1013: folderName, false, true);
1014: }
1015:
1016: public int getNumberOfUnreadNonPublicMessages_inMember_inFolder(
1017: int memberID, String folderName) throws DatabaseException {
1018: // get the number of unread message in folder "folderName" and belong to "memberID"
1019: return getNumberOfMessages_inMember_inFolder(memberID,
1020: folderName, true, true);
1021: }
1022:
1023: public int getNumberOfAllMessages_inMember_inFolder(int memberID,
1024: String folderName) throws DatabaseException {
1025: // get the number of message in folder "folderName" and belong to "memberID"
1026: return getNumberOfMessages_inMember_inFolder(memberID,
1027: folderName, false, false);
1028: }
1029:
1030: public int getNumberOfUnreadAllMessages_inMember_inFolder(
1031: int memberID, String folderName) throws DatabaseException {
1032: // get the number of unread message in folder "folderName" and belong to "memberID"
1033: return getNumberOfMessages_inMember_inFolder(memberID,
1034: folderName, true, false);
1035: }
1036:
1037: // if unread == true get the number of unread message in "folderName" and belong to "memberID"
1038: // else get the number of message in "folderName"
1039: private int getNumberOfMessages_inMember_inFolder(int memberID,
1040: String folderName, boolean unread, boolean onlyNonPublic)
1041: throws DatabaseException {
1042:
1043: Connection connection = null;
1044: PreparedStatement statement = null;
1045: ResultSet resultSet = null;
1046: StringBuffer sql = new StringBuffer(512);
1047: sql.append("SELECT Count(*)");
1048: sql.append(" FROM " + TABLE_NAME);
1049: sql.append(" WHERE FolderName = ? AND MemberID = ? ");
1050: if (unread) {
1051: sql.append(" AND MessageReadStatus = 0");
1052: }
1053: if (onlyNonPublic) {
1054: sql.append(" AND MessageType <> "
1055: + MessageBean.MESSAGE_TYPE_PUBLIC);
1056: }
1057: try {
1058: connection = DBUtils.getConnection();
1059: statement = connection.prepareStatement(sql.toString());
1060: statement.setString(1, folderName);
1061: statement.setInt(2, memberID);
1062: resultSet = statement.executeQuery();
1063: AssertionUtil
1064: .doAssert(
1065: resultSet.next(),
1066: "Assertion in MessageDAOImplJDBC.getNumberOfMessages_inMember_inFolder(flagUnread, onlyNonPublic).");
1067: return resultSet.getInt(1);
1068: } catch (SQLException sqle) {
1069: log.error("Sql Execution Error!", sqle);
1070: throw new DatabaseException(
1071: "Error executing SQL in MessageDAOImplJDBC.getNumberOfMessages_inMember_inFolder(flagUnread, onlyNonPublic).");
1072: } finally {
1073: DBUtils.closeResultSet(resultSet);
1074: DBUtils.closeStatement(statement);
1075: DBUtils.closeConnection(connection);
1076: }
1077: }
1078: }
|