postdaoimpljdbc.java

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

            statement.setInt(1, threadID);
            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) {
                PostBean bean = new PostBean();
                bean.setPostID(resultSet.getInt("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"));
                retValue.add(bean);
                if (retValue.size() == rowsToReturn) break;// Fix the Sybase bug
                loop = resultSet.next();
            }//while
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.getBeans_inThread_limit_general.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.resetStatement(statement);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public int getNumberOfPosts_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 = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());

            statement.setInt(1, memberID);

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

    public int getNumberOfEnablePosts_inForum(int forumID)
        throws AssertionException, DatabaseException {

        return getNumberOfPosts_inForum(forumID, true);
    }

    public int getNumberOfDisablePosts_inForum(int forumID)
        throws AssertionException, DatabaseException {

        return getNumberOfPosts_inForum(forumID, false);
    }

    public int getNumberOfPosts_inForum(int forumID, boolean enable)
        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 ForumID = ? ");
        if (enable) {
            sql.append(" AND PostStatus <> 1 ");
        } else {//disable
            sql.append(" AND PostStatus = 1 ");
        }
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());

            statement.setInt(1, forumID);

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

    public int getNumberOfEnablePosts_inThread(int threadID)
        throws AssertionException, DatabaseException {

        return getNumberOfPosts_inThread(threadID, true);
    }

    public int getNumberOfDisablePosts_inThread(int threadID)
        throws AssertionException, DatabaseException {

        return getNumberOfPosts_inThread(threadID, false);
    }

    public int getNumberOfPosts_inThread(int threadID, boolean enable)
        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 ThreadID = ?");
        if (enable) {
            sql.append(" AND PostStatus <> 1 ");
        } else {//disable
            sql.append(" AND PostStatus = 1 ");
        }
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());

            statement.setInt(1, threadID);

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

    public void updateParentPostID(int oldParentPostID, int newParentPostID)
        throws ObjectNotFoundException, DatabaseException {

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

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

            // condition column
            statement.setInt(2, oldParentPostID);

            if (statement.executeUpdate() != 1) {
                throw new ObjectNotFoundException("No row is updated in table Post where ParentPostID = (" + oldParentPostID + ").");
            }
            setDirty(true);
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.updateParentPostID.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

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

        Connection connection = null;
        PreparedStatement statement = null;
        String sql = "UPDATE " + TABLE_NAME + " SET PostEditCount = PostEditCount + 1 WHERE PostID = ?";
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setInt(1, postID);
            if (statement.executeUpdate() != 1) {
                throw new ObjectNotFoundException("Cannot update the PostEditCount in table Post. 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 executing SQL in PostDAOImplJDBC.increaseEditCount.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: PostID, ParentPostID, ForumID, ThreadID, MemberID,
     *                   MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate,
     *                   PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption,
     *                   PostOption, PostStatus, PostIcon, PostAttachCount
     * Excluded columns:
     */
    public Collection getLastEnablePosts_inThread_limit(int threadID, int rowsToReturn)
        throws IllegalArgumentException, DatabaseException {
        if (rowsToReturn <= 0) throw new IllegalArgumentException("The rowsToReturn <= 0 is not allowed.");

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Collection retValue = new ArrayList();
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT PostID, 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 ThreadID = ? AND PostStatus = 0");
        sql.append(" ORDER BY PostCreationDate DESC ");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setMaxRows(rowsToReturn);
            try {
                statement.setFetchSize(rowsToReturn);
            } catch (SQLException sqle) {
                //do nothing, postgreSQL doesnt support this method
            }

            statement.setInt(1, threadID);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                PostBean bean = new PostBean();
                bean.setPostID(resultSet.getInt("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"));