threadwebhelper.java

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

/*
// @todo: copy this method for derived class
    public static ThreadBean getThread(int threadID)
        throws BadInputException, DatabaseException {
        return net.myvietnam.webplugin.mvnforum.db.ThreadWebHelper.getBean(threadID);
    }
*/
    /*
     * Included columns: ForumID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody,
     *                   ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType,
     *                   ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount,
     *                   ThreadIcon, ThreadDuration
     * Excluded columns: ThreadID
     */
    protected static ThreadBean getBean(int threadID)
        throws BadInputException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT ForumID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE ThreadID = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, threadID);
            resultSet = statement.executeQuery();
            if(!resultSet.next()) {
                throw new BadInputException("Cannot find the row in table Thread where primary key = (" + threadID + ").");
            }

            ThreadBean bean = new ThreadBean();
            // @todo: uncomment the following line(s) as needed
            bean.setThreadID(threadID);
            bean.setForumID(resultSet.getInt("ForumID"));
            bean.setMemberName(resultSet.getString("MemberName"));
            bean.setLastPostMemberName(resultSet.getString("LastPostMemberName"));
            bean.setThreadTopic(resultSet.getString("ThreadTopic"));
            bean.setThreadBody(resultSet.getString("ThreadBody"));
            bean.setThreadVoteCount(resultSet.getInt("ThreadVoteCount"));
            bean.setThreadVoteTotalStars(resultSet.getInt("ThreadVoteTotalStars"));
            bean.setThreadCreationDate(resultSet.getTimestamp("ThreadCreationDate"));
            bean.setThreadLastPostDate(resultSet.getTimestamp("ThreadLastPostDate"));
            bean.setThreadType(resultSet.getInt("ThreadType"));
            bean.setThreadOption(resultSet.getInt("ThreadOption"));
            bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
            bean.setThreadHasPoll(resultSet.getInt("ThreadHasPoll"));
            bean.setThreadViewCount(resultSet.getInt("ThreadViewCount"));
            bean.setThreadReplyCount(resultSet.getInt("ThreadReplyCount"));
            bean.setThreadIcon(resultSet.getString("ThreadIcon"));
            bean.setThreadDuration(resultSet.getInt("ThreadDuration"));
            return bean;
        } catch(SQLException sqle) {
            sqle.printStackTrace();
            throw new DatabaseException("Error executing SQL in ThreadWebHelper.getBean(pk).");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

/*
// @todo: copy this method for derived class
    public static Collection getThreads_limit(int offset, int rowsToReturn)
        throws IllegalArgumentException, DatabaseException {
        return net.myvietnam.webplugin.mvnforum.db.ThreadWebHelper.getBeans_limit(offset, rowsToReturn);
    }
*/
    /*
     * Included columns: ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic,
     *                   ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate,
     *                   ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount,
     *                   ThreadReplyCount, ThreadIcon, ThreadDuration
     * Excluded columns:
     */
    protected static Collection getBeans_limit(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 ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration");
        sql.append(" FROM " + TABLE_NAME);
        //sql.append(" WHERE "); // @todo: uncomment as needed
        sql.append(" ORDER BY ThreadCreationDate DESC ");
        sql.append(" LIMIT ?, ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, offset);
            statement.setInt(2, rowsToReturn);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                ThreadBean bean = new ThreadBean();
                bean.setThreadID(resultSet.getInt("ThreadID"));
                bean.setForumID(resultSet.getInt("ForumID"));
                bean.setMemberName(resultSet.getString("MemberName"));
                bean.setLastPostMemberName(resultSet.getString("LastPostMemberName"));
                bean.setThreadTopic(resultSet.getString("ThreadTopic"));
                bean.setThreadBody(resultSet.getString("ThreadBody"));
                bean.setThreadVoteCount(resultSet.getInt("ThreadVoteCount"));
                bean.setThreadVoteTotalStars(resultSet.getInt("ThreadVoteTotalStars"));
                bean.setThreadCreationDate(resultSet.getTimestamp("ThreadCreationDate"));
                bean.setThreadLastPostDate(resultSet.getTimestamp("ThreadLastPostDate"));
                bean.setThreadType(resultSet.getInt("ThreadType"));
                bean.setThreadOption(resultSet.getInt("ThreadOption"));
                bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
                bean.setThreadHasPoll(resultSet.getInt("ThreadHasPoll"));
                bean.setThreadViewCount(resultSet.getInt("ThreadViewCount"));
                bean.setThreadReplyCount(resultSet.getInt("ThreadReplyCount"));
                bean.setThreadIcon(resultSet.getString("ThreadIcon"));
                bean.setThreadDuration(resultSet.getInt("ThreadDuration"));
                retValue.add(bean);
            }
            return retValue;
        } catch(SQLException sqle) {
            sqle.printStackTrace();
            throw new DatabaseException("Error executing SQL in ThreadWebHelper.getBeans_limit.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

/*
// @todo: copy this method for derived class
    public static Collection getThreads_inForum_limit(int forumID, int offset, int rowsToReturn)
        throws IllegalArgumentException, DatabaseException {
        return net.myvietnam.webplugin.mvnforum.db.ThreadWebHelper.getBeans_inForum_limit(forumID, offset, rowsToReturn);
    }
*/
    /*
     * Included columns: ThreadID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody,
     *                   ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType,
     *                   ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount,
     *                   ThreadIcon, ThreadDuration
     * Excluded columns: ForumID
     */
    /**
     * Note: This is a customized method
     */
    protected static Collection getBeans_inForum_limit(int forumID, 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 ThreadID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE ForumID = ?");
        sql.append(" ORDER BY ThreadCreationDate DESC ");
        sql.append(" LIMIT ?, ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, forumID);
            statement.setInt(2, offset);
            statement.setInt(3, rowsToReturn);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                ThreadBean bean = new ThreadBean();
                bean.setThreadID(resultSet.getInt("ThreadID"));
                bean.setForumID(forumID);
                bean.setMemberName(resultSet.getString("MemberName"));
                bean.setLastPostMemberName(resultSet.getString("LastPostMemberName"));
                bean.setThreadTopic(resultSet.getString("ThreadTopic"));
                bean.setThreadBody(resultSet.getString("ThreadBody"));
                bean.setThreadVoteCount(resultSet.getInt("ThreadVoteCount"));
                bean.setThreadVoteTotalStars(resultSet.getInt("ThreadVoteTotalStars"));
                bean.setThreadCreationDate(resultSet.getTimestamp("ThreadCreationDate"));
                bean.setThreadLastPostDate(resultSet.getTimestamp("ThreadLastPostDate"));
                bean.setThreadType(resultSet.getInt("ThreadType"));
                bean.setThreadOption(resultSet.getInt("ThreadOption"));
                bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
                bean.setThreadHasPoll(resultSet.getInt("ThreadHasPoll"));
                bean.setThreadViewCount(resultSet.getInt("ThreadViewCount"));
                bean.setThreadReplyCount(resultSet.getInt("ThreadReplyCount"));
                bean.setThreadIcon(resultSet.getString("ThreadIcon"));
                bean.setThreadDuration(resultSet.getInt("ThreadDuration"));
                retValue.add(bean);
            }
            return retValue;
        } catch(SQLException sqle) {
            sqle.printStackTrace();
            throw new DatabaseException("Error executing SQL in ThreadWebHelper.getBeans_limit.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

/*
// @todo: copy this method for derived class
    public static int getNumberOfThreads_inForum(int forumID)
        throws AssertionException, DatabaseException {
        return net.myvietnam.webplugin.mvnforum.db.ThreadWebHelper.getNumberOfBeans_inForum(forumID);
    }
*/
    /**
     * Note: this is a customized method
     */
    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 = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, forumID);
            resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new AssertionException("Assertion in ThreadWebHelper.getNumberOfThreads.");
            }
            return resultSet.getInt(1);
        } catch(SQLException sqle) {
            sqle.printStackTrace();
            throw new DatabaseException("Error executing SQL in ThreadWebHelper.getNumberOfThreads.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

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


}// end of class ThreadWebHelper