postwebhelper.java

easy to use, easy to setup bulletin board (forum)

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

            if (statement.executeUpdate() != 1) {
                throw new BadInputException("Cannot update table Post where primary key = (" + postID + ").");
            }
            m_dirty = true;
        } catch(SQLException sqle) {
            sqle.printStackTrace();
            throw new DatabaseException("Error executing SQL in PostWebHelper.update.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

/*
// @todo: copy this method for derived class
    public static PostBean getPost(int postID)
        throws BadInputException, DatabaseException {
        return net.myvietnam.webplugin.mvnforum.db.PostWebHelper.getBean(postID);
    }
*/
    /*
     * Included columns: ParentPostID, ForumID, ThreadID, MemberID, MemberName,
     *                   LastEditMemberName, PostTopic, PostBody, PostCreationDate, PostLastEditDate,
     *                   PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption, PostOption,
     *                   PostStatus, PostIcon, PostAttachCount
     * Excluded columns: PostID
     */
    protected static PostBean getBean(int postID)
        throws BadInputException, 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 BadInputException("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) {
            sqle.printStackTrace();
            throw new DatabaseException("Error executing SQL in PostWebHelper.getBean(pk).");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

/*
// @todo: copy this method for derived class
    public static Collection getPosts_inThread_limit(int threadID, int offset, int rowsToReturn)
        throws IllegalArgumentException, DatabaseException {
        return net.myvietnam.webplugin.mvnforum.db.PostWebHelper.getBeans_inThread_limit(threadID, offset, rowsToReturn);
    }
*/
    /*
     * Included columns: PostID, ParentPostID, ForumID, ThreadID, MemberID,
     *                   MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate,
     *                   PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption,
     *                   PostOption, PostStatus, PostIcon, PostAttachCount
     * Excluded columns:
     */
    protected static Collection getBeans_inThread_limit(int threadID, int offset, int rowsToReturn)
        throws IllegalArgumentException, DatabaseException {
        if (offset < 0) throw new IllegalArgumentException("The offset < 0 is not allowed.");
        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 = ?"); // @todo: uncomment as needed
        //sql.append(" ORDER BY ColumnName ASC|DESC "); // @todo: uncomment as needed
        sql.append(" LIMIT ?, ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, threadID);
            statement.setInt(2, offset);
            statement.setInt(3, rowsToReturn);
            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) {
            sqle.printStackTrace();
            throw new DatabaseException("Error executing SQL in PostWebHelper.getBeans_limit.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

/*
// @todo: copy this method for derived class
    public static int getNumberOfPosts_inForum(int forumID)
        throws AssertionException, DatabaseException {
        return net.myvietnam.webplugin.mvnforum.db.PostWebHelper.getNumberOfBeans_inForum(forumID);
    }
*/
    protected static int getNumberOfBeans_inForum(int forumID)
        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 = ?"); // @todo: uncomment as needed
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());

            statement.setInt(1, forumID);

            resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new AssertionException("Assertion in PostWebHelper.getNumberOfPosts.");
            }
            return resultSet.getInt(1);
        } catch(SQLException sqle) {
            sqle.printStackTrace();
            throw new DatabaseException("Error executing SQL in PostWebHelper.getNumberOfPosts_inForum(forumID).");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

/*
// @todo: copy this method for derived class
    public static int getNumberOfPosts_inThread(int threadID)
        throws AssertionException, DatabaseException {
        return net.myvietnam.webplugin.mvnforum.db.PostWebHelper.getNumberOfBeans_inThread(threadID);
    }
*/
    protected static int getNumberOfBeans_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);
        sql.append(" WHERE ThreadID = ?"); // @todo: uncomment as needed
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());

            statement.setInt(1, threadID);

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

/*
// @todo: copy this method for derived class
    public static int getNumberOfPosts()
        throws AssertionException, DatabaseException {
        return net.myvietnam.webplugin.mvnforum.db.PostWebHelper.getNumberOfBeans();
    }
*/
    protected static int getNumberOfBeans()
        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 PostWebHelper.getNumberOfPosts.");
            }
            return resultSet.getInt(1);
        } catch(SQLException sqle) {
            sqle.printStackTrace();
            throw new DatabaseException("Error executing SQL in PostWebHelper.getNumberOfPosts.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }


}// end of class PostWebHelper