attachmentdaoimpljdbc.java

来自「解觖java技术中后台无法上传数给的情况」· Java 代码 · 共 676 行 · 第 1/3 页

        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.getAttachment(pk).");
        } 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 getAttachments()
        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 "); // @todo: uncomment as needed
        //sql.append(" ORDER BY ColumnName ASC|DESC "); // @todo: uncomment as needed
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            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.getAttachments.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

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

    public int getNumberOfAttachments_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.getNumberOfAttachments_inPost.");
            }
            return resultSet.getInt(1);
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.getNumberOfAttachments_inPost.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

   public int getNumberOfAttachments_inThread(int threadID)
        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 + " attachment , " + PostDAO.TABLE_NAME + " post");
        sql.append(" WHERE attachment.PostID = post.PostID ");
        sql.append(" AND post.ThreadID = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, threadID);
            resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new AssertionException("Assertion in AttachmentDAOImplJDBC.getNumberOfAttachments_inThread.");
            }
            return resultSet.getInt(1);
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.getNumberOfAttachments_inThread.");
        } 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 getAttachments_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 = ?");