This article is part of our Academy Course titled jOOQ – Type safe DB querying.
jOOQ is a good choice in a Java application where SQL and the specific relational database are important. It is an alternative when JPA / Hibernate abstract too much, JDBC too little. It shows, how a modern domain-specific language can greatly increase developer productivity, internalising SQL into Java.
In this course, we’ll see how we can efficiently query databases using jOOQ. Check it out here!
Table Of Contents
1. Introduction
There are a variety of SPIs (Service Provider Interfaces) which allow for managing the lifecycle of various entities within jOOQ. These SPIs can be injected via a Configuration object. In this section, we’re going to learn how these lifecycle management objects interact with jOOQ.
Examples displayed in this section are also available from the org.jooq.academy.section4 package.
2. ConnectionProvider
The most important SPI is the one that provides jOOQ with a JDBC Connection. In the examples, so far, the Connection was passed directly to the DSL.using() method:
DSLContext dsl = DSL.using(connection);
This notation is merely a convenience for the more verbose variant where connection is wrapped in a DefaultConnectionProvider:
DSLContext dsl = DSL.using(new DefaultConfiguration().set(new DefaultConnectionProvider(connection)));
In more sophisticated setups than this tutorial, you may wish to provide jOOQ with a DataSource instead, e.g. when using Connection pooling, or even when using distributed transactions via JTA. Popular use-cases involving DataSources are supported natively, also via convenience methods (although you’ll need to provide the SQLDialect, as that cannot be derived from the DataSource
DSLContext dsl = DSL.using(dataSource, SQLDialect.H2);
Should you wish to implement any other type of JDBC Connection source, you can implement your own ConnectionProvider
public interface ConnectionProvider {
// jOOQ will acquire a connection through this method prior to query execution
Connection acquire() throws DataAccessException;
// jOOQ will release previously acquired connections again through this method after query execution
void release(Connection connection) throws DataAccessException;
} 3. SQLDialect
jOOQ will generate and execute your SQL statement in the context of a concrete SQLDialect. This can be best illustrated by example when running the following program:
// This renders SELECT 1 / SELECT 1 FROM DUAL in various SQL dialect families
Arrays.stream(SQLDialect.families())
.map(family -> String.format("%9s : ", family) + DSL.using(family).render(DSL.selectOne()))
.forEach(System.out::println); When you execute the above program, you might get something like the below:
ACCESS : select 1 as [one] from (select count(*) dual from MSysResources) as dual
ASE : select 1 [one]
CUBRID : select 1 "one" from "db_root"
DB2 : select 1 "one" from "SYSIBM"."DUAL"
DERBY : select 1 as "one" from "SYSIBM"."SYSDUMMY1"
FIREBIRD : select 1 "one" from "RDB$DATABASE"
H2 : select 1 "one" from dual
INFORMIX : select 1 "one" from (select 1 as dual from systables where tabid = 1) as dual
INGRES : select 1 "one" from (select 1 as dual) as dual
HSQLDB : select 1 as "one" from "INFORMATION_SCHEMA"."SYSTEM_USERS"
MARIADB : select 1 as `one` from dual
MYSQL : select 1 as `one` from dual
ORACLE : select 1 "one" from dual
POSTGRES : select 1 as "one"
SQLITE : select 1 one
SQLSERVER : select 1 [one]
SYBASE : select 1 [one] from [SYS].[DUMMY] The statements were all generated from the same jOOQ DSL expression DSL.selectOne(). In most cases, you do not need to worry about the little differences between the various SQL dialects, as jOOQ will abstract them all away from you in a single API.
Whether any given jOOQ API element is supported by your SQLDialect can be seen from the @Support annotation on most DSL methods. Take the DSL.denseRank() method, which models the DENSE_RANK() window function. It is declared in the jOOQ API as such:
@Support({ CUBRID, DB2, INFORMIX, POSTGRES, ORACLE, SQLSERVER, SYBASE })
public static WindowOverStep<Integer> rank() { ... } 4. Settings
Settings are used to provide jOOQ with information about general query rendering and execution behaviour. They are governed by an XSD that is available from here: http://www.jooq.org/xsd/jooq-runtime-3.3.0.xsd (check the latest manual or the website for potential updates)
In its current version, the jOOQ settings contain flags to govern…
- Whether tables should be fully qualified with schema
- Whether tables and schemas should be translated / mapped (e.g. to implement multi-tenancy)
- Whether schema, table, and column names should be quoted (e.g. to support case-sensitive names)
- Whether generated SQL keywords should be upper-cased or lower-cased
- Whether generated SQL should be formatted (e.g. for debug logging)
- Whether bind values should be rendered as question marks, named parameters, or inlined
- Whether static statements or prepared statements should be executed
- Whether execution logging is active
- Whether optimistic locking is active
- Whether active records should keep a reference to the
Configurationthat produced them - Whether active records have updatable primary keys
- Whether reflection information should be cached
5. ExecuteListeners
An ExecuteListener is one of a couple of SPIs (Service Provider Interface) that you can use to hook into jOOQ’s query rendering, variable binding and execution lifecycle, on a high level. The following example shows a simple way to measure query execution time on a per-query basis.
5.1. The example ExecuteListener
ExecuteListener listener = new DefaultExecuteListener() {
@Override
public void start(ExecuteContext ctx) {
// Register the start time to the current context
ctx.data("time", System.nanoTime());
}
@Override
public void end(ExecuteContext ctx) {
// Extract the start time from the current context
Long time = (Long) ctx.data("time");
System.out.println("Execution time : " + ((System.nanoTime() - time) / 1000 / 1000.0) + "ms. Query : " + ctx.sql());
}
}; This listener can then be used in a Configuration as follows:
DSL.using(new DefaultConfiguration()
.set(SQLDialect.H2)
.set(new DefaultConnectionProvider(connection))
.set(new DefaultExecuteListenerProvider(listener))
)
.select(AUTHOR.ID)
.from(AUTHOR)
.fetch(); The call to fetch() will now initiate a whole query execution lifecycle including the implemented start() and end() callbacks. This will lead to something like the following output on the console:
Execution time : 0.101ms. Query : select "PUBLIC"."AUTHOR"."ID" from "PUBLIC"."AUTHOR"
There are other SPIs for specific use-cases. For details about those, please refer to the jOOQ manual.
