templates.docs.jdbc.html Maven / Gradle / Ivy
Show all versions of spincast-website Show documentation
{#==========================================
Docs : "JDBC / SQL"
==========================================#}
{#==========================================
Routing
==========================================#}
JDBC / SQL
Spincast provides utilities to perform SQL queries:
-
Scopes - automatic connections management and
support for transactions.
-
Statements - allow the creation of
SQL queries in a safe and easy manner, with
named parameters support.
-
Result Sets - with goodies and better
null
support compared to plain JDBC.
To access those JDBC features, you first have to install the
Spincast JDBC plugin. This plugin is not part of the
spincast-default artifact.
{#==========================================
Scopes
==========================================#}
Scopes
A JDBC scope is a block of code that provides a connection to a data source and automatically returns it to the connection
pool when it is not needed anymore.
There is three kinds of scopes: autoCommit, transactional
and specificConnection.
You start a JDBC scope by calling the
JdbcUtils's scopes() method.
Here's how to start an autoCommit scope :
getJdbcUtils().scopes().autoCommit(getMainDataSource(), new JdbcQueries<Void>() {
@Override
public Void run(Connection connection) {
// SQL query #1...
// SQL query #2...
return null;
}
});
You call getJdbcUtils().scopes().autoCommit(...) by passing the
DataSource to use, and a JdbcQueries
instance. In the run(...) method you receive a connection that is ready to be used
to run your SQL queries.
When the run(...) method exits, the connection is automatically returned to the connection pool.
A transactional scope adds an extra functionality: all the SQL queries performed inside that scope,
directly or indirectly, will be part of a single transaction. In other words, all the queries will be committed
only when the scope exits, or will all be rollbacked if an exception occurres.
There are two ways to create a transactional scope. It can be created the same way an autoCommit
one is, by passing a DataSource and a JdbcQueries
instance:
getJdbcUtils().scopes().transactional(getMainDataSource(), new JdbcQueries<Void>() {
@Override
public Void run(Connection connection) throws Exception {
// SQL query #1...
// SQL query #2...
return null;
};
});
Or it can be created only to start a transaction, without the immediate need for a connection :
getJdbcUtils().scopes().transactional(new TransactionalScope<Void>() {
@Override
public Void run() throws Exception {
// Use component #1...
// Use component #2...
return null;
};
});
In both situations, any SQL queries performed in the scope,
directly or indirectly, and targetting a common DataSource,
will be part of the same transaction.
The final type of scope is specificConnection. In such scope,
all queries (directly or indirectly) are going to be ran using the same connection, the one
provided when creating the scope.
getJdbcUtils().scopes().specificConnection(connection, getMainDataSource(), new JdbcQueries<Void>() {
@Override
public Void run(Connection connection) {
// SQL query #1...
// SQL query #2...
return null;
}
});
{#==========================================
Statements
==========================================#}
Statements
By using the JdbcStatementFactory, by injecting it or
from the JdbcUtils's statements()
utility method,
you start the creation of SQL statements :
getJdbcUtils().scopes().transactional(getMainDataSource(), new JdbcQueries<Void>() {
@Override
public Void run(Connection connection) throws Exception {
SelectStatement stm1 = getJdbcUtils().statements().createSelectStatement(connection);
InsertStatement stm2 = getJdbcUtils().statements().createInsertStatement(connection);
BatchInsertStatement stm3 = getJdbcUtils().statements().createBatchInsertStatement(connection);
UpdateStatement stm4 = getJdbcUtils().statements().createUpdateStatement(connection);
DeleteStatement stm5 = getJdbcUtils().statements().createDeleteStatement(connection);
// ...
return null;
};
});
From such statements, you can build your SQL query and bind named parameters.
For example :
SelectStatement stm = getJdbcUtils().statements().createSelectStatement(connection);
stm.sql("SELECT name, level " +
"FROM users " +
"WHERE name = :name ");
stm.setString("name", "Stromgol");
if (minLevel != null) {
stm.sql("AND level >= :minLevel ");
stm.setInteger("minLevel", minLevel);
}
There are utility methods for IN conditions:
SelectStatement stm = getJdbcUtils().statements().createSelectStatement(connection);
stm.sql("SELECT name, level " +
"FROM users " +
"WHERE name IN(:names) ");
stm.setInString("names", Sets.newHashSet("Stromgol", "Patof", "Bozo"));
A setInstant(...)
method is provided : it converts the Instant object to a Timestamp at the UTC timezone.
In association with the result set's getInstant(),
and a "timestamp with time zone" or "timestamptz" column type, it is
an easy and efficient way of dealing with dates and timezones.
You can retrieve the current SQL and clears it if you need to :
stm.sql("SELECT * FROM users ");
// "true" => human friendly formatted
String currentSql = stm.getSql(true);
// clears the current query
stm.clearSql();
Finally, when your SQL query is ready, you execute it. The method to call to execute the
query depends on the type of statement you are using:
// SelectStatement
SelectStatement stm1 = getJdbcUtils().statements().createSelectStatement(connection);
stm.selectOne(...);
// or
stm.selectList(...);
// or
stm.selectListAndTotal(...);
// InsertStatement
InsertStatement stm2 = getJdbcUtils().statements().createInsertStatement(connection);
stm2.insert();
// or
stm2.insertGetGeneratedKeys();
// BatchInsertStatement
BatchInsertStatement stm3 = getJdbcUtils().statements().createBatchInsertStatement(connection);
stm3.batchInsert();
// or
stm3.batchInsertGetGeneratedKeys();
// UpdateStatement
UpdateStatement stm4 = getJdbcUtils().statements().createUpdateStatement(connection);
stm4.update();
// DeleteStatement
DeleteStatement stm5 = getJdbcUtils().statements().createDeleteStatement(connection);
stm5.delete();
{#==========================================
Result Sets
==========================================#}
Result Sets
When you execute a SelectStatement, you have to pass a
ResultSetHandler
in order to use the values returned by the database.
For example :
SelectStatement stm = getJdbcUtils().statements().createSelectStatement(connection);
stm.sql("SELECT name, level " +
"FROM users " +
"WHERE name = :name ");
stm.setString("name", "Stromgol");
User user = stm.selectOne(new ResultSetHandler<User>() {
@Override
public User handle(SpincastResultSet rs) throws Exception {
User user = new User(rs.getString("name"),
rs.getIntegerOrNull("level"));
return user;
}
});
In this example, you can see that you receive a
SpincastResultSet
to deal with the data returned from the database.
This SpincastResultSet object implements the default Java's java.sql.ResultSet,
but also provide additional features.
In SpincastResultSet, those methods are deprecated: getBoolean(...), getByte(...),
getShort(...), getInteger(...), getLong(...), getFloat(...),
getDouble(...).
They are replaced by :
-
getBooleanOrNull(...)
-
getByteOrNull(...)
-
getShortOrNull(...)
-
getIntegerOrNull(...)
-
getLongOrNull(...)
-
getFloatOrNull(...)
-
getDoubleOrNull(...)
Or, to get 0 like the original JDBC's getters would return when the actual value in
the database is null:
-
getBooleanOrZero(...)
-
getByteOrZero(...)
-
getShortOrZero(...)
-
getIntegerOrZero(...)
-
getLongOrZero(...)
-
getFloatOrZero(...)
-
getDoubleOrZero(...)
We do this to deal with the bad decision JDBC's creators did by using primitive types as return types for
those getters and therefore preventing null from being returned properly. Using plain JDBC, when a null value is returned
by the database, is it transformed to the default value of the primitive type : false for booleans and 0 for number
types. Using SpincastResultSet, you receive a proper null value when this is what the database returns.
Finally, a getInstant()
method is provided to easily convert a column of type "timestamp with time zone" or "timestamptz" to an Instant object.