org.apache.commons.scaffold.sql
Class StatementUtils

java.lang.Object
  org.apache.commons.scaffold.sql.StatementUtils

public final class StatementUtils
extends Object

General purpose SQL Statements.

Version:

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

Author:

Ted Husted

Constructor Summary

StatementUtils()

Method Summary

static Object	createKey(String resource, int column, String query, Object key, String update) Returns next sequential key for given table.
static int	createTable(String resource, String tableName, String tableCreate) Create a new database table in an existing database, by sending "CREATE TABLE " and the parameters to the DBMS configured with the ConnectionPool.
static ResultSet	executeQuery(Connection connection, String command, Object[] parameters) Merges a SQL command with an array of parameters.
static int	executeUpdate(Connection connection, String resource, String command, Object[] parameters) Prepares statement using SQL statement and executes with DBMS configured with the ConnectionPool.
static int	executeUpdate(String command) Convenience method for calling executeUpdate(String,String,Object[]); without a parameter list.
static int	executeUpdate(String resource, String command) Convenience method for calling executeUpdate(String,String,Object[]); without a parameter list.
static int	executeUpdate(String resource, String command, Object parameter) Convenience method for calling executeUpdate(String,String,Object[]); with a single Object parameter.
static int	executeUpdate(String resource, String command, Object[] parameters) Prepares statement using SQL statement and executes with DBMS configured with the ConnectionPool.
static Collection	getCollection(String resource, Object target, String command) Convenience method for calling getCollection(String,Object,String,Object[]); without a parameter list.
static Collection	getCollection(String resource, Object target, String command, int key) Convenience method for calling getCollection(String,Object,String,Object[]); with a single int parameter.
static Collection	getCollection(String resource, Object target, String command, Object key) Convenience method for calling getCollection(String,Object,String,Object[]); with a single object parameter.
static Collection	getCollection(String resource, Object target, String command, Object[] parameters) Merges a SQL command with an array of parameters.
static Object	getColumn(String resource, int column, String command) Convenience method for calling getColumn(String,Object,String,Object[]); without a parameter list.
static Object	getColumn(String resource, int column, String command, int key) Convenience method for calling getColumn(String,Object,String,Object[]); with a single int parameter.
static Object	getColumn(String resource, int column, String command, Object key) Convenience method for calling getColumn(String,int,String,Object[]); with a single object parameter.
static Object	getColumn(String resource, int column, String command, Object[] parameters) Merges a SQL command with an array of parameters and returns a column from the first record of the result set as an Object.
static boolean	getElement(String resource, Object target, String command) Convenience method for calling getElement(String,Object,String,Object[]); without a parameter list.
static boolean	getElement(String resource, Object target, String command, int key) Convenience method for calling getElement(String,Object,String,Object[]); with a single int parameter.
static boolean	getElement(String resource, Object target, String command, Object key) Convenience method for calling getElement(String,Object,String,Object[]); with a single object parameter.
static boolean	getElement(String resource, Object target, String command, Object[] parameters) Merges a SQL command with an array of parameters.
static String	like(String parameter) Prepare the parameter for use in a SQL "LIKE" expression.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

StatementUtils

public StatementUtils()

Method Detail

like

public static final String like(String parameter)

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

createTable

public static final int createTable(String resource,
                                    String tableName,
                                    String tableCreate)
                             throws SQLException

Create a new database table in an existing database, by sending "CREATE TABLE " and the parameters to the DBMS configured with the ConnectionPool.

For safety, does not drop table first.

Returns false if a SQL exception is thrown; exception is written to the servlet log.

Parameters:

resource - The database resource key or null for default

tableName - The name of the table to create

tableCreate - The SQL command defining the fields and indices

Returns:

Result of statement.execute()

Throws:

SQL - Exception if SQL error occurs

SQLException

createKey

public static final Object createKey(String resource,
                                     int column,
                                     String query,
                                     Object key,
                                     String update)
                              throws SQLException

Returns next sequential key for given table.

This ensures compatibility for DBMS products that do not support auto-incrementing a key field.

Intended to generate primary keys, but could be used to create other serial numbers based on an unsigned int.

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.

This routine is "unrolled" for efficiency, but the same effect can be accomplished using getColumn() to fetch the next key, and executeUpdate to increment the key.

Parameters:

resource - The database resource key or null for default

column - The column to return from the result set.

query - The SQL statement to fetch the next key.

key - The table name or other replaceable parameter.

query - The SQL statement to increment the next key.

Throws:

SQLException - if SQL error occurs

executeUpdate

public static final int executeUpdate(Connection connection,
                                      String resource,
                                      String command,
                                      Object[] parameters)
                               throws SQLException

Prepares statement using SQL statement and executes with DBMS configured with the ConnectionPool.

Command may be an INSERT, UPDATE, or DELETE statement or anything that returns nothing, such as a DDL statement.

Parameters:

connection - The database connection or null to acquire

resource - The database resource key or null for default

command - The SQL statement to execute.

parameters - An array of parameter objects

Throws:

SQLException - if SQL error occurs

executeUpdate

public static final int executeUpdate(String resource,
                                      String command,
                                      Object parameter)
                               throws SQLException

Convenience method for calling executeUpdate(String,String,Object[]); with a single Object parameter.

Parameters:

resource - The database resource key or null for default

command - The SQL statement to execute.

parameter - A single parameter to use with command

Throws:

SQLException - if SQL error occurs

executeUpdate

public static final int executeUpdate(String resource,
                                      String command)
                               throws SQLException

Convenience method for calling executeUpdate(String,String,Object[]); without a parameter list.

Parameters:

resource - The database resource key or null for default

command - The SQL statement to execute.

Throws:

SQLException - if SQL error occurs

executeUpdate

public static final int executeUpdate(String resource,
                                      String command,
                                      Object[] parameters)
                               throws SQLException

Prepares statement using SQL statement and executes with DBMS configured with the ConnectionPool.

Command may be an INSERT, UPDATE, or DELETE statement or anything that returns nothing, such as a DDL statement.

Parameters:

resource - The database resource key or null for default

command - The SQL statement to execute.

parameters - An array of parameter objects

Throws:

SQLException - if SQL error occurs

executeUpdate

public static final int executeUpdate(String command)
                               throws SQLException

Convenience method for calling executeUpdate(String,String,Object[]); without a parameter list.

Parameters:

resource - The database resource key or null for default

command - The SQL statement to execute.

Throws:

SQLException - if SQL error occurs

executeQuery

public static final ResultSet executeQuery(Connection connection,
                                           String command,
                                           Object[] parameters)
                                    throws SQLException

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

Parameters:

resource - The database resource key or null for default

target - The JavaBean object to return in the collection.

command - The SQL statement to prepare and execute.

parameters - The replaceable parameters to use with command.

Throws:

SQLException - if SQL error occurs

getColumn

public static final Object getColumn(String resource,
                                     int column,
                                     String command,
                                     Object[] parameters)
                              throws SQLException

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 ExecuteQuery(). The ResultSet is converted to a collection using ResultSetUtils.getCollection(Object,ResultSet). The ResultSet is released, and the Collection returned.

Parameters:

resource - The database resource key or null for default

column - The column to return from the result set

command - The SQL statement to prepare and execute.

parameters - The replaceable parameters to use with command.

Throws:

SQLException - if SQL error occurs

getColumn

public static final Object getColumn(String resource,
                                     int column,
                                     String command,
                                     Object key)
                              throws SQLException

Convenience method for calling getColumn(String,int,String,Object[]); with a single object parameter.

Parameters:

resource - The database resource key or null for default

column - The column to return from the result set.

command - The SQL statement to prepare and execute.

key - The replaceable parameter

Throws:

SQLException - if SQL error occurs

getColumn

public static final Object getColumn(String resource,
                                     int column,
                                     String command,
                                     int key)
                              throws SQLException

Convenience method for calling getColumn(String,Object,String,Object[]); with a single int parameter.

Parameters:

resource - The database resource key or null for default

column - The column to return from the result set.

command - The SQL statement to prepare and execute.

key - The replaceable parameter to use, if any.

Throws:

SQLException - if SQL error occurs

getColumn

public static final Object getColumn(String resource,
                                     int column,
                                     String command)
                              throws SQLException

Convenience method for calling getColumn(String,Object,String,Object[]); without a parameter list.

Parameters:

resource - The database resource key or null for default

column - The column to return from the result set.

command - The SQL statement to prepare and execute.

key - The replaceable parameter.

Throws:

SQLException - if SQL error occurs

getElement

public static final boolean getElement(String resource,
                                       Object target,
                                       String command,
                                       Object[] parameters)
                                throws SQLException

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 ExecuteQuery(). The first record of the ResultSet is used to populate ResultSetUtils.getElement(Object,ResultSet). The ResultSet is released, and the bean returned.

Parameters:

resource - The database resource key or null for default

target - The JavaBean object to populate.

command - The SQL statement to prepare and execute.

parameters - The replaceable parameters to use with command.

Returns:

True if element is found

Throws:

SQLException - if SQL error occurs

getElement

public static final boolean getElement(String resource,
                                       Object target,
                                       String command,
                                       Object key)
                                throws SQLException

Convenience method for calling getElement(String,Object,String,Object[]); with a single object parameter.

Parameters:

resource - The database resource key or null for default

target - The JavaBean object to populate.

command - The SQL statement to prepare and execute.

key - The replaceable parameter to use with LIKE.

Returns:

True if element is found

Throws:

SQLException - if SQL error occurs

getElement

public static final boolean getElement(String resource,
                                       Object target,
                                       String command,
                                       int key)
                                throws SQLException

Convenience method for calling getElement(String,Object,String,Object[]); with a single int parameter.

Parameters:

resource - The database resource key or null for default

target - The JavaBean object to populate.

command - The SQL statement to prepare and execute.

key - The replaceable parameter to use with LIKE.

Returns:

True if element is found

Throws:

SQLException - if SQL error occurs

getElement

public static final boolean getElement(String resource,
                                       Object target,
                                       String command)
                                throws SQLException

Convenience method for calling getElement(String,Object,String,Object[]); without a parameter list.

Parameters:

resource - The database resource key or null for default

target - The JavaBean object to populate.

command - The SQL statement to prepare and execute.

key - The replaceable parameter to use with LIKE.

Returns:

True if element is found

Throws:

SQLException - if SQL error occurs

getCollection

public static final Collection getCollection(String resource,
                                             Object target,
                                             String command,
                                             Object[] parameters)
                                      throws SQLException

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 ExecuteQuery(). The ResultSet is converted to a collection using ResultSetUtils.getCollection(Object,ResultSet). The ResultSet is released, and the Collection returned.

Parameters:

resource - The database resource key or null for default

target - The JavaBean object to return in the collection.

command - The SQL statement to prepare and execute.

parameters - The replaceable parameters to use with command.

Throws:

SQLException - if SQL error occurs

getCollection

public static final Collection getCollection(String resource,
                                             Object target,
                                             String command,
                                             Object key)
                                      throws SQLException

Convenience method for calling getCollection(String,Object,String,Object[]); with a single object parameter.

Parameters:

resource - The database resource key or null for default

target - The JavaBean object to return in the collection.

command - The SQL statement to prepare and execute.

key - The replaceable parameter to use with LIKE.

Throws:

SQLException - if SQL error occurs

getCollection

public static final Collection getCollection(String resource,
                                             Object target,
                                             String command,
                                             int key)
                                      throws SQLException

Convenience method for calling getCollection(String,Object,String,Object[]); with a single int parameter.

Parameters:

resource - The database resource key or null for default

target - The JavaBean object to return in the collection.

command - The SQL statement to prepare and execute.

key - The replaceable parameter to use with LIKE.

Throws:

SQLException - if SQL error occurs

getCollection

public static final Collection getCollection(String resource,
                                             Object target,
                                             String command)
                                      throws SQLException

Convenience method for calling getCollection(String,Object,String,Object[]); without a parameter list.

Parameters:

resource - The database resource key or null for default

target - The JavaBean object to return in the collection.

command - The SQL statement to prepare and execute.

key - The replaceable parameter to use with LIKE.

Throws:

SQLException - if SQL error occurs