package com.sumware.sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.Collection;
import java.util.Iterator;

/**
 * DataAccess is designed to remove the complexities of the managing
 * a connection [or ConnectionPool] from the request of data.  
 * <p>
 * Adapted from Ted Husted's Scaffold package found at: 
 * <a href=http://www.husted.com/struts/resources.htm>
 * @author Ted Husted
 * @author Nathan Anderson
 * @version $Revision: 1.0 $ $Date: 2001/12/07 $
 */

public final class DataAccess
{
	
    /**
     * Executes an SQL statement to the DBMS configured 
     * with the ConnectionPool and returns a RowList.  
     * <p>
     * Command must be a SELECT statement. 
     * <p>
     * @param resource The name of the datasource to select from
     * @param command The SQL statement to execute.
     * @param params ArrayList of SQLParam values to insert into the 
     * prepared statement.
     * @returns RowList The result of the query
     * @exception SQLException if SQL error occurs
     */
     public static RowList executeQuery(String resource,
            String command, Collection params)
            throws SQLException {
            	
		SQLParam param = null;
		RowList rowList = null;	
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = ConnectionPool.getConnection(resource);
            statement = connection.prepareStatement(command);
            if (params!=null) {
	            int p = 1;
            	for (Iterator i=params.iterator(); i.hasNext(); p++) {
            		param = (SQLParam) i.next();
	        		statement.setObject(p, param.object(), param.type());
	        	}
	        }
            resultSet = statement.executeQuery();
            if (resultSet!=null) {
				rowList = new RowList(resultSet);
			}
        }
        finally {
            try {
                if (resultSet != null) resultSet.close();
                if (statement != null) statement.close();
                if (connection!= null) connection.close();
            }
            catch (SQLException sqle) {}
        }
        return rowList;
    } // ---- End executeQuery ----

	/**
     * Executes an SQL statement to the DBMS configured 
     * with the ConnectionPool.  
     * <p>
     * Command may be an INSERT, UPDATE, or DELETE statement
     * or anything that returns nothing, such as a DDL
     * statement.
     * <p>
     * @param resource The name of the datasource to update to
     * @param command The SQL statement to execute.
     * @param params ArrayList of SQLParams values to insert into the 
     * prepared statement.
     * @returns int The number of rows affected
     * @exception SQLException if SQL error occurs
     */
    public static int executeUpdate(String resource, 
    		String command, Collection params)
    		throws SQLException {

		SQLParam param = null;
        Connection connection = null;
        PreparedStatement statement = null;
        int result = 0;
        try {
            connection = ConnectionPool.getConnection(resource);
            statement = connection.prepareStatement(command);
            if (params!=null) {
	            int p = 1;
            	for (Iterator i=params.iterator(); i.hasNext(); p++) {
            		param = (SQLParam) i.next();
	        		statement.setObject(p, param.object(), param.type());
	        	}
        	}
            result = statement.executeUpdate();
        } finally {
            try {
                if (statement != null) statement.close();
                if (connection!= null) connection.close();
            }
            catch (SQLException sqle) {}
        }
        return result;
    } // ---- End executeUpdate ----

    /**
     * Executes an SQL statement to the DBMS configured 
     * with the ConnectionPool.  
     * <p>
     * Command must be a SELECT statement.  Used mostly for 
     * "COUNT" statements.
     * <p>
     * @param resource The name of the datasource to select from
     * @param command The SQL statement to execute.
     * @param params ArrayList of SQLParams values to insert into the 
     * prepared statement.
     * @returns int The value of the first column of the first row
     * @exception SQLException if SQL error occurs
     */
	public static int execute(String resource,
            String command, Collection params) 
            throws SQLException {
           	
		SQLParam param = null;
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        int result = 0;
        try {
            connection = ConnectionPool.getConnection(resource);
            statement = connection.prepareStatement(command);
            if (params!=null) {
	            int p = 1;
            	for (Iterator i=params.iterator(); i.hasNext(); p++) {
            		param = (SQLParam) i.next();
	        		statement.setObject(p, param.object(), param.type());
	        	}
	        }
            resultSet = statement.executeQuery();
            if (resultSet!=null) {            	
	            resultSet.next();
	            result = resultSet.getInt(1);
			}
        }
        finally {
            try {
                if (resultSet != null) resultSet.close();
                if (statement != null) statement.close();
                if (connection!= null) connection.close();
            }
            catch (SQLException sqle) {}
        }
        return result;
    } // ---- End execute ----	}	
}