com.github.hypfvieh.db

Class SimpleDatabaseConnector

java.lang.Object

com.github.hypfvieh.db.SimpleDatabaseConnector

public class SimpleDatabaseConnector
extends Object

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 SimpleDatabaseConnector can store multiple select queries in offline cache (map of SQL-Query(key) and Result (value)).

Sample Usage:

 
    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"));
            }
        }
    
  

Since:

1.0.1

Author:

hypfvieh

Constructor Summary

Constructors

Constructor and Description
SimpleDatabaseConnector(DbConnParms _connectionParams)

Method Summary

Modifier and Type	Method and Description
void	closeDatabase() Closes the database connection if it was previously connected.
PreparedStatement	createPreparedStatement(String _sql) Creates a prepared statement which can be used for batch statements.
boolean	executeBatchQuery(String _sqlQuery, List<Object[]> _sqlParameters, int _batchSize) Run update/inserts as batch update.
boolean	executeQuery(PreparedStatement _ps) Execute a previously created prepared statement (update or delete).
boolean	executeQuery(String _sql, Object... _args) Executes an sql query.
List<Map<String,String>>	executeSelectQuery(String _sql, Object... _args) Returns the result of an SQL Select-PreparedStatement as list of maps where each key in the map is a column.
Connection	getDbConnection()
boolean	isAutoCommit() Returns status of autocommit option.
boolean	isDbOpen() Returns true if database has been opened.
boolean	isSupportsBatch()
boolean	openDatabase() Trys to open a connection to the given database parameters.
void	setAutoCommit(boolean _onOff) Enable/Disable autocommit on database connection.
String	toString()

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait

Constructor Detail

SimpleDatabaseConnector

public SimpleDatabaseConnector(DbConnParms _connectionParams)

Method Detail

openDatabase

public final boolean openDatabase()
                           throws InvalidClassException,
                                  ClassNotFoundException

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.

Returns:

true if connected, false otherwise

Throws:

InvalidClassException - if class is not a java.sql.Driver derivative

ClassNotFoundException - if class could not be found

getDbConnection

public Connection getDbConnection()

closeDatabase

public void closeDatabase()
                   throws SQLException

Closes the database connection if it was previously connected.

Throws:

SQLException - if closing fails

isDbOpen

public boolean isDbOpen()

Returns true if database has been opened.

Returns:

true if open, false otherwise

executeBatchQuery

public boolean executeBatchQuery(String _sqlQuery,
                                 List<Object[]> _sqlParameters,
                                 int _batchSize)

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.

Parameters:

_sqlQuery - sql query to use (with '?' placeholders)

_sqlParameters - an array of values for replacing '?' placeholders in query

_batchSize - batch size to use

Returns:

true on successful execution, false if any error occurred

executeQuery

public boolean executeQuery(String _sql,
                            Object... _args)

Executes an sql query.

Parameters:

_sql - query to execute

_args - arguments to fill-in placeholders in query, can be omitted if none needed

Returns:

true if SQL-query returns an update-count, false on error or if result is resultset instead of update count.

executeSelectQuery

public List<Map<String,String>> executeSelectQuery(String _sql,
                                                   Object... _args)

Returns the result of an SQL Select-PreparedStatement as list of maps where each key in the map is a column.

Parameters:

_sql - the sql statement to execute (can use '?' placeholder which will be replaced by the parameters in _args)

_args - parameters to replace '?'- placeholder insert _sql (if none, this can be omitted)

Returns:

list of maps with the result of the query, each list entry is one row of the database

createPreparedStatement

public PreparedStatement createPreparedStatement(String _sql)

Creates a prepared statement which can be used for batch statements.

Parameters:

_sql - sql to create prepared statement for

Returns:

new prepared statement or null on error

executeQuery

public boolean executeQuery(PreparedStatement _ps)

Execute a previously created prepared statement (update or delete).

Parameters:

_ps - prepared statement to execute

Returns:

true if execution successfully, false otherwise

isSupportsBatch

public boolean isSupportsBatch()

setAutoCommit

public void setAutoCommit(boolean _onOff)
                   throws SQLException

Enable/Disable autocommit on database connection.

Parameters:

_onOff - enable/disable autocommit

Throws:

SQLException - if autocommit option cannot be changed

isAutoCommit

public boolean isAutoCommit()
                     throws SQLException

Returns status of autocommit option.

Returns:

true if autocommit enabled, false otherwise

Throws:

SQLException - if autocommit option status could not be determined

toString

public final String toString()

Overrides:

toString in class Object