net.dongliu.dbutils.SqlExecutor Maven / Gradle / Ivy
package net.dongliu.dbutils;
import net.dongliu.dbutils.exception.ReflectionException;
import net.dongliu.dbutils.exception.UncheckedSQLException;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.Objects;
/**
* @author Liu Dong
*/
class SqlExecutor {
/**
* Factory method that creates and initializes a
* PreparedStatement object for the given SQL.
* Database methods always call this method to prepare
* statements for them. Subclasses can override this method to provide
* special PreparedStatement configuration if needed. This implementation
* simply calls conn.prepareStatement(sql).
*
* @param conn The Connection used to create the
* PreparedStatement
* @param sql The SQL statement to prepare.
* @return An initialized PreparedStatement.
*/
private PreparedStatement prepareStatement(Connection conn, String sql) {
try {
return conn.prepareStatement(sql);
} catch (SQLException e) {
throw new UncheckedSQLException(e);
}
}
/**
* Factory method that creates and initializes a
* PreparedStatement object for the given SQL.
* Database methods always call this method to prepare
* statements for them. Subclasses can override this method to provide
* special PreparedStatement configuration if needed. This implementation
* simply calls conn.prepareStatement(sql, returnedKeys)
* which will result in the ability to retrieve the automatically-generated
* keys from an auto_increment column.
*
* @param conn The Connection used to create the
* PreparedStatement
* @param sql The SQL statement to prepare.
* @param returnedKeys Flag indicating whether to return generated keys or not.
* @return An initialized PreparedStatement.
*/
private PreparedStatement prepareStatement(Connection conn, String sql, int returnedKeys) {
try {
return conn.prepareStatement(sql, returnedKeys);
} catch (SQLException e) {
throw new UncheckedSQLException(e);
}
}
/**
* 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.
*/
int[] batch(Connection conn, boolean closeConn, String sql, Object[][] params) throws SQLException {
Objects.requireNonNull(conn);
Objects.requireNonNull(sql);
Objects.requireNonNull(params);
int[] rows = null;
try (PreparedStatement stmt = this.prepareStatement(conn, sql)) {
for (Object[] param : params) {
this.fillStatement(stmt, param);
stmt.addBatch();
}
rows = stmt.executeBatch();
} finally {
if (closeConn) {
Utils.close(conn);
}
}
return rows;
}
/**
* Fill the PreparedStatement replacement parameters with the given objects.
*
* @param stmt PreparedStatement to fill
* @param params Query replacement parameters; null is a valid value to pass in.
*/
public void fillStatement(PreparedStatement stmt, Object... params) {
// check the parameter count, if we can
for (int i = 0; i < params.length; i++) {
try {
if (params[i] != null) {
stmt.setObject(i + 1, params[i]);
} else {
// VARCHAR works with many drivers regardless
// of the actual column type. Oddly, NULL and
// OTHER don't work with Oracle's drivers.
int sqlType = Types.VARCHAR;
stmt.setNull(i + 1, sqlType);
}
} catch (SQLException e) {
throw new UncheckedSQLException(e);
}
}
}
/**
* Fill the PreparedStatement replacement parameters with the
* given object's bean property values.
*
* @param stmt PreparedStatement to fill
* @param bean a JavaBean object
* @param properties an ordered array of properties; this gives the order to insert
* values in the statement
*/
public void fillStatementWithBean(PreparedStatement stmt, Object bean, PropertyDescriptor[] properties) {
Object[] params = new Object[properties.length];
for (int i = 0; i < properties.length; i++) {
PropertyDescriptor property = properties[i];
Method method = property.getReadMethod();
if (method == null) {
throw new ReflectionException("No read method for bean property " + bean.getClass() + " "
+ property.getName());
}
try {
params[i] = method.invoke(bean);
} catch (IllegalAccessException | InvocationTargetException e) {
throw new ReflectionException(e);
}
}
fillStatement(stmt, params);
}
/**
* Fill the PreparedStatement replacement parameters with the
* given object's bean property values.
*
* @param stmt PreparedStatement to fill
* @param bean A JavaBean object
* @param propertyNames An ordered array of property names (these should match the
* getters/setters); this gives the order to insert values in the
* statement
*/
public void fillStatementWithBean(PreparedStatement stmt, Object bean, String... propertyNames) {
PropertyDescriptor[] descriptors;
try {
descriptors = Introspector.getBeanInfo(bean.getClass()).getPropertyDescriptors();
} catch (IntrospectionException e) {
throw new ReflectionException(e);
}
PropertyDescriptor[] sorted = new PropertyDescriptor[propertyNames.length];
for (int i = 0; i < propertyNames.length; i++) {
String propertyName = Objects.requireNonNull(propertyNames[i], "propertyName can't be null: " + i);
boolean found = false;
for (PropertyDescriptor descriptor : descriptors) {
if (propertyName.equals(descriptor.getName())) {
sorted[i] = descriptor;
found = true;
break;
}
}
if (!found) {
throw new ReflectionException("Couldn't find bean property: " + bean.getClass() + " " + propertyName);
}
}
fillStatementWithBean(stmt, bean, sorted);
}
/**
* 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.
*/
T query(Connection conn, boolean closeConn, String sql, ResultSetHandler rsh, Object... params)
throws SQLException {
Objects.requireNonNull(conn);
Objects.requireNonNull(sql);
Objects.requireNonNull(rsh);
Objects.requireNonNull(params);
try (PreparedStatement stmt = this.prepareStatement(conn, sql)) {
this.fillStatement(stmt, params);
try (ResultSet rs = stmt.executeQuery()) {
return rsh.handle(rs);
}
} finally {
if (closeConn) {
Utils.close(conn);
}
}
}
/**
* 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.
*/
int update(Connection conn, boolean closeConn, String sql, Object... params) throws SQLException {
Objects.requireNonNull(conn);
Objects.requireNonNull(sql);
Objects.requireNonNull(params);
int rows = 0;
try (PreparedStatement stmt = this.prepareStatement(conn, sql)) {
this.fillStatement(stmt, params);
rows = stmt.executeUpdate();
} finally {
if (closeConn) {
Utils.close(conn);
}
}
return rows;
}
/**
* 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 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.
*/
T insert(Connection conn, boolean closeConn, String sql, ResultSetHandler rsh, Object... params)
throws SQLException {
Objects.requireNonNull(conn);
Objects.requireNonNull(sql);
Objects.requireNonNull(rsh);
Objects.requireNonNull(params);
try (PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
this.fillStatement(stmt, params);
stmt.executeUpdate();
ResultSet resultSet = stmt.getGeneratedKeys();
return rsh.handle(resultSet);
} finally {
if (closeConn) {
Utils.close(conn);
}
}
}
/**
* 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 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.
*/
T insertBatch(Connection conn, boolean closeConn, String sql, ResultSetHandler rsh,
Object[][] params)
throws SQLException {
Objects.requireNonNull(conn);
Objects.requireNonNull(sql);
Objects.requireNonNull(rsh);
Objects.requireNonNull(params);
try (PreparedStatement stmt = this.prepareStatement(conn, sql, Statement.RETURN_GENERATED_KEYS)) {
for (Object[] param : params) {
this.fillStatement(stmt, param);
stmt.addBatch();
}
stmt.executeBatch();
ResultSet rs = stmt.getGeneratedKeys();
return rsh.handle(rs);
} finally {
if (closeConn) {
Utils.close(conn);
}
}
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy