messagedaoimpljdbc.java

解觖java技术中后台无法上传数给的情况

        }
        sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            statement.setInt(1, memberID);
            statement.setString(2, folderName);
            statement.setMaxRows(offset + rowsToReturn);
            try {
                statement.setFetchSize(rowsToReturn);
            } catch (SQLException sqle) {
                //do nothing, postgreSQL doesnt support this method
            }
            resultSet = statement.executeQuery();
            boolean loop = resultSet.absolute(offset + 1);// the absolute method begin with 1 instead of 0 as in the LIMIT clause
            while (loop) {
                MessageBean bean = new MessageBean();
                bean.setMessageID(resultSet.getInt("MessageID"));
                bean.setFolderName(resultSet.getString("FolderName"));
                bean.setMemberID(resultSet.getInt("MemberID"));
                bean.setMessageSenderID(resultSet.getInt("MessageSenderID"));
                bean.setMessageSenderName(resultSet.getString("MessageSenderName"));
                bean.setMessageToList(resultSet.getString("MessageToList"));
                bean.setMessageCcList(resultSet.getString("MessageCcList"));
                bean.setMessageBccList(resultSet.getString("MessageBccList"));
                bean.setMessageTopic(resultSet.getString("MessageTopic"));
                bean.setMessageBody(resultSet.getString("MessageBody"));
                bean.setMessageType(resultSet.getInt("MessageType"));
                bean.setMessageOption(resultSet.getInt("MessageOption"));
                bean.setMessageStatus(resultSet.getInt("MessageStatus"));
                bean.setMessageReadStatus(resultSet.getInt("MessageReadStatus"));
                bean.setMessageNotify(resultSet.getInt("MessageNotify"));
                bean.setMessageIcon(resultSet.getString("MessageIcon"));
                bean.setMessageAttachCount(resultSet.getInt("MessageAttachCount"));
                bean.setMessageIP(resultSet.getString("MessageIP"));
                bean.setMessageCreationDate(resultSet.getTimestamp("MessageCreationDate"));
                retValue.add(bean);
                if (retValue.size() == rowsToReturn) break;// Fix the Sybase bug
                loop = resultSet.next();
            }
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.getBeans_inMember_inFolder_withSortSupport_limit_general.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.resetStatement(statement);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: MessageID, FolderName, MemberID, MessageSenderID, MessageSenderName,
     *                   MessageToList, MessageCcList, MessageBccList, MessageTopic, MessageBody,
     *                   MessageType, MessageOption, MessageStatus, MessageReadStatus, MessageNotify,
     *                   MessageIcon, MessageAttachCount, MessageIP, MessageCreationDate
     * Excluded columns:
     */
    public Collection getPublicMessages()
        throws DatabaseException {

        // IMPORTANT NOTE: the checking of parameters is moved to method getBeans_inMember_inFolder_withSortSupport_limit
        // IF THERE ARE ANY CHANGES HERE, PLEASE MOVE BACK THE CHECKING OF PARAMETERS

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        Collection retValue = new ArrayList();
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT MessageID, FolderName, MemberID, MessageSenderID, MessageSenderName, MessageToList, MessageCcList, MessageBccList, MessageTopic, MessageBody, MessageType, MessageOption, MessageStatus, MessageReadStatus, MessageNotify, MessageIcon, MessageAttachCount, MessageIP, MessageCreationDate");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE MessageType = " + MessageBean.MESSAGE_TYPE_PUBLIC);
        sql.append(" ORDER BY MessageCreationDate DESC");
        try {
            connection = DBUtils.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql.toString());
            while (resultSet.next()) {
                MessageBean bean = new MessageBean();
                bean.setMessageID(resultSet.getInt("MessageID"));
                bean.setFolderName(resultSet.getString("FolderName"));
                bean.setMemberID(resultSet.getInt("MemberID"));
                bean.setMessageSenderID(resultSet.getInt("MessageSenderID"));
                bean.setMessageSenderName(resultSet.getString("MessageSenderName"));
                bean.setMessageToList(resultSet.getString("MessageToList"));
                bean.setMessageCcList(resultSet.getString("MessageCcList"));
                bean.setMessageBccList(resultSet.getString("MessageBccList"));
                bean.setMessageTopic(resultSet.getString("MessageTopic"));
                bean.setMessageBody(resultSet.getString("MessageBody"));
                bean.setMessageType(resultSet.getInt("MessageType"));
                bean.setMessageOption(resultSet.getInt("MessageOption"));
                bean.setMessageStatus(resultSet.getInt("MessageStatus"));
                bean.setMessageReadStatus(resultSet.getInt("MessageReadStatus"));
                bean.setMessageNotify(resultSet.getInt("MessageNotify"));
                bean.setMessageIcon(resultSet.getString("MessageIcon"));
                bean.setMessageAttachCount(resultSet.getInt("MessageAttachCount"));
                bean.setMessageIP(resultSet.getString("MessageIP"));
                bean.setMessageCreationDate(resultSet.getTimestamp("MessageCreationDate"));
                retValue.add(bean);
            }
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.getPublicMessages.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.resetStatement(statement);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: MessageID, FolderName, MemberID, MessageSenderID, MessageSenderName,
     *                   MessageToList, MessageCcList, MessageBccList, MessageTopic, MessageBody,
     *                   MessageType, MessageOption, MessageStatus, MessageReadStatus, MessageNotify,
     *                   MessageIcon, MessageAttachCount, MessageIP, MessageCreationDate
     * Excluded columns:
     */
    public MessageBean getMessage(int messageID)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT MessageID, FolderName, MemberID, MessageSenderID, MessageSenderName, MessageToList, MessageCcList, MessageBccList, MessageTopic, MessageBody, MessageType, MessageOption, MessageStatus, MessageReadStatus, MessageNotify, MessageIcon, MessageAttachCount, MessageIP, MessageCreationDate");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE MessageID = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, messageID);
            resultSet = statement.executeQuery();
            if(!resultSet.next()) {
                throw new ObjectNotFoundException("Cannot find the row in table Message where primary key = (" + messageID + ").");
            }

            MessageBean bean = new MessageBean();
            // @todo: uncomment the following line(s) as needed
            //bean.setMessageID(messageID);
            bean.setMessageID(resultSet.getInt("MessageID"));
            bean.setFolderName(resultSet.getString("FolderName"));
            bean.setMemberID(resultSet.getInt("MemberID"));
            bean.setMessageSenderID(resultSet.getInt("MessageSenderID"));
            bean.setMessageSenderName(resultSet.getString("MessageSenderName"));
            bean.setMessageToList(resultSet.getString("MessageToList"));
            bean.setMessageCcList(resultSet.getString("MessageCcList"));
            bean.setMessageBccList(resultSet.getString("MessageBccList"));
            bean.setMessageTopic(resultSet.getString("MessageTopic"));
            bean.setMessageBody(resultSet.getString("MessageBody"));
            bean.setMessageType(resultSet.getInt("MessageType"));
            bean.setMessageOption(resultSet.getInt("MessageOption"));
            bean.setMessageStatus(resultSet.getInt("MessageStatus"));
            bean.setMessageReadStatus(resultSet.getInt("MessageReadStatus"));
            bean.setMessageNotify(resultSet.getInt("MessageNotify"));
            bean.setMessageIcon(resultSet.getString("MessageIcon"));
            bean.setMessageAttachCount(resultSet.getInt("MessageAttachCount"));
            bean.setMessageIP(resultSet.getString("MessageIP"));
            bean.setMessageCreationDate(resultSet.getTimestamp("MessageCreationDate"));
            return bean;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.getMessage(pk).");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public int getNumberOfNonPublicMessages_inMember(int memberID)
        throws AssertionException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT Count(*)");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE MemberID = ?");
        boolean onlyNonPublic = true;
        if (onlyNonPublic) {
            sql.append(" AND MessageType <> " + MessageBean.MESSAGE_TYPE_PUBLIC);
        }
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, memberID);
            resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new AssertionException("Assertion in MessageDAOImplJDBC.getNumberOfMessages_inMember.");
            }
            return resultSet.getInt(1);
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.getNumberOfMessages_inMember.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public void updateMessageReadStatus(int messageID, // primary key
                                        int memberID, int messageReadStatus)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("UPDATE " + TABLE_NAME + " SET MessageReadStatus = ?");
        sql.append(" WHERE MessageID = ?");
        sql.append(" AND MemberID = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());

            // // column(s) to update
            statement.setInt(1, messageReadStatus);
            // primary key column(s)
            statement.setInt(2, messageID);

            statement.setInt(3, memberID);