5.9.13. Running SQL Using QueryRunner
QueryRunner
is a class designed to run SQL. It should be used instead of JDBC in all cases where using plain SQL is necessary and working with the ORM tools of the same purpose is not desired.
The platform’s QueryRunner is a variant of Apache DbUtils QueryRunner with the added ability to use Java Generics.
Usage example:
QueryRunner runner = new QueryRunner(persistence.getDataSource());
try {
Set<String> scripts = runner.query("select SCRIPT_NAME from SYS_DB_CHANGELOG",
new ResultSetHandler<Set<String>>() {
public Set<String> handle(ResultSet rs) throws SQLException {
Set<String> rows = new HashSet<String>();
while (rs.next()) {
rows.add(rs.getString(1));
}
return rows;
}
});
return scripts;
} catch (SQLException e) {
throw new RuntimeException(e);
}
There are two ways of using QueryRunner
: current transaction or separate transaction in autocommit mode.
-
To run a query in current transaction
QueryRunner
must be instantiated using a parameterless constructor. Then,query()
orupdate()
methods should be called with aConnection
parameter retrieved viaEntityManager.getConnection()
. There is no need to close theConnection
after the query, as it will be closed when the transaction is committed. -
To run a query in a separate transaction,
QueryRunner
instance must be created using a constructor with theDataSource
parameter retrieved usingPersistence.getDataSource()
. Then,query()
orupdate()
methods should be called without theConnection
parameter. Connection will be created from the specifiedDataSource
and immediately closed afterwards.