threaddaomysql.java

一个功能较为完善的论坛

package org.redsoft.forum.dao.mysql;

/**
 * DAO implmentation for mysql
 *
 * @author Charles Huang
 * @version 1.0
 */
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.redsoft.forum.dao.PersistentThread;
import java.util.Collection;
import org.redsoft.forum.exception.ThreadNotFoundException;
import java.sql.ResultSet;
import org.redsoft.forum.util.Validation;
import java.util.ArrayList;
import org.redsoft.forum.exception.CategoryNotFoundException;
import org.redsoft.forum.dao.ThreadSequence;
import org.redsoft.forum.dao.ThreadDAO;
import org.redsoft.forum.fixture.MysqlFixture;
import org.redsoft.forum.util.StringUtils;
import org.redsoft.forum.web.Thread;
import java.util.Vector;
import java.util.Collection;

public class ThreadDAOmySql implements ThreadDAO{

    public ThreadDAOmySql() {
	}


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

	private PersistentThread 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);
        boolean notify = notifyString.equals("Y")?true:false;

		PersistentThread thread = new PersistentThread(id,
                   title,
	               content,
        	       author,
	    	       timeStamp,
                   parent_id,
                   category,
                   lastUpdated,
                   reply,
				   click,
				   repliedThreadID,
                   notify );
		return thread;
	}

	/**
     *  Add a new thread to the forum
     *
     *  @param newThread - A new persistent thread object
     *  @exception SQLException
     */
    public PersistentThread addThread( final PersistentThread thread ) throws SQLException{
        Validation.validateNotNull( thread );
        final Connection conn = getConnection();
        final PersistentThread newThread = thread;
        newThread.setID( ThreadSequence.getInstance().getThreadSequence());
        String notifyString = thread.getNotify() == 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 + ") "
                                    + " values( "
                                    + newThread.getID() + ","
                                    + "?,?,?"
                                    + ","
                                    + newThread.getTimeStamp() + ","
                                    + newThread.getParentID() + ","
                                    + newThread.getCategory() + ","
                                    + newThread.getLastUpdated() + ","
						            + newThread.getReply() + ","
						            + newThread.getRepliedThreadID() + ","
                                    + "?"
                                    + ")" );

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

		// This is a reply thread
		if( newThread.getParentID() != -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.getParentID() );
			stat.execute();
		}


        stat.close();
        conn.close();
        return newThread;
    }

    /**
     *  Remove a thread from forum
     *
     *  @param newThread - A  persistent thread object
     *  @exception SQLException
     */
    public void removeThread( final PersistentThread thread ) throws SQLException{
        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.getParentID() );
		stat.execute();
		stat.close();
        conn.close();
    }

	/**
     *  Remove a thread from forum
     *
     *  @param newThread - A  persistent thread object
     *  @exception SQLException
     */
    public void removeThread( final long threadID ) throws SQLException{
      	try{
	      	final PersistentThread 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
     * @excetpion SQLException
     * @ThreadNotFoundException
     */
    public PersistentThread findByUID( final long id )
                                                throws SQLException,
                                                       ThreadNotFoundException {
        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 PersistentThread thread = internalMapResultSet(resultSet);
            stat.close();
            conn.close();
            return thread;
        }
        else {
           stat.close();
           conn.close();
           throw new ThreadNotFoundException();
        }
    }

    /**
     *  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 length - The number of records to be tretieved
     *  @return Collection - A collection of threads under the given category
     */
    public Collection findByCategory( final int category,
                                      final int startIndex,
                                      final int endIndex ) throws SQLException,
			CategoryNotFoundException {
        final Connection conn = getConnection();
        final ArrayList result = new ArrayList();
        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 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)) );
            }while( resultSet.next() && (resultSet.getRow() <= endIndex) );

        }
        stat.close();
        conn.close();
        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