messagedaoimpljdbc.java

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

            if (statement.executeUpdate() != 1) {
                throw new ObjectNotFoundException("Cannot update table Message where primary key = (" + messageID + ") and MemberID = " + memberID + ".");
            }
            m_dirty = true;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.updateMessageReadStatus.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    //@todo: should we update also based on MemberID ???
    public void updateAttachCount(int messageID, int messageAttachCount)
        throws ObjectNotFoundException, DatabaseException {

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

            // // column(s) to update
            statement.setInt(1, messageAttachCount);

            // primary key column(s)
            statement.setInt(2, messageID);

            if (statement.executeUpdate() != 1) {
                throw new ObjectNotFoundException("Cannot update AttachCount in table Message where primary key = (" + messageID + ").");
            }
            m_dirty = true;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.updateAttachCount.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

   public void updateFolderName(int messageID, // primary key
                                int memberID, String folderName)
        throws ObjectNotFoundException, DatabaseException {

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

            // // column(s) to update
            statement.setString(1, folderName);
            // primary key column(s)
            statement.setInt(2, messageID);
            statement.setInt(3, memberID);

            if (statement.executeUpdate() != 1) {
                throw new ObjectNotFoundException("Cannot update table Message where primary key = (" + messageID + ").");
            }
            m_dirty = true;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.updateFolderName.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public void deleteMessage(int messageID, int memberID)
        throws DatabaseException, ObjectNotFoundException {

        Connection connection = null;
        PreparedStatement statement = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("DELETE FROM " + TABLE_NAME);
        sql.append(" WHERE MessageID = ?");
        sql.append(" AND MemberID = ?");

        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, messageID);
            statement.setInt(2, memberID);
            if (statement.executeUpdate() != 1) {
                throw new ObjectNotFoundException("Cannot delete a row in table Message where primary key = (" + messageID + ") and MemberID = " + memberID + ".");
            }
            m_dirty = true;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.deleteMessage.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public void deleteSenderMessages(int senderID)
        throws DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("DELETE FROM " + TABLE_NAME);
        sql.append(" WHERE MessageSenderID = ?");

        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, senderID);

            statement.executeUpdate();
            m_dirty = true;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.deleteSenderMessages.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public void deleteMessages_inFolderName_inMember(String folderName, int memberID)
        throws DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("DELETE FROM " + TABLE_NAME);
        sql.append(" WHERE FolderName = ?");
        sql.append(" AND MemberID = ?");

        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setString(1, folderName);
            statement.setInt(2, memberID);

            statement.executeUpdate();

            m_dirty = true;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.deleteMessages_inFolderName_inMember.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }

    }

    public int getNumberOfNonPublicMessages_inMember_inFolder(int memberID, String folderName)
        throws AssertionException, DatabaseException {
        // get the number of message in folder "folderName" and belong to "memberID"
        return getNumberOfMessages_inMember_inFolder(memberID, folderName, false, true);
    }

    public int getNumberOfUnreadNonPublicMessages_inMember_inFolder(int memberID, String folderName)
        throws AssertionException, DatabaseException {
        // get the number of unread message in folder "folderName" and belong to "memberID"
        return getNumberOfMessages_inMember_inFolder(memberID, folderName, true, true);
    }

    public int getNumberOfAllMessages_inMember_inFolder(int memberID, String folderName)
        throws AssertionException, DatabaseException {
        // get the number of message in folder "folderName" and belong to "memberID"
        return getNumberOfMessages_inMember_inFolder(memberID, folderName, false, false);
    }

    public int getNumberOfUnreadAllMessages_inMember_inFolder(int memberID, String folderName)
        throws AssertionException, DatabaseException {
        // get the number of unread message in folder "folderName" and belong to "memberID"
        return getNumberOfMessages_inMember_inFolder(memberID, folderName, true, false);
    }

    // if unread == true get the number of unread message in "folderName" and belong to "memberID"
    // else get the number of message in "folderName"
    private int getNumberOfMessages_inMember_inFolder(int memberID, String folderName, boolean unread, boolean onlyNonPublic)
        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 FolderName = ? AND MemberID = ? ");
        if (unread) {
            sql.append(" AND MessageReadStatus = 0");
        }
        if (onlyNonPublic) {
            sql.append(" AND MessageType <> " + MessageBean.MESSAGE_TYPE_PUBLIC);
        }
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setString(1, folderName);
            statement.setInt(2, memberID);
            resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new AssertionException("Assertion in MessageDAOImplJDBC.getNumberOfMessages_inMember_inFolder(flagUnread, onlyNonPublic).");
            }
            return resultSet.getInt(1);
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.getNumberOfMessages_inMember_inFolder(flagUnread, onlyNonPublic).");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }
}