/*
 * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/AttachmentDAOImplJDBC.java,v 1.13 2005/01/18 11:52:19 minhnn Exp $
 * $Author: minhnn $
 * $Revision: 1.13 $
 * $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 AttachmentDAOImplJDBC implements AttachmentDAO {

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

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

    public AttachmentDAOImplJDBC() {
    }

    protected static boolean isDirty() {
        return m_dirty;
    }

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

    /*
     * Included columns: PostID, MemberID, AttachFilename, AttachFileSize, AttachMimeType,
     *                   AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount,
     *                   AttachOption, AttachStatus
     * Excluded columns: AttachID
     */
    public void create(int postID, int memberID, String attachFilename,
                        int attachFileSize, String attachMimeType, String attachDesc,
                        String attachCreationIP, Timestamp attachCreationDate, Timestamp attachModifiedDate,
                        int attachDownloadCount, int attachOption, int attachStatus)
                        throws CreateException, DatabaseException, ForeignKeyNotFoundException {

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

        //if admin allowed guest to send attachments, we must allow that too
        if (memberID!=0) {
            try {
                // @todo: modify the parameter list as needed
                // You may have to regenerate this method if the needed columns dont have attribute 'include'
                DAOFactory.getMemberDAO().findByPrimaryKey(memberID);
            } catch(ObjectNotFoundException e) {
                throw new ForeignKeyNotFoundException("Foreign key refers to table 'Member' does not exist. Cannot create new Attachment.");
            }
        }

        Connection connection = null;
        PreparedStatement statement = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("INSERT INTO " + TABLE_NAME + " (PostID, MemberID, AttachFilename, AttachFileSize, AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount, AttachOption, AttachStatus)");
        sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());

            statement.setInt(1, postID);
            statement.setInt(2, memberID);
            statement.setString(3, attachFilename);
            statement.setInt(4, attachFileSize);
            statement.setString(5, attachMimeType);
            if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
                statement.setCharacterStream(6, new StringReader(attachDesc), attachDesc.length());
            } else {
                statement.setString(6, attachDesc);
            }
            statement.setString(7, attachCreationIP);
            statement.setTimestamp(8, attachCreationDate);
            statement.setTimestamp(9, attachModifiedDate);
            statement.setInt(10, attachDownloadCount);
            statement.setInt(11, attachOption);
            statement.setInt(12, attachStatus);

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

    public int createAttachment(int postID, int memberID, String attachFilename,
                        int attachFileSize, String attachMimeType, String attachDesc,
                        String attachCreationIP, Timestamp attachCreationDate, Timestamp attachModifiedDate,
                        int attachDownloadCount, int attachOption, int attachStatus)
        throws CreateException, DatabaseException, ForeignKeyNotFoundException, ObjectNotFoundException {

        create(postID, memberID, attachFilename, attachFileSize, attachMimeType, attachDesc, attachCreationIP, attachCreationDate, attachModifiedDate, attachDownloadCount, attachOption, attachStatus);

        int attachID = 0;
        try {
            attachID = findAttachID(postID, memberID, attachCreationDate);
        } catch (ObjectNotFoundException ex) {
            // Hack the Oracle 9i problem
            Timestamp roundTimestamp = new Timestamp((attachCreationDate.getTime()/1000)*1000);
            attachID = findAttachID(postID, memberID, roundTimestamp);
        }
        return attachID;
    }

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

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

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

    /*
     * Included columns: PostID, MemberID, AttachFilename, AttachFileSize, AttachMimeType,
     *                   AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount,
     *                   AttachOption, AttachStatus
     * Excluded columns: AttachID
     */
    public AttachmentBean getAttachment(int attachID)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT PostID, MemberID, AttachFilename, AttachFileSize, AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount, AttachOption, AttachStatus");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE AttachID = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, attachID);
            resultSet = statement.executeQuery();
            if(!resultSet.next()) {
                throw new ObjectNotFoundException("Cannot find the row in table Attachment where primary key = (" + attachID + ").");
            }

            AttachmentBean bean = new AttachmentBean();
            bean.setAttachID(attachID);
            bean.setPostID(resultSet.getInt("PostID"));
            bean.setMemberID(resultSet.getInt("MemberID"));
            bean.setAttachFilename(resultSet.getString("AttachFilename"));
            bean.setAttachFileSize(resultSet.getInt("AttachFileSize"));
            bean.setAttachMimeType(resultSet.getString("AttachMimeType"));
            bean.setAttachDesc(resultSet.getString("AttachDesc"));
            bean.setAttachCreationIP(resultSet.getString("AttachCreationIP"));
            bean.setAttachCreationDate(resultSet.getTimestamp("AttachCreationDate"));
            bean.setAttachModifiedDate(resultSet.getTimestamp("AttachModifiedDate"));
            bean.setAttachDownloadCount(resultSet.getInt("AttachDownloadCount"));
            bean.setAttachOption(resultSet.getInt("AttachOption"));
            bean.setAttachStatus(resultSet.getInt("AttachStatus"));
            return bean;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.getAttachment(pk).");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: AttachID, PostID, MemberID, AttachFilename, AttachFileSize,
     *                   AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate,
     *                   AttachDownloadCount, AttachOption, AttachStatus
     * Excluded columns:
     */
    public Collection getAttachments()
        throws DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Collection retValue = new ArrayList();
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT AttachID, PostID, MemberID, AttachFilename, AttachFileSize, AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount, AttachOption, AttachStatus");
        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()) {
                AttachmentBean bean = new AttachmentBean();
                bean.setAttachID(resultSet.getInt("AttachID"));
                bean.setPostID(resultSet.getInt("PostID"));
                bean.setMemberID(resultSet.getInt("MemberID"));
                bean.setAttachFilename(resultSet.getString("AttachFilename"));
                bean.setAttachFileSize(resultSet.getInt("AttachFileSize"));
                bean.setAttachMimeType(resultSet.getString("AttachMimeType"));
                bean.setAttachDesc(resultSet.getString("AttachDesc"));
                bean.setAttachCreationIP(resultSet.getString("AttachCreationIP"));
                bean.setAttachCreationDate(resultSet.getTimestamp("AttachCreationDate"));
                bean.setAttachModifiedDate(resultSet.getTimestamp("AttachModifiedDate"));
                bean.setAttachDownloadCount(resultSet.getInt("AttachDownloadCount"));
                bean.setAttachOption(resultSet.getInt("AttachOption"));
                bean.setAttachStatus(resultSet.getInt("AttachStatus"));
                retValue.add(bean);
            }
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.getAttachments.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public int getNumberOfAttachments()
        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 AttachmentDAOImplJDBC.getNumberOfAttachments.");
            }
            return resultSet.getInt(1);
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.getNumberOfAttachments.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

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

   public int getNumberOfAttachments_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 + " attachment , " + PostDAO.TABLE_NAME + " post");
        sql.append(" WHERE attachment.PostID = post.PostID ");
        sql.append(" AND post.ThreadID = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, threadID);
            resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new AssertionException("Assertion in AttachmentDAOImplJDBC.getNumberOfAttachments_inThread.");
            }
            return resultSet.getInt(1);
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.getNumberOfAttachments_inThread.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }
    /************************************************
     * Customized methods come below
     ************************************************/

    /**
     * This is a customized method
     */
    protected static int findAttachID(int postID, int memberID, Timestamp attachCreationDate)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT AttachID");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE PostID = ? AND MemberID = ? AND AttachCreationDate = ? ");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, postID);
            statement.setInt(2, memberID);
            statement.setTimestamp(3, attachCreationDate);
            resultSet = statement.executeQuery();
            if(!resultSet.next()) {
                throw new ObjectNotFoundException("Cannot find the AttachID in table Attachment.");
            }

            return resultSet.getInt("AttachID");
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.findAttachID.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public void delete_inPost(int postID)
        throws DatabaseException {

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

        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, postID);
            statement.executeUpdate();
            m_dirty = true;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.delete_inPost.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: AttachID, PostID, MemberID, AttachFilename, AttachFileSize,
     *                   AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate,
     *                   AttachDownloadCount, AttachOption, AttachStatus
     * Excluded columns:
     */
    public Collection getAttachments_inPost(int postID)
        throws DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Collection retValue = new ArrayList();
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT AttachID, PostID, MemberID, AttachFilename, AttachFileSize, AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount, AttachOption, AttachStatus");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE PostID = ?");
        sql.append(" ORDER BY AttachID ASC ");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, postID);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                AttachmentBean bean = new AttachmentBean();
                bean.setAttachID(resultSet.getInt("AttachID"));
                bean.setPostID(resultSet.getInt("PostID"));
                bean.setMemberID(resultSet.getInt("MemberID"));
                bean.setAttachFilename(resultSet.getString("AttachFilename"));
                bean.setAttachFileSize(resultSet.getInt("AttachFileSize"));
                bean.setAttachMimeType(resultSet.getString("AttachMimeType"));
                bean.setAttachDesc(resultSet.getString("AttachDesc"));
                bean.setAttachCreationIP(resultSet.getString("AttachCreationIP"));
                bean.setAttachCreationDate(resultSet.getTimestamp("AttachCreationDate"));
                bean.setAttachModifiedDate(resultSet.getTimestamp("AttachModifiedDate"));
                bean.setAttachDownloadCount(resultSet.getInt("AttachDownloadCount"));
                bean.setAttachOption(resultSet.getInt("AttachOption"));
                bean.setAttachStatus(resultSet.getInt("AttachStatus"));
                retValue.add(bean);
            }
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.getAttachments_inPost.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: AttachID, PostID, MemberID, AttachFilename, AttachFileSize,
     *                   AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate,
     *                   AttachDownloadCount, AttachOption, AttachStatus
     * Excluded columns:
     */
    public Collection getAttachments_inThread(int threadID)
        throws DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Collection retValue = new ArrayList();
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT AttachID, attachment.PostID, attachment.MemberID, AttachFilename, AttachFileSize, AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount, AttachOption, AttachStatus");
        sql.append(" FROM " + TABLE_NAME + " attachment, " + PostDAO.TABLE_NAME + " post ");
        sql.append(" WHERE attachment.PostID = post.PostID AND post.ThreadID = ? ");
        sql.append(" ORDER BY AttachID ASC ");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, threadID);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                AttachmentBean bean = new AttachmentBean();
                bean.setAttachID(resultSet.getInt("AttachID"));
                bean.setPostID(resultSet.getInt("PostID"));
                bean.setMemberID(resultSet.getInt("MemberID"));
                bean.setAttachFilename(resultSet.getString("AttachFilename"));
                bean.setAttachFileSize(resultSet.getInt("AttachFileSize"));
                bean.setAttachMimeType(resultSet.getString("AttachMimeType"));
                bean.setAttachDesc(resultSet.getString("AttachDesc"));
                bean.setAttachCreationIP(resultSet.getString("AttachCreationIP"));
                bean.setAttachCreationDate(resultSet.getTimestamp("AttachCreationDate"));
                bean.setAttachModifiedDate(resultSet.getTimestamp("AttachModifiedDate"));
                bean.setAttachDownloadCount(resultSet.getInt("AttachDownloadCount"));
                bean.setAttachOption(resultSet.getInt("AttachOption"));
                bean.setAttachStatus(resultSet.getInt("AttachStatus"));
                retValue.add(bean);
            }
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.getAttachments_inThread.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: AttachID, PostID, MemberID, AttachFilename, AttachFileSize,
     *                   AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate,
     *                   AttachDownloadCount, AttachOption, AttachStatus
     * Excluded columns:
     */
    public Collection getAttachments_inForum(int forumID)
        throws DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Collection retValue = new ArrayList();
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT AttachID, attachment.PostID, attachment.MemberID, AttachFilename, AttachFileSize, AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount, AttachOption, AttachStatus");
        sql.append(" FROM " + TABLE_NAME + " attachment, " + PostDAO.TABLE_NAME + " post ");
        sql.append(" WHERE attachment.PostID = post.PostID AND post.ForumID = ? ");
        sql.append(" ORDER BY AttachID ASC ");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, forumID);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                AttachmentBean bean = new AttachmentBean();
                bean.setAttachID(resultSet.getInt("AttachID"));
                bean.setPostID(resultSet.getInt("PostID"));
                bean.setMemberID(resultSet.getInt("MemberID"));
                bean.setAttachFilename(resultSet.getString("AttachFilename"));
                bean.setAttachFileSize(resultSet.getInt("AttachFileSize"));
                bean.setAttachMimeType(resultSet.getString("AttachMimeType"));
                bean.setAttachDesc(resultSet.getString("AttachDesc"));
                bean.setAttachCreationIP(resultSet.getString("AttachCreationIP"));
                bean.setAttachCreationDate(resultSet.getTimestamp("AttachCreationDate"));
                bean.setAttachModifiedDate(resultSet.getTimestamp("AttachModifiedDate"));
                bean.setAttachDownloadCount(resultSet.getInt("AttachDownloadCount"));
                bean.setAttachOption(resultSet.getInt("AttachOption"));
                bean.setAttachStatus(resultSet.getInt("AttachStatus"));
                retValue.add(bean);
            }
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.getAttachments_inForum.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

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

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

    public void updateAttachDesc(int attachID, String attachDesc)
        throws DatabaseException, ObjectNotFoundException {

        Connection connection = null;
        PreparedStatement statement = null;
        String sql = "UPDATE " + TABLE_NAME + " SET AttachDesc = ? WHERE AttachID = ?";
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql);

            if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
                statement.setCharacterStream(1, new StringReader(attachDesc), attachDesc.length());
            } else {
                statement.setString(1, attachDesc);
            }
            statement.setInt(2, attachID);
            if (statement.executeUpdate() != 1) {
                throw new ObjectNotFoundException("Cannot update the Desc in table Attachment. 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 AttachmentDAOImplJDBC.updateAttachDesc.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

}// end of class AttachmentDAOImplJDBC