/*

  * Copyright 2001,2004 The Apache Software Foundation.

  * 

  * Licensed under the Apache License, Version 2.0 (the "License");

  * you may not use this file except in compliance with the License.

  * You may obtain a copy of the License at

  * 

  *      http://www.apache.org/licenses/LICENSE-2.0

  * 

  * Unless required by applicable law or agreed to in writing, software

  * distributed under the License is distributed on an "AS IS" BASIS,

  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

  * See the License for the specific language governing permissions and

  * limitations under the License.

  */

 package org.apache.commons.scaffold.sql;

 import java.sql.Connection;

 import java.sql.SQLException;

 import java.sql.PreparedStatement;

 import java.sql.Statement;

 import java.sql.ResultSet;

 import java.util.Collection;

 /**

  * General purpose SQL Statements.

  *

  * @author Ted Husted

  * @version $Revision: 155464 $ $Date: 2005-02-26 13:26:54 +0000 (Sat, 26 Feb 2005) $

  */

 public final class StatementUtils {

     /**

      * The delimiter used by a SQL "LIKE" expression.

      */

     private static final String LIKE_DELIM = "%";

     /**

      * Prepare the parameter for use in a SQL "LIKE" expression.

      */

     public static final String like(String parameter) {

         return LIKE_DELIM + parameter + LIKE_DELIM;

     }

     /**

      * Create a new database table in an existing database,

      * by sending "CREATE TABLE " and the parameters to

      * the DBMS configured with the ConnectionPool.

      * <p>

      * For safety, does <b>not</b> drop table first.

      * <p>

      * Returns false if a SQL exception is thrown; exception

      * is written to the servlet log.

      *

      * @param resource The database resource key or null for default

      * @param tableName The name of the table to create

      * @param tableCreate The SQL command defining the

      * fields and indices

      * @return Result of statement.execute()

      * @exception SQL Exception if SQL error occurs

      */

     public static final int createTable(

             String resource,

             String tableName,

             String tableCreate)

         throws SQLException {

         return executeUpdate(resource,"CREATE TABLE " +

             tableName + " " + tableCreate);

     } // end createTable

    /**

      * Returns next sequential key for given table.

      * <p>

      * This ensures compatibility for DBMS products that do not

      * support auto-incrementing a key field.

      * <p>

      * Intended to generate primary keys, but could be used to

      * create other serial numbers based on an unsigned int.

      * <p>

      * Allocating the key involves reading the current key, and

      * then incrementing the key for the next user. The method

      * is synchronized so that two threads do not read the

      * same key before it is incremented.

      * <p>

      * This routine is "unrolled" for efficiency, but the same

      * effect can be accomplished using <code>getColumn()</code>

      * to fetch the next key,

      * and <code>executeUpdate</code> to increment the key.

      *

      * @param resource The database resource key or null for default

      * @param column The column to return from the result set.

      * @param query The SQL statement to fetch the next key.

      * @param key The table name or other replaceable parameter.

      * @param query The SQL statement to increment the next key.

      * @exception SQLException if SQL error occurs

      */

     public static final synchronized Object createKey(

             String resource,

             int column,

             String query,

             Object key,

             String update)

             throws SQLException {

         Connection connection = null;

         ResultSet resultSet = null;

         PreparedStatement preparedStatement = null;

         Object result = null;

         int resultCode = 0;

         try {

             connection =

                 ConnectionAdaptor.getPool().getConnection(resource);

         // fetch the next key

         // - Object next = getColumn(resource,1,query,key);

             preparedStatement = connection.prepareStatement(query);

             preparedStatement.setObject(1,key);

             resultSet = preparedStatement.executeQuery();

             if (resultSet.next()) {

                result = resultSet.getObject(column);

             }

         // increment key (SQL command handles increment)

         // - int result = executeUpdate(resource,update,key);

             if (preparedStatement!=null) preparedStatement.close();

             preparedStatement = connection.prepareStatement(update);

             preparedStatement.setObject(1,key);

             resultCode = preparedStatement.executeUpdate();

         }

         finally {

             try {

                 if (preparedStatement!=null) preparedStatement.close();

                 if (resultSet!=null) resultSet.close();

                 if (connection!=null) connection.close();

             }

             catch (SQLException sqle) {

                 // do nothing

             }

         }

         return result;

    } // end createKey

 // ------------------------------------------------------ executeUpdate

     /**

      * Used to select correct executeUpdate signature.

      */

     private static final Object[] nullParams = null;

     /**

      * Prepares statement using SQL statement and executes

      * with 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.

      *

      * @param connection The database connection or null to acquire

      * @param resource The database resource key or null for default

      * @param command The SQL statement to execute.

      * @param parameters An array of parameter objects

      * @exception SQLException if SQL error occurs

      */

     public static final int executeUpdate(

             Connection connection,

             String resource,

             String command,

             Object[] parameters)

         throws SQLException {

         boolean acqConnection = (connection==null);

         Statement statement = null;

         PreparedStatement preparedStatement = null;

         int result = 0;

         try {

             if (acqConnection) {

                 connection =

                     ConnectionAdaptor.getPool().getConnection(resource);

             }

             if ((parameters==null) || (parameters.length==0)) {

                 statement = connection.createStatement();

                 result = statement.executeUpdate(command);

             }

             else {

                 preparedStatement = connection.prepareStatement(command);

                 for (int i=0; i<parameters.length; i++) {

                     preparedStatement.setObject(i+1, parameters[i]);

                 }

                 result = preparedStatement.executeUpdate();

             }

         } // end try

         finally {

             try {

                 if (preparedStatement != null) preparedStatement.close();

                 if (statement != null) statement.close();

                 if ((acqConnection) && (connection!= null)) connection.close();

             }

             catch (SQLException sqle) {

                 // do nothing

             }

         }

         return result;

     } // end executeUpdate

     /**

      * Convenience method for calling

      * <code>executeUpdate(String,String,Object[]);</code>

      * with a single Object parameter.

      *

      * @param resource The database resource key or null for default

      * @param command The SQL statement to execute.

      * @param parameter A single parameter to use with command

      * @exception SQLException if SQL error occurs

      */

     public static final int executeUpdate(

             String resource,

             String command,

             Object parameter)

         throws SQLException {

         Object[] parameters = new Object[1];

         parameters[0] = parameter;

         return executeUpdate(null,resource,command,parameters);

     } // end executeUpdate

     /**

      * Convenience method for calling

      * <code>executeUpdate(String,String,Object[]);</code>

      * without a parameter list.

      *

      * @param resource The database resource key or null for default

      * @param command The SQL statement to execute.

      * @exception SQLException if SQL error occurs

      */

     public static final int executeUpdate(

             String resource,

             String command)

         throws SQLException {

         return executeUpdate(null,resource,command,nullParams);

     } // end executeUpdate

     /**

      * Prepares statement using SQL statement and executes

      * with 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.

      *

      * @param resource The database resource key or null for default

      * @param command The SQL statement to execute.

      * @param parameters An array of parameter objects

      * @exception SQLException if SQL error occurs

      */

     public static final int executeUpdate(

             String resource,

             String command,

             Object[] parameters)

         throws SQLException {

         return executeUpdate(null,resource,command,parameters);

     } // end executeUpdate

     /**

      * Convenience method for calling

      * <code>executeUpdate(String,String,Object[]);</code>

      * without a parameter list.

      *

      * @param resource The database resource key or null for default

      * @param command The SQL statement to execute.

      * @exception SQLException if SQL error occurs

      */

     public static final int executeUpdate(

             String command)

         throws SQLException {

         return executeUpdate(null,null,command,nullParams);

     } // end executeUpdate

 // ------------------------------------------------------- executeQuery

     /**

      * Merges a SQL command with an array of parameters.

      * Any number or no parameters may be passed.

      * The parameters parameter may also be null.

      * <p>

      * The Statement or PreparedStatement used internally

      * are closed, but the Connection is left open so that the

      * ResultSet remains valid.

      * The caller should close the Connection and ResultSet

      * as soon as possible, especially if they are pooled.

      *

      * @param resource The database resource key or null for default

      * @param target The JavaBean object to return in the collection.

      * @param command The SQL statement to prepare and execute.

      * @param parameters The replaceable parameters to use with command.

      * @exception SQLException if SQL error occurs

      */

     public static final ResultSet executeQuery(

             Connection connection,

             String command,

             Object[] parameters)

         throws SQLException {

         Statement statement = null;

         PreparedStatement preparedStatement = null;

         ResultSet resultSet = null;

         try {

             if ((parameters==null) || (parameters.length==0)) {

                 statement = connection.createStatement();

                 resultSet = statement.executeQuery(command);

             }

             else {

                 preparedStatement = connection.prepareStatement(command);

                 for (int i=0; i<parameters.length; i++) {

                     preparedStatement.setObject(i+1, parameters[i]);

                 }

                 resultSet = preparedStatement.executeQuery();

             }

         }

         finally {

             try {

                 if (statement != null) statement.close();

                 if (preparedStatement != null) preparedStatement.close();

             }

             catch (SQLException sqle) {}

         }

         return resultSet;

     } // end executeQuery

 // ---------------------------------------------------------- getColumn

     /**

      * Merges a SQL command with an array of parameters

      * and returns a column from the first record of the result set as

      * an Object.

      * If an empty set results, null is returned.

      * Any number or no parameters may be passed.

      * The parameters parameter may also be null.

      * The SQL Statement/ResultSet are handled by

      * <code>ExecuteQuery()</code>.

      * The ResultSet is converted to a collection using

      * <code>ResultSetUtils.getCollection(Object,ResultSet)</code>.

      * The ResultSet is released, and the Collection returned.

      *

      * @param resource The database resource key or null for default

      * @param column The column to return from the result set

      * @param command The SQL statement to prepare and execute.

      * @param parameters The replaceable parameters to use with command.

      * @exception SQLException if SQL error occurs

      */

     public static final Object getColumn(

             String resource,

             int column,

             String command,

             Object[] parameters)

         throws SQLException {

         Object result = null;

         Connection connection = null;

         ResultSet resultSet = null;

         try {

             connection =

                 ConnectionAdaptor.getPool().getConnection(resource);

             resultSet = executeQuery(connection,command,parameters);

             if (resultSet.next()) {

                result = resultSet.getObject(column);

             }

         }

         finally {

             try {

                 if (resultSet!=null) resultSet.close();

                 if (connection!=null) connection.close();

             }

             catch (SQLException sqle) {

                 // do nothing

             }

         }

         return result;

     } // end getColumn

     /**

      * Convenience method for calling

      * <code>getColumn(String,int,String,Object[]);</code>

      * with a single object parameter.

      *

      * @param resource The database resource key or null for default

      * @param column The column to return from the result set.

      * @param command The SQL statement to prepare and execute.

      * @param key The replaceable parameter

      * @exception SQLException if SQL error occurs

      */

     public static final Object getColumn(

             String resource,

             int column,

             String command,

             Object key)

         throws SQLException {

         Object[] parameters = new Object[1];

         parameters[0] = key;

         return getColumn(resource,column,command,parameters);

     } // end getColumn

     /**

      * Convenience method for calling

      * <code>getColumn(String,Object,String,Object[]);</code>

      *

      * with a single int parameter.

      * @param resource The database resource key or null for default

      * @param column The column to return from the result set.

      * @param command The SQL statement to prepare and execute.

      * @param key The replaceable parameter to use, if any.

      * @exception SQLException if SQL error occurs

      */

     public static final Object getColumn(

             String resource,

             int column,

             String command,

             int key)

         throws SQLException {

         Object[] parameters = new Object[1];

         parameters[0] = new Integer(key);

         return getColumn(resource,column,command,parameters);

     } // end getColumn

     /**

      * Convenience method for calling

      * <code>getColumn(String,Object,String,Object[]);</code>

      * without a parameter list.

      *

      * @param resource The database resource key or null for default

      * @param column The column to return from the result set.

      * @param command The SQL statement to prepare and execute.

      * @param key The replaceable parameter.

      * @exception SQLException if SQL error occurs

      */

     public static final Object getColumn(

             String resource,

             int column,

             String command)

         throws SQLException {

         return getColumn(resource,column,command,null);

     } // end getColumn

 // ------------------------------------------------------ getElement

     /**

      * Merges a SQL command with an array of parameters.

      * Any number or no parameters may be passed.

      * The parameters parameter may also be null.

      * The SQL Statement/ResultSet are handled by

      * <code>ExecuteQuery()</code>.

      * The first record of the ResultSet is used to populate

      * <code>ResultSetUtils.getElement(Object,ResultSet)</code>.

      * The ResultSet is released, and the bean returned.

      *

      * @param resource The database resource key or null for default

      * @param target The JavaBean object to populate.

      * @param command The SQL statement to prepare and execute.

      * @param parameters The replaceable parameters to use with

      * command.

      * @exception SQLException if SQL error occurs

      * @return True if element is found

      */

     public static final boolean getElement(

             String resource,

             Object target,

             String command,

             Object[] parameters)

         throws SQLException {

         Object collection = null;

         Connection connection = null;

         ResultSet resultSet = null;

         boolean found = false;

         try {

             connection =

                 ConnectionAdaptor.getPool().getConnection(resource);

             resultSet = executeQuery(connection,command,parameters);

             found = ResultSetUtils.getElement(target,resultSet);

         }

         finally {

             try {

                 if (resultSet!=null) resultSet.close();

                 if (connection!=null) connection.close();

             }

             catch (SQLException sqle) {

                 // do nothing

             }

         }

         return found;

     } // end getElement

     /**

      * Convenience method for calling

      * <code>getElement(String,Object,String,Object[]);</code>

      * with a single object parameter.

      *

      * @param resource The database resource key or null for default

      * @param target The JavaBean object to populate.

      * @param command The SQL statement to prepare and execute.

      * @param key The replaceable parameter to use with LIKE.

      * @exception SQLException if SQL error occurs

      * @return True if element is found

      */

     public static final boolean getElement(

             String resource,

             Object target,

             String command,

             Object key)

         throws SQLException {

         Object[] parameters = new Object[1];

         parameters[0] = key;

         return getElement(resource,target,command,parameters);

     } // end getElement

     /**

      * Convenience method for calling

      * <code>getElement(String,Object,String,Object[]);</code>

      * with a single int parameter.

      *

      * @param resource The database resource key or null for default

      * @param target The JavaBean object to populate.

      * @param command The SQL statement to prepare and execute.

      * @param key The replaceable parameter to use with LIKE.

      * @exception SQLException if SQL error occurs

      * @return True if element is found

      */

     public static final boolean getElement(

             String resource,

             Object target,

             String command,

             int key)

         throws SQLException {

         Object[] parameters = new Object[1];

         parameters[0] = new Integer(key);

         return getElement(resource,target,command,parameters);

     } // end getElement

     /**

      * Convenience method for calling

      * <code>getElement(String,Object,String,Object[]);</code>

      * without a parameter list.

      *

      * @param resource The database resource key or null for default

      * @param target The JavaBean object to populate.

      * @param command The SQL statement to prepare and execute.

      * @param key The replaceable parameter to use with LIKE.

      * @exception SQLException if SQL error occurs

      * @return True if element is found

      */

     public static final boolean getElement(

             String resource,

             Object target,

             String command)

         throws SQLException {

         return getElement(resource,target,command,null);

     } // end getElement

 // ------------------------------------------------------ getCollection

     /**

      * Merges a SQL command with an array of parameters.

      * Any number or no parameters may be passed.

      * The parameters parameter may also be null.

      * The SQL Statement/ResultSet are handled by

      * <code>ExecuteQuery()</code>.

      * The ResultSet is converted to a collection using

      * <code>ResultSetUtils.getCollection(Object,ResultSet)</code>.

      * The ResultSet is released, and the Collection returned.

      *

      * @param resource The database resource key or null for default

      * @param target The JavaBean object to return in the collection.

      * @param command The SQL statement to prepare and execute.

      * @param parameters The replaceable parameters to use with command.

      * @exception SQLException if SQL error occurs

      */

     public static final Collection getCollection(

             String resource,

             Object target,

             String command,

             Object[] parameters)

         throws SQLException {

         Object collection = null;

         Connection connection = null;

         ResultSet resultSet = null;

         try {

             connection =

                 ConnectionAdaptor.getPool().getConnection(resource);

             resultSet = executeQuery(connection,command,parameters);

             collection = ResultSetUtils.getCollection(target,resultSet);

         }

         finally {

             try {

                 if (resultSet!=null) resultSet.close();

                 if (connection!=null) connection.close();

             }

             catch (SQLException sqle) {

                 // do nothing

             }

         }

         return (Collection) collection;

     } // end getCollection

     /**

      * Convenience method for calling

      * <code>getCollection(String,Object,String,Object[]);</code>

      * with a single object parameter.

      *

      * @param resource The database resource key or null for default

      * @param target The JavaBean object to return in the collection.

      * @param command The SQL statement to prepare and execute.

      * @param key The replaceable parameter to use with LIKE.

      * @exception SQLException if SQL error occurs

      */

     public static final Collection getCollection(

             String resource,

             Object target,

             String command,

             Object key)

         throws SQLException {

         Object[] parameters = new Object[1];

         parameters[0] = key;

         return getCollection(resource,target,command,parameters);

     } // end getCollection

     /**

      * Convenience method for calling

      * <code>getCollection(String,Object,String,Object[]);</code>

      * with a single int parameter.

      *

      * @param resource The database resource key or null for default

      * @param target The JavaBean object to return in the collection.

      * @param command The SQL statement to prepare and execute.

      * @param key The replaceable parameter to use with LIKE.

      * @exception SQLException if SQL error occurs

      */

     public static final Collection getCollection(

             String resource,

             Object target,

             String command,

             int key)

         throws SQLException {

         Object[] parameters = new Object[1];

         parameters[0] = new Integer(key);

         return getCollection(resource,target,command,parameters);

     } // end getCollection

     /**

      * Convenience method for calling

      * <code>getCollection(String,Object,String,Object[]);</code>

      * without a parameter list.

      *

      * @param resource The database resource key or null for default

      * @param target The JavaBean object to return in the collection.

      * @param command The SQL statement to prepare and execute.

      * @param key The replaceable parameter to use with LIKE.

      * @exception SQLException if SQL error occurs

      */

     public static final Collection getCollection(

             String resource,

             Object target,

             String command)

         throws SQLException {

         return getCollection(resource,target,command,null);

     } // end getCollection

 } // end StatementUtils