Consider aproach like the following:

package ru.factor.toolkit.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
* Contains convenient methos for working with JDBC.  <u>This class it
not thread safe</u>
* because it keeps created connection in instance variable during
call of any method.
* It made for performance resasons. Parameters to stored procedures
and functions passing as
* <code>java.lang.Object[][]</code>, where each row represent one
paramter.
* Each paramter is array of three objects (<code>java.lang.Object[]</
code>):
* <ul><li>parameter's value <code>java.lang.Object</code>.
* <li>type of paramter {@link ParamType}  (<code>ParamType.IN</code>
by default).
* <li>type of value of parameter {@link JDBCType}.
* </ul>
* The order is not relevant. Which of elements is required depends on
usage.
* The main rule is that {@link JDBCType}
* can be omitted when it can be deterimined by class of value.
Therefore if value is <code>null</code> or
* parameter is <code>OUT</code> you must specify {@link JDBCType}
explicitly.
* <p/>
* For example:
* <pre>
*     new Object[][] {{null, JDBCType.INT}, {"string_value"}}
* <p/>
*   Equals to:
* <p/>
*     new Object[][] {{null, ParamType.IN, JDBCType.INT},
{"string_value". ParamType.IN, JDBCType.STRING}}
* <p/>
*   And can be passed to function with signatue:
* <p/>
*    function someFunction(i number, s varchar) return someType;
* </pre>
*
* @author Andrey Ryabov
*/
public class JDBCTemplates {
   private static final Log log =
LogFactory.getLog(JDBCTemplates.class);
   private static final int MAX_SQL_LENGTH = 100;
   private DataSource dataSource;
   private Connection connection;
   private ConnectionListener listener;
   private int counter;

   private static final ConnectionListener NULL_LISTENER = new
ConnectionListener() {
       public void afterCreate(Connection connect) {
           // do nothing
       }

       public void beforeClose(Connection connect) {
           // do nothing
       }
   };

   /**
    * Intitializes by <code>DataSource</code>
    *
    * @param dataSource
    */
   public JDBCTemplates(DataSource dataSource) {
       this(dataSource, NULL_LISTENER);
   }

   /**
    * Intitializes by <code>DataSource</code>
    *
    * @param dataSource
    * @param listener
    */
   public JDBCTemplates(DataSource dataSource, ConnectionListener
listener) {
       this.dataSource = dataSource;
       this.listener = listener;
   }

   public static JDBCTemplates createNonThreadSafe(DataSource
dataSource) {
       return new JDBCTemplates(dataSource);
   }

   public static JDBCTemplates createThreadSafe(final DataSource
dataSource) {
       return new JDBCTemplates(dataSource) {
          public <T, E extends Exception> T
execute(ConnectionHandler<T, E> handler) throws E {
              Connection connection = null;
              try {
                   connection = dataSource.getConnection();
                  return handler.onConnection(connection);
              } catch (SQLException ex) {
                  log.error("Error due to access to database", ex);
                  throw new DataAccessException("Error due to access to
database: ", ex);
              } finally {
                   closeNoEx(connection);
              }
          }
       };
   }

   /**
    * Prepares connection for working.
    * Gets connection from DataSource, pass it to {@link
ConnectionHandler#onConnection(java.sql.Connection)}
    * and close connection.
    *
    * @param handler Handler of <code>connection</code>
    * @return result of processing if present
    */
   public <T, E extends Exception> T execute(ConnectionHandler<T, E>
handler) throws E {
       try {
           if (counter++ == 0) {
               connection = dataSource.getConnection();
               listener.afterCreate(connection);
           }
           return handler.onConnection(connection);
       } catch (SQLException ex) {
           log.error("Error due to access to database", ex);
           throw new DataAccessException("Error due to access to
database: ", ex);
       } finally {
           if (--counter == 0) {
               try {
                   listener.beforeClose(connection);
               } catch (SQLException e) {
                   log.error("Error in ConnectionListener", e);
               }
               closeNoEx(connection);
               connection = null;
           }
       }
   }

   /**
    * Manage <code>PreparedStatement</code> creation and handling.
Gets <code>PreparedStatement</code> via
    * {@link
PreparedStatementCreator#createPreparedStatement(java.sql.Connection)},
    * pass it to {@link
PreparedStatementHandler#onPreparedStatement(java.sql.PreparedStatement)},
close it.
    *
    * @param creator PreparedStatement abstract factory
    * @param handler PreparedStatement handler.
    * @return result of processing if present
    */
   public <T, E extends Exception> T execute(final
PreparedStatementCreator creator,
                                             final
PreparedStatementHandler<T, E> handler) throws E {
       return execute(new ConnectionHandler<T, E>() {
           public T onConnection(Connection connection) throws E,
SQLException {
               PreparedStatement statement =
creator.createPreparedStatement(connection);
               try {
                   return handler.onPreparedStatement(statement);
               } finally {
                   closeNoEx(statement);
               }
           }
       });
   }

   /**
    * Creates initialized prepared statement. Creates
<code>PreparedStatement</code>, initialize it by parameters
    * and pass it to {@link
PreparedStatementHandler#onPreparedStatement(java.sql.PreparedStatement)},
close it.
    *
    * @param sql     SQL query string. It can be select, update,
delete.
    * @param params  array with statement parameters.
    * @param handler PreparedStatement handler.
    * @return result of processing if present.
    */
   public <T, E extends Exception> T execute(final String sql, final
Object[][] params,
                                             final
PreparedStatementHandler<T, E> handler) throws E {
       return execute(new PreparedStatementCreator() {
           public PreparedStatement
createPreparedStatement(Connection connection) throws SQLException {
               PreparedStatement statement =
connection.prepareStatement(sql);
               fillPreparedStatement(statement, params, 1);
               return statement;
           }
       }, handler);
   }

   /**
    * Creates uninitialized prepared statement.
    */
   public <T, E extends Exception> T execute(final String sql,
               final PreparedStatementHandler<T, E> handler) throws E {
       return execute(new PreparedStatementCreator() {
           public PreparedStatement
createPreparedStatement(Connection connection) throws SQLException {
               PreparedStatement statement =
connection.prepareStatement(sql);
               return statement;
           }
       }, handler);
   }

   /**
    * Manage <code>ResultSet</code> creation and handling. Gets
<code>PreparedStatement</code> via
    * {@link
PreparedStatementCreator#createPreparedStatement(java.sql.Connection)},
    * invokes <code>PreparedStatement.executeQuery()</code> and pass
result set to
    * {@link ResultSetHandler#onResultSet(java.sql.ResultSet)}, close
it.
    *
    * @param creator <code>PreparedStatement</code> abstract factory.
    * @param handler <code>ResultSet</code> handler.
    * @return result of processing if present.
    */
   public <T, E extends Exception> T execute(PreparedStatementCreator
creator,
                                             final
ResultSetHandler<T, E> handler)
           throws E {
       return execute(creator, new PreparedStatementHandler<T, E>() {
           public T onPreparedStatement(PreparedStatement
preparedStatement) throws E, SQLException {
               ResultSet resultSet =
preparedStatement.executeQuery();
               try {
                   return handler.onResultSet(resultSet);
               } finally {
                   closeNoEx(resultSet);
               }
           }
       });
   }

   /**
    * Manage <code>CallableStatement</code> creation and handling.
Creates <code>CallableStatement</code> by
    * {@link
CallableStatementCreator#createCallableStatement(java.sql.Connection)},
    * pass it to {@link
CallableStatementHandler#onCallableStatement(java.sql.CallableStatement)},
close it.
    *
    * @param creator <code>CallableStatement</code> abstract factory.
    * @param handler <code>CallableStatement</code> handler
    * @return result of processing if present.
    */
   public <T, E extends Exception> T execute(final
CallableStatementCreator creator,
                                             final
CallableStatementHandler<T, E> handler) throws E {
       return execute(new ConnectionHandler<T, E>() {
           public T onConnection(Connection connection) throws E,
SQLException {
               CallableStatement statement =
creator.createCallableStatement(connection);
               try {
                   return handler.onCallableStatement(statement);
               } finally {
                   closeNoEx(statement);
               }
           }
       });
   }

   /**
    * Performs select query. Executes select statement and pass
<code>ResultSet</code> to
    * {@link ResultSetHandler#onResultSet(java.sql.ResultSet)}
    *
    * @param querySQL select query.
    * @param params   query parametes.
    * @param handler  <code>ResulSet</code> handler.
    * @return result of processing if present.
    */
   public <T, E extends Exception> T executeSelect(final String
querySQL, final Object[][] params,
                                                   final
ResultSetHandler<T, E> handler) throws E {
       return execute(new PreparedStatementCreator() {
           public PreparedStatement
createPreparedStatement(Connection connection) throws SQLException {
               PreparedStatement statement =
connection.prepareStatement(querySQL);
               fillPreparedStatement(statement, params, 1);
               return statement;
           }
       }, handler);
   }

   /**
    * Performs select query. Executes select statement and pass
<code>ResultSet</code> to
    * {@link ResultSetHandler#onResultSet(java.sql.ResultSet)}
    */
   public <T, E extends Exception> T executeSelect(final String
querySQL, final int rsType,
                                                   final int rsConcurrency, final int
rsHoldability,
                                                   final Object[][] params,
           final ResultSetHandler<T, E> handler) throws E {
       return execute(new PreparedStatementCreator() {
           public PreparedStatement
createPreparedStatement(Connection connection) throws SQLException {
               PreparedStatement statement =
connection.prepareStatement(querySQL, rsType,
                       rsConcurrency, rsHoldability);
               fillPreparedStatement(statement, params, 1);
               return statement;
           }
       }, handler);
   }

   /**
    * Shortcut for {@link #executeSelect(String, Object[][],
ResultSetHandler<T,E>)}
    */
   public <T, E extends Exception> T executeSelect(String querySQL,
ResultSetHandler<T, E> handler,
                                                   Object... params)
throws E {
       return executeSelect(querySQL, convertParams(params),
handler);
   }

   /**
    * Shortcut for {@link #executeSelect(String, int, int, int,
Object[][], ResultSetHandler)}
    */
   public <T, E extends Exception> T executeSelect(String querySQL,
int rsType, int rsConcurrency,
               int rsHoldability, ResultSetHandler<T, E> handler, Object...
params) throws E {
       return executeSelect(querySQL, rsType, rsConcurrency,
rsHoldability,
               convertParams(params), handler);
   }

   /**
    * Returns first column of queries result as List. You should use
appropriate
    * {@link JDBCTypeConverter}
    * to convert from JDBC type to type you needed.
    *
    * @param querySQL select query.
    * @param params   parameters of query.
    * @return result of processing if present.
    */
   public <T> List<T> selectForList(String querySQL, final
JDBCTypeConverter converter, Object[][] params) {
       return executeSelect(querySQL, params, new
ResultSetHandler<List<T>, DataAccessException>() {
           @SuppressWarnings("unchecked")
           public List<T> onResultSet(ResultSet resultSet) throws
SQLException {
               List<T> result = new ArrayList<T>();
               while (resultSet.next()) {
                   result.add((T)
converter.convertFromJDBC(resultSet.getObject(1)));
               }
               return result;
           }
       });
   }

   /**
    * Shortcut for {@link #selectForList(String, JDBCTypeConverter,
Object[][])}
    */
   public <T> List<T> selectForList(String querySQL, Object...
params) {
       return selectForList(querySQL,
JDBCTypeConverters.NO_CONVERSION, convertParams(params));
   }

   /**
    * Returns first column of first row of result as Object. You
should use appropriate
    * {@link JDBCTypeConverter}
    * to convert from JDBC type to type you needed.
    *
    * @param querySQL select query.
    * @param params   query parameters.
    * @return result of processing if present.
    */
   public <T> T selectForObject(final String querySQL, final
JDBCTypeConverter converter, Object[][] params) {
       return executeSelect(querySQL, params, new ResultSetHandler<T,
DataAccessException>() {
           @SuppressWarnings("unchecked")
           public T onResultSet(ResultSet resultSet) throws
SQLException {
               if (!resultSet.next()) {
                   throw new NoDataFoundException("One row required
for query: " + querySQL);
               }
               return (T)
converter.convertFromJDBC(resultSet.getObject(1));
           }
       });
   }

   /**
    * Shortcut for {@link #selectForObject(String, JDBCTypeConverter,
Object[][])}
    */
   @SuppressWarnings("unchecked")
   public <T> T selectForObject(String querySQL, Object... params) {
       return (T) selectForObject(querySQL,
JDBCTypeConverters.NO_CONVERSION, convertParams(params));
   }

   /**
    * Performs update query and returns number of rows updated.
    *
    * @param updateSQL update query.
    * @param params    query parameters.
    * @return number of rows updated.
    */
   public int executeUpdate(final String updateSQL, Object[][]
params) {
       return execute(updateSQL, params, new
PreparedStatementHandler<Integer, DataAccessException>() {
           public Integer onPreparedStatement(PreparedStatement
preparedStatement) throws SQLException {
               return new Integer(preparedStatement.executeUpdate());
           }
       });
   }

   /**
    * Shortcut for {@link #executeUpdate(String, Object[][])}
    */
   public int executeUpdate(String updateSQL, Object... params) {
       return executeUpdate(updateSQL, convertParams(params));
   }

   /**
    * Performs insert query and returns object generated identity
object.
    *
    * @param insertSQL update query.
    * @param params    query parameters.
    * @return generated identity object.
    */
   public Object executeInsert(final String insertSQL, final Object[]
[] params) {
       return execute(new PreparedStatementCreator() {
               public PreparedStatement
createPreparedStatement(Connection connection) throws SQLException {
                   PreparedStatement statement =
connection.prepareStatement(insertSQL,
Statement.RETURN_GENERATED_KEYS);
                   fillPreparedStatement(statement, params, 1);
                   return statement;
               }},
           new PreparedStatementHandler<Object, DataAccessException>() {
               public Object onPreparedStatement(PreparedStatement
preparedStatement) throws SQLException {
                   preparedStatement.executeUpdate();
                   ResultSet rSet =
preparedStatement.getGeneratedKeys();
                   try {
                       return rSet.next() ? rSet.getObject(1) : null;
                   } finally {
                       JDBCTemplates.closeNoEx(rSet);
                   }
               }});
   }

   /**
    * Shortcut for {@link #executeInsert(String, Object[][])}
    */
    public Object executeInsert(String insertSQL, Object... params) {
       return executeInsert(insertSQL, convertParams(params));
   }

   /**
    * Executes SQL statement.
    *
    * @param statementSQL sql statemenet
    * @return same value as {@link
java.sql.Statement#execute(String)}
    * @see {@link java.sql.Statement#execute(String)}
    */
   public boolean executeStatement(final String statementSQL, final
Object... params) {
       return execute(new ConnectionHandler<Boolean,
DataAccessException>() {
           public Boolean onConnection(Connection connection) throws
SQLException {
               PreparedStatement statement =
connection.prepareStatement(statementSQL);
               fillPreparedStatement(statement,
convertParams(params), 1);
               return statement.execute();
           }
       });
   }

   /**
    * Performs stored function call. Call stored function, gets
return value convert it by
    * {@link JDBCTypeConverter}
    * from {@link JDBCType}.
    *
    * @param functionSQL query.
    * @param returnType  type of expected result.
    * @param params      params of function.
    * @return function's return value.
    */
   public <T> T functionCall(final String functionSQL, final JDBCType
returnType, final Object[][] params) {
       return execute(new CallableStatementCreator() {
           public CallableStatement
createCallableStatement(Connection connect) throws SQLException {
               CallableStatement call =
connect.prepareCall(functionSQL);
               returnType.registerReturnParameter(call, 1);
               fillPreparedStatement(call, params, 2);
               return call;
           }
       }, new CallableStatementHandler<T, DataAccessException>() {
           @SuppressWarnings("unchecked")
           public T onCallableStatement(CallableStatement call)
throws SQLException {
               call.execute();
               return (T) returnType.getReturnParameter(call, 1);
           }
       });
   }

   /**
    * Performs call of stored procedure. If parameter is marked as
<code>OUT</code> ou <code>IN_OUT</code>
    * it's value will be set in result of stored procedure. If {@link
ParamType}
    * is <code>OUT<code> or <code>IN_OUT</code> and its {@link
JDBCType} can't be
    * determined, the type must be specified explicitly.
    *
    * @param procedureSQL query string.
    * @param params       parameters of procedure.
    * @return same array as passed to method.
    */
   public Object[][] procedureCall(final String procedureSQL, final
Object[][] params) {
       return execute(new CallableStatementCreator() {
           public CallableStatement
createCallableStatement(Connection connect) throws SQLException {
               CallableStatement call =
connect.prepareCall(procedureSQL);
               fillPreparedStatement(call, params, 1);
               registerOutputParams(call, params);
               return call;
           }
       }, new CallableStatementHandler<Object[][],
DataAccessException>() {
           public Object[][] onCallableStatement(CallableStatement
call) throws SQLException {
               call.execute();
               fillOutputParams(call, params);
               return params;
           }
       });
   }

   /**
    * Deletes white spaces and abbreviate query. Used in proposes of
logging.
    */
   public static String formatSQL(String originalSQL) {
       return formatSQL(originalSQL, MAX_SQL_LENGTH);
   }

   /**
    * Deletes white spaces and abbreviate query. Used in proposes of
logging.
    *
    * @param originalSQL  original query text.
    * @param maxSqlLength maximal length of formated query.
    */
   public static String formatSQL(String originalSQL, int
maxSqlLength) {
       String[] clauses = StringUtils.split(originalSQL);
       StringBuffer result = new StringBuffer();
       for (int i = 0; i < clauses.length; i++) {
           if (i != 0) {
               result.append(" ");
           }
           result.append(clauses[i]);
       }
       return StringUtils.abbreviate(result.toString(),
maxSqlLength);
   }

   public static void fillOutputParams(CallableStatement call,
Object[][] params) throws SQLException {
       for (int i = 0; i < params.length; i++) {
           ParamType paramType = extractParamType(params[i]);
           if (paramType != ParamType.IN) {
               JDBCType jdbcType = extractJDBCType(params[i]);
               params[i] = new Object[]
{jdbcType.getReturnParameter(call, i + 1), jdbcType, paramType};
           }
       }
   }

   public static void fillPreparedStatement(PreparedStatement call,
final Object[][] params, int startWith)
           throws SQLException {
       for (int i = 0; i < params.length; i++) {
           ParamType paramType = extractParamType(params[i]);
           if (paramType != ParamType.OUT) {
               extractJDBCType(params[i]).setParameter(call,
startWith + i, extractValue(params[i]));
           }
       }
   }

   private void registerOutputParams(CallableStatement call, final
Object[][] params) throws SQLException {
       for (int i = 0; i < params.length; i++) {
           ParamType paramType = extractParamType(params[i]);
           if (paramType != ParamType.IN) {

extractJDBCType(params[i]).registerReturnParameter(call, i + 1);
           }
       }
   }

   private static JDBCType extractJDBCType(Object[] param) {
       for (Object p : param) {
           if (p instanceof JDBCType) {
               return (JDBCType) p;
           }
       }
       Object paramValue = extractValue(param);
       if (paramValue != null) {
           return JDBCType.defineType(paramValue);
       }
       return JDBCType.NULL;
   }

   private static Object extractValue(Object[] param) {
       for (Object p : param) {
           if (!(p instanceof JDBCType) && !(p instanceof ParamType))
{
               return p;
           }
       }
       log.error("Value in param wasn't found: " + param);
       throw new IllegalArgumentException("There is no value in
param: " + param);
   }

   private static ParamType extractParamType(Object[] param) {
       for (Object p : param) {
           if (p instanceof ParamType) {
               return (ParamType) p;
           }
           if (p instanceof ResultSetHandler) {
               return ParamType.OUT;
           }
       }
       return ParamType.IN;
   }

   public static Object[][] convertParams(Object[] params) {
       Object[][] result = new Object[params.length][];
       for (int i = 0; i < params.length; i++) {
           result[i] = new Object[]{params[i]};
       }
       return result;
   }

   public static void closeNoEx(Connection connection) {
       try {
           if (connection != null) {
               connection.close();
           }
       } catch (SQLException e) {
           log.error("Error closing connection", e);
       }
   }

   public static void closeNoEx(PreparedStatement statement) {
       try {
           if (statement != null) {
               statement.close();
           }
       } catch (SQLException e) {
           log.error("Error closing prepared statement", e);
       }
   }

   public static void closeNoEx(ResultSet resultSet) {
       try {
           if (resultSet != null) {
               resultSet.close();
           }
       } catch (SQLException e) {
           log.error("Error closing result set", e);
       }
   }
}

Prepared statements are one technique to avoid creating large numbers of
almost-identical SQL statements.  You can use prepared statements if you
expect to issue many queries such as

  SELECT name,address FROM CUSTOMER WHERE customer_id = 123456

but for different values of customer_id.  Instead of creating a new SQL
qstatement for each customer_id, you use a pattern like this:

  // Initialisation code, probably in a class constructor
  String query =
    "SELECT name,address FROM CUSTOMER WHERE customer_id =?";
  PreparedStatement pstmt = conn.prepareStatement(query);

  ...

  // Set parameter #1 in prepared statement
  int customer_id = 123456;
  pstmt.setInt(1, customer_id);

  // Execute the query using the specified parameter
  ResultSet rs = pstmt.executeQuery();

  // Now use ResultSet as usual

The java.util.ResourceBundle class is one solution to this problem.  You
put your SQL strings in a text file, separate from your source code,
with entries such as

sqlCustomerByID=SELECT name,address FROM CUSTOMER WHERE customer_id =?

and retrieve them with code like this:

  ResourceBundle sqlResources =
      ResourceBundle.getBundle("SQLQueries.txt");

  String query = sqlResources.getString("sqlCustomerByID");

David Harper
Cambridge, England

hi all,

wouldn't an object-relational mapping approach, such as hibernate, toplink,
etc., provide for an even greater degree of transparency, a lot less code, and
practically no SQL (because the SQL is auto-generated)?  in addition, to
minimize db management code even further, i would think using aspects (e.g.,
aspectj) to treat persistence as a cross-cutting concern would further
reduce the amount of code to be written, simplify that which does have to be
written, and centralize all the persistence code so that it's easier to manage
and guaranteed to be applied consistently.

just my $0.02.  i'd be curious to hear arguments for or against this.

cheers,
doug