postdaoimpljdbc.java

java servlet著名论坛源代码

            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: LastEditMemberName, PostTopic, PostBody, PostLastEditDate, PostLastEditIP,
     *                   PostFormatOption, PostOption, PostStatus, PostIcon
     * Excluded columns: PostID, ParentPostID, ForumID, ThreadID, MemberID,
     *                   MemberName, PostCreationDate, PostCreationIP, PostEditCount, PostAttachCount
     */
    public void update(int postID, // primary key
                        String lastEditMemberName, String postTopic, String postBody,
                        Timestamp postLastEditDate, String postLastEditIP, int postFormatOption,
                        int postOption, int postStatus, String postIcon)
        throws ObjectNotFoundException, DatabaseException, ForeignKeyNotFoundException {

        //if admin allows guests to edit posts
        if ((lastEditMemberName!=null) && (lastEditMemberName.length()>0)) {
            try {
                // @todo: modify the parameter list as needed
                // If this method does not change the foreign key columns, you can comment this block of code.
                DAOFactory.getMemberDAO().findByAlternateKey_MemberName(lastEditMemberName);
            } catch(ObjectNotFoundException e) {
                throw new ForeignKeyNotFoundException("Foreign key refers to table 'Member' does not exist. Cannot update table 'Post'.");
            }
        } else lastEditMemberName=""; //so we don't get 'null' in sql query

        Connection connection = null;
        PreparedStatement statement = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("UPDATE " + TABLE_NAME + " SET LastEditMemberName = ?, PostTopic = ?, PostBody = ?, PostLastEditDate = ?, PostLastEditIP = ?, PostFormatOption = ?, PostOption = ?, PostStatus = ?, PostIcon = ?");
        sql.append(" WHERE PostID = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());

            // // column(s) to update
            statement.setString(1, lastEditMemberName);
            statement.setString(2, postTopic);
            if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
                statement.setCharacterStream(3, new StringReader(postBody), postBody.length());
            } else {
                statement.setString(3, postBody);
            }
            statement.setTimestamp(4, postLastEditDate);
            statement.setString(5, postLastEditIP);
            statement.setInt(6, postFormatOption);
            statement.setInt(7, postOption);
            statement.setInt(8, postStatus);
            statement.setString(9, postIcon);

            // primary key column(s)
            statement.setInt(10, postID);

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

    /*
     * Included columns: PostAttachCount
     * Excluded columns: PostID, ParentPostID, ForumID, ThreadID, MemberID,
     *                   MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate,
     *                   PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption,
     *                   PostOption, PostStatus, PostIcon
     */
    public void updateAttachCount(int postID, // primary key
                                  int postAttachCount)
        throws ObjectNotFoundException, DatabaseException {

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

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

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

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

    /*
     * Included columns: PostStatus
     * Excluded columns: PostID, ParentPostID, ForumID, ThreadID, MemberID,
     *                   MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate,
     *                   PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption,
     *                   PostOption, PostIcon, PostAttachCount
     */
    public void updateStatus(int postID, // primary key
                             int postStatus)
        throws ObjectNotFoundException, DatabaseException {

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

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

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

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

    /*
     * Included columns: ForumID
     */
    public void update_ForumID_inThread(int threadID, int forumID)
        throws DatabaseException, ForeignKeyNotFoundException {

        try {
            // @todo: modify the parameter list as needed
            // If this method does not change the foreign key columns, you can comment this block of code.
            DAOFactory.getForumDAO().findByPrimaryKey(forumID);
        } catch(ObjectNotFoundException e) {
            throw new ForeignKeyNotFoundException("Foreign key refers to table 'Forum' does not exist. Cannot update table 'Post'.");
        }

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

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

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

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

    private int findPostID(int forumID, String memberName, Timestamp postCreationDate)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT PostID");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE ForumID = ? AND MemberName = ? AND PostCreationDate = ? ");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, forumID);
            statement.setString(2, memberName);
            statement.setTimestamp(3, postCreationDate);
            resultSet = statement.executeQuery();
            if(!resultSet.next()) {
                throw new ObjectNotFoundException("Cannot find the PostID in table Post.");
            }

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

    /*
     * Included columns: ParentPostID, ForumID, ThreadID, MemberID, MemberName,
     *                   LastEditMemberName, PostTopic, PostBody, PostCreationDate, PostLastEditDate,
     *                   PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption, PostOption,
     *                   PostStatus, PostIcon, PostAttachCount
     * Excluded columns: PostID
     */
    public PostBean getPost(int postID)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT ParentPostID, ForumID, ThreadID, MemberID, MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate, PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption, PostOption, PostStatus, PostIcon, PostAttachCount");
        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 ObjectNotFoundException("Cannot find the row in table Post where primary key = (" + postID + ").");
            }

            PostBean bean = new PostBean();
            // @todo: uncomment the following line(s) as needed
            bean.setPostID(postID);
            bean.setParentPostID(resultSet.getInt("ParentPostID"));
            bean.setForumID(resultSet.getInt("ForumID"));
            bean.setThreadID(resultSet.getInt("ThreadID"));
            bean.setMemberID(resultSet.getInt("MemberID"));
            bean.setMemberName(resultSet.getString("MemberName"));
            bean.setLastEditMemberName(resultSet.getString("LastEditMemberName"));
            bean.setPostTopic(resultSet.getString("PostTopic"));
            bean.setPostBody(resultSet.getString("PostBody"));
            bean.setPostCreationDate(resultSet.getTimestamp("PostCreationDate"));
            bean.setPostLastEditDate(resultSet.getTimestamp("PostLastEditDate"));
            bean.setPostCreationIP(resultSet.getString("PostCreationIP"));
            bean.setPostLastEditIP(resultSet.getString("PostLastEditIP"));
            bean.setPostEditCount(resultSet.getInt("PostEditCount"));
            bean.setPostFormatOption(resultSet.getInt("PostFormatOption"));
            bean.setPostOption(resultSet.getInt("PostOption"));
            bean.setPostStatus(resultSet.getInt("PostStatus"));
            bean.setPostIcon(resultSet.getString("PostIcon"));
            bean.setPostAttachCount(resultSet.getInt("PostAttachCount"));
            return bean;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.getPost(pk).");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public PostBean getFirstPost_inThread(int threadID)
        throws ObjectNotFoundException, DatabaseException {

        // Note that because the status of the first post are always Enable
        // so that we can safely use the below method
        Collection enablePostBeans = getEnablePosts_inThread_limit(threadID, 0, 1);
        Iterator iter = enablePostBeans.iterator();
        if (iter.hasNext()) {
            PostBean postBean = (PostBean)iter.next();
            return postBean;
        }
        throw new ObjectNotFoundException("Cannot find the first post in thread = " + threadID);
    }

    public Collection getEnablePosts_inThread_limit(int threadID, int offset, int rowsToReturn)
        throws IllegalArgumentException, DatabaseException {
        if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
            return getBeans_inThread_limit_mysql(threadID, offset, rowsToReturn, true);
        } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
            return getBeans_inThread_limit_noscroll(threadID, offset, rowsToReturn, true);
        }
        return getBeans_inThread_limit_general(threadID, offset, rowsToReturn, true);
    }

    public Collection getDisablePosts_inThread_limit(int threadID, int offset, int rowsToReturn)
        throws IllegalArgumentException, DatabaseException {
        if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
            return getBeans_inThread_limit_mysql(threadID, offset, rowsToReturn, false);
        } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
            return getBeans_inThread_limit_noscroll(threadID, offset, rowsToReturn, false);
        }
        return getBeans_inThread_limit_general(threadID, offset, rowsToReturn, false);