com.arakelian.jdbc.utils.DatabaseUtils Maven / Gradle / Ivy
The newest version!
/*
* 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 com.arakelian.jdbc.utils;
import java.io.Closeable;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;
import java.util.NoSuchElementException;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.TimeUnit;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.arakelian.jdbc.conn.ConnectionFactory;
import com.arakelian.jdbc.handler.ResultSetHandler;
import com.arakelian.jdbc.handler.ScalarHandler;
import com.arakelian.jdbc.model.Field;
import com.arakelian.jdbc.model.ImmutableField;
import com.arakelian.jdbc.model.ImmutableIndexField;
import com.arakelian.jdbc.model.Index;
import com.arakelian.jdbc.model.IndexField;
import com.arakelian.store.StoreException;
import com.github.rholder.retry.RetryException;
import com.github.rholder.retry.Retryer;
import com.github.rholder.retry.RetryerBuilder;
import com.github.rholder.retry.StopStrategies;
import com.github.rholder.retry.WaitStrategies;
import com.google.common.base.Preconditions;
public class DatabaseUtils {
public static class JdbcIterator implements Iterator, Closeable {
private final ConnectionFactory connectionFactory;
private final String sql;
private final Object[] params;
private final ResultSetHandler rowHandler;
private Connection conn;
private PreparedStatement stmt;
private ResultSet rs;
private ResultSetMetaData rsmd;
private boolean closed;
private T lookahead;
private JdbcIterator(
final ConnectionFactory connectionFactory,
final Connection conn,
final String sql,
final Object[] params,
final ResultSetHandler rowHandler) {
Preconditions.checkArgument(
connectionFactory != null || conn != null,
"connectionFactory or conn must be non-null");
Preconditions.checkArgument(!StringUtils.isEmpty(sql), "sql must be non-empty");
Preconditions.checkArgument(rowHandler != null, "rowHandler must be non-null");
this.connectionFactory = connectionFactory;
this.conn = conn;
this.sql = sql;
this.params = params;
this.rowHandler = rowHandler;
}
@Override
public void close() {
if (closed) {
return;
}
try {
closeQuietly(rs);
closeQuietly(stmt);
closeQuietly(conn);
} finally {
rs = null;
stmt = null;
conn = null;
closed = true;
}
}
@Override
public boolean hasNext() {
return !closed && (lookahead != null || (lookahead = peek()) != null);
}
@Override
public T next() {
if (lookahead != null) {
final T t = lookahead;
lookahead = null;
return t;
}
final T t = peek();
if (closed) {
throw new NoSuchElementException();
}
return t;
}
private T peek() {
if (lookahead != null) {
final T result = lookahead;
lookahead = null;
return result;
}
try {
if (stmt == null && !closed) {
if (conn == null) {
conn = connectionFactory.createConnection();
}
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(DatabaseUtils.toString(null, sql, params));
}
stmt = conn.prepareStatement(sql);
fillStatement(stmt, params);
rs = stmt.executeQuery();
rsmd = rs.getMetaData();
}
final T row = rowHandler.handle(rs, rsmd);
if (rowHandler.wasLast(row)) {
close();
}
return row;
} catch (final SQLException e) {
throw new StoreException(createSqlException(e, sql, params));
}
}
}
/**
* Logger
*/
private static final Logger LOGGER = LoggerFactory.getLogger(DatabaseUtils.class);
/**
* Closes the given connection. This method will quietly log any SQLException that
* may occur but will otherwise ignore it.
*
* @param connection
* connection to close. If a null value is provided, this method has no effect.
*/
public static void closeQuietly(final Connection connection) {
try {
if (connection != null) {
connection.close();
}
} catch (final Throwable ignorable) {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(ignorable.getMessage(), ignorable);
}
}
}
/**
* Closes the given ResultSet. This method will quietly log any
* SQLException that may occur but will otherwise ignore it.
*
* Note: According to JDBC specification, this has the side-effect of closing the associated
* statement, unless the connection property "retainStatementAfterResultSetClose" is set to true
* (default is false).
*
* @param rs
* ResultSet to close. If a null value is provided, this method has no
* effect.
*/
public static void closeQuietly(final ResultSet rs) {
try {
if (rs != null) {
rs.close();
}
} catch (final Throwable ignorable) {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(ignorable.getMessage(), ignorable);
}
}
}
/**
* Closes the given statement. This method will quietly log any SQLException that
* may occur but will otherwise ignore it.
*
* @param statement
* statement to close. If a null value is provided, this method has no effect.
*/
public static void closeQuietly(final Statement statement) {
try {
if (statement instanceof PreparedStatement) {
((PreparedStatement) statement).clearParameters();
}
if (statement != null) {
statement.close();
}
} catch (final Throwable ignorable) {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(ignorable.getMessage(), ignorable);
}
}
}
/**
* Execute a batch INSERT, UPDATE, or DELETE query. The caller is responsible for closing the
* connection.
*
* @param conn
* The connection to the database
* @param sql
* The SQL to execute.
* @param batchParams
* The query replacement parameters.
* @return The number of rows updated.
* @throws SQLException
* if a database error occurs
*/
public static int[] executeBatchUpdate(
final Connection conn,
final String sql,
final Iterator batchParams) throws SQLException {
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
while (batchParams != null && batchParams.hasNext()) {
final Object[] params = batchParams.next();
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(toString(null, sql, params));
}
fillStatement(stmt, params);
stmt.addBatch();
}
final int[] rowsAffected = stmt.executeBatch();
LOGGER.debug("Rows affected: {}", rowsAffected);
return rowsAffected;
} catch (final SQLException e) {
throw createSqlException(e, sql);
}
}
/**
* Execute a batch INSERT, UPDATE, or DELETE query.
*
* @param connectionFactory
* The connection factory used to create the connection
* @param sql
* The SQL to execute.
* @param batchParams
* batch parameters
* @return The number of rows affected for each batch.
* @throws SQLException
* if a database error occurs
*/
public static int[] executeBatchUpdate(
final ConnectionFactory connectionFactory,
final String sql,
final Iterator batchParams) throws SQLException {
try (Connection conn = connectionFactory.createConnection()) {
final int[] rowsAffected = executeBatchUpdate(conn, sql, batchParams);
return rowsAffected;
} catch (final SQLException e) {
throw createSqlException(e, sql);
}
}
/**
* Execute an SQL SELECT query without any replacement parameters. The caller is responsible for
* closing the connection.
*
* @param
* The type of object that the handler returns
* @param conn
* The connection to the database.
* @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 error occurs
*/
public static T executeQuery(final Connection conn, final String sql, final ResultSetHandler rsh)
throws SQLException {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(toString(null, sql));
}
try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql)) {
final ResultSetMetaData rsmd = rs.getMetaData();
return rsh.handle(rs, rsmd);
} catch (final SQLException e) {
throw createSqlException(e, sql, (Object[]) null);
}
}
/**
* Execute an SQL SELECT query with replacement parameters.
*
* @param
* The type of object that the handler returns
* @param conn
* The connection to the database.
* @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 error occurs
*/
public static T executeQuery(
final Connection conn,
final String sql,
final ResultSetHandler rsh,
final Object... params) throws SQLException {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(toString(null, sql, params));
}
try (PreparedStatement stmt = conn.prepareStatement(sql);
ResultSet rs = fillStatement(stmt, params).executeQuery()) {
return rsh.handle(rs, rs.getMetaData());
} catch (final SQLException e) {
throw createSqlException(e, sql, params);
}
}
/**
* Execute an SQL SELECT query without any replacement parameters.
*
* @param
* The type of object that the handler returns
* @param connectionFactory
* The connection factory used to create the connection
* @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 error occurs
*/
public static T executeQuery(
final ConnectionFactory connectionFactory,
final String sql,
final ResultSetHandler rsh) throws SQLException {
try (Connection conn = connectionFactory.createConnection()) {
return executeQuery(conn, sql, rsh);
} catch (final SQLException e) {
throw createSqlException(e, sql, (Object[]) null);
}
}
/**
* Execute an SQL SELECT query with replacement parameters.The caller is responsible for closing
* the connection.
*
* @param
* The type of object that the handler returns
* @param connectionFactory
* The connection factory used to create the connection
* @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 error occurs
*/
public static T executeQuery(
final ConnectionFactory connectionFactory,
final String sql,
final ResultSetHandler rsh,
final Object... params) throws SQLException {
try (Connection conn = connectionFactory.createConnection()) {
return executeQuery(conn, sql, rsh, params);
} catch (final SQLException e) {
throw createSqlException(e, sql, params);
}
}
/**
* Execute an SQL INSERT, UPDATE, or DELETE query without replacement parameters. The caller is
* responsible for closing the connection.
*
* @param conn
* The connection to the database.
* @param sql
* The SQL to execute.
* @return The number of rows updated.
* @throws SQLException
* if a database error occurs
*/
public static int executeUpdate(final Connection conn, final String sql) throws SQLException {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(toString(null, sql));
}
try (Statement stmt = conn.createStatement()) {
final int rowsAffected = stmt.executeUpdate(sql);
LOGGER.debug("Rows affected: {}", rowsAffected);
return rowsAffected;
} catch (final SQLException e) {
throw createSqlException(e, sql, (Object[]) null);
}
}
/**
* Execute an SQL INSERT, UPDATE, or DELETE query. The caller is responsible for closing the
* connection.
*
* @param conn
* The connection to the database
* @param sql
* The SQL to execute.
* @param params
* The query replacement parameters.
* @return The number of rows updated.
* @throws SQLException
* if a database error occurs
*/
public static int executeUpdate(final Connection conn, final String sql, final Object... params)
throws SQLException {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(toString(null, sql, params));
}
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
fillStatement(stmt, params);
return stmt.executeUpdate();
} catch (final SQLException e) {
throw createSqlException(e, sql, params);
}
}
/**
* Execute an SQL INSERT, UPDATE, or DELETE query without replacement parameters.
*
* @param connectionFactory
* The connection factory used to create the connection
* @param sql
* The SQL to execute.
* @return The number of rows updated.
* @throws SQLException
* if a database error occurs
*/
public static int executeUpdate(final ConnectionFactory connectionFactory, final String sql)
throws SQLException {
try (Connection conn = connectionFactory.createConnection()) {
final int rowsAffected = executeUpdate(conn, sql);
return rowsAffected;
} catch (final SQLException e) {
throw createSqlException(e, sql, (Object[]) null);
}
}
/**
* Execute an SQL INSERT, UPDATE, or DELETE query.
*
* @param connectionFactory
* The connection factory used to create the connection
* @param sql
* The SQL to execute.
* @param params
* The query replacement parameters.
* @return The number of rows updated.
* @throws SQLException
* if a database error occurs
*/
public static int executeUpdate(
final ConnectionFactory connectionFactory,
final String sql,
final Object... params) throws SQLException {
try (Connection conn = connectionFactory.createConnection()) {
final int rowsAffected = executeUpdate(conn, sql, params);
return rowsAffected;
} catch (final SQLException e) {
throw createSqlException(e, sql, params);
}
}
/**
* 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.
* @return a PreparedStatement with parameters set to the given objects.
* @throws SQLException
* if a database error occurs
*/
public static PreparedStatement fillStatement(final PreparedStatement stmt, final Object... params)
throws SQLException {
if (params == null) {
return stmt;
}
ParameterMetaData pmd = null;
boolean pmdBroken = false;
boolean tooManyParams = false;
try {
pmd = stmt.getParameterMetaData();
tooManyParams = pmd.getParameterCount() < params.length;
} catch (final SQLException e) {
pmdBroken = true;
}
if (tooManyParams) {
throw new SQLException("Too many parameters: expected " + pmd.getParameterCount() + ", was given "
+ params.length);
}
for (int i = 0; i < params.length; i++) {
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.
int sqlType = Types.VARCHAR;
if (!pmdBroken) {
try {
sqlType = pmd.getParameterType(i + 1);
} catch (final SQLException e) {
pmdBroken = true;
}
}
stmt.setNull(i + 1, sqlType);
}
}
return stmt;
}
public static String[] getColumnNames(final ResultSetMetaData rsmd) throws SQLException {
final int columns = rsmd.getColumnCount();
final String[] columnNames = new String[columns];
for (int i = 0; i < columns; i++) {
columnNames[i] = rsmd.getColumnLabel(i + 1); // 1-based!
}
return columnNames;
}
public static List getFields(
final ConnectionFactory connectionFactory,
final String schema,
final String tableName) throws SQLException {
try (Connection conn = connectionFactory.createConnection()) {
return getFields(conn.getMetaData(), schema, tableName);
}
}
public static List getFields(
final DatabaseMetaData dbmd,
final String schema,
final String tableName) throws SQLException {
try (ResultSet rs = dbmd.getColumns(null, schema, tableName, null)) {
// build list of indexes and their fields
final List fields = new ArrayList<>();
while (rs.next()) {
final Field field = ImmutableField.builder() //
.name(rs.getString(4)) //
.dataType(rs.getInt(5)) //
.typeName(rs.getString(6)) //
.columnSize(rs.getInt(7)) //
.decimalDigits(rs.getInt(9)) //
.radix(rs.getInt(10)) //
.nullable(rs.getInt(11)) //
.comments(rs.getString(12)) //
.ordinalPosition(rs.getInt(17)) //
.build();
fields.add(field);
}
return fields;
}
}
/**
* Return database metadata associated with given connection.
*
* @param connectionFactory
* connection factory
* @param schema
* schema name
* @param tableName
* table name
* @param unique
* when true, return only indices for unique values; when false, return indices
* regardless of whether unique or not
* @param approximate
* when true, result is allowed to reflect approximate or out of data values; when
* false, results are requested to be accurate
* @return database metadata associated with given connection.
* @throws SQLException
* if a database error occurs
*/
public static List getIndexes(
final ConnectionFactory connectionFactory,
final String schema,
final String tableName,
final boolean unique,
final boolean approximate) throws SQLException {
try (Connection conn = connectionFactory.createConnection()) {
return getIndexes(conn.getMetaData(), schema, tableName, unique, approximate);
}
}
/**
* Returns a list of indexes and their associated fields.
*
* @param dbmd
* DatabaseMetaData
* @param schema
* a schema name; must match the schema name as it is stored in this database; ""
* retrieves those without a schema; null means that the schema name
* should not be used to narrow the search
* @param tableName
* a table name; must match the table name as it is stored in this database
* @param unique
* when true, return only indices for unique values; when false, return indices
* regardless of whether unique or not
* @param approximate
* when true, result is allowed to reflect approximate or out of data values; when
* false, results are requested to be accurate
*
* @return a list of indexes and their associated files.
* @throws SQLException
* if a database error occurs
*/
public static List getIndexes(
final DatabaseMetaData dbmd,
final String schema,
final String tableName,
final boolean unique,
final boolean approximate) throws SQLException {
// build list of indexes and their fields
try (ResultSet rs = dbmd.getIndexInfo(null, schema, tableName, unique, approximate)) {
List indexes = null;
List indexFields = null;
String lastIndexName = null;
while (rs.next()) {
final String indexName = rs.getString(6);
if (StringUtils.isEmpty(indexName)) {
// ignore table statistics
continue;
}
if (!StringUtils.equals(indexName, lastIndexName)) {
indexFields = new ArrayList<>();
if (indexes == null) {
indexes = new ArrayList<>();
}
indexes.add(new Index(indexName, indexFields));
}
lastIndexName = indexName;
final int sequence = rs.getInt(8);
final String columnName = rs.getString(9);
final boolean ascending = "A"
.equalsIgnoreCase(StringUtils.defaultString(rs.getString(10), "A"));
indexFields.add(
ImmutableIndexField.builder() //
.columnName(columnName) //
.sequence(sequence) //
.isAscending(ascending) //
.build());
}
if (indexes != null) {
for (final Index index : indexes) {
index.sortBySequence();
}
}
return indexes != null ? indexes : Collections. emptyList();
}
}
/**
* Returns the primary key for the given table.
*
* WARNING: Note that some JDBC drivers, such as the Oracle JDBC driver, are case-sensitive with
* schema and table names.
*
* @param dbmd
* database metadata
* @param schema
* schema, or null
* @param tableName
* table name
* @return the primary key for the given table
* @throws SQLException
* if a database error occurs
*/
public static Index getPrimaryKey(
final DatabaseMetaData dbmd,
final String schema,
final String tableName) throws SQLException {
try (ResultSet rs = dbmd.getPrimaryKeys(null, schema, tableName)) {
// build list of primary keys; note: according to JDBC, the
// primary key fields are returned ordered by column name.
List indexFields = null;
while (rs.next()) {
final String columnName = rs.getString(4);
final int keySequence = rs.getInt(5);
if (indexFields == null) {
indexFields = new ArrayList<>();
}
indexFields.add(
ImmutableIndexField.builder() //
.columnName(columnName) //
.sequence(keySequence) //
.isAscending(true) //
.build());
}
if (indexFields != null) {
final Index index = new Index(null, indexFields);
index.sortBySequence();
return index;
} else {
return null;
}
}
}
public static JdbcIterator iterator(
final Connection conn,
final String sql,
final ResultSetHandler rsh) {
return new JdbcIterator<>(null, conn, sql, null, rsh);
}
public static JdbcIterator iterator(
final Connection conn,
final String sql,
final ResultSetHandler rsh,
final Object... params) {
return new JdbcIterator<>(null, conn, sql, params, rsh);
}
public static JdbcIterator iterator(
final ConnectionFactory connectionFactory,
final String sql,
final ResultSetHandler rsh) {
return new JdbcIterator<>(connectionFactory, null, sql, null, rsh);
}
public static JdbcIterator iterator(
final ConnectionFactory connectionFactory,
final String sql,
final ResultSetHandler rsh,
final Object... params) {
return new JdbcIterator<>(connectionFactory, null, sql, params, rsh);
}
/**
* Returns a SQL string literal from given value.
*
* The return value will begin and end with single quotes, with the given value in between. If
* there are any single quote characters within the provided value, they are "escaped" by
* replacing each of them with two single quote characters.
*
* @param value
* string value
* @return SQL string literal from given value.
*/
public static String quotedStringLiteral(final String value) {
if (value != null && (!value.startsWith("'") || !value.endsWith("'"))) {
final StringBuilder buf = new StringBuilder();
buf.append('\'');
int start = 0;
final int length = value.length();
for (; start < length;) {
final int quote = value.indexOf('\'', start);
if (quote == -1) {
buf.append(value, start, length);
break;
} else {
buf.append(value, start, quote);
// escape single quote by inserting two single quotes
buf.append("\'\'");
start = quote + 1;
}
}
buf.append('\'');
return buf.toString();
}
return value;
}
public static boolean waitForDatabaseReady(
final ConnectionFactory connectionFactory,
final String validationQuery,
final long timeout,
final TimeUnit unit) {
final Retryer retryer = RetryerBuilder. newBuilder() //
.retryIfException() //
.withStopStrategy(StopStrategies.stopAfterDelay(timeout, unit)) //
.withWaitStrategy(WaitStrategies.fixedWait(5, TimeUnit.SECONDS)) //
.build();
// wait for elastic
try {
retryer.call(() -> {
DatabaseUtils
.executeQuery(connectionFactory, validationQuery, new ScalarHandler<>(Integer.class))
.intValue();
return null;
});
return true;
} catch (final ExecutionException e) {
LOGGER.warn("Unable to retrieve Elastic information after {} {}", timeout, unit, e);
return false;
} catch (final RetryException e) {
LOGGER.warn("Unable to retrieve Elastic information after {} {}", timeout, unit, e);
return false;
}
}
/**
* Throws a new exception with a more informative error message.
*
* @param cause
* The original exception that will be chained to the new exception when it's
* rethrown.
*
* @param sql
* The query that was executing when the exception happened.
*
* @param params
* The query replacement parameters; null is a valid value to pass in.
*
* @throws SQLException
* if a database error occurs
*/
private static SQLException createSqlException(
final SQLException cause,
final String sql,
final Object... params) {
String causeMessage = cause.getMessage();
if (causeMessage == null) {
causeMessage = "";
} else {
// only wrap query once
if (causeMessage.contains("Query: ")) {
return cause;
}
}
final SQLException e = new SQLException(toString(causeMessage, sql, params), cause.getSQLState(),
cause.getErrorCode());
e.initCause(cause);
return e;
}
private static String toString(final String msg, final String sql, final Object... params) {
final StringBuilder buf = new StringBuilder();
if (StringUtils.isEmpty(msg)) {
buf.append("Sql: ");
} else {
buf.append(msg).append(", Sql: ");
}
buf.append(sql);
buf.append(", Parameters: ");
if (params == null) {
buf.append("[]");
} else {
buf.append(Arrays.deepToString(params));
}
return buf.toString();
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy