net.freeutils.util.db.DBRunner Maven / Gradle / Ivy
Show all versions of jelementary Show documentation
/*
* Copyright © 2003-2024 Amichai Rothman
*
* This file is part of JElementary - the Java Elementary Utilities package.
*
* JElementary is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 2 of the License, or
* (at your option) any later version.
*
* JElementary is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with JElementary. If not, see
* It works in two different modes, both supplying the same query interface:
* transaction and non-transaction mode.
*
* Transaction mode is initiated using the constructor which accepts a Connection.
* This connection is used throughout all queries on this runner, and nothing
* is committed, rolled back, or closed automatically - it is the responsibility
* of the caller to do so when the transaction is complete (auto-commit is disabled).
* For example:
*
* DBRunner runner = new DBRunner(connection);
* try {
* // run queries and updates using runner...
* runner.commit();
* } catch (Exception e) {
* // handle error...
* runner.rollback();
* } finally {
* runner.close();
* }
*
*
* Non-transaction mode is initiated using the constructor which accepts a DBManager.
* In this case, each query or update is run independently using a fresh connection
* taken from this DBManager, so that no connection resources are held in between queries.
* Each connection is closed automatically after the query is executed, and updates
* are committed or rolled back automatically as well (auto-commit is enabled).
*
* This class can be extended by subclasses to customize parameter handling
* (by overriding the {@link #setParams} method), add additional convenience
* query methods (with custom ResultHandlers or validations), etc.
*
* @author Amichai Rothman
* @since 2010-02-20
*/
public class DBRunner implements Closeable {
protected final boolean transaction;
protected final DBManager manager;
protected Connection conn;
protected int retries = 1;
protected boolean useBatch;
protected String batchQuery;
protected Connection batchConn;
protected PreparedStatement batch;
/**
* Constructs a DBRunner which runs queries in transactions
* on the given connection. The transaction must be committed or
* rolled back, and the runner must be closed by the caller.
*
* See the {@link DBRunner class javadocs} for more details.
*
* @param conn the connection to run the queries on
*/
public DBRunner(Connection conn) {
this.manager = null;
this.conn = conn;
this.transaction = true;
setAutoCommit(conn, false);
}
/**
* Constructs a DBRunner which runs queries with no transaction
* using connections from the given DBManager.
*
* See the {@link DBRunner class javadocs} for more details.
*
* @param manager the DBManager providing the connections to run the queries on
*/
public DBRunner(DBManager manager) {
this.manager = manager;
this.conn = null;
this.transaction = false;
}
/**
* Sets the total number of times each query/update will be attempted before
* any thrown SQLException is propagated.
*
* @param retries the number of retries (including the first attempt)
* @throws IllegalArgumentException if retries is less than 1
*/
public void setRetries(int retries) {
if (retries < 1)
throw new IllegalArgumentException("retries must be greater than 0");
this.retries = retries;
}
/**
* Sets the auto-commit mode of the given connection.
*
* @param conn the connection whose auto-commit mode is to be set
* @param autocommit specifies whether auto-commit is enabled or not
*/
protected void setAutoCommit(Connection conn, boolean autocommit) {
try {
conn.setAutoCommit(autocommit);
} catch (SQLException ignore) {
// if this fails, then further queries will fail anyway
}
}
/**
* Returns a connection to run queries on.
* It is the caller's responsibility to close the connection if necessary.
*
* @return a connection to run queries on
* @throws SQLException if an error occurs
*/
public Connection getConnection() throws SQLException {
Connection conn = transaction ? this.conn : manager.getConnection();
if (!transaction)
setAutoCommit(conn, true);
else if (conn == null)
throw new SQLException("DBRunner is closed");
return conn;
}
/**
* Commits the transaction.
* If no connection is associated with this runner, does nothing.
*
* @throws SQLException if an error occurs
*/
public void commit() throws SQLException {
if (conn != null)
conn.commit(); // only when auto-commit is disabled
}
/**
* Rolls back the transaction.
* If no connection is associated with this runner, does nothing.
*
* @throws SQLException if an error occurs
*/
public void rollback() throws SQLException {
if (conn != null)
conn.rollback(); // only when auto-commit is disabled
}
/**
* Closes the underlying connection.
* If no connection is associated with this runner, does nothing.
*/
@Override
public void close() {
if (conn != null)
closeSilently(conn);
conn = null;
}
/**
* Sets the parameters for the given statement, in their given order.
*
* Subclasses can override this method to implement parameter type
* conversions, invoke various type-specific statement setters to set
* the parameters, etc.
*
* @param statement a statement
* @param params the parameters to set
* @throws SQLException if an error occurs
*/
public void setParams(PreparedStatement statement, Object... params) throws SQLException {
int i = 1;
for (Object param : params) {
if (param == null)
statement.setNull(i++, Types.NULL);
else
statement.setObject(i++, param);
}
}
/**
* Starts a batch update.
*
* This method does not create a batch immediately (it has no
* query string to do so). Instead, the first invocation of
* a regular update method after starting the batch will create
* the batch and add the update to it, and subsequent invocations
* of the update method (with the same query string) will add
* the updates to the batch instead of executing them immediately.
*
* The batch is executed by calling {@link #execBatch},
* or aborted by calling {@link #closeBatch()}.
*
* This mechanism allows code that performs multiple updates with the
* same query to be reused both for sequential execution and batch
* execution, depending on whether it is wrapped by startBatch
* and execBatch calls.
*/
public void startBatch() {
useBatch = true;
}
/**
* Creates a new batch using the given query.
*
* @param query the query
* @throws SQLException if an error occurs
* @throws IllegalStateException if there is an existing batch with a different query
*/
public void createBatch(String query) throws SQLException {
if (batch != null) {
if (query.equals(batchQuery))
return; // batch already exists with same query
throw new IllegalStateException("previous batch must be closed before creating a new one");
}
try {
batchConn = getConnection();
batch = batchConn.prepareStatement(query);
} catch (SQLException sqle) {
try {
closeBatch();
} catch (SQLException sqle2) {
throw sqle;
}
}
batchQuery = query;
}
/**
* Adds an update to the current batch, creating it first if necessary.
* If the batch already exists, the given query must be identical to it.
*
* @param query the query to batch
* @param params the query parameters
* @throws SQLException if an error occurs
* @throws IllegalStateException if there is an existing batch with a different query
*/
public void updateBatch(String query, Object... params) throws SQLException {
if (batch == null) {
createBatch(query);
} else if (!query.equals(batchQuery)) {
throw new IllegalStateException("all queries in a batch must be identical");
}
setParams(batch, params);
batch.addBatch();
}
/**
* Executes the current batch and closes it.
*
* @return the update results of each respective query in the batch
* @throws SQLException if an error occurs
* @throws IllegalStateException if there is no current batch
*/
public int[] execBatch() throws SQLException {
try {
if (batch == null) {
if (useBatch)
return new int[0]; // batch started but no updates in it
throw new IllegalStateException("there is no batch to execute");
} else {
return batch.executeBatch();
}
} finally {
closeBatch();
}
}
/**
* Closes the current batch (without executing it).
*
* @throws SQLException if an error occurs
*/
public void closeBatch() throws SQLException {
try {
try {
if (batch != null)
batch.close();
} finally {
if (batchConn != null && !transaction)
batchConn.close();
}
} finally {
useBatch = false;
batchQuery = null;
batchConn = null;
batch = null;
}
}
/**
* Executes a query or update.
*
* @param conn the connection on which the query is executed
* @param query the query to execute
* @param params the query parameters
* @throws SQLException if an error occurs
*/
public void exec(Connection conn, String query, Object... params) throws SQLException {
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(query);
setParams(ps, params);
ps.execute();
} finally {
closeSilently(ps);
}
}
/**
* Executes all queries in the given script file, delimited by the given delimiter.
* If successful, a commit is performed at the end, otherwise a rollback is performed.
*
* The script file is executed by passing its contents to the overloaded
* {@link #exec(InputStream, String, boolean) exec} method.
* See its documentation for further details.
*
* @param script the script file to execute
* @param delimiter the sequence that delimits queries within the file
* @param continueOnError if true an error during query execution will be ignored and
* the following queries will be executed as well, otherwise any encountered error
* will result in an exception being thrown and further processing aborted
* @throws IOException if an error occurs reading the script file
* @throws SQLException if an error occurs running the queries
*/
public void exec(File script, String delimiter, boolean continueOnError) throws IOException, SQLException {
exec(new FileInputStream(script.getAbsolutePath()), delimiter, continueOnError);
}
/**
* Executes all queries in the given script, delimited by the given delimiter.
* If successful, a commit is performed at the end, otherwise a rollback is performed.
* The stream is closed by this method.
*
* Note that while ";" is a natural choice of delimiter for simple scripts, it will pose
* a problem for scripts containing code blocks (stored procedures etc.) which contain
* semicolons internally. For such scripts, a unique comment sequence such as "--/" can
* be used, which has the added benefit of being ignored by other script-processing
* applications. Another option would be the sequence "\n;\n", or a semicolon on a line
* of its own.
*
* Also note that if this DBRunner uses a pooled connection, all queries in the
* script will be cached. For bulk inserts or other scripts that should not be
* cached, call this method on a DBRunner constructed with a non-pooled connection.
*
* @param script the script to execute
* @param delimiter the sequence that delimits queries within the file
* @param continueOnError if true an error during query execution will be ignored and
* the following queries will be executed as well, otherwise any encountered error
* will result in an exception being thrown and further processing aborted
* @throws IOException if an error occurs reading the script file
* @throws SQLException if an error occurs running the queries
*/
public void exec(InputStream script, String delimiter, boolean continueOnError)
throws IOException, SQLException {
String content = Streams.readString(script, "UTF-8"); // also closes stream
String[] queries = Strings.split(content, delimiter, true);
Connection conn = getConnection();
try {
for (String query : queries) {
try {
exec(conn, Strings.trimRight(query, ';'));
} catch (SQLException sqle) {
if (!continueOnError)
throw sqle;
}
}
if (transaction)
conn.commit();
} catch (SQLException sqle) {
if (transaction)
conn.rollback();
throw sqle;
} finally {
if (!transaction)
closeSilently(conn);
}
}
/**
* Executes an update or selection query.
*
* @param update true if the query is an update; false if it is a selection query
* @param handler the results handler that handles the results
* of an update, or null for a selection query
* @param query the query to execute
* @param params the query parameters
* @return the returned ResultSet or update count, depending on the query type
* @throws SQLException if an error occurs
*/
protected Object exec(boolean update, ResultsHandler handler, String query, Object... params) throws SQLException {
SQLException e = null;
for (int i = retries; i > 0; i--) {
try {
if (useBatch) {
updateBatch(query, params);
return 0;
}
PreparedStatement ps = null;
Connection conn = getConnection();
ResultSet rs = null;
try {
ps = conn.prepareStatement(query);
setParams(ps, params);
if (update) {
return ps.executeUpdate();
} else {
rs = ps.executeQuery();
return handler.handle(rs);
}
} finally {
if (rs != null)
rs.close();
closeSilently(ps);
if (!transaction)
closeSilently(conn);
}
} catch (SQLException sqle) {
if (e == null)
e = sqle;
}
}
throw e;
}
/**
* Executes a selection query.
*
* @param the type of the result returned by the handler
* @param handler the results handler that handles the results
* @param query the query to execute
* @param params the query parameters
* @return the result of the handler's handling of the query's result set
* @throws SQLException if an error occurs
*/
@SuppressWarnings("unchecked")
public T query(ResultsHandler handler, String query, Object... params) throws SQLException {
return (T)exec(false, handler, query, params);
}
/**
* Executes an update query.
*
* @param query the query to execute
* @param params the query parameters
* @return the number of updated rows
* @throws SQLException if an error occurs
*/
public int update(String query, Object... params) throws SQLException {
return (Integer)exec(true, null, query, params);
}
/**
* Executes an update query. The result is verified to have
* updated at least 1 row, otherwise an exception is thrown.
* In batch mode, no exception is thrown because the batch
* has not yet been executed.
*
* @param query the query to execute
* @param params the query parameters
* @return the number of updated rows
* @throws SQLException if an error occurs or no rows were affected by the update
*/
public int update1(String query, Object... params) throws SQLException {
int res = update(query, params);
if (res < 1 && batch == null)
throw new SQLException("update did not affect any rows");
return res;
}
/**
* Executes a selection query which returns a single scalar result.
*
* @param query the query to execute
* @param params the query parameters
* @return the returned scalar, or null if the result set is empty
* @throws SQLException if an error occurs
*/
public Object queryScalar(String query, Object... params) throws SQLException {
return query(SCALAR_HANDLER, query, params);
}
/**
* Executes a selection query which returns a single row as an array.
*
* @param query the query to execute
* @param params the query parameters
* @return the returned array, or null if the result set is empty
* @throws SQLException if an error occurs
*/
public Object[] queryArray(String query, Object... params) throws SQLException {
return query(ARRAY_HANDLER, query, params);
}
/**
* Executes a selection query which returns a list of rows as arrays.
*
* @param query the query to execute
* @param params the query parameters
* @return the returned list, or an empty list if the result set is empty
* @throws SQLException if an error occurs
*/
public List queryArrayList(String query, Object... params) throws SQLException {
return query(ARRAY_LIST_HANDLER, query, params);
}
/**
* Executes a selection query to check for existence of a result.
* Returns true if there are any results, false if not.
*
* @param query the query to execute
* @param params the query parameters
* @return true if there are any results, false if not
* @throws SQLException if an error occurs
*/
public boolean queryExists(String query, Object... params) throws SQLException {
return query(EXISTS_HANDLER, query, params);
}
/**
* Executes a selection query which returns a single numerical result.
*
* @param query the query to execute
* @param params the query parameters
* @return the returned number, or null if the result set is empty
* @throws SQLException if an error occurs
*/
public Number queryNumber(String query, Object... params) throws SQLException {
return (Number)query(SCALAR_HANDLER, query, params);
}
/**
* Executes a selection query which returns a single int result.
*
* @param query the query to execute
* @param params the query parameters
* @return the returned int, or null if the result set is empty
* @throws SQLException if an error occurs
*/
public Integer queryInt(String query, Object... params) throws SQLException {
Number n = queryNumber(query, params);
return n == null ? null : n.intValue();
}
/**
* Executes a selection query which returns a single long result.
*
* @param query the query to execute
* @param params the query parameters
* @return the returned long, or null if the result set is empty
* @throws SQLException if an error occurs
*/
public Long queryLong(String query, Object... params) throws SQLException {
Number n = queryNumber(query, params);
return n == null ? null : n.longValue();
}
/**
* Executes a selection query which returns a single numerical result,
* and returns whether the result is non-zero.
* This can be useful to check if a COUNT query returns a non-zero value.
*
* @param query the query to execute
* @param params the query parameters
* @return true if the result is non-zero, false if it is zero
* @throws SQLException if an error occurs
*/
public boolean queryNonZero(String query, Object... params) throws SQLException {
return queryLong(query, params) != 0;
}
/**
* Executes a selection query which returns a single string result.
*
* @param query the query to execute
* @param params the query parameters
* @return the returned string, or null if the result set is empty
* @throws SQLException if an error occurs
*/
public String queryString(String query, Object... params) throws SQLException {
return (String)query(SCALAR_HANDLER, query, params);
}
/**
* Executes a selection query which returns a byte array result.
* The entire array is returned in-memory, so this should be used
* only for small arrays.
*
* @param query the query to execute
* @param params the query parameters
* @return the returned array, or null if the result set is empty
* @throws SQLException if an error occurs
*/
public byte[] queryBytes(String query, Object... params) throws SQLException {
return (byte[])query(SCALAR_HANDLER, query, params);
}
/**
* Executes a selection query which returns a single bean result.
*
* @param the bean type
* @param cls the bean class
* @param query the query to execute
* @param params the query parameters
* @return the returned bean, or null if the result set is empty
* @throws SQLException if an error occurs
*/
public T queryBean(Class cls, String query, Object... params) throws SQLException {
return query(new BeanResultsHandler<>(cls), query, params);
}
/**
* Executes a selection query which returns a single bean result.
*
* @param the bean type
* @param cls the bean class
* @param ignoreErrors if true, fields that cannot be set are ignored; if false, an exception is thrown
* @param query the query to execute
* @param params the query parameters
* @return the returned bean, or null if the result set is empty
* @throws SQLException if an error occurs
*/
public T queryBean(Class cls, boolean ignoreErrors, String query, Object... params) throws SQLException {
return query(new BeanResultsHandler<>(cls, ignoreErrors), query, params);
}
/**
* Executes a selection query which returns a list of rows as beans.
*
* @param the bean type
* @param cls the bean class
* @param query the query to execute
* @param params the query parameters
* @return the returned list, or an empty list if the result set is empty
* @throws SQLException if an error occurs
*/
public List queryBeans(Class cls, String query, Object... params) throws SQLException {
return query(new BeanListResultsHandler<>(cls), query, params);
}
/**
* Executes a selection query which returns a list of rows as beans.
*
* @param the bean type
* @param cls the bean class
* @param ignoreErrors if true, fields that cannot be set are ignored; if false, an exception is thrown
* @param query the query to execute
* @param params the query parameters
* @return the returned list, or an empty list if the result set is empty
* @throws SQLException if an error occurs
*/
public List queryBeans(Class cls, boolean ignoreErrors, String query, Object... params) throws SQLException {
return query(new BeanListResultsHandler<>(cls, ignoreErrors), query, params);
}
}