attachmentdaoimpljdbc.java

java servlet著名论坛源代码

            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new AssertionException("Assertion in AttachmentDAOImplJDBC.getNumberOfBeans.");
            }
            return resultSet.getInt(1);
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.getNumberOfBeans.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public int getNumberOfBeans_inPost(int postID)
        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 PostID = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, postID);
            resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new AssertionException("Assertion in AttachmentDAOImplJDBC.getNumberOfBeans_inPost.");
            }
            return resultSet.getInt(1);
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.getNumberOfBeans_inPost.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /************************************************
     * Customized methods come below
     ************************************************/

    /**
     * This is a customized method
     */
    protected static int findAttachID(int postID, int memberID, Timestamp attachCreationDate)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT AttachID");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE PostID = ? AND MemberID = ? AND AttachCreationDate = ? ");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, postID);
            statement.setInt(2, memberID);
            statement.setTimestamp(3, attachCreationDate);
            resultSet = statement.executeQuery();
            if(!resultSet.next()) {
                throw new ObjectNotFoundException("Cannot find the AttachID in table Attachment.");
            }

            return resultSet.getInt("AttachID");
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.findAttachID.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public void delete_inPost(int postID)
        throws DatabaseException {

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

        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, postID);
            statement.executeUpdate();
            m_dirty = true;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.delete_inPost.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: AttachID, PostID, MemberID, AttachFilename, AttachFileSize,
     *                   AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate,
     *                   AttachDownloadCount, AttachOption, AttachStatus
     * Excluded columns:
     */
    public Collection getBeans_inPost(int postID)
        throws DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Collection retValue = new ArrayList();
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT AttachID, PostID, MemberID, AttachFilename, AttachFileSize, AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount, AttachOption, AttachStatus");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE PostID = ?");
        sql.append(" ORDER BY AttachID ASC ");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, postID);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                AttachmentBean bean = new AttachmentBean();
                bean.setAttachID(resultSet.getInt("AttachID"));
                bean.setPostID(resultSet.getInt("PostID"));
                bean.setMemberID(resultSet.getInt("MemberID"));
                bean.setAttachFilename(resultSet.getString("AttachFilename"));
                bean.setAttachFileSize(resultSet.getInt("AttachFileSize"));
                bean.setAttachMimeType(resultSet.getString("AttachMimeType"));
                bean.setAttachDesc(resultSet.getString("AttachDesc"));
                bean.setAttachCreationIP(resultSet.getString("AttachCreationIP"));
                bean.setAttachCreationDate(resultSet.getTimestamp("AttachCreationDate"));
                bean.setAttachModifiedDate(resultSet.getTimestamp("AttachModifiedDate"));
                bean.setAttachDownloadCount(resultSet.getInt("AttachDownloadCount"));
                bean.setAttachOption(resultSet.getInt("AttachOption"));
                bean.setAttachStatus(resultSet.getInt("AttachStatus"));
                retValue.add(bean);
            }
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.getBeans_inPost.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: AttachID, PostID, MemberID, AttachFilename, AttachFileSize,
     *                   AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate,
     *                   AttachDownloadCount, AttachOption, AttachStatus
     * Excluded columns:
     */
    public Collection getBeans_inThread(int threadID)
        throws DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Collection retValue = new ArrayList();
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT AttachID, attachment.PostID, attachment.MemberID, AttachFilename, AttachFileSize, AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount, AttachOption, AttachStatus");
        sql.append(" FROM " + TABLE_NAME + " attachment, " + PostDAO.TABLE_NAME + " post ");
        sql.append(" WHERE attachment.PostID = post.PostID AND post.ThreadID = ? ");
        sql.append(" ORDER BY AttachID ASC ");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, threadID);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                AttachmentBean bean = new AttachmentBean();
                bean.setAttachID(resultSet.getInt("AttachID"));
                bean.setPostID(resultSet.getInt("PostID"));
                bean.setMemberID(resultSet.getInt("MemberID"));
                bean.setAttachFilename(resultSet.getString("AttachFilename"));
                bean.setAttachFileSize(resultSet.getInt("AttachFileSize"));
                bean.setAttachMimeType(resultSet.getString("AttachMimeType"));
                bean.setAttachDesc(resultSet.getString("AttachDesc"));
                bean.setAttachCreationIP(resultSet.getString("AttachCreationIP"));
                bean.setAttachCreationDate(resultSet.getTimestamp("AttachCreationDate"));
                bean.setAttachModifiedDate(resultSet.getTimestamp("AttachModifiedDate"));
                bean.setAttachDownloadCount(resultSet.getInt("AttachDownloadCount"));
                bean.setAttachOption(resultSet.getInt("AttachOption"));
                bean.setAttachStatus(resultSet.getInt("AttachStatus"));
                retValue.add(bean);
            }
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.getBeans_inThread.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: AttachID, PostID, MemberID, AttachFilename, AttachFileSize,
     *                   AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate,
     *                   AttachDownloadCount, AttachOption, AttachStatus
     * Excluded columns:
     */
    public Collection getBeans_inForum(int forumID)
        throws DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Collection retValue = new ArrayList();
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT AttachID, attachment.PostID, attachment.MemberID, AttachFilename, AttachFileSize, AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount, AttachOption, AttachStatus");
        sql.append(" FROM " + TABLE_NAME + " attachment, " + PostDAO.TABLE_NAME + " post ");
        sql.append(" WHERE attachment.PostID = post.PostID AND post.ForumID = ? ");
        sql.append(" ORDER BY AttachID ASC ");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, forumID);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                AttachmentBean bean = new AttachmentBean();
                bean.setAttachID(resultSet.getInt("AttachID"));
                bean.setPostID(resultSet.getInt("PostID"));
                bean.setMemberID(resultSet.getInt("MemberID"));
                bean.setAttachFilename(resultSet.getString("AttachFilename"));
                bean.setAttachFileSize(resultSet.getInt("AttachFileSize"));
                bean.setAttachMimeType(resultSet.getString("AttachMimeType"));
                bean.setAttachDesc(resultSet.getString("AttachDesc"));
                bean.setAttachCreationIP(resultSet.getString("AttachCreationIP"));
                bean.setAttachCreationDate(resultSet.getTimestamp("AttachCreationDate"));
                bean.setAttachModifiedDate(resultSet.getTimestamp("AttachModifiedDate"));
                bean.setAttachDownloadCount(resultSet.getInt("AttachDownloadCount"));
                bean.setAttachOption(resultSet.getInt("AttachOption"));
                bean.setAttachStatus(resultSet.getInt("AttachStatus"));
                retValue.add(bean);
            }
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.getBeans_inForum.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /**
     * This method should be call only when we can make sure that postID is in database
     */
    public void increaseDownloadCount(int attachID)
        throws DatabaseException, ObjectNotFoundException {

        Connection connection = null;
        PreparedStatement statement = null;
        String sql = "UPDATE " + TABLE_NAME + " SET AttachDownloadCount = AttachDownloadCount + 1 WHERE AttachID = ?";
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setInt(1, attachID);
            if (statement.executeUpdate() != 1) {
                throw new ObjectNotFoundException("Cannot update the AttachDownloadCount in table Attachment. Please contact Web site Administrator.");
            }
            //@todo: coi lai cho nay
            // ATTENTION !!!
            setDirty(true);
        } catch (SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error occured when update Attachment: column name = AttachDownloadCount.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

}// end of class AttachmentDAOImplJDBC