@Loggable(value=1) 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();
This class is thread-safe.
| Modifier and Type | Class and Description |
|---|---|
static interface |
JdbcSession.Fetcher
The fetcher.
|
| Constructor and Description |
|---|
JdbcSession(Connection cnx)
Public ctor.
|
JdbcSession(DataSource src)
Public ctor.
|
| Modifier and Type | Method and Description |
|---|---|
JdbcSession |
autocommit(boolean autocommit)
Shall we auto-commit?
|
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. |
<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(@NotNull(message="data source can\'t be NULL") DataSource src)
src - Data sourcepublic JdbcSession(@NotNull(message="connection can\'t be NULL") Connection cnx)
cnx - Connection to usepublic JdbcSession sql(@NotNull(message="SQL query can\'t be NULL") 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 void commit()
throws SQLException
Connection.commit() and then
Connection.close()).SQLException - If fails to do the SQL operationpublic <T> T insert(@NotNull(message="outcome can\'t be NULL") 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(@NotNull(message="outcome can\'t be NULL") 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 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(@NotNull(message="outcome can\'t be NULL") 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–2014 jcabi.com. All rights reserved.