threaddaomysql.java

Chinaxp 论坛源代码

package org.redsoft.forum.dao.mysql;

/**
 * DAO implmentation for mysql
 *
 * @author Charles Huang
 * @version 1.0
 */
import org.redsoft.forum.dao.PersistentThread;
import org.redsoft.forum.dao.ThreadDAO;
import org.redsoft.forum.dao.ThreadSequence;
import org.redsoft.forum.exception.CategoryNotFoundException;
import org.redsoft.forum.exception.DAOException;
import org.redsoft.forum.exception.ThreadNotFoundException;
import org.redsoft.forum.util.Validation;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Vector;

public class ThreadDAOmySql implements ThreadDAO{

    public ThreadDAOmySql() {
    }


    public Connection getConnection() throws SQLException {
        return MysqlDAOFactory.getConnection();
    }

    private org.redsoft.forum.dao.Thread internalMapResultSet(ResultSet rs) throws SQLException {
        final long id = rs.getLong(PersistentThread.PROPERTY_ID);
        final String title = rs.getString(PersistentThread.PROPERTY_TITLE);
        final String content = rs.getString(PersistentThread.PROPERTY_CONTENT);
        final String author = rs.getString(PersistentThread.PROPERTY_AUTHOR);
        final long timeStamp = rs.getLong(PersistentThread.PROPERTY_TIMESTAMP);
        final long parent_id = rs.getLong(PersistentThread.PROPERTY_PARENT_ID);
        final int category = rs.getInt(PersistentThread.PROPERTY_CATEGORY);
        final long lastUpdated = rs.getLong(PersistentThread.PROPERTY_LAST_UPDATED);
        final int reply =rs.getInt(PersistentThread.PROPERTY_REPLY);
        final int click = rs.getInt(PersistentThread.PROPERTY_CLICK);
        final long repliedThreadID = rs.getLong(PersistentThread.PROPERTY_REPLIED_THREAD);
        final String notifyString = rs.getString(PersistentThread.PROPERTY_NOTIFY);
        final String columnThreadString = rs.getString(PersistentThread.PROPERTY_COLUMN_THREAD);
        boolean notify = notifyString.equals("Y")?true:false;
        boolean columnThread = columnThreadString.equals("Y")?true:false;

        org.redsoft.forum.dao.Thread thread = new org.redsoft.forum.dao.Thread(id,
                title,
                content,
                author,
                timeStamp,
                parent_id,
                category,
                lastUpdated,
                reply,
                click,
                repliedThreadID,
                notify );
        thread.setColumnThread( columnThread );
        return thread;
    }

    /**
     *  Add a new thread to the forum
     *
     *  @param thread - A new persistent thread object
     *  @exception DAOException
     */
    public org.redsoft.forum.dao.Thread addThread( final org.redsoft.forum.dao.Thread thread ) throws DAOException{
        Validation.validateNotNull( thread );
        final org.redsoft.forum.dao.Thread newThread = thread;
        try {
            final Connection conn = getConnection();
            newThread.setId( ThreadSequence.getInstance().getThreadSequence());
            String notifyString = thread.isNotify() == true ?"Y":"N";
            String columnThreadString = thread.isColumnThread() == true ?"Y":"N";
            PreparedStatement stat
                    = conn.prepareStatement("insert into "
                    + PersistentThread.TABLE_PERSISTENCE
                    + " ("
                    + PersistentThread.PROPERTY_ID + ","
                    + PersistentThread.PROPERTY_TITLE + ","
                    + PersistentThread.PROPERTY_CONTENT + ","
                    + PersistentThread.PROPERTY_AUTHOR + ","
                    + PersistentThread.PROPERTY_TIMESTAMP + ","
                    + PersistentThread.PROPERTY_PARENT_ID + ","
                    + PersistentThread.PROPERTY_CATEGORY + ","
                    + PersistentThread.PROPERTY_LAST_UPDATED + ","
                    + PersistentThread.PROPERTY_REPLY + ","
                    + PersistentThread.PROPERTY_REPLIED_THREAD + ","
                    + PersistentThread.PROPERTY_NOTIFY + ","
                    + PersistentThread.PROPERTY_COLUMN_THREAD + ") "
                    + " values( "
                    + newThread.getId() + ","
                    + "?,?,?"
                    + ","
                    + newThread.getTimeStamp() + ","
                    + newThread.getParent_id() + ","
                    + newThread.getCategory() + ","
                    + newThread.getLast_update() + ","
                    + newThread.getReply() + ","
                    + newThread.getReplied_thread() + ","
                    + "?,?"
                    + ")" );

            stat.setString( 1, newThread.getTitle() );
            stat.setString( 2, newThread.getContent() );
            stat.setString( 3, newThread.getAuthor() );
            stat.setString( 4, notifyString );
            stat.setString( 5, columnThreadString );
            stat.execute();

            // This is a reply thread
            if( newThread.getParent_id() != -1 ){

                // Increment the reply and update the last update timestamp of the parent thread record
                stat = conn.prepareStatement("update "
                        + PersistentThread.TABLE_PERSISTENCE
                        + " set "
                        + PersistentThread.PROPERTY_REPLY
                        + "=" + PersistentThread.PROPERTY_REPLY + " + 1,"
                        + PersistentThread.PROPERTY_LAST_UPDATED
                        + "=" + newThread.getTimeStamp()
                        + " where "
                        + PersistentThread.PROPERTY_ID
                        + "="
                        + newThread.getParent_id() );
                stat.execute();
            }

            stat.close();
            conn.close();
        } catch (SQLException e) {
            throw new DAOException( e );
        }
        return newThread;
    }

    /**
     *  Remove a thread from forum
     *
     *  @param thread - A  persistent thread object
     *  @exception DAOException
     */
    public void removeThread( final org.redsoft.forum.dao.Thread thread ) throws DAOException{
        try{
            final Connection conn = getConnection();
            PreparedStatement stat
                    = conn.prepareStatement( "delete from threads where id=" + thread.getId() );
            stat.execute();

            // Increment the reply and update the last update timestamp of the parent thread record
            stat = conn.prepareStatement("update "
                    + PersistentThread.TABLE_PERSISTENCE
                    + " set "
                    + PersistentThread.PROPERTY_REPLY
                    + "=" + PersistentThread.PROPERTY_REPLY + " - 1 "
                    + " where "
                    + PersistentThread.PROPERTY_ID
                    + "="
                    + thread.getParent_id() );
            stat.execute();
            stat.close();
            conn.close();
        } catch (SQLException e) {
            throw new DAOException( e );
        }
    }

    /**
     *  Remove a thread from forum
     *
     *  @param threadID - Thread ID
     *  @exception DAOException
     */
    public void removeThread( final long threadID ) throws DAOException{
        try{
            final org.redsoft.forum.dao.Thread thread = findByUID( threadID );
            removeThread( thread );
        }catch( final ThreadNotFoundException threadNotFoundException ){
            // Nothing we can do
        }
    }

    /**
     *  Get a thread given a unique id
     *
     * @return PersistentThread - The corresponding thread
     * @exception DAOException
     * @exception ThreadNotFoundException
     */
    public org.redsoft.forum.dao.Thread findByUID( final long id )
            throws DAOException,
            ThreadNotFoundException {
        try{
            final Connection conn = getConnection();
            final PreparedStatement stat
                    = conn.prepareStatement( "select * from threads where " + PersistentThread.PROPERTY_ID
                    + " = " + id );
            final ResultSet resultSet = stat.executeQuery();
            if( resultSet.next() ){
                final org.redsoft.forum.dao.Thread thread = internalMapResultSet(resultSet);
                stat.close();
                conn.close();
                return thread;
            }
            else {
                stat.close();
                conn.close();
                throw new ThreadNotFoundException( "with id :" + id );
            }
        } catch (SQLException e) {
            throw new DAOException( e );
        }
    }

    /**
     *  Get the top-level threads under the given category from start index
     *
     *  @param category - The category
     *  @param startIndex - The start index for retrieveing the records
     *  @param endIndex - The end index for retrieveing the records
     *  @return Collection - A collection of threads under the given category
     */
    public Collection findByCategory( final int category,
                                      final int startIndex,
                                      final int endIndex ) throws DAOException,
            CategoryNotFoundException {
        final ArrayList result = new ArrayList();
        try{
            final Connection conn = getConnection();
            final PreparedStatement stat
                    = conn.prepareStatement( "select * from threads where " + PersistentThread.PROPERTY_CATEGORY
                    + "= " + category + " and "
                    + PersistentThread.PROPERTY_PARENT_ID
                    + "=-1 order by " + PersistentThread.PROPERTY_LAST_UPDATED + " DESC" );
            final ResultSet resultSet = stat.executeQuery();
            if( resultSet.absolute( startIndex ) )
            {
                do{
                    result.add( new org.redsoft.forum.dao.Thread( resultSet.getLong( PersistentThread.PROPERTY_ID ),
                            resultSet.getString( PersistentThread.PROPERTY_TITLE ),
                            resultSet.getString( PersistentThread.PROPERTY_CONTENT ),
                            resultSet.getString( PersistentThread.PROPERTY_AUTHOR ),
                            resultSet.getLong( PersistentThread.PROPERTY_TIMESTAMP ),
                            resultSet.getLong( PersistentThread.PROPERTY_PARENT_ID ),
                            resultSet.getInt( PersistentThread.PROPERTY_CATEGORY ),
                            resultSet.getLong( PersistentThread.PROPERTY_LAST_UPDATED),
                            resultSet.getInt( PersistentThread.PROPERTY_REPLY ),
                            resultSet.getInt(PersistentThread.PROPERTY_CLICK),
                            resultSet.getLong(PersistentThread.PROPERTY_REPLIED_THREAD),
                            false ) );
                }while( resultSet.next() && (resultSet.getRow() <= endIndex) );

            }
            stat.close();
            conn.close();
        } catch (SQLException e) {
            throw new DAOException( e );
        }
        return result;
    }

    /**
     *  Get the top-level threads count under the given category from start index
     *
     *  @param category - The category
     *  @return int - The top-level threads count under a category
     */
    public int findCountByCategory( final int category ) throws DAOException{
        int count = 0;
        try{
            final Connection conn = getConnection();
            final PreparedStatement stat
                    = conn.prepareStatement( "select count(*)"
                    + " from " + PersistentThread.TABLE_PERSISTENCE
                    +" where " + PersistentThread.PROPERTY_CATEGORY
                    + "= " + category + " and "
                    + PersistentThread.PROPERTY_PARENT_ID
                    + "=-1 ");
            final ResultSet resultSet = stat.executeQuery();

            // Get the first column which is the only column,the count of threads
            // under this category
            if( resultSet.next() ){
                count = resultSet.getInt( 1 );
            }
            stat.close();
            conn.close();
        } catch (SQLException e) {
            throw new DAOException( e );
        }
        return count;
    }

    /**
     *  Get all the top-level threads under the given category
     *
     *  @param category - The category
     *  @return Collection - A collection of threads under the given category
     */
    public Collection findByCategory( final int category )
            throws DAOException,
            CategoryNotFoundException{
        final ArrayList result = new ArrayList();
        try{
            final Connection conn = getConnection();
            final PreparedStatement stat
                    = conn.prepareStatement( "select * from threads where " + PersistentThread.PROPERTY_CATEGORY
                    + "= " + category + " and "
                    + PersistentThread.PROPERTY_PARENT_ID
                    + "=-1 order by " + PersistentThread.PROPERTY_TIMESTAMP );
            final ResultSet resultSet = stat.executeQuery();
            while( resultSet.next() ){
                PersistentThread pt = internalMapResultSet(resultSet);
                result.add(pt);
            }
            stat.close();
            conn.close();
        } catch (SQLException e) {
            throw new DAOException( e );
        }
        return result;
    }


    /**
     *  Get all the children threads given a id of a parent threa
     *
     *  @return Collection - A collection of children threads
     *
     */