/*
* Copyright 2010-2020 Redgate Software Ltd
*
* Licensed 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.flywaydb.core.internal.jdbc;
import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
Collection of utility methods for querying the DB. Inspired by Spring's JdbcTemplate.
/**
* Collection of utility methods for querying the DB. Inspired by Spring's JdbcTemplate.
*/
public class JdbcTemplate {
The DB connection to use.
/**
* The DB connection to use.
*/
private final Connection connection;
The type to assign to a null value.
/**
* The type to assign to a null value.
*/
private final int nullType;
Creates a new JdbcTemplate.
Params: - connection – The database connection to use.
/**
* Creates a new JdbcTemplate.
*
* @param connection The database connection to use.
*/
public JdbcTemplate(Connection connection) {
this(connection, DatabaseType.fromJdbcConnection(connection));
}
Creates a new JdbcTemplate.
Params: - connection – The database connection to use.
/**
* Creates a new JdbcTemplate.
*
* @param connection The database connection to use.
*/
public JdbcTemplate(Connection connection, DatabaseType databaseType) {
this.connection = connection;
this.nullType = databaseType.getNullType();
}
Returns: The DB connection to use.
/**
* @return The DB connection to use.
*/
public Connection getConnection() {
return connection;
}
Executes this query with these parameters against this connection.
Params: - query – The query to execute.
- params – The query parameters.
Throws: - SQLException – when the query execution failed.
Returns: The query results.
/**
* Executes this query with these parameters against this connection.
*
* @param query The query to execute.
* @param params The query parameters.
* @return The query results.
* @throws SQLException when the query execution failed.
*/
public List<Map<String, String>> queryForList(String query, Object... params) throws SQLException {
PreparedStatement statement = null;
ResultSet resultSet = null;
List<Map<String, String>> result;
try {
statement = prepareStatement(query, params);
resultSet = statement.executeQuery();
result = new ArrayList<>();
while (resultSet.next()) {
Map<String, String> rowMap = new LinkedHashMap<>();
for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
rowMap.put(resultSet.getMetaData().getColumnLabel(i), resultSet.getString(i));
}
result.add(rowMap);
}
} finally {
JdbcUtils.closeResultSet(resultSet);
JdbcUtils.closeStatement(statement);
}
return result;
}
Executes this query with these parameters against this connection.
Params: - query – The query to execute.
- params – The query parameters.
Throws: - SQLException – when the query execution failed.
Returns: The query results as a list of strings.
/**
* Executes this query with these parameters against this connection.
*
* @param query The query to execute.
* @param params The query parameters.
* @return The query results as a list of strings.
* @throws SQLException when the query execution failed.
*/
public List<String> queryForStringList(String query, String... params) throws SQLException {
PreparedStatement statement = null;
ResultSet resultSet = null;
List<String> result;
try {
statement = prepareStatement(query, params);
resultSet = statement.executeQuery();
result = new ArrayList<>();
while (resultSet.next()) {
result.add(resultSet.getString(1));
}
} finally {
JdbcUtils.closeResultSet(resultSet);
JdbcUtils.closeStatement(statement);
}
return result;
}
Executes this query with these parameters against this connection.
Params: - query – The query to execute.
- params – The query parameters.
Throws: - SQLException – when the query execution failed.
Returns: The query result.
/**
* Executes this query with these parameters against this connection.
*
* @param query The query to execute.
* @param params The query parameters.
* @return The query result.
* @throws SQLException when the query execution failed.
*/
public int queryForInt(String query, String... params) throws SQLException {
PreparedStatement statement = null;
ResultSet resultSet = null;
int result;
try {
statement = prepareStatement(query, params);
resultSet = statement.executeQuery();
resultSet.next();
result = resultSet.getInt(1);
} finally {
JdbcUtils.closeResultSet(resultSet);
JdbcUtils.closeStatement(statement);
}
return result;
}
Executes this query with these parameters against this connection.
Params: - query – The query to execute.
- params – The query parameters.
Throws: - SQLException – when the query execution failed.
Returns: The query result.
/**
* Executes this query with these parameters against this connection.
*
* @param query The query to execute.
* @param params The query parameters.
* @return The query result.
* @throws SQLException when the query execution failed.
*/
public boolean queryForBoolean(String query, String... params) throws SQLException {
PreparedStatement statement = null;
ResultSet resultSet = null;
boolean result;
try {
statement = prepareStatement(query, params);
resultSet = statement.executeQuery();
resultSet.next();
result = resultSet.getBoolean(1);
} finally {
JdbcUtils.closeResultSet(resultSet);
JdbcUtils.closeStatement(statement);
}
return result;
}
Executes this query with these parameters against this connection.
Params: - query – The query to execute.
- params – The query parameters.
Throws: - SQLException – when the query execution failed.
Returns: The query result.
/**
* Executes this query with these parameters against this connection.
*
* @param query The query to execute.
* @param params The query parameters.
* @return The query result.
* @throws SQLException when the query execution failed.
*/
public String queryForString(String query, String... params) throws SQLException {
PreparedStatement statement = null;
ResultSet resultSet = null;
String result;
try {
statement = prepareStatement(query, params);
resultSet = statement.executeQuery();
result = null;
if (resultSet.next()) {
result = resultSet.getString(1);
}
} finally {
JdbcUtils.closeResultSet(resultSet);
JdbcUtils.closeStatement(statement);
}
return result;
}
Executes this sql statement using a PreparedStatement.
Params: - sql – The statement to execute.
- params – The statement parameters.
Throws: - SQLException – when the execution failed.
/**
* Executes this sql statement using a PreparedStatement.
*
* @param sql The statement to execute.
* @param params The statement parameters.
* @throws SQLException when the execution failed.
*/
public void execute(String sql, Object... params) throws SQLException {
PreparedStatement statement = null;
try {
statement = prepareStatement(sql, params);
statement.execute();
} finally {
JdbcUtils.closeStatement(statement);
}
}
Executes this sql statement using an ordinary Statement.
Params: - sql – The statement to execute.
Returns: the results of the execution.
/**
* Executes this sql statement using an ordinary Statement.
*
* @param sql The statement to execute.
* @return the results of the execution.
*/
public Results executeStatement(String sql) {
Results results = new Results();
Statement statement = null;
try {
statement = connection.createStatement();
statement.setEscapeProcessing(false);
boolean hasResults;
try {
hasResults = statement.execute(sql);
} finally {
extractWarnings(results, statement);
}
extractResults(results, statement, hasResults);
} catch (final SQLException e) {
extractErrors(results, e);
} finally {
JdbcUtils.closeStatement(statement);
}
return results;
}
private void extractWarnings(Results results, Statement statement) throws SQLException {
SQLWarning warning = statement.getWarnings();
while (warning != null) {
int code = warning.getErrorCode();
String state = warning.getSQLState();
String message = warning.getMessage();
if (state == null)
{
state = "";
}
if (message == null)
{
message = "";
}
results.addWarning(new WarningImpl(code, state, message));
warning = warning.getNextWarning();
}
}
public void extractErrors(Results results, SQLException e) {
results.setException(e);
}
private void extractResults(Results results, Statement statement, boolean hasResults) throws SQLException {
// retrieve all results to ensure all errors are detected
int updateCount = -1;
while (hasResults || (updateCount = statement.getUpdateCount()) != -1) {
List<String> columns = null;
List<List<String>> data = null;
if (hasResults) {
try (ResultSet resultSet = statement.getResultSet()) {
columns = new ArrayList<>();
ResultSetMetaData metadata = resultSet.getMetaData();
int columnCount = metadata.getColumnCount();
for (int i = 1; i <= columnCount; i++) {
columns.add(metadata.getColumnName(i));
}
data = new ArrayList<>();
while (resultSet.next()) {
List<String> row = new ArrayList<>();
for (int i = 1; i <= columnCount; i++) {
row.add(resultSet.getString(i));
}
data.add(row);
}
}
}
results.addResult(new Result(updateCount, columns, data));
hasResults = statement.getMoreResults();
}
}
Executes this update sql statement.
Params: - sql – The statement to execute.
- params – The statement parameters.
Throws: - SQLException – when the execution failed.
/**
* Executes this update sql statement.
*
* @param sql The statement to execute.
* @param params The statement parameters.
* @throws SQLException when the execution failed.
*/
public void update(String sql, Object... params) throws SQLException {
PreparedStatement statement = null;
try {
statement = prepareStatement(sql, params);
statement.executeUpdate();
} finally {
JdbcUtils.closeStatement(statement);
}
}
Creates a new prepared statement for this sql with these params.
Params: - sql – The sql to execute.
- params – The params.
Throws: - SQLException – when the statement could not be prepared.
Returns: The new prepared statement.
/**
* Creates a new prepared statement for this sql with these params.
*
* @param sql The sql to execute.
* @param params The params.
* @return The new prepared statement.
* @throws SQLException when the statement could not be prepared.
*/
private PreparedStatement prepareStatement(String sql, Object[] params) throws SQLException {
PreparedStatement statement = connection.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
if (params[i] == null) {
statement.setNull(i + 1, nullType);
} else if (params[i] instanceof Integer) {
statement.setInt(i + 1, (Integer) params[i]);
} else if (params[i] instanceof Boolean) {
statement.setBoolean(i + 1, (Boolean) params[i]);
} else {
statement.setString(i + 1, params[i].toString());
}
}
return statement;
}
Executes this query and map the results using this row mapper.
Params: - sql – The query to execute.
- rowMapper – The row mapper to use.
Type parameters: - <T> – The type of the result objects.
Throws: - SQLException – when the query failed to execute.
Returns: The list of results.
/**
* Executes this query and map the results using this row mapper.
*
* @param sql The query to execute.
* @param rowMapper The row mapper to use.
* @param <T> The type of the result objects.
* @return The list of results.
* @throws SQLException when the query failed to execute.
*/
public <T> List<T> query(String sql, RowMapper<T> rowMapper, Object... params) throws SQLException {
PreparedStatement statement = null;
ResultSet resultSet = null;
List<T> results;
try {
statement = prepareStatement(sql, params);
resultSet = statement.executeQuery();
results = new ArrayList<>();
while (resultSet.next()) {
results.add(rowMapper.mapRow(resultSet));
}
} finally {
JdbcUtils.closeResultSet(resultSet);
JdbcUtils.closeStatement(statement);
}
return results;
}
}