/* | |
* Licensed to the Apache Software Foundation (ASF) under one or more | |
* contributor license agreements. See the NOTICE file distributed with | |
* this work for additional information regarding copyright ownership. | |
* The ASF licenses this file to You 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.dbutils; | |
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.LinkedList; | |
import java.util.List; | |
import javax.sql.DataSource; | |
/** | |
* Executes SQL queries with pluggable strategies for handling | |
* {@code ResultSet}s. This class is thread safe. | |
* | |
* @see ResultSetHandler | |
*/ | |
public class QueryRunner extends AbstractQueryRunner { | |
/** | |
* Constructor for QueryRunner. | |
*/ | |
public QueryRunner() { | |
} | |
/** | |
* Constructor for QueryRunner that controls the use of {@code ParameterMetaData}. | |
* | |
* @param pmdKnownBroken Some drivers don't support {@link java.sql.ParameterMetaData#getParameterType(int) }; | |
* if {@code pmdKnownBroken} is set to true, we won't even try it; if false, we'll try it, | |
* and if it breaks, we'll remember not to use it again. | |
*/ | |
public QueryRunner(final boolean pmdKnownBroken) { | |
super(pmdKnownBroken); | |
} | |
/** | |
* Constructor for QueryRunner that takes a {@code DataSource} to use. | |
* | |
* Methods that do not take a {@code Connection} parameter will retrieve connections from this | |
* {@code DataSource}. | |
* | |
* @param ds The {@code DataSource} to retrieve connections from. | |
*/ | |
public QueryRunner(final DataSource ds) { | |
super(ds); | |
} | |
/** | |
* Constructor for QueryRunner that takes a {@code StatementConfiguration} to configure statements when | |
* preparing them. | |
* | |
* @param stmtConfig The configuration to apply to statements when they are prepared. | |
*/ | |
public QueryRunner(final StatementConfiguration stmtConfig) { | |
super(stmtConfig); | |
} | |
/** | |
* Constructor for QueryRunner that takes a {@code DataSource} and controls the use of {@code ParameterMetaData}. | |
* Methods that do not take a {@code Connection} parameter will retrieve connections from this | |
* {@code DataSource}. | |
* | |
* @param ds The {@code DataSource} to retrieve connections from. | |
* @param pmdKnownBroken Some drivers don't support {@link java.sql.ParameterMetaData#getParameterType(int) }; | |
* if {@code pmdKnownBroken} is set to true, we won't even try it; if false, we'll try it, | |
* and if it breaks, we'll remember not to use it again. | |
*/ | |
public QueryRunner(final DataSource ds, final boolean pmdKnownBroken) { | |
super(ds, pmdKnownBroken); | |
} | |
/** | |
* Constructor for QueryRunner that takes a {@code DataSource} to use and a {@code StatementConfiguration}. | |
* | |
* Methods that do not take a {@code Connection} parameter will retrieve connections from this | |
* {@code DataSource}. | |
* | |
* @param ds The {@code DataSource} to retrieve connections from. | |
* @param stmtConfig The configuration to apply to statements when they are prepared. | |
*/ | |
public QueryRunner(final DataSource ds, final StatementConfiguration stmtConfig) { | |
super(ds, stmtConfig); | |
} | |
/** | |
* Constructor for QueryRunner that takes a {@code DataSource}, a {@code StatementConfiguration}, and | |
* controls the use of {@code ParameterMetaData}. Methods that do not take a {@code Connection} parameter | |
* will retrieve connections from this {@code DataSource}. | |
* | |
* @param ds The {@code DataSource} to retrieve connections from. | |
* @param pmdKnownBroken Some drivers don't support {@link java.sql.ParameterMetaData#getParameterType(int) }; | |
* if {@code pmdKnownBroken} is set to true, we won't even try it; if false, we'll try it, | |
* and if it breaks, we'll remember not to use it again. | |
* @param stmtConfig The configuration to apply to statements when they are prepared. | |
*/ | |
public QueryRunner(final DataSource ds, final boolean pmdKnownBroken, final StatementConfiguration stmtConfig) { | |
super(ds, pmdKnownBroken, stmtConfig); | |
} | |
/** | |
* Execute a batch of SQL INSERT, UPDATE, or DELETE queries. | |
* | |
* @param conn The Connection to use to run the query. The caller is | |
* responsible for closing this Connection. | |
* @param sql The SQL to execute. | |
* @param params An array of query replacement parameters. Each row in | |
* this array is one set of batch replacement values. | |
* @return The number of rows updated per statement. | |
* @throws SQLException if a database access error occurs | |
* @since DbUtils 1.1 | |
*/ | |
public int[] batch(final Connection conn, final String sql, final Object[][] params) throws SQLException { | |
return this.batch(conn, false, sql, params); | |
} | |
/** | |
* Execute a batch of SQL INSERT, UPDATE, or DELETE queries. The | |
* {@code Connection} is retrieved from the {@code DataSource} | |
* set in the constructor. This {@code Connection} must be in | |
* auto-commit mode or the update will not be saved. | |
* | |
* @param sql The SQL to execute. | |
* @param params An array of query replacement parameters. Each row in | |
* this array is one set of batch replacement values. | |
* @return The number of rows updated per statement. | |
* @throws SQLException if a database access error occurs | |
* @since DbUtils 1.1 | |
*/ | |
public int[] batch(final String sql, final Object[][] params) throws SQLException { | |
final Connection conn = this.prepareConnection(); | |
return this.batch(conn, true, sql, params); | |
} | |
/** | |
* Calls update after checking the parameters to ensure nothing is null. | |
* @param conn The connection to use for the batch call. | |
* @param closeConn True if the connection should be closed, false otherwise. | |
* @param sql The SQL statement to execute. | |
* @param params An array of query replacement parameters. Each row in | |
* this array is one set of batch replacement values. | |
* @return The number of rows updated in the batch. | |
* @throws SQLException If there are database or parameter errors. | |
*/ | |
private int[] batch(final Connection conn, final boolean closeConn, final String sql, final Object[][] params) throws SQLException { | |
if (conn == null) { | |
throw new SQLException("Null connection"); | |
} | |
if (sql == null) { | |
if (closeConn) { | |
close(conn); | |
} | |
throw new SQLException("Null SQL statement"); | |
} | |
if (params == null) { | |
if (closeConn) { | |
close(conn); | |
} | |
throw new SQLException("Null parameters. If parameters aren't need, pass an empty array."); | |
} | |
PreparedStatement stmt = null; | |
int[] rows = null; | |
try { | |
stmt = this.prepareStatement(conn, sql); | |
for (final Object[] param : params) { | |
this.fillStatement(stmt, param); | |
stmt.addBatch(); | |
} | |
rows = stmt.executeBatch(); | |
} catch (final SQLException e) { | |
this.rethrow(e, sql, (Object[])params); | |
} finally { | |
close(stmt); | |
if (closeConn) { | |
close(conn); | |
} | |
} | |
return rows; | |
} | |
/** | |
* Execute an SQL SELECT query with a single replacement parameter. The | |
* caller is responsible for closing the connection. | |
* @param <T> The type of object that the handler returns | |
* @param conn The connection to execute the query in. | |
* @param sql The query to execute. | |
* @param param The replacement parameter. | |
* @param rsh The handler that converts the results into an object. | |
* @return The object returned by the handler. | |
* @throws SQLException if a database access error occurs | |
* @deprecated Use {@link #query(Connection, String, ResultSetHandler, Object...)} | |
*/ | |
@Deprecated | |
public <T> T query(final Connection conn, final String sql, final Object param, final ResultSetHandler<T> rsh) throws SQLException { | |
return this.<T>query(conn, false, sql, rsh, param); | |
} | |
/** | |
* Execute an SQL SELECT query with replacement parameters. The | |
* caller is responsible for closing the connection. | |
* @param <T> The type of object that the handler returns | |
* @param conn The connection to execute the query in. | |
* @param sql The query to execute. | |
* @param params The replacement parameters. | |
* @param rsh The handler that converts the results into an object. | |
* @return The object returned by the handler. | |
* @throws SQLException if a database access error occurs | |
* @deprecated Use {@link #query(Connection,String,ResultSetHandler,Object...)} instead | |
*/ | |
@Deprecated | |
public <T> T query(final Connection conn, final String sql, final Object[] params, final ResultSetHandler<T> rsh) throws SQLException { | |
return this.<T>query(conn, false, sql, rsh, params); | |
} | |
/** | |
* Execute an SQL SELECT query with replacement parameters. The | |
* caller is responsible for closing the connection. | |
* @param <T> The type of object that the handler returns | |
* @param conn The connection to execute the query in. | |
* @param sql The query to execute. | |
* @param rsh The handler that converts the results into an object. | |
* @param params The replacement parameters. | |
* @return The object returned by the handler. | |
* @throws SQLException if a database access error occurs | |
*/ | |
public <T> T query(final Connection conn, final String sql, final ResultSetHandler<T> rsh, final Object... params) throws SQLException { | |
return this.<T>query(conn, false, sql, rsh, params); | |
} | |
/** | |
* Execute an SQL SELECT query without any replacement parameters. The | |
* caller is responsible for closing the connection. | |
* @param <T> The type of object that the handler returns | |
* @param conn The connection to execute the query in. | |
* @param sql The query to execute. | |
* @param rsh The handler that converts the results into an object. | |
* @return The object returned by the handler. | |
* @throws SQLException if a database access error occurs | |
*/ | |
public <T> T query(final Connection conn, final String sql, final ResultSetHandler<T> rsh) throws SQLException { | |
return this.<T>query(conn, false, sql, rsh, (Object[]) null); | |
} | |
/** | |
* Executes the given SELECT SQL with a single replacement parameter. | |
* The {@code Connection} is retrieved from the | |
* {@code DataSource} set in the constructor. | |
* @param <T> The type of object that the handler returns | |
* @param sql The SQL statement to execute. | |
* @param param The replacement parameter. | |
* @param rsh The handler used to create the result object from | |
* the {@code ResultSet}. | |
* | |
* @return An object generated by the handler. | |
* @throws SQLException if a database access error occurs | |
* @deprecated Use {@link #query(String, ResultSetHandler, Object...)} | |
*/ | |
@Deprecated | |
public <T> T query(final String sql, final Object param, final ResultSetHandler<T> rsh) throws SQLException { | |
final Connection conn = this.prepareConnection(); | |
return this.<T>query(conn, true, sql, rsh, param); | |
} | |
/** | |
* Executes the given SELECT SQL query and returns a result object. | |
* The {@code Connection} is retrieved from the | |
* {@code DataSource} set in the constructor. | |
* @param <T> The type of object that the handler returns | |
* @param sql The SQL statement to execute. | |
* @param params Initialize the PreparedStatement's IN parameters with | |
* this array. | |
* | |
* @param rsh The handler used to create the result object from | |
* the {@code ResultSet}. | |
* | |
* @return An object generated by the handler. | |
* @throws SQLException if a database access error occurs | |
* @deprecated Use {@link #query(String, ResultSetHandler, Object...)} | |
*/ | |
@Deprecated | |
public <T> T query(final String sql, final Object[] params, final ResultSetHandler<T> rsh) throws SQLException { | |
final Connection conn = this.prepareConnection(); | |
return this.<T>query(conn, true, sql, rsh, params); | |
} | |
/** | |
* Executes the given SELECT SQL query and returns a result object. | |
* The {@code Connection} is retrieved from the | |
* {@code DataSource} set in the constructor. | |
* @param <T> The type of object that the handler returns | |
* @param sql The SQL statement to execute. | |
* @param rsh The handler used to create the result object from | |
* the {@code ResultSet}. | |
* @param params Initialize the PreparedStatement's IN parameters with | |
* this array. | |
* @return An object generated by the handler. | |
* @throws SQLException if a database access error occurs | |
*/ | |
public <T> T query(final String sql, final ResultSetHandler<T> rsh, final Object... params) throws SQLException { | |
final Connection conn = this.prepareConnection(); | |
return this.<T>query(conn, true, sql, rsh, params); | |
} | |
/** | |
* Executes the given SELECT SQL without any replacement parameters. | |
* The {@code Connection} is retrieved from the | |
* {@code DataSource} set in the constructor. | |
* @param <T> The type of object that the handler returns | |
* @param sql The SQL statement to execute. | |
* @param rsh The handler used to create the result object from | |
* the {@code ResultSet}. | |
* | |
* @return An object generated by the handler. | |
* @throws SQLException if a database access error occurs | |
*/ | |
public <T> T query(final String sql, final ResultSetHandler<T> rsh) throws SQLException { | |
final Connection conn = this.prepareConnection(); | |
return this.<T>query(conn, true, sql, rsh, (Object[]) null); | |
} | |
/** | |
* Calls query after checking the parameters to ensure nothing is null. | |
* @param conn The connection to use for the query call. | |
* @param closeConn True if the connection should be closed, false otherwise. | |
* @param sql The SQL statement to execute. | |
* @param params An array of query replacement parameters. Each row in | |
* this array is one set of batch replacement values. | |
* @return The results of the query. | |
* @throws SQLException If there are database or parameter errors. | |
*/ | |
private <T> T query(final Connection conn, final boolean closeConn, final String sql, final ResultSetHandler<T> rsh, final Object... params) | |
throws SQLException { | |
if (conn == null) { | |
throw new SQLException("Null connection"); | |
} | |
if (sql == null) { | |
if (closeConn) { | |
close(conn); | |
} | |
throw new SQLException("Null SQL statement"); | |
} | |
if (rsh == null) { | |
if (closeConn) { | |
close(conn); | |
} | |
throw new SQLException("Null ResultSetHandler"); | |
} | |
Statement stmt = null; | |
ResultSet rs = null; | |
T result = null; | |
try { | |
if (params != null && params.length > 0) { | |
final PreparedStatement ps = this.prepareStatement(conn, sql); | |
stmt = ps; | |
this.fillStatement(ps, params); | |
rs = this.wrap(ps.executeQuery()); | |
} else { | |
stmt = conn.createStatement(); | |
rs = this.wrap(stmt.executeQuery(sql)); | |
} | |
result = rsh.handle(rs); | |
} catch (final SQLException e) { | |
this.rethrow(e, sql, params); | |
} finally { | |
closeQuietly(rs); | |
closeQuietly(stmt); | |
if (closeConn) { | |
close(conn); | |
} | |
} | |
return result; | |
} | |
/** | |
* Execute an SQL INSERT, UPDATE, or DELETE query without replacement | |
* parameters. | |
* | |
* @param conn The connection to use to run the query. | |
* @param sql The SQL to execute. | |
* @return The number of rows updated. | |
* @throws SQLException if a database access error occurs | |
*/ | |
public int update(final Connection conn, final String sql) throws SQLException { | |
return this.update(conn, false, sql, (Object[]) null); | |
} | |
/** | |
* Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement | |
* parameter. | |
* | |
* @param conn The connection to use to run the query. | |
* @param sql The SQL to execute. | |
* @param param The replacement parameter. | |
* @return The number of rows updated. | |
* @throws SQLException if a database access error occurs | |
*/ | |
public int update(final Connection conn, final String sql, final Object param) throws SQLException { | |
return this.update(conn, false, sql, param); | |
} | |
/** | |
* Execute an SQL INSERT, UPDATE, or DELETE query. | |
* | |
* @param conn The connection to use to run the query. | |
* @param sql The SQL to execute. | |
* @param params The query replacement parameters. | |
* @return The number of rows updated. | |
* @throws SQLException if a database access error occurs | |
*/ | |
public int update(final Connection conn, final String sql, final Object... params) throws SQLException { | |
return update(conn, false, sql, params); | |
} | |
/** | |
* Executes the given INSERT, UPDATE, or DELETE SQL statement without | |
* any replacement parameters. The {@code Connection} is retrieved | |
* from the {@code DataSource} set in the constructor. This | |
* {@code Connection} must be in auto-commit mode or the update will | |
* not be saved. | |
* | |
* @param sql The SQL statement to execute. | |
* @throws SQLException if a database access error occurs | |
* @return The number of rows updated. | |
*/ | |
public int update(final String sql) throws SQLException { | |
final Connection conn = this.prepareConnection(); | |
return this.update(conn, true, sql, (Object[]) null); | |
} | |
/** | |
* Executes the given INSERT, UPDATE, or DELETE SQL statement with | |
* a single replacement parameter. The {@code Connection} is | |
* retrieved from the {@code DataSource} set in the constructor. | |
* This {@code Connection} must be in auto-commit mode or the | |
* update will not be saved. | |
* | |
* @param sql The SQL statement to execute. | |
* @param param The replacement parameter. | |
* @throws SQLException if a database access error occurs | |
* @return The number of rows updated. | |
*/ | |
public int update(final String sql, final Object param) throws SQLException { | |
final Connection conn = this.prepareConnection(); | |
return this.update(conn, true, sql, param); | |
} | |
/** | |
* Executes the given INSERT, UPDATE, or DELETE SQL statement. The | |
* {@code Connection} is retrieved from the {@code DataSource} | |
* set in the constructor. This {@code Connection} must be in | |
* auto-commit mode or the update will not be saved. | |
* | |
* @param sql The SQL statement to execute. | |
* @param params Initializes the PreparedStatement's IN (i.e. '?') | |
* parameters. | |
* @throws SQLException if a database access error occurs | |
* @return The number of rows updated. | |
*/ | |
public int update(final String sql, final Object... params) throws SQLException { | |
final Connection conn = this.prepareConnection(); | |
return this.update(conn, true, sql, params); | |
} | |
/** | |
* Calls update after checking the parameters to ensure nothing is null. | |
* @param conn The connection to use for the update call. | |
* @param closeConn True if the connection should be closed, false otherwise. | |
* @param sql The SQL statement to execute. | |
* @param params An array of update replacement parameters. Each row in | |
* this array is one set of update replacement values. | |
* @return The number of rows updated. | |
* @throws SQLException If there are database or parameter errors. | |
*/ | |
private int update(final Connection conn, final boolean closeConn, final String sql, final Object... params) throws SQLException { | |
if (conn == null) { | |
throw new SQLException("Null connection"); | |
} | |
if (sql == null) { | |
if (closeConn) { | |
close(conn); | |
} | |
throw new SQLException("Null SQL statement"); | |
} | |
Statement stmt = null; | |
int rows = 0; | |
try { | |
if (params != null && params.length > 0) { | |
final PreparedStatement ps = this.prepareStatement(conn, sql); | |
stmt = ps; | |
this.fillStatement(ps, params); | |
rows = ps.executeUpdate(); | |
} else { | |
stmt = conn.createStatement(); | |
rows = stmt.executeUpdate(sql); | |
} | |
} catch (final SQLException e) { | |
this.rethrow(e, sql, params); | |
} finally { | |
close(stmt); | |
if (closeConn) { | |
close(conn); | |
} | |
} | |
return rows; | |
} | |
/** | |
* Executes the given INSERT SQL without any replacement parameters. | |
* The {@code Connection} is retrieved from the | |
* {@code DataSource} set in the constructor. | |
* @param <T> The type of object that the handler returns | |
* @param sql The SQL statement to execute. | |
* @param rsh The handler used to create the result object from | |
* the {@code ResultSet} of auto-generated keys. | |
* @return An object generated by the handler. | |
* @throws SQLException if a database access error occurs | |
* @since 1.6 | |
*/ | |
public <T> T insert(final String sql, final ResultSetHandler<T> rsh) throws SQLException { | |
return insert(this.prepareConnection(), true, sql, rsh, (Object[]) null); | |
} | |
/** | |
* Executes the given INSERT SQL statement. The | |
* {@code Connection} is retrieved from the {@code DataSource} | |
* set in the constructor. This {@code Connection} must be in | |
* auto-commit mode or the insert will not be saved. | |
* @param <T> The type of object that the handler returns | |
* @param sql The SQL statement to execute. | |
* @param rsh The handler used to create the result object from | |
* the {@code ResultSet} of auto-generated keys. | |
* @param params Initializes the PreparedStatement's IN (i.e. '?') | |
* @return An object generated by the handler. | |
* @throws SQLException if a database access error occurs | |
* @since 1.6 | |
*/ | |
public <T> T insert(final String sql, final ResultSetHandler<T> rsh, final Object... params) throws SQLException { | |
return insert(this.prepareConnection(), true, sql, rsh, params); | |
} | |
/** | |
* Execute an SQL INSERT query without replacement parameters. | |
* @param <T> The type of object that the handler returns | |
* @param conn The connection to use to run the query. | |
* @param sql The SQL to execute. | |
* @param rsh The handler used to create the result object from | |
* the {@code ResultSet} of auto-generated keys. | |
* @return An object generated by the handler. | |
* @throws SQLException if a database access error occurs | |
* @since 1.6 | |
*/ | |
public <T> T insert(final Connection conn, final String sql, final ResultSetHandler<T> rsh) throws SQLException { | |
return insert(conn, false, sql, rsh, (Object[]) null); | |
} | |
/** | |
* Execute an SQL INSERT query. | |
* @param <T> The type of object that the handler returns | |
* @param conn The connection to use to run the query. | |
* @param sql The SQL to execute. | |
* @param rsh The handler used to create the result object from | |
* the {@code ResultSet} of auto-generated keys. | |
* @param params The query replacement parameters. | |
* @return An object generated by the handler. | |
* @throws SQLException if a database access error occurs | |
* @since 1.6 | |
*/ | |
public <T> T insert(final Connection conn, final String sql, final ResultSetHandler<T> rsh, final Object... params) throws SQLException { | |
return insert(conn, false, sql, rsh, params); | |
} | |
/** | |
* Executes the given INSERT SQL statement. | |
* @param conn The connection to use for the query call. | |
* @param closeConn True if the connection should be closed, false otherwise. | |
* @param sql The SQL statement to execute. | |
* @param rsh The handler used to create the result object from | |
* the {@code ResultSet} of auto-generated keys. | |
* @param params The query replacement parameters. | |
* @return An object generated by the handler. | |
* @throws SQLException If there are database or parameter errors. | |
* @since 1.6 | |
*/ | |
private <T> T insert(final Connection conn, final boolean closeConn, final String sql, final ResultSetHandler<T> rsh, final Object... params) | |
throws SQLException { | |
if (conn == null) { | |
throw new SQLException("Null connection"); | |
} | |
if (sql == null) { | |
if (closeConn) { | |
close(conn); | |
} | |
throw new SQLException("Null SQL statement"); | |
} | |
if (rsh == null) { | |
if (closeConn) { | |
close(conn); | |
} | |
throw new SQLException("Null ResultSetHandler"); | |
} | |
Statement stmt = null; | |
T generatedKeys = null; | |
try { | |
if (params != null && params.length > 0) { | |
final PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); | |
stmt = ps; | |
this.fillStatement(ps, params); | |
ps.executeUpdate(); | |
} else { | |
stmt = conn.createStatement(); | |
stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS); | |
} | |
final ResultSet resultSet = stmt.getGeneratedKeys(); | |
generatedKeys = rsh.handle(resultSet); | |
} catch (final SQLException e) { | |
this.rethrow(e, sql, params); | |
} finally { | |
close(stmt); | |
if (closeConn) { | |
close(conn); | |
} | |
} | |
return generatedKeys; | |
} | |
/** | |
* Executes the given batch of INSERT SQL statements. The | |
* {@code Connection} is retrieved from the {@code DataSource} | |
* set in the constructor. This {@code Connection} must be in | |
* auto-commit mode or the insert will not be saved. | |
* @param <T> The type of object that the handler returns | |
* @param sql The SQL statement to execute. | |
* @param rsh The handler used to create the result object from | |
* the {@code ResultSet} of auto-generated keys. | |
* @param params Initializes the PreparedStatement's IN (i.e. '?') | |
* @return The result generated by the handler. | |
* @throws SQLException if a database access error occurs | |
* @since 1.6 | |
*/ | |
public <T> T insertBatch(final String sql, final ResultSetHandler<T> rsh, final Object[][] params) throws SQLException { | |
return insertBatch(this.prepareConnection(), true, sql, rsh, params); | |
} | |
/** | |
* Executes the given batch of INSERT SQL statements. | |
* @param <T> The type of object that the handler returns | |
* @param conn The connection to use to run the query. | |
* @param sql The SQL to execute. | |
* @param rsh The handler used to create the result object from | |
* the {@code ResultSet} of auto-generated keys. | |
* @param params The query replacement parameters. | |
* @return The result generated by the handler. | |
* @throws SQLException if a database access error occurs | |
* @since 1.6 | |
*/ | |
public <T> T insertBatch(final Connection conn, final String sql, final ResultSetHandler<T> rsh, final Object[][] params) throws SQLException { | |
return insertBatch(conn, false, sql, rsh, params); | |
} | |
/** | |
* Executes the given batch of INSERT SQL statements. | |
* @param conn The connection to use for the query call. | |
* @param closeConn True if the connection should be closed, false otherwise. | |
* @param sql The SQL statement to execute. | |
* @param rsh The handler used to create the result object from | |
* the {@code ResultSet} of auto-generated keys. | |
* @param params The query replacement parameters. | |
* @return The result generated by the handler. | |
* @throws SQLException If there are database or parameter errors. | |
* @since 1.6 | |
*/ | |
private <T> T insertBatch(final Connection conn, final boolean closeConn, final String sql, final ResultSetHandler<T> rsh, final Object[][] params) | |
throws SQLException { | |
if (conn == null) { | |
throw new SQLException("Null connection"); | |
} | |
if (sql == null) { | |
if (closeConn) { | |
close(conn); | |
} | |
throw new SQLException("Null SQL statement"); | |
} | |
if (params == null) { | |
if (closeConn) { | |
close(conn); | |
} | |
throw new SQLException("Null parameters. If parameters aren't need, pass an empty array."); | |
} | |
PreparedStatement stmt = null; | |
T generatedKeys = null; | |
try { | |
stmt = this.prepareStatement(conn, sql, Statement.RETURN_GENERATED_KEYS); | |
for (final Object[] param : params) { | |
this.fillStatement(stmt, param); | |
stmt.addBatch(); | |
} | |
stmt.executeBatch(); | |
final ResultSet rs = stmt.getGeneratedKeys(); | |
generatedKeys = rsh.handle(rs); | |
} catch (final SQLException e) { | |
this.rethrow(e, sql, (Object[])params); | |
} finally { | |
close(stmt); | |
if (closeConn) { | |
close(conn); | |
} | |
} | |
return generatedKeys; | |
} | |
/** | |
* Execute an SQL statement, including a stored procedure call, which does | |
* not return any result sets. | |
* Any parameters which are instances of {@link OutParameter} will be | |
* registered as OUT parameters. | |
* <p> | |
* Use this method when invoking a stored procedure with OUT parameters | |
* that does not return any result sets. If you are not invoking a stored | |
* procedure, or the stored procedure has no OUT parameters, consider using | |
* {@link #update(java.sql.Connection, String, Object...) }. | |
* If the stored procedure returns result sets, use | |
* {@link #execute(java.sql.Connection, String, org.apache.commons.dbutils.ResultSetHandler, Object...) }. | |
* | |
* @param conn The connection to use to run the query. | |
* @param sql The SQL to execute. | |
* @param params The query replacement parameters. | |
* @return The number of rows updated. | |
* @throws SQLException if a database access error occurs | |
*/ | |
public int execute(final Connection conn, final String sql, final Object... params) throws SQLException { | |
return this.execute(conn, false, sql, params); | |
} | |
/** | |
* Execute an SQL statement, including a stored procedure call, which does | |
* not return any result sets. | |
* Any parameters which are instances of {@link OutParameter} will be | |
* registered as OUT parameters. | |
* <p> | |
* Use this method when invoking a stored procedure with OUT parameters | |
* that does not return any result sets. If you are not invoking a stored | |
* procedure, or the stored procedure has no OUT parameters, consider using | |
* {@link #update(String, Object...) }. | |
* If the stored procedure returns result sets, use | |
* {@link #execute(String, org.apache.commons.dbutils.ResultSetHandler, Object...) }. | |
* <p> | |
* The {@code Connection} is retrieved from the {@code DataSource} | |
* set in the constructor. This {@code Connection} must be in | |
* auto-commit mode or the update will not be saved. | |
* | |
* @param sql The SQL statement to execute. | |
* @param params Initializes the CallableStatement's parameters (i.e. '?'). | |
* @throws SQLException if a database access error occurs | |
* @return The number of rows updated. | |
*/ | |
public int execute(final String sql, final Object... params) throws SQLException { | |
final Connection conn = this.prepareConnection(); | |
return this.execute(conn, true, sql, params); | |
} | |
/** | |
* Execute an SQL statement, including a stored procedure call, which | |
* returns one or more result sets. | |
* Any parameters which are instances of {@link OutParameter} will be | |
* registered as OUT parameters. | |
* <p> | |
* Use this method when: a) running SQL statements that return multiple | |
* result sets; b) invoking a stored procedure that return result | |
* sets and OUT parameters. Otherwise you may wish to use | |
* {@link #query(java.sql.Connection, String, org.apache.commons.dbutils.ResultSetHandler, Object...) } | |
* (if there are no OUT parameters) or | |
* {@link #execute(java.sql.Connection, String, Object...) } | |
* (if there are no result sets). | |
* | |
* @param <T> The type of object that the handler returns | |
* @param conn The connection to use to run the query. | |
* @param sql The SQL to execute. | |
* @param rsh The result set handler | |
* @param params The query replacement parameters. | |
* @return A list of objects generated by the handler | |
* @throws SQLException if a database access error occurs | |
*/ | |
public <T> List<T> execute(final Connection conn, final String sql, final ResultSetHandler<T> rsh, final Object... params) throws SQLException { | |
return this.execute(conn, false, sql, rsh, params); | |
} | |
/** | |
* Execute an SQL statement, including a stored procedure call, which | |
* returns one or more result sets. | |
* Any parameters which are instances of {@link OutParameter} will be | |
* registered as OUT parameters. | |
* <p> | |
* Use this method when: a) running SQL statements that return multiple | |
* result sets; b) invoking a stored procedure that return result | |
* sets and OUT parameters. Otherwise you may wish to use | |
* {@link #query(String, org.apache.commons.dbutils.ResultSetHandler, Object...) } | |
* (if there are no OUT parameters) or | |
* {@link #execute(String, Object...) } | |
* (if there are no result sets). | |
* | |
* @param <T> The type of object that the handler returns | |
* @param sql The SQL to execute. | |
* @param rsh The result set handler | |
* @param params The query replacement parameters. | |
* @return A list of objects generated by the handler | |
* @throws SQLException if a database access error occurs | |
*/ | |
public <T> List<T> execute(final String sql, final ResultSetHandler<T> rsh, final Object... params) throws SQLException { | |
final Connection conn = this.prepareConnection(); | |
return this.execute(conn, true, sql, rsh, params); | |
} | |
/** | |
* Invokes the stored procedure via update after checking the parameters to | |
* ensure nothing is null. | |
* @param conn The connection to use for the update call. | |
* @param closeConn True if the connection should be closed, false otherwise. | |
* @param sql The SQL statement to execute. | |
* @param params An array of update replacement parameters. Each row in | |
* this array is one set of update replacement values. | |
* @return The number of rows updated. | |
* @throws SQLException If there are database or parameter errors. | |
*/ | |
private int execute(final Connection conn, final boolean closeConn, final String sql, final Object... params) throws SQLException { | |
if (conn == null) { | |
throw new SQLException("Null connection"); | |
} | |
if (sql == null) { | |
if (closeConn) { | |
close(conn); | |
} | |
throw new SQLException("Null SQL statement"); | |
} | |
CallableStatement stmt = null; | |
int rows = 0; | |
try { | |
stmt = this.prepareCall(conn, sql); | |
this.fillStatement(stmt, params); | |
stmt.execute(); | |
rows = stmt.getUpdateCount(); | |
this.retrieveOutParameters(stmt, params); | |
} catch (final SQLException e) { | |
this.rethrow(e, sql, params); | |
} finally { | |
close(stmt); | |
if (closeConn) { | |
close(conn); | |
} | |
} | |
return rows; | |
} | |
/** | |
* Invokes the stored procedure via update after checking the parameters to | |
* ensure nothing is null. | |
* @param conn The connection to use for the update call. | |
* @param closeConn True if the connection should be closed, false otherwise. | |
* @param sql The SQL statement to execute. | |
* @param rsh The result set handler | |
* @param params An array of update replacement parameters. Each row in | |
* this array is one set of update replacement values. | |
* @return List of all objects generated by the ResultSetHandler for all result sets handled. | |
* @throws SQLException If there are database or parameter errors. | |
*/ | |
private <T> List<T> execute(final Connection conn, final boolean closeConn, final String sql, final ResultSetHandler<T> rsh, final Object... params) throws SQLException { | |
if (conn == null) { | |
throw new SQLException("Null connection"); | |
} | |
if (sql == null) { | |
if (closeConn) { | |
close(conn); | |
} | |
throw new SQLException("Null SQL statement"); | |
} | |
if (rsh == null) { | |
if (closeConn) { | |
close(conn); | |
} | |
throw new SQLException("Null ResultSetHandler"); | |
} | |
CallableStatement stmt = null; | |
final List<T> results = new LinkedList<>(); | |
try { | |
stmt = this.prepareCall(conn, sql); | |
this.fillStatement(stmt, params); | |
boolean moreResultSets = stmt.execute(); | |
// Handle multiple result sets by passing them through the handler | |
// retaining the final result | |
ResultSet rs = null; | |
while (moreResultSets) { | |
try { | |
rs = this.wrap(stmt.getResultSet()); | |
results.add(rsh.handle(rs)); | |
moreResultSets = stmt.getMoreResults(); | |
} finally { | |
close(rs); | |
} | |
} | |
this.retrieveOutParameters(stmt, params); | |
} catch (final SQLException e) { | |
this.rethrow(e, sql, params); | |
} finally { | |
close(stmt); | |
if (closeConn) { | |
close(conn); | |
} | |
} | |
return results; | |
} | |
/** | |
* Set the value on all the {@link OutParameter} instances in the | |
* {@code params} array using the OUT parameter values from the | |
* {@code stmt}. | |
* @param stmt the statement from which to retrieve OUT parameter values | |
* @param params the parameter array for the statement invocation | |
* @throws SQLException when the value could not be retrieved from the | |
* statement. | |
*/ | |
private void retrieveOutParameters(final CallableStatement stmt, final Object[] params) throws SQLException { | |
if (params != null) { | |
for (int i = 0; i < params.length; i++) { | |
if (params[i] instanceof OutParameter) { | |
((OutParameter)params[i]).setValue(stmt, i + 1); | |
} | |
} | |
} | |
} | |
} |