com.github.hypfvieh.db.SimpleDatabaseConnector Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of java-utils Show documentation
Show all versions of java-utils Show documentation
A collection of utils commonly used in my projects.
Feel free to use it (or parts of it) in your own projects.
The newest version!
package com.github.hypfvieh.db;
import java.io.InvalidClassException;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLRecoverableException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import java.util.concurrent.atomic.AtomicInteger;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.github.hypfvieh.util.StringUtil;
import com.github.hypfvieh.util.TypeUtil;
/**
* Simple class to allow connection to any JDBC compatible database.
* Allows to send any SQL statement to the database as well as retrieving data (SELECT) from a database in a List-of-Map format.
*
* All columns selected will be accessible by iterating through the list of maps.
* Each item of the list represents one row as HashMap where each key is one column.
* Column names are case insensitive!
*
* It allows caching of retrieved DB results in JSON format. If caching is enabled it will automatically use the offline cache if
* database is unreachable.
*
* One instance of {@link SimpleDatabaseConnector} can store multiple select queries in offline cache (map of SQL-Query(key) and Result (value)).
*
* Sample Usage:
*
*
* {@code
IDatabaseConnector sdc = newSqlConnectorInstance(getDatabaseConnectionParameters("EnxMasterdata"), true); // use false to disable cache
if (sdc.openDatabase() || sdc.isOffline()) { // if cache is disabled you should stop if isOffline() == true
List<Map<String, String>> selectedRows = sdc.executeSelectQuery(true, sqlGattungString);
for (Map<String, String> tableEntry : selectedRows) {
isin2enx.put(tableEntry.get("isin"), tableEntry.get("enx_code"));
}
}
}
*
*
* @author hypfvieh
* @since 1.0.1
*/
public class SimpleDatabaseConnector {
private final Logger logger;
private final AtomicInteger connectionRetries;
private boolean dbOpen = false;
private boolean supportsBatch = false;
private DbConnParms connectionParams = null;
private Connection dbConnection = null;
public SimpleDatabaseConnector(DbConnParms _connectionParams) {
if (_connectionParams == null) {
throw new IllegalArgumentException("Database connection parameters cannot be null.");
}
logger = LoggerFactory.getLogger(getClass());
dbOpen = false;
connectionParams = _connectionParams;
connectionRetries = new AtomicInteger(0);
}
/**
* Trys to open a connection to the given database parameters.
* Returns true if connection could be established, false otherwise.
*
* Throws InvalidClassException if given SQL-Driver is not JDBC compatible.
* Throws ClassNotFoundException if given SQL-Driver class could not be found.
* Throws SQLException on any connection error.
*
* @return true if connected, false otherwise
* @throws InvalidClassException if class is not a java.sql.Driver derivative
* @throws ClassNotFoundException if class could not be found
*/
public final synchronized boolean openDatabase() throws InvalidClassException, ClassNotFoundException {
if (dbOpen) {
logger.warn("Connection to database already opened.");
return dbOpen;
}
Class driverClazz;
try {
driverClazz = Class.forName(connectionParams.getDriverClassName());
Object driverInstance = driverClazz.getDeclaredConstructor().newInstance();
if (!(driverInstance instanceof Driver)) {
logger.error("{} does not implement java.sql.Driver interface!", connectionParams.getDriverClassName());
throw new InvalidClassException(connectionParams.getDriverClassName() + " does not implement java.sql.Driver interface!");
}
} catch (InstantiationException | IllegalAccessException | IllegalArgumentException
| InvocationTargetException | NoSuchMethodException | SecurityException _ex) {
logger.error("Cannot instanciate database driver: " + connectionParams.getDriverClassName(), _ex);
}
// open the connection or switch to offline mode if connection fails
openConnection();
if (dbOpen && dbConnection != null) {
// if database could be opened, check if we can use batch processing, as it is much faster
try {
if (dbConnection.getMetaData().supportsBatchUpdates()) {
supportsBatch = true;
}
} catch (SQLException _ex) {
logger.info("Could not determine if database supports batch update feature.", _ex);
}
}
return dbOpen;
}
/**
* This method opens the DB connection.
* If this fails and file caching is allowed, {@link #offline} mode will be enabled and null is returned.
* @return Connection or null
*/
private void openConnection() {
try {
dbConnection = DriverManager.getConnection(connectionParams.getUrl(), connectionParams.getUser(), connectionParams.getPassword());
dbOpen = true;
logger.debug("Connection to database at: {} established", connectionParams.getUrl());
} catch (SQLRecoverableException _ex) { // is thrown if connection could not be established, so we may retry connection
if (connectionRetries.incrementAndGet() > connectionParams.getMaxRetries()) {
logger.error("Connection could not be established within {} attempts, url={}", connectionParams.getMaxRetries(), connectionParams.getUrl());
} else {
logger.error("Connection could not be established. Reconnection attempt #{} of {}, url={}, exception={}", connectionRetries.get(), connectionParams.getMaxRetries(), connectionParams.getUrl(), _ex.getMessage());
openConnection();
}
} catch (SQLException _ex) {
logger.error("Database at [{}] could not be opened and offline cache was disabled.", connectionParams.getUrl());
// if debug logging is enabled, print exception as well (may help analyzing issues)
logger.debug("Exception was: ", _ex);
}
}
public Connection getDbConnection() {
return dbConnection;
}
/**
* Closes the database connection if it was previously connected.
* @throws SQLException if closing fails
*/
public synchronized void closeDatabase() throws SQLException {
if (dbConnection != null) {
dbConnection.close();
}
dbOpen = false;
}
/**
* Returns true if database has been opened.
* @return true if open, false otherwise
*/
public synchronized boolean isDbOpen() {
return dbOpen;
}
/**
* Run update/inserts as batch update.
* Will fallback to sequential insert/update if database implemenation does not support batch.
*
* Attention: Do not use batch when calling stored procedures in oracle databases, as this is not supported by oracle and will not work.
*
* @param _sqlQuery sql query to use (with '?' placeholders)
* @param _sqlParameters an array of values for replacing '?' placeholders in query
* @param _batchSize batch size to use
* @return true on successful execution, false if any error occurred
*/
public synchronized boolean executeBatchQuery(String _sqlQuery, List _sqlParameters, int _batchSize) {
if (dbConnection == null) {
logger.error("Database connection for [{}] not established yet", connectionParams);
return false;
}
if (_sqlParameters == null || _sqlParameters.isEmpty()) {
return false;
} else if (isSupportsBatch()) {
logger.debug("About to perform {} updates with batch size {}.", _sqlParameters.size(), _batchSize);
List> splitList = TypeUtil.splitList(_sqlParameters, _batchSize);
boolean hasError = false;
try {
for (List batchPart : splitList) {
try (PreparedStatement stmt = dbConnection.prepareStatement(_sqlQuery)) {
for (Object[] sqlParams : batchPart) {
if (sqlParams != null) {
for (int i = 0; i < sqlParams.length; i++) {
stmt.setObject(i + 1, sqlParams[i]);
}
stmt.addBatch();
}
}
int[] numUpdates = stmt.executeBatch();
for (int i = 0; i < numUpdates.length; i++) {
if (numUpdates[i] == Statement.SUCCESS_NO_INFO) {
logger.trace("Execution of batch {}: successful, but unknown number of rows affected", i);
} else if (numUpdates[i] == Statement.EXECUTE_FAILED) {
logger.error("Execution of batch {}/{} failed, parms: {}", i, numUpdates.length, Arrays.toString(batchPart.get(i)));
hasError = true;
} else {
logger.trace("Execution of batch {} successful.", i);
}
}
}
}
return !hasError;
} catch (SQLException _ex) {
logger.error("Error while processing batch.",_ex);
return false;
}
} else {
logger.warn("Using serial insert/update as database implementation does not support batch update!");
boolean hasError = false;
for (Object[] args : _sqlParameters) {
if (logger.isDebugEnabled()) { // do not do conversion of array to list if logging is disabled
logger.debug("Executing query {} with arguments: {}", _sqlQuery, Arrays.asList(args));
}
if (!executeQuery(_sqlQuery, args)) {
hasError = true;
}
}
return !hasError;
}
}
/**
* Executes an sql query.
*
* @param _sql query to execute
* @param _args arguments to fill-in placeholders in query, can be omitted if none needed
* @return true if SQL-query returns an update-count, false on error or if result is resultset instead of update count.
*/
public synchronized boolean executeQuery(String _sql, Object... _args) {
if (dbConnection == null) {
logger.error("Database connection for [{}] not established yet", connectionParams);
return false;
}
try (PreparedStatement ps = dbConnection.prepareStatement(_sql)) {
if (_args != null) {
for (int i = 0; i < _args.length; i++) {
ps.setObject(i + 1, _args[i]);
}
}
return !ps.execute();
} catch (SQLException _ex) {
logger.error("Failed to execute sql statement: " + _sql, _ex);
}
return false;
}
/**
* Returns the result of an SQL Select-PreparedStatement as list of maps where each key in the map is a column.
*
* @param _sql the sql statement to execute (can use '?' placeholder which will be replaced by the parameters in _args)
* @param _args parameters to replace '?'- placeholder insert _sql (if none, this can be omitted)
* @return list of maps with the result of the query, each list entry is one row of the database
*/
public synchronized List> executeSelectQuery(String _sql, Object... _args) {
if (dbConnection == null) {
logger.error("Database connection for [{}] not established yet", connectionParams);
return null;
}
List> queryResult = new ArrayList<>();
try (PreparedStatement ps = dbConnection.prepareStatement(_sql)) {
if (_args != null) {
for (int i = 0; i < _args.length; i++) {
ps.setObject(i + 1, _args[i]);
}
}
try (ResultSet result = ps.executeQuery()) {
while (result.next()) {
Map map = new TreeMap<>(String.CASE_INSENSITIVE_ORDER);
for (int i = 0; i < result.getMetaData().getColumnCount(); i++) {
String columnName = result.getMetaData().getColumnLabel(i + 1);
map.put(columnName, result.getString(i + 1));
}
queryResult.add(map);
}
}
} catch (SQLException _ex) {
logger.error("Failed to execute sql statement: " + _sql, _ex);
}
logger.debug("Query: '{}' returned {} rows with parms: {}", _sql, queryResult.size(), Arrays.toString(_args));
return queryResult;
}
/**
* Creates a prepared statement which can be used for batch statements.
* @param _sql sql to create prepared statement for
* @return new prepared statement or null on error
*/
public synchronized PreparedStatement createPreparedStatement(String _sql) {
if (dbConnection == null || !isDbOpen()) {
logger.error("Could not create prepared statement: database connection missing for [{}]", connectionParams);
return null;
}
if (StringUtil.isBlank(_sql)) {
logger.error("Could not create prepared statement: statement cannot be empty or null");
return null;
}
PreparedStatement ps;
try {
ps = dbConnection.prepareStatement(_sql);
} catch (SQLException _ex) {
logger.error("Could not create prepared statement: ", _ex);
return null;
}
return ps;
}
/**
* Execute a previously created prepared statement (update or delete).
* @param _ps prepared statement to execute
* @return true if execution successfully, false otherwise
*/
public synchronized boolean executeQuery(PreparedStatement _ps) {
if (dbConnection == null) {
logger.error("Database connection for [{}] not established yet", connectionParams);
return false;
}
if (_ps == null) {
logger.error("Statement should not be null!");
return false;
}
try {
return !_ps.execute();
} catch (SQLException _ex) {
logger.error("Failed to execute sql statement:", _ex);
return false;
}
}
public synchronized boolean isSupportsBatch() {
return supportsBatch;
}
/**
* Enable/Disable autocommit on database connection.
* @param _onOff enable/disable autocommit
*
* @throws SQLException if autocommit option cannot be changed
*/
public void setAutoCommit(boolean _onOff) throws SQLException {
if (dbConnection != null) {
dbConnection.setAutoCommit(_onOff);
}
}
/**
* Returns status of autocommit option.
* @return true if autocommit enabled, false otherwise
* @throws SQLException if autocommit option status could not be determined
*/
public boolean isAutoCommit() throws SQLException {
if (dbConnection != null) {
return dbConnection.getAutoCommit();
}
return false;
}
@Override
public final String toString() {
return getClass().getSimpleName() + "[open=" + dbOpen + ", connectionParams=" + connectionParams + "]";
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy