Package com.blackrook.sql

Class SQLConnection

java.lang.Object

com.blackrook.sql.SQLConnection

All Implemented Interfaces:

SQLCallable, AutoCloseable

public class SQLConnection
extends Object
implements SQLCallable, AutoCloseable

A wrapped SQL connection for ease-of-querying. Closing this connection closes the wrapped connection - in some cases, like when wrapping connections from another pool or manager, this may be undesirable.

Author:

Matthew Tropiano

Nested Class Summary

Nested Classes

Modifier and Type	Class	Description
class	SQLConnection.Transaction	A transaction object that holds a connection that guarantees an isolation level of some kind.
static enum	SQLConnection.TransactionLevel	Enumeration of transaction levels.

Field Summary

Fields inherited from interface com.blackrook.sql.SQLCallable

DEFAULT_BATCH_SIZE

Method Summary

Modifier and Type	Method	Description
void	close()	Closes this connection.
<T> T[]	getResult(Class<T> type, String query, Object... parameters)	Performs a query and creates objects from the resultant rows, setting relevant fields on them.
SQLResult	getResult(String query, Object... parameters)	Performs a query that returns rows and extracts them into a result.
<T> T	getRow(Class<T> type, String query, Object... parameters)	Performs a query and creates an object from it from the first result row extracted, setting relevant fields.
SQLRow	getRow(String query, Object... parameters)	Performs a query and extracts the first row result into a single SQLRow.
int[]	getUpdateBatch(String query, int granularity, Collection<Object[]> parameterList)	Performs a series of update queries on a single statement on a connection and returns the batch result.
SQLResult[]	getUpdateBatchResult(String query, Collection<Object[]> parameterList)	Performs an update query (INSERT, DELETE, UPDATE, or other commands that do not return rows) and extracts each set of result data into a SQLResult.
long[]	getUpdateLargeBatch(String query, int granularity, Collection<Object[]> parameterList)	Performs a series of update queries on a single statement on a connection and returns the batch result.
SQLResult	getUpdateResult(String query, Object... parameters)	Performs an update query (INSERT, DELETE, UPDATE, or other commands that do not return rows) and extracts the data/affected data/generated data into a SQLResult.
boolean	inTransaction()
boolean	isClosed()
SQLConnection.Transaction	startTransaction(SQLConnection.TransactionLevel transactionLevel)	Starts a transaction with a provided level.
void	startTransactionAnd(SQLConnection.TransactionLevel transactionLevel, SQLTransactionConsumer handler)	Starts a transaction with a provided level, performs actions on it, then auto-closes it.
<R> R	startTransactionAnd(SQLConnection.TransactionLevel transactionLevel, SQLTransactionFunction<R> handler)	Starts a transaction with a provided level, performs actions on it, returns a value, then auto-closes it.

Methods inherited from class java.lang.Object

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

Methods inherited from interface com.blackrook.sql.SQLCallable

getUpdateBatch, getUpdateBatch, getUpdateBatch, getUpdateBatchResult, getUpdateLargeBatch, getUpdateLargeBatch, getUpdateLargeBatch

Method Details

startTransaction

public SQLConnection.Transaction startTransaction(SQLConnection.TransactionLevel transactionLevel)
                                           throws SQLException

Starts a transaction with a provided level.

While this transaction is active, calls to this connection's query handling methods will throw an IllegalStateException.

The connection gets Connection.setAutoCommit(boolean) called on it with a FALSE parameter, and sets the transaction isolation level. These settings are restored when the transaction is finished via SQLConnection.Transaction.close(), SQLConnection.Transaction.commit(), or SQLConnection.Transaction.abort(). It is recommended to use an auto-closing mechanism to ensure that the transaction is completed and the connection transaction state is restored.

Parameters:

transactionLevel - the transaction level to set on this transaction.

Returns:

a new transaction.

Throws:

IllegalStateException - if this connection is already in a transaction.

SQLException - if this transaction could not be prepared.

startTransactionAnd

public void startTransactionAnd(SQLConnection.TransactionLevel transactionLevel,
 SQLTransactionConsumer handler)
                         throws SQLException

Starts a transaction with a provided level, performs actions on it, then auto-closes it.

While this transaction is active, calls to this connection's query handling methods will throw an IllegalStateException.

The connection gets Connection.setAutoCommit(boolean) called on it with a FALSE parameter, and sets the transaction isolation level. These settings are restored when the transaction is finished via SQLConnection.Transaction.close(), SQLConnection.Transaction.commit(), or SQLConnection.Transaction.abort(). It is recommended to use an auto-closing mechanism to ensure that the transaction is completed and the connection transaction state is restored.

Parameters:

transactionLevel - the transaction level to set on this transaction.

handler - the consumer function that accepts the retrieved connection and returns a value.

Throws:

IllegalStateException - if this connection is already in a transaction.

SQLException - if this transaction could not be prepared.

startTransactionAnd

public <R> R startTransactionAnd(SQLConnection.TransactionLevel transactionLevel,
 SQLTransactionFunction<R> handler)
                          throws SQLException

Starts a transaction with a provided level, performs actions on it, returns a value, then auto-closes it.

While this transaction is active, calls to this connection's query handling methods will throw an IllegalStateException.

The connection gets Connection.setAutoCommit(boolean) called on it with a FALSE parameter, and sets the transaction isolation level. These settings are restored when the transaction is finished via SQLConnection.Transaction.close(), SQLConnection.Transaction.commit(), or SQLConnection.Transaction.abort(). It is recommended to use an auto-closing mechanism to ensure that the transaction is completed and the connection transaction state is restored.

Type Parameters:

R - the return type.

Parameters:

transactionLevel - the transaction level to set on this transaction.

handler - the consumer function that accepts the retrieved connection and returns a value.

Returns:

the return value of the handler function.

Throws:

IllegalStateException - if this connection is already in a transaction.

SQLException - if this transaction could not be prepared.

inTransaction

public boolean inTransaction()

Returns:

true if this connection is in a transaction, false if not.

getRow

public SQLRow getRow(String query,
 Object... parameters)

Description copied from interface: SQLCallable

Performs a query and extracts the first row result into a single SQLRow.

Specified by:

getRow in interface SQLCallable

Parameters:

query - the query statement to execute.

parameters - list of parameters for parameterized queries.

Returns:

the single result row returned, or null if no row returned.

getRow

public <T> T getRow(Class<T> type,
 String query,
 Object... parameters)

Description copied from interface: SQLCallable

Performs a query and creates an object from it from the first result row extracted, setting relevant fields.

Each result row is applied via the target object's public fields and setter methods.

For instance, if there is a column is a row called "color", its value will be applied via the public field "color" or the setter "setColor()". Public fields take precedence over setters.

Only certain types are converted without issue. Below is a set of source types and their valid target types:

Conversion of Types

Boolean	Boolean, all numeric primitives and their autoboxed equivalents, String.
Number	Boolean (zero is false, nonzero is true), all numeric primitives and their autoboxed equivalents, String, Date, Timestamp.
Timestamp	Long (both primitive and object as milliseconds since the Epoch), Timestamp, Date, String
Date	Long (both primitive and object as milliseconds since the Epoch), Timestamp, Date, String
String	Boolean, all numeric primitives and their autoboxed equivalents, String, byte[], char[].
Clob	Boolean, all numeric primitives and their autoboxed equivalents, String, byte[], char[].
Blob	String, byte[], char[].
NClob	Boolean, all numeric primitives and their autoboxed equivalents, String, byte[], char[].
byte[]	String, byte[], char[].
char[]	Boolean, all numeric primitives and their autoboxed equivalents, String, byte[], char[].

Specified by:

getRow in interface SQLCallable

Type Parameters:

T - the returned data type.

Parameters:

type - the class type to instantiate.

query - the query to execute.

parameters - list of parameters for parameterized queries.

Returns:

an instantiated object with the pertinent fields set, or null if no rows.

getResult

public SQLResult getResult(String query,
 Object... parameters)

Description copied from interface: SQLCallable

Performs a query that returns rows and extracts them into a result.

Specified by:

getResult in interface SQLCallable

Parameters:

query - the query to execute.

parameters - list of parameters for parameterized queries.

Returns:

the result of the query.

getResult

public <T> T[] getResult(Class<T> type,
 String query,
 Object... parameters)

Description copied from interface: SQLCallable

Performs a query and creates objects from the resultant rows, setting relevant fields on them.

Each result row is applied via the target object's public fields and setter methods.

For instance, if there is a column is a row called "color", its value will be applied via the public field "color" or the setter "setColor()". Public fields take precedence over setters.

Only certain types are converted without issue. Below is a set of source types and their valid target types:

Conversion of Types

Boolean	Boolean, all numeric primitives and their autoboxed equivalents, String.
Number	Boolean (zero is false, nonzero is true), all numeric primitives and their autoboxed equivalents, String, Date, Timestamp.
Timestamp	Long (both primitive and object as milliseconds since the Epoch), Timestamp, Date, String
Date	Long (both primitive and object as milliseconds since the Epoch), Timestamp, Date, String
String	Boolean, all numeric primitives and their autoboxed equivalents, String, byte[], char[].
Clob	Boolean, all numeric primitives and their autoboxed equivalents, String, byte[], char[].
Blob	String, byte[], char[].
NClob	Boolean, all numeric primitives and their autoboxed equivalents, String, byte[], char[].
byte[]	String, byte[], char[].
char[]	Boolean, all numeric primitives and their autoboxed equivalents, String, byte[], char[].

Specified by:

getResult in interface SQLCallable

Type Parameters:

T - the returned data type.

Parameters:

type - the class type to instantiate.

query - the query to execute.

parameters - list of parameters for parameterized queries.

Returns:

an array of instantiated objects with the pertinent fields set for each row.

getUpdateResult

public SQLResult getUpdateResult(String query,
 Object... parameters)

Description copied from interface: SQLCallable

Performs an update query (INSERT, DELETE, UPDATE, or other commands that do not return rows) and extracts the data/affected data/generated data into a SQLResult.

Specified by:

getUpdateResult in interface SQLCallable

Parameters:

query - the query to execute.

parameters - list of parameters for parameterized queries.

Returns:

the update result returned (usually number of rows affected and or generated ids).

getUpdateBatch

public int[] getUpdateBatch(String query,
 int granularity,
 Collection<Object[]> parameterList)

Description copied from interface: SQLCallable

Performs a series of update queries on a single statement on a connection and returns the batch result.

Specified by:

getUpdateBatch in interface SQLCallable

Parameters:

query - the query statement to execute.

granularity - the amount of statements to execute at a time. If 0 or less, no granularity.

parameterList - the list of parameter sets to pass to the query for each update.

Returns:

the update result returned (usually number of rows affected and or generated ids).

getUpdateLargeBatch

public long[] getUpdateLargeBatch(String query,
 int granularity,
 Collection<Object[]> parameterList)

Description copied from interface: SQLCallable

Performs a series of update queries on a single statement on a connection and returns the batch result.

Specified by:

getUpdateLargeBatch in interface SQLCallable

Parameters:

query - the query statement to execute.

granularity - the amount of statements to execute at a time. If 0 or less, no granularity.

parameterList - the list of parameter sets to pass to the query for each update.

Returns:

the update result returned (usually number of rows affected and or generated ids).

getUpdateBatchResult

public SQLResult[] getUpdateBatchResult(String query,
 Collection<Object[]> parameterList)

Description copied from interface: SQLCallable

Performs an update query (INSERT, DELETE, UPDATE, or other commands that do not return rows) and extracts each set of result data into a SQLResult.

This is usually more efficient than multiple calls of SQLCallable.getUpdateResult(String, Object...), since it uses the same prepared statement. However, it is not as efficient as SQLCallable.getUpdateBatch(String, int, Collection), but for this method, you will get the generated ids in each result, if any.

Specified by:

getUpdateBatchResult in interface SQLCallable

Parameters:

query - the query statement to execute.

parameterList - the list of parameter sets to pass to the query for each update.

Returns:

the list of update results returned, each corresponding to an update.

isClosed

public boolean isClosed()
                 throws SQLException

Returns:

true if this connection is closed, false if open.

Throws:

SQLException - if checking the connection status results in an error.

close

public void close()

Closes this connection. If a transaction is active, and the transaction is not finished, this aborts it.

Specified by:

close in interface AutoCloseable

See Also:

• SQLConnection.Transaction.abort()