Home » Java » Enterprise Java » The jOOQ Configuration

About Lukas Eder

Lukas Eder
Lukas is a Java and SQL enthusiast developer. He created the Data Geekery GmbH. He is the creator of jOOQ, a comprehensive SQL library for Java, and he is blogging mostly about these three topics: Java, SQL and jOOQ.

The jOOQ Configuration

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!
 
 

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 Configuration that 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.

(0 rating, 0 votes)
You need to be a registered member to rate this.
Start the discussion Views Tweet it!
Do you want to know how to develop your skillset to become a Java Rockstar?
Subscribe to our newsletter to start Rocking right now!
To get you started we give you our best selling eBooks for FREE!
1. JPA Mini Book
2. JVM Troubleshooting Guide
3. JUnit Tutorial for Unit Testing
4. Java Annotations Tutorial
5. Java Interview Questions
6. Spring Interview Questions
7. Android UI Design
and many more ....
I agree to the Terms and Privacy Policy

Leave a Reply

avatar

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  Subscribe  
Notify of