entriesdaoimpl.java

JAVA 经典案例 源代码 Part2.

/*
 * 
 * 开发时间:2004-08-20 at 05:40:07
 *
 * 网站:网站?
 *
 * 网站:www.helpsoft.org  电子邮件:[e-mail]
 */

package org.helpsoft.blog.jdbc;

import org.helpsoft.blog.dao.*;
import org.helpsoft.blog.dto.*;
import org.helpsoft.blog.exceptions.*;
import java.sql.Connection;
import java.sql.Types;
import java.util.Collection;
import org.apache.log4j.Logger;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.util.List;
import java.util.Iterator;
import java.util.ArrayList;
import java.sql.CallableStatement;

public class EntriesDaoImpl extends AbstractDataAccessObject implements EntriesDao
{
	/**
	 * The factory class for this DAO has two versions of the create() method - one that
takes no arguments and one that takes a Connection argument. If the Connection version
is chosen then the connection will be stored in this attribute and will be used by all
calls to this DAO, otherwise a new Connection will be allocated for each operation.
	 */
	protected java.sql.Connection userConn;

	protected static final Logger logger = Logger.getLogger( EntriesDaoImpl.class );

	/**
	 * All finder methods in this class use this SELECT constant to build their queries
	 */
	protected final String SQL_SELECT = "SELECT t0.entryid, t0.name, t0.author, t0.date, t0.content FROM " + getTableName() + " t0";

	/**
	 * SQL INSERT statement for this table
	 */
	protected final String SQL_INSERT = "INSERT INTO " + getTableName() + " ( name, author, date, content ) VALUES ( ?, ?, ?, ? );SELECT @@IDENTITY";

	/**
	 * SQL UPDATE statement for this table
	 */
	protected final String SQL_UPDATE = "UPDATE " + getTableName() + " SET entryid = ?, name = ?, author = ?, date = ?, content = ? WHERE entryid = ?";

	/**
	 * SQL DELETE statement for this table
	 */
	protected final String SQL_DELETE = "DELETE FROM " + getTableName() + " WHERE entryid = ?";

	/**
	 * Index of column entryid
	 */
	protected static final int COLUMN_ENTRYID = 1;

	/**
	 * Index of column name
	 */
	protected static final int COLUMN_NAME = 2;

	/**
	 * Index of column author
	 */
	protected static final int COLUMN_AUTHOR = 3;

	/**
	 * Index of column date
	 */
	protected static final int COLUMN_DATE = 4;

	/**
	 * Index of column content
	 */
	protected static final int COLUMN_CONTENT = 5;

	/**
	 * Number of columns
	 */
	protected static final int NUMBER_OF_COLUMNS = 5;

	/**
	 * Index of primary-key column entryid
	 */
	protected static final int PK_COLUMN_ENTRYID = 1;

	/**
	 * Inserts a new row in the entries table.
	 */
	public EntriesPk insert(Entries dto) throws EntriesDaoException
	{
		// declare variables
		final boolean isConnSupplied = (userConn != null);
		Connection conn = null;
		CallableStatement stmt = null;
		ResultSet rs = null;

		try {
			// get the user-specified connection or get a connection from the ResourceManager
			conn = isConnSupplied ? userConn : ResourceManager.getPool().getConnection();

			stmt = conn.prepareCall( SQL_INSERT );
			stmt.setString( COLUMN_NAME - 1, dto.getName() );
			stmt.setString( COLUMN_AUTHOR - 1, dto.getAuthor() );
			stmt.setString( COLUMN_DATE - 1, dto.getDate() );
			stmt.setString( COLUMN_CONTENT - 1, dto.getContent() );
			if (logger.isDebugEnabled()) {
				logger.debug( "Executing " + SQL_INSERT);
			}

			stmt.execute();
			int rows = stmt.getUpdateCount();
			if (logger.isDebugEnabled()) {
				logger.debug( rows + " rows affected");
			}


			// retrieve values from IDENTITY columns
			rs = stmt.getResultSet();
			if (stmt.getMoreResults()) {
				rs = stmt.getResultSet();
			}

			if (rs.next()) {
				dto.setEntryid( rs.getInt( 1 ) );
			}

			return dto.createPk();
		}
		catch (SQLException _e) {
			logger.error( "SQLException: " + _e.getMessage(), _e );
			throw new EntriesDaoException( "SQLException: " + _e.getMessage(), _e );
		}
		catch (Exception _e) {
			logger.error( "Exception: " + _e.getMessage(), _e );
			throw new EntriesDaoException( "Exception: " + _e.getMessage(), _e );
		}
		finally {
			ResourceManager.close(stmt);
			if (!isConnSupplied) {
				ResourceManager.close(conn);
			}

		}

	}

	/**
	 * Updates a single row in the entries table.
	 */
	public void update(EntriesPk pk, Entries dto) throws EntriesDaoException
	{
		long t1 = System.currentTimeMillis();
		// declare variables
		final boolean isConnSupplied = (userConn != null);
		Connection conn = null;
		PreparedStatement stmt = null;

		try {
			// get the user-specified connection or get a connection from the ResourceManager
			conn = isConnSupplied ? userConn : ResourceManager.getPool().getConnection();

			if (logger.isDebugEnabled()) {
				logger.debug( "Executing " + SQL_UPDATE + " with DTO: " + dto);
			}

			stmt = conn.prepareStatement( SQL_UPDATE );
			stmt.setInt( COLUMN_ENTRYID, dto.getEntryid() );
			stmt.setString( COLUMN_NAME, dto.getName() );
			stmt.setString( COLUMN_AUTHOR, dto.getAuthor() );
			stmt.setString( COLUMN_DATE, dto.getDate() );
			stmt.setString( COLUMN_CONTENT, dto.getContent() );
			stmt.setInt( 6, pk.getEntryid() );
			int rows = stmt.executeUpdate();
			long t2 = System.currentTimeMillis();
			if (logger.isDebugEnabled()) {
				logger.debug( rows + " rows affected (" + (t2-t1) + " ms)");
			}

		}
		catch (SQLException _e) {
			logger.error( "SQLException: " + _e.getMessage(), _e );
			throw new EntriesDaoException( "SQLException: " + _e.getMessage(), _e );
		}
		catch (Exception _e) {
			logger.error( "Exception: " + _e.getMessage(), _e );
			throw new EntriesDaoException( "Exception: " + _e.getMessage(), _e );
		}
		finally {
			ResourceManager.close(stmt);
			if (!isConnSupplied) {
				ResourceManager.close(conn);
			}

		}

	}

	/**
	 * Deletes a single row in the entries table.
	 */
	public void delete(EntriesPk pk) throws EntriesDaoException
	{
		long t1 = System.currentTimeMillis();
		// declare variables
		final boolean isConnSupplied = (userConn != null);
		Connection conn = null;
		PreparedStatement stmt = null;

		try {
			// get the user-specified connection or get a connection from the ResourceManager
			conn = isConnSupplied ? userConn : ResourceManager.getPool().getConnection();

			if (logger.isDebugEnabled()) {
				logger.debug( "Executing " + SQL_DELETE + " with PK: " + pk);
			}

			stmt = conn.prepareStatement( SQL_DELETE );
			stmt.setInt( 1, pk.getEntryid() );
			int rows = stmt.executeUpdate();
			long t2 = System.currentTimeMillis();
			if (logger.isDebugEnabled()) {
				logger.debug( rows + " rows affected (" + (t2-t1) + " ms)");
			}

		}
		catch (SQLException _e) {
			logger.error( "SQLException: " + _e.getMessage(), _e );
			throw new EntriesDaoException( "SQLException: " + _e.getMessage(), _e );
		}
		catch (Exception _e) {
			logger.error( "Exception: " + _e.getMessage(), _e );
			throw new EntriesDaoException( "Exception: " + _e.getMessage(), _e );
		}
		finally {
			ResourceManager.close(stmt);
			if (!isConnSupplied) {
				ResourceManager.close(conn);
			}

		}

	}

	/**
	 * Returns the rows from the entries table that matches the specified primary-key value.
	 */
	public Entries findByPrimaryKey(EntriesPk pk) throws EntriesDaoException
	{
		return findByPrimaryKey( pk.getEntryid() );
	}

	/**
	 * Returns all rows from the entries table.
	 */
	public Entries[] findAll() throws EntriesDaoException
	{
		return findByDynamicSelect( SQL_SELECT + " ORDER BY entryid", null );
	}

	/**
	 * Returns all rows from the entries table that match the criteria 'entryid = :entryid'.
	 */
	public Entries[] findWhereEntryidEquals(int entryid) throws EntriesDaoException
	{
		return findByDynamicSelect( SQL_SELECT + " WHERE entryid = ? ORDER BY entryid", new Object[] {  new Integer(entryid) } );
	}

	/**
	 * Returns all rows from the entries table that match the criteria 'name = :name'.
	 */
	public Entries[] findWhereNameEquals(String name) throws EntriesDaoException
	{
		return findByDynamicSelect( SQL_SELECT + " WHERE name = ? ORDER BY name", new Object[] { name } );
	}

	/**
	 * Returns all rows from the entries table that match the criteria 'author = :author'.
	 */
	public Entries[] findWhereAuthorEquals(String author) throws EntriesDaoException
	{
		return findByDynamicSelect( SQL_SELECT + " WHERE author = ? ORDER BY author", new Object[] { author } );
	}

	/**
	 * Returns all rows from the entries table that match the criteria 'date = :date'.
	 */
	public Entries[] findWhereDateEquals(String date) throws EntriesDaoException
	{
		return findByDynamicSelect( SQL_SELECT + " WHERE date = ? ORDER BY date", new Object[] { date } );
	}

	/**
	 * Returns all rows from the entries table that match the criteria 'entryid = :entryid'.
	 */
	public Entries findByPrimaryKey(int entryid) throws EntriesDaoException
	{
		Entries ret[] = findByDynamicSelect( SQL_SELECT + " WHERE entryid = ?", new Object[] {  new Integer(entryid) } );
		return ret.length==0 ? null : ret[0];
	}

	/**
	 * Method 'EntriesDaoImpl'
	 *
	 */
	public EntriesDaoImpl()
	{
	}

	/**
	 * Method 'EntriesDaoImpl'
	 *
	 * @param userConn
	 */
	public EntriesDaoImpl(final java.sql.Connection userConn)
	{
		this.userConn = userConn;
	}

	/**
	 * Method 'getTableName'
	 *
	 * @return String
	 */
	public String getTableName()
	{
		return "entries";
	}

	/**
	 * Fetches a single row from the result set
	 */
	protected Entries fetchSingleResult(ResultSet rs) throws SQLException
	{
		if (rs.next()) {
			Entries dto = new Entries();
			populateDto( dto, rs);
			return dto;
		} else {
			return null;
		}

	}

	/**
	 * Fetches multiple rows from the result set
	 */
	protected Entries[] fetchMultiResults(ResultSet rs) throws SQLException
	{
		Collection resultList = new ArrayList();
		while (rs.next()) {
			Entries dto = new Entries();
			populateDto( dto, rs);
			resultList.add( dto );
		}

		Entries ret[] = new Entries[ resultList.size() ];
		resultList.toArray( ret );
		return ret;
	}

	/**
	 * Populates a DTO with data from a ResultSet
	 */
	protected void populateDto(Entries dto, ResultSet rs) throws SQLException
	{
		dto.setEntryid( rs.getInt( COLUMN_ENTRYID ) );
		dto.setName( rs.getString( COLUMN_NAME ) );
		dto.setAuthor( rs.getString( COLUMN_AUTHOR ) );
		dto.setDate( rs.getString( COLUMN_DATE ) );
		dto.setContent( rs.getString( COLUMN_CONTENT ) );
	}

	/**
	 * Returns all rows from the entries table that match the specified arbitrary SQL statement
	 */
	public Entries[] findByDynamicSelect(String sql, Object[] sqlParams) throws EntriesDaoException
	{
		// declare variables
		final boolean isConnSupplied = (userConn != null);
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			// get the user-specified connection or get a connection from the ResourceManager
			conn = isConnSupplied ? userConn : ResourceManager.getPool().getConnection();

			// construct the SQL statement
			final String SQL = sql;


			if (logger.isDebugEnabled()) {
				logger.debug( "Executing " + SQL);
			}

			// prepare statement
			stmt = conn.prepareStatement( SQL );

			// bind parameters
			for (int i=0; sqlParams!=null && i<sqlParams.length; i++ ) {
				stmt.setObject( i+1, sqlParams[i] );
			}


			rs = stmt.executeQuery();

			// fetch the results
			return fetchMultiResults(rs);
		}
		catch (SQLException _e) {
			logger.error( "SQLException: " + _e.getMessage(), _e );
			throw new EntriesDaoException( "SQLException: " + _e.getMessage(), _e );
		}
		catch (Exception _e) {
			logger.error( "Exception: " + _e.getMessage(), _e );
			throw new EntriesDaoException( "Exception: " + _e.getMessage(), _e );
		}
		finally {
			ResourceManager.close(rs);
			ResourceManager.close(stmt);
			if (!isConnSupplied) {
				ResourceManager.close(conn);
			}

		}

	}

	/**
	 * Returns all rows from the entries table that match the specified arbitrary SQL statement
	 */
	public Entries[] findByDynamicWhere(String sql, Object[] sqlParams) throws EntriesDaoException
	{
		// declare variables
		final boolean isConnSupplied = (userConn != null);
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			// get the user-specified connection or get a connection from the ResourceManager
			conn = isConnSupplied ? userConn : ResourceManager.getPool().getConnection();

			// construct the SQL statement
			final String SQL = SQL_SELECT + " WHERE " + sql;


			if (logger.isDebugEnabled()) {
				logger.debug( "Executing " + SQL);
			}

			// prepare statement
			stmt = conn.prepareStatement( SQL );

			// bind parameters
			for (int i=0; sqlParams!=null && i<sqlParams.length; i++ ) {
				stmt.setObject( i+1, sqlParams[i] );
			}


			rs = stmt.executeQuery();

			// fetch the results
			return fetchMultiResults(rs);
		}
		catch (SQLException _e) {
			logger.error( "SQLException: " + _e.getMessage(), _e );
			throw new EntriesDaoException( "SQLException: " + _e.getMessage(), _e );
		}
		catch (Exception _e) {
			logger.error( "Exception: " + _e.getMessage(), _e );
			throw new EntriesDaoException( "Exception: " + _e.getMessage(), _e );
		}
		finally {
			ResourceManager.close(rs);
			ResourceManager.close(stmt);
			if (!isConnSupplied) {
				ResourceManager.close(conn);
			}

		}

	}

}