postdaoimpljdbc.java

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

                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
            }
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.getLastEnablePosts_inThread_limit.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.resetStatement(statement);
            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_inForum_limit(int forumID, 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 ForumID = ? AND PostStatus = 0");// only get enable posts
        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, forumID);
            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"));
                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
            }
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.getLastEnablePosts_inForum_limit.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.resetStatement(statement);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

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

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

        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, PostTopic, PostBody, PostCreationDate, PostLastEditDate, PostAttachCount");
        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()) {
                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.setPostTopic(resultSet.getString("PostTopic"));
                bean.setPostBody(resultSet.getString("PostBody"));
                bean.setPostCreationDate(resultSet.getTimestamp("PostCreationDate"));
                bean.setPostLastEditDate(resultSet.getTimestamp("PostLastEditDate"));
                bean.setPostAttachCount(resultSet.getInt("PostAttachCount"));
                retValue.add(bean);
            }
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.getPosts.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public int getMaxPostID()
        throws AssertionException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT MAX(PostID)");
        sql.append(" FROM " + TABLE_NAME);
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new AssertionException("Assertion in PostDAOImplJDBC.getMaxPostID.");
            }
            return resultSet.getInt(1);
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.getMaxPostID.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            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 getPosts_fromIDRange(int fromID, int toID)
        throws IllegalArgumentException, DatabaseException {

        if (fromID < 0) throw new IllegalArgumentException("The fromID < 0 is not allowed.");
        if (toID < fromID) throw new IllegalArgumentException("toID < fromID 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 (PostID >= ?) AND (PostID <= ?)");
        sql.append(" ORDER BY PostID ASC ");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, fromID);
            statement.setInt(2, toID);
            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"));
                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);
            }
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.getPosts_fromIDRange.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /**
     * This is a special method. This method return a collection of ActiveMember
     * instead of a collection of PostBean
     */
    public Collection getMostActiveMembers(Timestamp since, int rowsToReturn)
        throws DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet