com.jcabi.jdbc

Class JdbcSession

java.lang.Object

com.jcabi.jdbc.JdbcSession

public final class JdbcSession
extends Object

Universal JDBC wrapper.

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 JdbcSession.Handler<String>() {
       @Override
       public String handle(final ResultSet rset) throws SQLException {
         rset.next();
         return rset.getString(1);
       }
     }
   );

There are a number of convenient pre-defined handlers, like VoidHandler, NotEmptyHandler, SingleHandler, etc.

Methods insert(Handler), update(), and select(Handler) 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")
   .update()
   .sql("DELETE FROM foo WHERE id = ?")
   .set(444)
   .update()
   .set(555)
   .update()
   .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")
   .update();
 

This class is thread-safe.

Since:

0.1.8

Version:

$Id$

Author:

Yegor Bugayenko (yegor@tpc2.com)

Nested Class Summary

Nested Classes

Modifier and Type	Class and Description
static interface	JdbcSession.Handler<T> Handler or ResultSet.

Constructor Summary

Constructors

Constructor and Description
JdbcSession(DataSource source) Public ctor.

Method Summary

Methods

Modifier and Type	Method and Description
JdbcSession	autocommit(boolean autocommit) Shall we auto-commit?
void	commit() Commit the transation (calls Connection.commit() and then Connection.close()).
<T> T	insert(JdbcSession.Handler<T> handler) Make SQL INSERT request.
<T> T	select(JdbcSession.Handler<T> handler) Make SQL SELECT request.
JdbcSession	set(Object value) Set new param for the query.
JdbcSession	sql(String sql) Use this SQL query (with optional parameters inside).
JdbcSession	update() Make SQL UPDATE request.

Methods inherited from class java.lang.Object

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

Constructor Detail

JdbcSession

public JdbcSession(@NotNull
           DataSource source)

Public ctor.

Parameters:

source - Data source

Method Detail

sql

@Loggable(value=2)
public JdbcSession sql(@NotNull
                            String sql)

Use this SQL query (with optional parameters inside).

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(new VoidHandler());
 

Parameters:

sql - The SQL query to use

Returns:

This object

autocommit

@Loggable(value=2)
public JdbcSession autocommit(boolean autocommit)

Shall we auto-commit?

By default this flag is set to TRUE, which means that methods insert(Handler), update(), and select(Handler) will call Connection.commit() after their successful execution.

Parameters:

autocommit - Shall we?

Returns:

This object

set

@Loggable(value=2)
public JdbcSession set(Object value)

Set new param for the query.

The following types are supported: Boolean, Date, Utc, Long, Integer. All other types will be converted to String using their toString() methods.

Parameters:

value - The value to add

Returns:

This object

commit

@Loggable(value=2)
public void commit()

Commit the transation (calls Connection.commit() and then Connection.close()).

insert

@Loggable(value=2)
public <T> T insert(@NotNull
                         JdbcSession.Handler<T> handler)

Make SQL INSERT request.

JdbcSession.Handler will receive a ResultSet of generated keys.

Type Parameters:

T - Type of response

Parameters:

handler - The handler or result

Returns:

The result

update

@Loggable(value=2)
public JdbcSession update()

Make SQL UPDATE request.

Returns:

This object

select

@Loggable(value=2)
public <T> T select(@NotNull
                         JdbcSession.Handler<T> handler)

Make SQL SELECT request.

Type Parameters:

T - Type of response

Parameters:

handler - The handler or result

Returns:

The result