public final class JdbcSession extends Object
Execute a simple SQL query over a JDBC data source:
String name = new JdbcSession(source) .sql("SELECT name FROM foo WHERE id = ?") .set(123) .select( new Outcome<String>() { @Override public String handle(final ResultSet rset) throws SQLException { rset.next(); return rset.getString(1); } } );
There are a number of convenient pre-defined outcomes, like
Outcome.VOID
, Outcome.NOT_EMPTY
, Outcome.UPDATE_COUNT
SingleOutcome
, etc.
Methods insert(Outcome)
,
update(Outcome)
,
execute()
, and
select(Outcome)
clean the list of arguments pre-set by
set(Object)
. The class can be used for a complex transaction, when
it's necessary to perform a number of SQL statements in a group. For
example, the following construct will execute two SQL queries, in a single
transaction and will "commit" at the end (or rollback the entire transaction
in case of any error in between):
new JdbcSession(source) .autocommit(false) .sql("START TRANSACTION") .execute() .sql("DELETE FROM foo WHERE id = ?") .set(444) .execute() .set(555) .execute() .commit();
The following SQL queries will be sent to the database:
START TRANSACTION; DELETE FROM foo WHERE id = 444; DELETE FROM foo WHERE id = 555; COMMIT;
autocommit(boolean)
(with false
as an argument)
can be used when it's necessary to execute
a statement and leave the connection open. For example when shutting down
the database through SQL:
new JdbcSession(/* H2 Database data source */) .autocommit(false) .sql("SHUTDOWN COMPACT") .execute();IMPORTANT:
If you rely on one specific Connection
instance, be careful if
you are using it in more places, especially if more references of this class
use it - one of those references might close the connection if you forget
to call #autocommit(false)
Connection connection = [...]; DataSource ds = new StaticSource(connection); new JdbcSession(ds) .sql("SQL STATEMENT") .execute(); new JdbcSession(ds) .sql("SQL STATEMENT 2") .execute();
The above example will fail because the first JdbcSession closes
the connection, and the next one tries to work with it closed. In order to
not have this failure, the first session has to call
#autocommit(false)
, like this:
Connection connection = [...]; DataSource ds = new StaticSource(connection); new JdbcSession(ds) .autocommit(false) .sql("SQL STATEMENT") .execute(); new JdbcSession(ds) .sql("SQL STATEMENT 2") .execute();
This class is thread-safe.
Constructor and Description |
---|
JdbcSession(DataSource src)
Public ctor.
If all you have is a Connection , wrap it inside our
StaticSource , but make sure you understand the autocommit
mechanism we have in place here. |
Modifier and Type | Method and Description |
---|---|
JdbcSession |
autocommit(boolean autocommit)
Shall we auto-commit?
|
<T> T |
call(Outcome<T> outcome)
Call an SQL stored procedure.
|
JdbcSession |
clear()
Clear all pre-set parameters (args, preparations, etc).
|
void |
commit()
Commit the transaction (calls
Connection.commit() and then
Connection.close() ). |
JdbcSession |
execute()
Make SQL request expecting no response from the server.
|
<T> T |
insert(Outcome<T> outcome)
Make SQL
INSERT request. |
JdbcSession |
prepare(Preparation prp)
Run this preparation before executing the statement.
|
<T> T |
select(Outcome<T> outcome)
Make SQL
SELECT request. |
JdbcSession |
set(Object value)
Set new parameter for the query.
|
JdbcSession |
sql(String sql)
Use this SQL query (with optional parameters inside).
|
<T> T |
update(Outcome<T> outcome)
Make SQL
UPDATE request. |
public JdbcSession(DataSource src)
Connection
, wrap it inside our
StaticSource
, but make sure you understand the autocommit
mechanism we have in place here. Read the class' javadoc (especially the
last paragraph, marked with IMPORTANT).src
- Data sourcepublic JdbcSession sql(String sql)
The query will be used in PreparedStatement
, that's why
you can use the same formatting as there. Arguments shall be marked
as "?"
(question marks). For example:
String name = new JdbcSession(source) .sql("INSERT INTO foo (id, name) VALUES (?, ?)") .set(556677) .set("Jeffrey Lebowski") .insert(Outcome.VOID);
sql
- The SQL query to usepublic JdbcSession autocommit(boolean autocommit)
By default this flag is set to TRUE, which means that methods
insert(Outcome)
, execute()
, and
select(Outcome)
will
call Connection.commit()
after
their successful execution.
autocommit
- Shall we?public JdbcSession set(Object value)
The following types are supported: Boolean
,
Date
,
Utc
, Long
, Integer
. All other types will be
converted to String
using their toString()
methods.
value
- The value to addpublic JdbcSession prepare(Preparation prp)
prp
- Preparationpublic JdbcSession clear()
public void commit() throws SQLException
Connection.commit()
and then
Connection.close()
).SQLException
- If fails to do the SQL operationpublic <T> T insert(Outcome<T> outcome) throws SQLException
INSERT
request.
Outcome
will receive
a ResultSet
of generated keys.
JDBC connection is opened and, optionally, closed by this method.
T
- Type of responseoutcome
- The outcome of the operationSQLException
- If failspublic <T> T update(Outcome<T> outcome) throws SQLException
UPDATE
request.
JDBC connection is opened and, optionally, closed by this method.
T
- Type of result expectedoutcome
- Outcome of the operationSQLException
- If failspublic <T> T call(Outcome<T> outcome) throws SQLException
JDBC connection is opened and, optionally, commited by this method, depending on the autocommit class attribute: if it's value is true, the connection will be commited after this call.
T
- Type of result expectedoutcome
- Outcome of the operationSQLException
- If failspublic JdbcSession execute() throws SQLException
This method should be used for schema manipulation statements,
like CREATE TABLE, CREATE INDEX, DROP COLUMN, etc. and server-side
instructions that return no data back. Main difference between this
one and #execute()
is that the later requests JDBC to return
generated keys. When SQL server doesn't return any keys this may
cause runtime exceptions in JDBC.
JDBC connection is opened and, optionally, closed by this method.
SQLException
- If failspublic <T> T select(Outcome<T> outcome) throws SQLException
SELECT
request.
JDBC connection is opened and, optionally, closed by this method.
T
- Type of responseoutcome
- The outcome of the operatonSQLException
- If failsCopyright © 2012–2018 jcabi.com. All rights reserved.