/*
 * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/ForumDAOImplJDBC.java,v 1.9 2005/01/18 11:52:19 minhnn Exp $
 * $Author: minhnn $
 * $Revision: 1.9 $
 * $Date: 2005/01/18 11:52:19 $
 *
 * ====================================================================
 *
 * Copyright (C) 2002-2005 by MyVietnam.net
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or any later version.
 *
 * All copyright notices regarding mvnForum MUST remain intact
 * in the scripts and in the outputted HTML.
 * The "powered by" text/logo with a link back to
 * http://www.mvnForum.com and http://www.MyVietnam.net in the
 * footer of the pages MUST remain visible when the pages
 * are viewed on the internet or intranet.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 *
 * Support can be obtained from support forums at:
 * http://www.mvnForum.com/mvnforum/index
 *
 * Correspondence and Marketing Questions can be sent to:
 * info@MyVietnam.net
 *
 * @author: Minh Nguyen  minhnn@MyVietnam.net
 * @author: Mai  Nguyen  mai.nh@MyVietnam.net
 */
package com.mvnforum.db.jdbc;

import java.io.StringReader;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;

import com.mvnforum.db.*;
import net.myvietnam.mvncore.db.DBUtils;
import net.myvietnam.mvncore.exception.*;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class ForumDAOImplJDBC implements ForumDAO {

    private static Log log = LogFactory.getLog(ForumDAOImplJDBC.class);

    // this variable will support caching if cache for this class is needed
    private static boolean m_dirty = true;

    public ForumDAOImplJDBC() {
    }

    public static boolean isDirty() {
        return m_dirty;
    }

    public static void setDirty(boolean dirty) {
        m_dirty = dirty;
    }

    public void findByPrimaryKey(int forumID)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT ForumID");
        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 ObjectNotFoundException("Cannot find the primary key (" + forumID + ") in table 'Forum'.");
            }
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.findByPrimaryKey.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public void findByAlternateKey_ForumName_CategoryID(String forumName, int categoryID)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT ForumName, CategoryID");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE ForumName = ? AND CategoryID = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setString(1, forumName);
            statement.setInt(2, categoryID);
            resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new ObjectNotFoundException("Cannot find the alternate key [ForumName, CategoryID] (" + forumName + ", " + categoryID + ") in table 'Forum'.");
            }
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.findByAlternateKey_ForumName_CategoryID.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: CategoryID, LastPostMemberName, ForumName, ForumDesc, ForumCreationDate,
     *                   ForumModifiedDate, ForumLastPostDate, ForumOrder, ForumType, ForumFormatOption,
     *                   ForumOption, ForumStatus, ForumModerationMode, ForumPassword, ForumThreadCount,
     *                   ForumPostCount
     * Excluded columns: ForumID
     */
    public void create(int categoryID, String lastPostMemberName, String forumName,
                        String forumDesc, Timestamp forumCreationDate, Timestamp forumModifiedDate,
                        Timestamp forumLastPostDate, int forumOrder, int forumType,
                        int forumFormatOption, int forumOption, int forumStatus,
                        int forumModerationMode, String forumPassword, int forumThreadCount,
                        int forumPostCount)
         throws CreateException, DatabaseException, DuplicateKeyException, ForeignKeyNotFoundException {

         // check valid
         ForumBean.validateForumType(forumType);
         ForumBean.validateForumFormatOption(forumFormatOption);
         ForumBean.validateForumOption(forumOption);
         ForumBean.validateForumStatus(forumStatus);
         ForumBean.validateForumModerationMode(forumModerationMode);

        // @todo: Comment this try-catch block if the needed columns dont have attribute 'include'
        // If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
        try {
            //Check if alternate key already exists
            findByAlternateKey_ForumName_CategoryID(forumName, categoryID);
            //If so, then we have to throw an exception
            throw new DuplicateKeyException("Alternate key already exists. Cannot create new Forum with the same [ForumName, CategoryID] (" + forumName + ", " + categoryID + ").");
        } catch(ObjectNotFoundException e) {
            //Otherwise we can go ahead
        }

        try {
            // @todo: modify the parameter list as needed
            // You may have to regenerate this method if the needed columns dont have attribute 'include'
            DAOFactory.getCategoryDAO().findByPrimaryKey(categoryID);
        } catch(ObjectNotFoundException e) {
            throw new ForeignKeyNotFoundException("Foreign key refers to table 'Category' does not exist. Cannot create new Forum.");
        }

        Connection connection = null;
        PreparedStatement statement = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("INSERT INTO " + TABLE_NAME + " (CategoryID, LastPostMemberName, ForumName, ForumDesc, ForumCreationDate, ForumModifiedDate, ForumLastPostDate, ForumOrder, ForumType, ForumFormatOption, ForumOption, ForumStatus, ForumModerationMode, ForumPassword, ForumThreadCount, ForumPostCount)");
        sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());

            statement.setInt(1, categoryID);
            statement.setString(2, lastPostMemberName);
            statement.setString(3, forumName);
            if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
                statement.setCharacterStream(4, new StringReader(forumDesc), forumDesc.length());
            } else {
                statement.setString(4, forumDesc);
            }
            statement.setTimestamp(5, forumCreationDate);
            statement.setTimestamp(6, forumModifiedDate);
            statement.setTimestamp(7, forumLastPostDate);
            statement.setInt(8, forumOrder);
            statement.setInt(9, forumType);
            statement.setInt(10, forumFormatOption);
            statement.setInt(11, forumOption);
            statement.setInt(12, forumStatus);
            statement.setInt(13, forumModerationMode);
            statement.setString(14, forumPassword);
            statement.setInt(15, forumThreadCount);
            statement.setInt(16, forumPostCount);

            if (statement.executeUpdate() != 1) {
                throw new CreateException("Error adding a row into table 'Forum'.");
            }
            m_dirty = true;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.create.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: CategoryID, LastPostMemberName, ForumName, ForumDesc, ForumCreationDate,
     *                   ForumModifiedDate, ForumLastPostDate, ForumOrder, ForumType, ForumFormatOption,
     *                   ForumOption, ForumStatus, ForumModerationMode, ForumPassword, ForumThreadCount,
     *                   ForumPostCount
     * Excluded columns: ForumID
     */
    public int createForum(int categoryID, String lastPostMemberName, String forumName,
                        String forumDesc, Timestamp forumCreationDate, Timestamp forumModifiedDate,
                        Timestamp forumLastPostDate, int forumOrder, int forumType,
                        int forumFormatOption, int forumOption, int forumStatus,
                        int forumModerationMode, String forumPassword, int forumThreadCount,
                        int forumPostCount)
         throws CreateException, DatabaseException, DuplicateKeyException, ForeignKeyNotFoundException {

         create(categoryID, lastPostMemberName, forumName, forumDesc, forumCreationDate, forumModifiedDate, forumLastPostDate, forumOrder,
                forumType, forumFormatOption, forumOption, forumStatus, forumModerationMode, forumPassword, forumThreadCount, forumPostCount);

         ForumBean forumBean = null;
         try {
             forumBean = getBean_byAlternateKey_ForumName_CategoryID(forumName, categoryID);
         } catch (ObjectNotFoundException ex) {
             log.error("Cannot find ForumID after creating forum.", ex);
         }
         return forumBean.getForumID();
    }

    public void delete(int forumID)
        throws DatabaseException, ObjectNotFoundException {

        Connection connection = null;
        PreparedStatement statement = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("DELETE FROM " + TABLE_NAME);
        sql.append(" WHERE ForumID = ?");

        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, forumID);
            if (statement.executeUpdate() != 1) {
                throw new ObjectNotFoundException("Cannot delete a row in table Forum where primary key = (" + forumID + ").");
            }
            m_dirty = true;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.delete.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: CategoryID, ForumName, ForumDesc, ForumModifiedDate, ForumOrder,
     *                   ForumType, ForumFormatOption, ForumOption, ForumStatus, ForumModerationMode
     * Excluded columns: ForumID, LastPostMemberName, ForumCreationDate, ForumLastPostDate, ForumPassword,
     *                   ForumThreadCount, ForumPostCount
     */
    public void update(int forumID, // primary key
                       int categoryID, String forumName, String forumDesc,
                       Timestamp forumModifiedDate, int forumOrder, int forumType,
                       int forumFormatOption, int forumOption, int forumStatus,
                       int forumModerationMode)
        throws ObjectNotFoundException, DatabaseException, DuplicateKeyException, ForeignKeyNotFoundException, IllegalArgumentException {

        // check valid
        ForumBean.validateForumType(forumType);
        ForumBean.validateForumFormatOption(forumFormatOption);
        ForumBean.validateForumOption(forumOption);
        ForumBean.validateForumStatus(forumStatus);
        ForumBean.validateForumModerationMode(forumModerationMode);

        ForumBean bean = getForum(forumID);

        if ( !forumName.equals(bean.getForumName()) ||
             (categoryID != bean.getCategoryID()) ) {
            // Forum tries to change its alternate key <ForumName, CategoryID>, so we must check if it already exist
            try {
                findByAlternateKey_ForumName_CategoryID(forumName, categoryID);
                throw new DuplicateKeyException("Alternate key [ForumName, CategoryID] (" + forumName + ", " + categoryID + ")already exists. Cannot update Forum.");
            } catch(ObjectNotFoundException e) {
                //Otherwise we can go ahead
            }
        }

        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.getCategoryDAO().findByPrimaryKey(categoryID);
        } catch(ObjectNotFoundException e) {
            throw new ForeignKeyNotFoundException("Foreign key refers to table 'Category' does not exist. Cannot update table 'Forum'.");
        }

        Connection connection = null;
        PreparedStatement statement = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("UPDATE " + TABLE_NAME + " SET CategoryID = ?, ForumName = ?, ForumDesc = ?, ForumModifiedDate = ?, ForumOrder = ?, ForumType = ?, ForumFormatOption = ?, ForumOption = ?, ForumStatus = ?, ForumModerationMode = ?");
        sql.append(" WHERE ForumID = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());

            // // column(s) to update
            statement.setInt(1, categoryID);
            statement.setString(2, forumName);
            if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
                statement.setCharacterStream(3, new StringReader(forumDesc), forumDesc.length());
            } else {
                statement.setString(3, forumDesc);
            }
            statement.setTimestamp(4, forumModifiedDate);
            statement.setInt(5, forumOrder);
            statement.setInt(6, forumType);
            statement.setInt(7, forumFormatOption);
            statement.setInt(8, forumOption);
            statement.setInt(9, forumStatus);
            statement.setInt(10, forumModerationMode);

            // primary key column(s)
            statement.setInt(11, forumID);

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

    /*
     * Included columns: LastPostMemberName
     * Excluded columns: ForumID, CategoryID, ForumName, ForumDesc, ForumCreationDate,
     *                   ForumModifiedDate, ForumLastPostDate, ForumOrder, ForumType, ForumFormatOption,
     *                   ForumOption, ForumStatus, ForumModerationMode, ForumPassword, ForumThreadCount,
     *                   ForumPostCount
     */
    public void updateLastPostMemberName(int forumID, // primary key
                        String lastPostMemberName)
        throws ObjectNotFoundException, DatabaseException, ForeignKeyNotFoundException {

        //allow anonymous/guests to send posts (if admin allows that)
        if ((lastPostMemberName!=null) && (lastPostMemberName.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(lastPostMemberName);
            } catch(ObjectNotFoundException e) {
                throw new ForeignKeyNotFoundException("Foreign key refers to table 'Member' does not exist. Cannot update table 'Forum'.");
            }
        } else lastPostMemberName=""; //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 LastPostMemberName = ?");
        sql.append(" WHERE ForumID = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());

            // // column(s) to update
            statement.setString(1, lastPostMemberName);

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

            if (statement.executeUpdate() != 1) {
                // Some drivers dont update database if it detect old and new data are the same
                // @todo: should check driver, not check database
                // Currently there is only one driver: Caucho MySql driver
                if ( DBUtils.getDatabaseType() != DBUtils.DATABASE_MYSQL ) {
                    throw new ObjectNotFoundException("Cannot update table Forum where primary key = (" + forumID + ").");
                } else {
                    log.warn("WARNING: By pass the check for Caucho MySql driver.");
                }
            }
            //@todo: coi lai cho nay
            //ATTENTION
            setDirty(true);
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.updateLastPostMemberName.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: ForumLastPostDate
     * Excluded columns: ForumID, CategoryID, LastPostMemberName, ForumName, ForumDesc,
     *                   ForumCreationDate, ForumModifiedDate, ForumOrder, ForumType, ForumFormatOption,
     *                   ForumOption, ForumStatus, ForumModerationMode, ForumPassword, ForumThreadCount,
     *                   ForumPostCount
     */
    public void updateLastPostDate(int forumID, // primary key
                        Timestamp forumLastPostDate)
        throws ObjectNotFoundException, DatabaseException {

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

            // // column(s) to update
            statement.setTimestamp(1, forumLastPostDate);

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

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

    /*
     * Included columns: ForumThreadCount, ForumPostCount
     * Excluded columns: ForumID, CategoryID, LastPostMemberName, ForumName, ForumDesc,
     *                   ForumCreationDate, ForumModifiedDate, ForumLastPostDate, ForumOrder, ForumType,
     *                   ForumFormatOption, ForumOption, ForumStatus, ForumModerationMode, ForumPassword
     */
    public void updateStatistics(int forumID, // primary key
                        int forumThreadCount, int forumPostCount)
        throws ObjectNotFoundException, DatabaseException {

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

            // // column(s) to update
            statement.setInt(1, forumThreadCount);
            statement.setInt(2, forumPostCount);

            // primary key column(s)
            statement.setInt(3, forumID);

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

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

        Connection connection = null;
        PreparedStatement statement = null;
        String sql = "UPDATE " + TABLE_NAME + " SET ForumPostCount = ForumPostCount + 1 WHERE ForumID = ?";
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setInt(1, forumID);
            if (statement.executeUpdate() != 1) {
                throw new ObjectNotFoundException("Cannot update the ForumPostCount in table Forum. 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 ForumDAOImplJDBC.increasePostCount.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

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

        Connection connection = null;
        PreparedStatement statement = null;
        String sql = "UPDATE " + TABLE_NAME + " SET ForumThreadCount = ForumThreadCount + 1 WHERE ForumID = ?";
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setInt(1, forumID);
            if (statement.executeUpdate() != 1) {
                throw new ObjectNotFoundException("Cannot update the ForumThreadCount in table Forum. Please contact Web site Administrator.");
            }
            setDirty(true);
        } catch (SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.increaseThreadCount.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

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

        Connection connection = null;
        PreparedStatement statement = null;
        String sql = "UPDATE " + TABLE_NAME + " SET ForumThreadCount = ForumThreadCount - 1 WHERE ForumID = ?";
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setInt(1, forumID);
            if (statement.executeUpdate() != 1) {
                throw new ObjectNotFoundException("Cannot update the ForumThreadCount in table Forum. Please contact Web site Administrator.");
            }
            setDirty(true);
        } catch (SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.decreaseThreadCount.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: CategoryID, LastPostMemberName, ForumName, ForumDesc, ForumCreationDate,
     *                   ForumModifiedDate, ForumLastPostDate, ForumOrder, ForumType, ForumFormatOption,
     *                   ForumOption, ForumStatus, ForumModerationMode, ForumPassword, ForumThreadCount,
     *                   ForumPostCount
     * Excluded columns: ForumID
     */
    public ForumBean getForum(int forumID)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT CategoryID, LastPostMemberName, ForumName, ForumDesc, ForumCreationDate, ForumModifiedDate, ForumLastPostDate, ForumOrder, ForumType, ForumFormatOption, ForumOption, ForumStatus, ForumModerationMode, ForumPassword, ForumThreadCount, ForumPostCount");
        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 ObjectNotFoundException("Cannot find the row in table Forum where primary key = (" + forumID + ").");
            }

            ForumBean bean = new ForumBean();
            // @todo: uncomment the following line(s) as needed
            bean.setForumID(forumID);
            bean.setCategoryID(resultSet.getInt("CategoryID"));
            bean.setLastPostMemberName(resultSet.getString("LastPostMemberName"));
            bean.setForumName(resultSet.getString("ForumName"));
            bean.setForumDesc(resultSet.getString("ForumDesc"));
            bean.setForumCreationDate(resultSet.getTimestamp("ForumCreationDate"));
            bean.setForumModifiedDate(resultSet.getTimestamp("ForumModifiedDate"));
            bean.setForumLastPostDate(resultSet.getTimestamp("ForumLastPostDate"));
            bean.setForumOrder(resultSet.getInt("ForumOrder"));
            bean.setForumType(resultSet.getInt("ForumType"));
            bean.setForumFormatOption(resultSet.getInt("ForumFormatOption"));
            bean.setForumOption(resultSet.getInt("ForumOption"));
            bean.setForumStatus(resultSet.getInt("ForumStatus"));
            bean.setForumModerationMode(resultSet.getInt("ForumModerationMode"));
            bean.setForumPassword(resultSet.getString("ForumPassword"));
            bean.setForumThreadCount(resultSet.getInt("ForumThreadCount"));
            bean.setForumPostCount(resultSet.getInt("ForumPostCount"));
            return bean;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.getForum(pk).");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: ForumID, CategoryID, LastPostMemberName, ForumName, ForumDesc,
     *                   ForumCreationDate, ForumModifiedDate, ForumLastPostDate, ForumOrder, ForumType,
     *                   ForumFormatOption, ForumOption, ForumStatus, ForumModerationMode, ForumPassword,
     *                   ForumThreadCount, ForumPostCount
     * Excluded columns:
     */
    public Collection getForums()
        throws DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Collection retValue = new ArrayList();
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT ForumID, CategoryID, LastPostMemberName, ForumName, ForumDesc, ForumCreationDate, ForumModifiedDate, ForumLastPostDate, ForumOrder, ForumType, ForumFormatOption, ForumOption, ForumStatus, ForumModerationMode, ForumPassword, ForumThreadCount, ForumPostCount");
        sql.append(" FROM " + TABLE_NAME);
        //sql.append(" WHERE "); // @todo: uncomment as needed
        sql.append(" ORDER BY CategoryID ASC, ForumOrder ASC "); // @todo: uncomment as needed
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                ForumBean bean = new ForumBean();
                bean.setForumID(resultSet.getInt("ForumID"));
                bean.setCategoryID(resultSet.getInt("CategoryID"));
                bean.setLastPostMemberName(resultSet.getString("LastPostMemberName"));
                bean.setForumName(resultSet.getString("ForumName"));
                bean.setForumDesc(resultSet.getString("ForumDesc"));
                bean.setForumCreationDate(resultSet.getTimestamp("ForumCreationDate"));
                bean.setForumModifiedDate(resultSet.getTimestamp("ForumModifiedDate"));
                bean.setForumLastPostDate(resultSet.getTimestamp("ForumLastPostDate"));
                bean.setForumOrder(resultSet.getInt("ForumOrder"));
                bean.setForumType(resultSet.getInt("ForumType"));
                bean.setForumFormatOption(resultSet.getInt("ForumFormatOption"));
                bean.setForumOption(resultSet.getInt("ForumOption"));
                bean.setForumStatus(resultSet.getInt("ForumStatus"));
                bean.setForumModerationMode(resultSet.getInt("ForumModerationMode"));
                bean.setForumPassword(resultSet.getString("ForumPassword"));
                bean.setForumThreadCount(resultSet.getInt("ForumThreadCount"));
                bean.setForumPostCount(resultSet.getInt("ForumPostCount"));
                retValue.add(bean);
            }
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.getForums.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: ForumID, CategoryID, LastPostMemberName, ForumName, ForumDesc,
     *                   ForumCreationDate, ForumModifiedDate, ForumLastPostDate, ForumOrder, ForumType,
     *                   ForumFormatOption, ForumOption, ForumStatus, ForumModerationMode, ForumPassword,
     *                   ForumThreadCount, ForumPostCount
     * Excluded columns:
     */
    public Collection getForums_inCategory(int categoryID)
        throws DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Collection retValue = new ArrayList();
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT ForumID, CategoryID, LastPostMemberName, ForumName, ForumDesc, ForumCreationDate, ForumModifiedDate, ForumLastPostDate, ForumOrder, ForumType, ForumFormatOption, ForumOption, ForumStatus, ForumModerationMode, ForumPassword, ForumThreadCount, ForumPostCount");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE CategoryID = ?");
        sql.append(" ORDER BY CategoryID ASC, ForumOrder ASC "); // @todo: uncomment as needed
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, categoryID);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                ForumBean bean = new ForumBean();
                bean.setForumID(resultSet.getInt("ForumID"));
                bean.setCategoryID(resultSet.getInt("CategoryID"));
                bean.setLastPostMemberName(resultSet.getString("LastPostMemberName"));
                bean.setForumName(resultSet.getString("ForumName"));
                bean.setForumDesc(resultSet.getString("ForumDesc"));
                bean.setForumCreationDate(resultSet.getTimestamp("ForumCreationDate"));
                bean.setForumModifiedDate(resultSet.getTimestamp("ForumModifiedDate"));
                bean.setForumLastPostDate(resultSet.getTimestamp("ForumLastPostDate"));
                bean.setForumOrder(resultSet.getInt("ForumOrder"));
                bean.setForumType(resultSet.getInt("ForumType"));
                bean.setForumFormatOption(resultSet.getInt("ForumFormatOption"));
                bean.setForumOption(resultSet.getInt("ForumOption"));
                bean.setForumStatus(resultSet.getInt("ForumStatus"));
                bean.setForumModerationMode(resultSet.getInt("ForumModerationMode"));
                bean.setForumPassword(resultSet.getString("ForumPassword"));
                bean.setForumThreadCount(resultSet.getInt("ForumThreadCount"));
                bean.setForumPostCount(resultSet.getInt("ForumPostCount"));
                retValue.add(bean);
            }
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.getForums_inCategory.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /************************************************
     * Customized methods come below
     ************************************************/

    /**
     * This method should be call only when we can make sure that memberID is in database
     */
    public void decreaseForumOrder(int forumID, Timestamp forumModifiedDate)
        throws DatabaseException, ObjectNotFoundException {

        Connection connection = null;
        PreparedStatement statement = null;
        String sql = "UPDATE " + TABLE_NAME + " SET ForumOrder = ForumOrder - 1, ForumModifiedDate = ? WHERE ForumID = ?";
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setTimestamp(1, forumModifiedDate);
            statement.setInt(2, forumID);
            if (statement.executeUpdate() != 1) {
                throw new ObjectNotFoundException("Cannot update the ForumOrder in table Forum. 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 ForumDAOImplJDBC.decreaseForumOrder.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /**
     * This method should be call only when we can make sure that memberID is in database
     */
    public void increaseForumOrder(int forumID, Timestamp forumModifiedDate)
        throws DatabaseException, ObjectNotFoundException {

        Connection connection = null;
        PreparedStatement statement = null;
        String sql = "UPDATE " + TABLE_NAME + " SET ForumOrder = ForumOrder + 1, ForumModifiedDate = ? WHERE ForumID = ?";
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setTimestamp(1, forumModifiedDate);
            statement.setInt(2, forumID);
            if (statement.executeUpdate() != 1) {
                throw new ObjectNotFoundException("Cannot update the ForumOrder in table Forum. 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 ForumDAOImplJDBC.increaseForumOrder.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: ForumID, CategoryID, LastPostMemberName, ForumName, ForumDesc,
     *                   ForumCreationDate, ForumModifiedDate, ForumLastPostDate, ForumOrder, ForumType,
     *                   ForumFormatOption, ForumOption, ForumStatus, ForumModerationMode, ForumPassword,
     *                   ForumThreadCount, ForumPostCount
     * Excluded columns:
     */
    protected static ForumBean getBean_byAlternateKey_ForumName_CategoryID(String forumName, int categoryID)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT ForumID, CategoryID, LastPostMemberName, ForumName, ForumDesc, ForumCreationDate, ForumModifiedDate, ForumLastPostDate, ForumOrder, ForumType, ForumFormatOption, ForumOption, ForumStatus, ForumModerationMode, ForumPassword, ForumThreadCount, ForumPostCount");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE ForumName = ? AND CategoryID = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setString(1, forumName);
            statement.setInt(2, categoryID);
            resultSet = statement.executeQuery();
            if(!resultSet.next()) {
                throw new ObjectNotFoundException("Cannot find the row in table Forum where alternate key [ForumName, CategoryID] = (" + forumName + ", " + categoryID + ").");
            }

            ForumBean bean = new ForumBean();
            // @todo: uncomment the following line(s) as needed
            //bean.setForumName(forumName);
            //bean.setCategoryID(categoryID);
            bean.setForumID(resultSet.getInt("ForumID"));
            bean.setCategoryID(resultSet.getInt("CategoryID"));
            bean.setLastPostMemberName(resultSet.getString("LastPostMemberName"));
            bean.setForumName(resultSet.getString("ForumName"));
            bean.setForumDesc(resultSet.getString("ForumDesc"));
            bean.setForumCreationDate(resultSet.getTimestamp("ForumCreationDate"));
            bean.setForumModifiedDate(resultSet.getTimestamp("ForumModifiedDate"));
            bean.setForumLastPostDate(resultSet.getTimestamp("ForumLastPostDate"));
            bean.setForumOrder(resultSet.getInt("ForumOrder"));
            bean.setForumType(resultSet.getInt("ForumType"));
            bean.setForumFormatOption(resultSet.getInt("ForumFormatOption"));
            bean.setForumOption(resultSet.getInt("ForumOption"));
            bean.setForumStatus(resultSet.getInt("ForumStatus"));
            bean.setForumModerationMode(resultSet.getInt("ForumModerationMode"));
            bean.setForumPassword(resultSet.getString("ForumPassword"));
            bean.setForumThreadCount(resultSet.getInt("ForumThreadCount"));
            bean.setForumPostCount(resultSet.getInt("ForumPostCount"));
            return bean;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in ForumDAOImplJDBC.getBean_byAlternateKey_ForumName_CategoryID(ak).");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

}// end of class ForumDAOImplJDBC