Consider aproach like the following:
package ru.factor.toolkit.jdbc;
im****t java.sql.CallableStatement;
im****t java.sql.Connection;
im****t java.sql.PreparedStatement;
im****t java.sql.ResultSet;
im****t java.sql.SQLException;
im****t java.sql.Statement;
im****t java.util.ArrayList;
im****t java.util.List;
im****t javax.sql.DataSource;
im****t org.apache.commons.lang.StringUtils;
im****t org.apache.commons.logging.Log;
im****t 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 {@[EMAIL PROTECTED]
ParamType} (<code>ParamType.IN</code>
by default).
* <li>type of value of parameter {@[EMAIL PROTECTED]
JDBCType}.
* </ul>
* The order is not relevant. Which of elements is required depends on
usage.
* The main rule is that {@[EMAIL PROTECTED]
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 {@[EMAIL PROTECTED]
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>
*
* @[EMAIL PROTECTED]
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>
*
* @[EMAIL PROTECTED]
dataSource
*/
public JDBCTemplates(DataSource dataSource) {
this(dataSource, NULL_LISTENER);
}
/**
* Intitializes by <code>DataSource</code>
*
* @[EMAIL PROTECTED]
dataSource
* @[EMAIL PROTECTED]
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 DataAcces***ception("Error due to access to
database: ", ex);
} finally {
closeNoEx(connection);
}
}
};
}
/**
* Prepares connection for working.
* Gets connection from DataSource, pass it to {@[EMAIL PROTECTED]
(java.sql.Connection)}
* and close connection.
*
* @[EMAIL PROTECTED]
handler Handler of <code>connection</code>
* @[EMAIL PROTECTED]
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 DataAcces***ception("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
* {@[EMAIL PROTECTED]
(java.sql.Connection)},
* pass it to {@[EMAIL PROTECTED]
(java.sql.PreparedStatement)},
close it.
*
* @[EMAIL PROTECTED]
creator PreparedStatement abstract factory
* @[EMAIL PROTECTED]
handler PreparedStatement handler.
* @[EMAIL PROTECTED]
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 {@[EMAIL PROTECTED]
(java.sql.PreparedStatement)},
close it.
*
* @[EMAIL PROTECTED]
sql SQL query string. It can be select, update,
delete.
* @[EMAIL PROTECTED]
params array with statement parameters.
* @[EMAIL PROTECTED]
handler PreparedStatement handler.
* @[EMAIL PROTECTED]
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
* {@[EMAIL PROTECTED]
(java.sql.Connection)},
* invokes <code>PreparedStatement.executeQuery()</code> and pass
result set to
* {@[EMAIL PROTECTED]
ResultSetHandler#onResultSet(java.sql.ResultSet)}, close
it.
*
* @[EMAIL PROTECTED]
creator <code>PreparedStatement</code> abstract factory.
* @[EMAIL PROTECTED]
handler <code>ResultSet</code> handler.
* @[EMAIL PROTECTED]
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
* {@[EMAIL PROTECTED]
(java.sql.Connection)},
* pass it to {@[EMAIL PROTECTED]
(java.sql.CallableStatement)},
close it.
*
* @[EMAIL PROTECTED]
creator <code>CallableStatement</code> abstract factory.
* @[EMAIL PROTECTED]
handler <code>CallableStatement</code> handler
* @[EMAIL PROTECTED]
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
* {@[EMAIL PROTECTED]
ResultSetHandler#onResultSet(java.sql.ResultSet)}
*
* @[EMAIL PROTECTED]
querySQL select query.
* @[EMAIL PROTECTED]
params query parametes.
* @[EMAIL PROTECTED]
handler <code>ResulSet</code> handler.
* @[EMAIL PROTECTED]
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
* {@[EMAIL PROTECTED]
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 {@[EMAIL PROTECTED]
#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 {@[EMAIL PROTECTED]
#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
* {@[EMAIL PROTECTED]
JDBCTypeConverter}
* to convert from JDBC type to type you needed.
*
* @[EMAIL PROTECTED]
querySQL select query.
* @[EMAIL PROTECTED]
params parameters of query.
* @[EMAIL PROTECTED]
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>, DataAcces***ception>() {
@[EMAIL PROTECTED]
("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 {@[EMAIL PROTECTED]
#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
* {@[EMAIL PROTECTED]
JDBCTypeConverter}
* to convert from JDBC type to type you needed.
*
* @[EMAIL PROTECTED]
querySQL select query.
* @[EMAIL PROTECTED]
params query parameters.
* @[EMAIL PROTECTED]
result of processing if present.
*/
public <T> T selectForObject(final String querySQL, final
JDBCTypeConverter converter, Object[][] params) {
return executeSelect(querySQL, params, new ResultSetHandler<T,
DataAcces***ception>() {
@[EMAIL PROTECTED]
("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 {@[EMAIL PROTECTED]
#selectForObject(String, JDBCTypeConverter,
Object[][])}
*/
@[EMAIL PROTECTED]
("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.
*
* @[EMAIL PROTECTED]
updateSQL update query.
* @[EMAIL PROTECTED]
params query parameters.
* @[EMAIL PROTECTED]
number of rows updated.
*/
public int executeUpdate(final String updateSQL, Object[][]
params) {
return execute(updateSQL, params, new
PreparedStatementHandler<Integer, DataAcces***ception>() {
public Integer onPreparedStatement(PreparedStatement
preparedStatement) throws SQLException {
return new Integer(preparedStatement.executeUpdate());
}
});
}
/**
* Shortcut for {@[EMAIL PROTECTED]
#executeUpdate(String, Object[][])}
*/
public int executeUpdate(String updateSQL, Object... params) {
return executeUpdate(updateSQL, convertParams(params));
}
/**
* Performs insert query and returns object generated identity
object.
*
* @[EMAIL PROTECTED]
insertSQL update query.
* @[EMAIL PROTECTED]
params query parameters.
* @[EMAIL PROTECTED]
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, DataAcces***ception>() {
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 {@[EMAIL PROTECTED]
#executeInsert(String, Object[][])}
*/
public Object executeInsert(String insertSQL, Object... params) {
return executeInsert(insertSQL, convertParams(params));
}
/**
* Executes SQL statement.
*
* @[EMAIL PROTECTED]
statementSQL sql statemenet
* @[EMAIL PROTECTED]
same value as {@[EMAIL PROTECTED]
(String)}
* @[EMAIL PROTECTED]
{@[EMAIL PROTECTED]
java.sql.Statement#execute(String)}
*/
public boolean executeStatement(final String statementSQL, final
Object... params) {
return execute(new ConnectionHandler<Boolean,
DataAcces***ception>() {
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
* {@[EMAIL PROTECTED]
JDBCTypeConverter}
* from {@[EMAIL PROTECTED]
JDBCType}.
*
* @[EMAIL PROTECTED]
functionSQL query.
* @[EMAIL PROTECTED]
returnType type of expected result.
* @[EMAIL PROTECTED]
params params of function.
* @[EMAIL PROTECTED]
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, DataAcces***ception>() {
@[EMAIL PROTECTED]
("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 {@[EMAIL PROTECTED]
* is <code>OUT<code> or <code>IN_OUT</code> and its {@[EMAIL PROTECTED]
can't be
* determined, the type must be specified explicitly.
*
* @[EMAIL PROTECTED]
procedureSQL query string.
* @[EMAIL PROTECTED]
params parameters of procedure.
* @[EMAIL PROTECTED]
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[][],
DataAcces***ception>() {
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.
*
* @[EMAIL PROTECTED]
originalSQL original query text.
* @[EMAIL PROTECTED]
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);
}
}
}
|
