Package ai.libs.jaicore.db.sql

Class SQLAdapter

java.lang.Object

ai.libs.jaicore.db.sql.SQLAdapter

All Implemented Interfaces:

IDatabaseAdapter, java.io.Serializable, java.lang.AutoCloseable, org.api4.java.common.control.ILoggingCustomizable

public class SQLAdapter
extends java.lang.Object
implements IDatabaseAdapter

This is a simple util class for easy database access and query execution in sql. You need to make sure that the respective JDBC connector is in the class path. By default, the adapter uses the mysql driver, but any jdbc driver can be used.

See Also:

Serialized Form

Constructor Summary

Constructors

Constructor	Description
SQLAdapter(IDatabaseConfig config)	Standard c'tor.
SQLAdapter(java.lang.String host, java.lang.String user, java.lang.String password, java.lang.String database)	Constructor for an SQLAdapter.
SQLAdapter(java.lang.String host, java.lang.String user, java.lang.String password, java.lang.String database, boolean ssl)	Constructor for an SQLAdapter.
SQLAdapter(java.lang.String driver, java.lang.String host, java.lang.String user, java.lang.String password, java.lang.String database, java.util.Properties connectionProperties)	Constructor for an SQLAdapter.
SQLAdapter(java.lang.String driver, java.lang.String host, java.lang.String user, java.lang.String password, java.lang.String database, java.util.Properties connectionProperties, boolean ssl)	Constructor for an SQLAdapter.

Method Summary

Modifier and Type	Method	Description
void	checkConnection()	Checks whether the connection to the database is still alive and re-establishs the connection if it is not.
void	close()	Close the connection.
void	createTable(java.lang.String tablename, java.lang.String nameOfPrimaryField, java.util.Collection<java.lang.String> fieldnames, java.util.Map<java.lang.String,java.lang.String> types, java.util.Collection<java.lang.String> keys)
void	executeQueriesAtomically(java.util.List<java.sql.PreparedStatement> queries)	Executes the given statements atomically.
java.lang.String	getDriver()	Getter for the sql database driver.
java.lang.String	getLoggerName()
java.sql.PreparedStatement	getPreparedStatement(java.lang.String query)	Returns a prepared statement for the given query so that any placeholder may be filled into the prepared statement.
java.util.Iterator<org.api4.java.datastructure.kvstore.IKVStore>	getResultIteratorOfQuery(java.lang.String query, java.util.List<java.lang.String> values)
java.util.List<org.api4.java.datastructure.kvstore.IKVStore>	getResultsOfQuery(java.lang.String query)	Retrieves the select result for the given query.
java.util.List<org.api4.java.datastructure.kvstore.IKVStore>	getResultsOfQuery(java.lang.String query, java.lang.String[] values)	Retrieves the select result for the given query that can have placeholders.
java.util.List<org.api4.java.datastructure.kvstore.IKVStore>	getResultsOfQuery(java.lang.String query, java.util.List<java.lang.String> values)	Retrieves the select result for the given query that can have placeholders.
java.util.Iterator<org.api4.java.datastructure.kvstore.IKVStore>	getRowIteratorOfTable(java.lang.String table)
java.util.Iterator<org.api4.java.datastructure.kvstore.IKVStore>	getRowIteratorOfTable(java.lang.String table, java.util.Map<java.lang.String,java.lang.String> conditions)
java.util.List<org.api4.java.datastructure.kvstore.IKVStore>	getRowsOfTable(java.lang.String table)	Retrieves all rows of a table.
java.util.List<org.api4.java.datastructure.kvstore.IKVStore>	getRowsOfTable(java.lang.String table, java.util.Map<java.lang.String,java.lang.String> conditions)	Retrieves all rows of a table which satisfy certain conditions (WHERE clause).
int[]	insert(java.lang.String sql, java.lang.String[] values)	Executes an insert query and returns the row ids of the created entries.
int[]	insert(java.lang.String sql, java.util.List<? extends java.lang.Object> values)	Executes an insert query and returns the row ids of the created entries.
int[]	insert(java.lang.String table, java.util.Map<java.lang.String,? extends java.lang.Object> map)	Creates and executes an insert query for the given table and the values as specified in the map.
int[]	insertMultiple(java.lang.String table, java.util.List<java.lang.String> keys, java.util.List<java.util.List<? extends java.lang.Object>> datarows)	Creates a multi-insert statement and executes it.
int[]	insertMultiple(java.lang.String table, java.util.List<java.lang.String> keys, java.util.List<java.util.List<? extends java.lang.Object>> datarows, int chunkSize)	Creates a multi-insert statement and executes it.
java.util.List<org.api4.java.datastructure.kvstore.IKVStore>	query(java.lang.String sqlStatement)	Sends a query to the database server which can be an arbitrary query.
void	setLoggerName(java.lang.String name)
int	update(java.lang.String sql)	Execute the given sql statement as an update.
int	update(java.lang.String sql, java.lang.String[] values)	Execute the given sql statement with placeholders as an update filling the placeholders with the given values beforehand.
int	update(java.lang.String sql, java.util.List<? extends java.lang.Object> values)	Execute the given sql statement with placeholders as an update filling the placeholders with the given values beforehand.
int	update(java.lang.String table, java.util.Map<java.lang.String,? extends java.lang.Object> updateValues, java.util.Map<java.lang.String,? extends java.lang.Object> conditions)	Create and execute an update statement for some table updating the values as described in updateValues and only affect those entries satisfying the conditions.

Methods inherited from class java.lang.Object

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

Constructor Detail

SQLAdapter

public SQLAdapter(IDatabaseConfig config)

Standard c'tor.

Parameters:

config - The database configuration including a definition of host, user, password, database and whether to connect to the server via SSL.

SQLAdapter

public SQLAdapter(java.lang.String host,
                  java.lang.String user,
                  java.lang.String password,
                  java.lang.String database,
                  boolean ssl)

Constructor for an SQLAdapter.

Parameters:

host - The host of the (remote) database server.

user - The username for logging into the database server.

password - The password corresponding to the username.

database - The name of the database to connect to.

ssl - Flag whether the connection must be ssl encrypted or not.

SQLAdapter

public SQLAdapter(java.lang.String host,
                  java.lang.String user,
                  java.lang.String password,
                  java.lang.String database)

Constructor for an SQLAdapter. The connection is by default SSL encrypted.

Parameters:

host - The host of the (remote) database server.

user - The username for logging into the database server.

password - The password corresponding to the username.

database - The name of the database to connect to.

SQLAdapter

public SQLAdapter(java.lang.String driver,
                  java.lang.String host,
                  java.lang.String user,
                  java.lang.String password,
                  java.lang.String database,
                  java.util.Properties connectionProperties)

Constructor for an SQLAdapter. The connection is by default SSL encrypted.

Parameters:

host - The host of the (remote) database server.

user - The username for logging into the database server.

password - The password corresponding to the username.

database - The name of the database to connect to.

connectionProperties - In these properties additional properties for the SQL connection may be defined.

SQLAdapter

public SQLAdapter(java.lang.String driver,
                  java.lang.String host,
                  java.lang.String user,
                  java.lang.String password,
                  java.lang.String database,
                  java.util.Properties connectionProperties,
                  boolean ssl)

Constructor for an SQLAdapter.

Parameters:

host - The host of the (remote) database server.

user - The username for logging into the database server.

password - The password corresponding to the username.

database - The name of the database to connect to.

connectionProperties - In these properties additional properties for the SQL connection may be defined.

ssl - Flag whether the connection must be ssl encrypted or not.

Method Detail

getPreparedStatement

public java.sql.PreparedStatement getPreparedStatement(java.lang.String query)
                                                throws java.sql.SQLException

Returns a prepared statement for the given query so that any placeholder may be filled into the prepared statement.

Parameters:

query - The query for which a prepared statement shall be returned.

Returns:

The prepared statement for the given query.

Throws:

java.sql.SQLException - Thrown, if there was an issue with the connection to the database.

checkConnection

public void checkConnection()
                     throws java.sql.SQLException

Checks whether the connection to the database is still alive and re-establishs the connection if it is not.

Specified by:

checkConnection in interface IDatabaseAdapter

Throws:

java.sql.SQLException - Thrown, if there was an issue with reconnecting to the database server.

getRowsOfTable

public java.util.List<org.api4.java.datastructure.kvstore.IKVStore> getRowsOfTable(java.lang.String table)
                                                                            throws java.sql.SQLException

Retrieves all rows of a table.

Specified by:

getRowsOfTable in interface IDatabaseAdapter

Parameters:

table - The table for which all entries shall be returned.

Returns:

A list of IKVStores containing the data of the table.

Throws:

java.sql.SQLException - Thrown, if there was an issue with the connection to the database.

getRowsOfTable

public java.util.List<org.api4.java.datastructure.kvstore.IKVStore> getRowsOfTable(java.lang.String table,
                                                                                   java.util.Map<java.lang.String,java.lang.String> conditions)
                                                                            throws java.sql.SQLException

Retrieves all rows of a table which satisfy certain conditions (WHERE clause).

Specified by:

getRowsOfTable in interface IDatabaseAdapter

Parameters:

table - The table for which all entries shall be returned.

conditions - The conditions a result entry must satisfy.

Returns:

A list of IKVStores containing the data of the table.

Throws:

java.sql.SQLException - Thrown, if there was an issue with the connection to the database.

getRowIteratorOfTable

public java.util.Iterator<org.api4.java.datastructure.kvstore.IKVStore> getRowIteratorOfTable(java.lang.String table)
                                                                                       throws java.sql.SQLException

Throws:

java.sql.SQLException

getRowIteratorOfTable

public java.util.Iterator<org.api4.java.datastructure.kvstore.IKVStore> getRowIteratorOfTable(java.lang.String table,
                                                                                              java.util.Map<java.lang.String,java.lang.String> conditions)
                                                                                       throws java.sql.SQLException

Throws:

java.sql.SQLException

getResultsOfQuery

public java.util.List<org.api4.java.datastructure.kvstore.IKVStore> getResultsOfQuery(java.lang.String query)
                                                                               throws java.sql.SQLException

Retrieves the select result for the given query.

Specified by:

getResultsOfQuery in interface IDatabaseAdapter

Parameters:

query - The SQL query which is to be executed.

Returns:

A list of IKVStores containing the result data of the query.

Throws:

java.sql.SQLException - Thrown, if there was an issue with the connection to the database.

getResultsOfQuery

public java.util.List<org.api4.java.datastructure.kvstore.IKVStore> getResultsOfQuery(java.lang.String query,
                                                                                      java.lang.String[] values)
                                                                               throws java.sql.SQLException

Retrieves the select result for the given query that can have placeholders.

Specified by:

getResultsOfQuery in interface IDatabaseAdapter

Parameters:

query - The SQL query which is to be executed (with placeholders).

values - An array of placeholder values that need to be filled in.

Returns:

A list of IKVStores containing the result data of the query.

Throws:

java.sql.SQLException - Thrown, if there was an issue with the connection to the database.

getResultsOfQuery

public java.util.List<org.api4.java.datastructure.kvstore.IKVStore> getResultsOfQuery(java.lang.String query,
                                                                                      java.util.List<java.lang.String> values)
                                                                               throws java.sql.SQLException

Retrieves the select result for the given query that can have placeholders.

Specified by:

getResultsOfQuery in interface IDatabaseAdapter

Parameters:

query - The SQL query which is to be executed (with placeholders).

values - A list of placeholder values that need to be filled in.

Returns:

A list of IKVStores containing the result data of the query.

Throws:

java.sql.SQLException - Thrown, if there was an issue with the query format or the connection to the database.

getResultIteratorOfQuery

public java.util.Iterator<org.api4.java.datastructure.kvstore.IKVStore> getResultIteratorOfQuery(java.lang.String query,
                                                                                                 java.util.List<java.lang.String> values)
                                                                                          throws java.sql.SQLException

Throws:

java.sql.SQLException

insert

public int[] insert(java.lang.String sql,
                    java.lang.String[] values)
             throws java.sql.SQLException

Executes an insert query and returns the row ids of the created entries.

Specified by:

insert in interface IDatabaseAdapter

Parameters:

sql - The insert statement which shall be executed that may have placeholders.

values - The values for the placeholders.

Returns:

An array of the row ids of the inserted entries.

Throws:

java.sql.SQLException - Thrown, if there was an issue with the query format or the connection to the database.

insert

public int[] insert(java.lang.String sql,
                    java.util.List<? extends java.lang.Object> values)
             throws java.sql.SQLException

Executes an insert query and returns the row ids of the created entries.

Specified by:

insert in interface IDatabaseAdapter

Parameters:

sql - The insert statement which shall be executed that may have placeholders.

values - A list of values for the placeholders.

Returns:

An array of the row ids of the inserted entries.

Throws:

java.sql.SQLException - Thrown, if there was an issue with the query format or the connection to the database.

insert

public int[] insert(java.lang.String table,
                    java.util.Map<java.lang.String,? extends java.lang.Object> map)
             throws java.sql.SQLException

Creates and executes an insert query for the given table and the values as specified in the map.

Specified by:

insert in interface IDatabaseAdapter

Parameters:

table - The table where to insert the data.

map - The map of key:value pairs to be inserted into the table.

Returns:

An array of the row ids of the inserted entries.

Throws:

java.sql.SQLException - Thrown, if there was an issue with the query format or the connection to the database.

insertMultiple

public int[] insertMultiple(java.lang.String table,
                            java.util.List<java.lang.String> keys,
                            java.util.List<java.util.List<? extends java.lang.Object>> datarows)
                     throws java.sql.SQLException

Creates a multi-insert statement and executes it. The returned array contains the row id's of the inserted rows. (By default it creates chunks of size 10.000 rows per query to be inserted.)

Specified by:

insertMultiple in interface IDatabaseAdapter

Parameters:

table - The table to which the rows are to be added.

keys - The list of column keys for which values are set.

datarows - The list of value lists to be filled into the table.

Returns:

An array of row id's of the inserted rows.

Throws:

java.sql.SQLException - Thrown, if the sql statement was malformed, could not be executed, or the connection to the database failed.

insertMultiple

public int[] insertMultiple(java.lang.String table,
                            java.util.List<java.lang.String> keys,
                            java.util.List<java.util.List<? extends java.lang.Object>> datarows,
                            int chunkSize)
                     throws java.sql.SQLException

Creates a multi-insert statement and executes it. The returned array contains the row id's of the inserted rows.

Specified by:

insertMultiple in interface IDatabaseAdapter

Parameters:

table - The table to which the rows are to be added.

keys - The list of column keys for which values are set.

datarows - The list of value lists to be filled into the table.

chunkSize - The number of rows which are added within one single database transaction. (10,000 seems to be a good value for this)

Returns:

An array of row id's of the inserted rows.

Throws:

java.sql.SQLException - Thrown, if the sql statement was malformed, could not be executed, or the connection to the database failed.

update

public int update(java.lang.String sql)
           throws java.sql.SQLException

Execute the given sql statement as an update.

Specified by:

update in interface IDatabaseAdapter

Parameters:

sql - The sql statement to be executed.

Returns:

The number of rows affected by the update statement.

Throws:

java.sql.SQLException - Thrown if the statement is malformed or an issue while executing the sql statement occurs.

update

public int update(java.lang.String sql,
                  java.lang.String[] values)
           throws java.sql.SQLException

Execute the given sql statement with placeholders as an update filling the placeholders with the given values beforehand.

Specified by:

update in interface IDatabaseAdapter

Parameters:

sql - The sql statement with placeholders to be executed.

sql - Array of values for the respective placeholders.

Returns:

The number of rows affected by the update statement.

Throws:

java.sql.SQLException - Thrown if the statement is malformed or an issue while executing the sql statement occurs.

update

public int update(java.lang.String sql,
                  java.util.List<? extends java.lang.Object> values)
           throws java.sql.SQLException

Execute the given sql statement with placeholders as an update filling the placeholders with the given values beforehand.

Specified by:

update in interface IDatabaseAdapter

Parameters:

sql - The sql statement with placeholders to be executed.

values - List of values for the respective placeholders.

Returns:

The number of rows affected by the update statement.

Throws:

java.sql.SQLException - Thrown if the statement is malformed or an issue while executing the sql statement occurs.

update

public int update(java.lang.String table,
                  java.util.Map<java.lang.String,? extends java.lang.Object> updateValues,
                  java.util.Map<java.lang.String,? extends java.lang.Object> conditions)
           throws java.sql.SQLException

Create and execute an update statement for some table updating the values as described in updateValues and only affect those entries satisfying the conditions.

Specified by:

update in interface IDatabaseAdapter

Parameters:

table - The table which is to be updated.

updateValues - The description how entries are to be updated.

conditions - The description of the where-clause, conditioning the entries which are to be updated.

Returns:

The number of rows affected by the update statement.

Throws:

java.sql.SQLException - Thrown if the statement is malformed or an issue while executing the sql statement occurs.

executeQueriesAtomically

public void executeQueriesAtomically(java.util.List<java.sql.PreparedStatement> queries)
                              throws java.sql.SQLException

Executes the given statements atomically. Only works if no other statements are sent through this adapter in parallel! Only use for single-threaded applications, otherwise side effects may happen as this changes the auto commit settings of the connection temporarily.

Specified by:

executeQueriesAtomically in interface IDatabaseAdapter

Parameters:

queries - The queries to execute atomically

Throws:

java.sql.SQLException - If the status of the connection cannot be changed. If something goes wrong while executing the given statements, they are rolled back before they are committed.

query

public java.util.List<org.api4.java.datastructure.kvstore.IKVStore> query(java.lang.String sqlStatement)
                                                                   throws java.sql.SQLException,
                                                                          java.io.IOException

Description copied from interface: IDatabaseAdapter

Sends a query to the database server which can be an arbitrary query.

Specified by:

query in interface IDatabaseAdapter

Parameters:

sqlStatement - The sql statement to be executed.

Returns:

If there is a result set returned it will be parsed into a list of IKVStores

Throws:

java.sql.SQLException - Thrown, if the the sql statement cannot be executed for whatever reasons.

java.io.IOException - Thrown, if the result set cannot be parsed into IKVStores.

close

public void close()

Close the connection. No more queries can be sent after having the access object closed

Specified by:

close in interface java.lang.AutoCloseable

Specified by:

close in interface IDatabaseAdapter

getDriver

public java.lang.String getDriver()

Getter for the sql database driver.

Returns:

The name of the database driver.

getLoggerName

public java.lang.String getLoggerName()

Specified by:

getLoggerName in interface org.api4.java.common.control.ILoggingCustomizable

setLoggerName

public void setLoggerName(java.lang.String name)

Specified by:

setLoggerName in interface org.api4.java.common.control.ILoggingCustomizable

createTable

public void createTable(java.lang.String tablename,
                        java.lang.String nameOfPrimaryField,
                        java.util.Collection<java.lang.String> fieldnames,
                        java.util.Map<java.lang.String,java.lang.String> types,
                        java.util.Collection<java.lang.String> keys)
                 throws java.sql.SQLException

Specified by:

createTable in interface IDatabaseAdapter

Throws:

java.sql.SQLException