A Frequent Question: Does jOOQ Have a First Level Cache?

One of the more frequent questions people have when switching from JPA to jOOQ is how to migrate from using JPA’s first level cache?

There are two important things to notice here:

jOOQ is mainly used for what JPA folks call “projections”

If you’re using only JPA in your application, you may have gotten used to occasionally fetch DTOs through “projections”. The term “projection” in this context stems from relational algebra, where a projection is simply a SELECT clause in your SQL statement.

Projections are useful when you know that the result of a query will only used for further data processing, but you’re not going to store any modifications to the data back into the database. There are two advantages to this:

  1. You can project arbitrary expressions, including things that cannot be mapped to entities
  2. You can bypass most of the entity management logic, including first and second level caches

When you’re doing this, you will be using SQL – mostly because JPQL (or HQL) are very limited in scope. Ideally, you would be using jOOQ as your projecting query will be type safe and vendor agnostic. You could even use jOOQ to only build the query and run by JPA, although if you’re not fetching entities, you’d lose all result type information that jOOQ would provide you with, otherwise.

So, the advantage of using jOOQ for projections (rather than JPA) is obvious. Sticking to JPA is mainly justified in case you only have very few projection use-cases and they’re also very simple.

jOOQ can also be used for basic CRUD

The question from the above tweet hints at the idea that SQL is not a very good language to implement basic CRUD. Or as I tend to say:

What I mean by this is that it’s really boring to manually express individual statements like these all the time:

INSERT INTO foo (a, b) VALUES (?, ?)
INSERT INTO bar (a, b, c) VALUES (?, ?, ?)
UPDATE baz SET x = ? WHERE id = ?

With most such CRUD operations, we’re simply inserting all the columns, or a given subset of columns, into a target table. Or we’re modifying all the changed columns in that table. These statements are always the same, but they break as soon as we add / remove columns, so we need to fix them throughout our application.

When you’re using an ORM like Hibernate, all you have to change is your annotated meta model, and the generated queries will adapt automatically throughout your application. That’s a huge win!

Additional features

Full-fledged ORMs like Hibernate come with tons of additional features, including:

  • A way to map relationships between entities
  • A way to cache entities in the client

Both of these features are very useful in more sophisticated CRUD use-cases, where an application desires to load, mutate, and persist a complex object graph with many involved entities.

Is this really needed?

However, in simple cases, it might be sufficient to load only 1-2 entities explicitly using jOOQ (jOOQ calls them UpdatableRecord), modify them, and store them back again into the database.

In such cases, it often doesn’t make sense to cache the entity in the client, nor to model the entity relationship in the client. Instead, we can write code like this:

// Fetch an author
AuthorRecord author : create.fetchOne(AUTHOR, AUTHOR.ID.eq(1));

// Create a new author, if it doesn't exist yet
if (author == null) {
    author = create.newRecord(AUTHOR);
    author.setId(1);
    author.setFirstName("Dan");
    author.setLastName("Brown");
}

// Mark the author as a "distinguished" author and store it
author.setDistinguished(1);

// Executes an update on existing authors, or insert on new ones
author.store();

Notice how we haven’t hand-written a single SQL statement. Instead, behind the scenes, jOOQ has generated the necessary INSERT or UPDATE statement for you.

If this is sufficient, you definitely don’t need JPA, and can use a more lightweight programming model through using jOOQ directly.

A few additional features are available, including:

Conclusion

The conclusion is, if you’ve found and read this article because you wanted to replace JPA’s first level cache while migrating to jOOQ is:

Re-think your migration

You don’t have to replace the entirety of JPA. If you need its more sophisticated features, by all means, keep using it along with jOOQ. However, if you don’t need its more sophisticated features and the above CRUD features in jOOQ are sufficient, let go of the idea of needing a first level cache and embrace moving more logic into your SQL queries.

Mocking JDBC Using a Set of SQL String / Result Pairs

In a previous blog post, I’ve shown how the programmatic MockDataProvider can be used to mock the entire JDBC API through a single functional interface:

// context contains the SQL string and bind variables, etc.
MockDataProvider provider = context -> {

    // This defines the update counts, result sets, etc.
    // depending on the context above.
    return new MockResult[] { ... }
};

Writing the provider manually can be tedious in some cases, especially when a few static SQL strings need to be mocked and constant result sets would be OK. In that case, the MockFileDatabase is a convenient implementation that is based on a text file (or SQL string), which contains a set of SQL string / result pairs of the form:

  • SQL string
  • Result set
  • Update count

Assuming this is the content of the mocking.txt file:

select first_name, last_name from actor;
> first_name last_name
> ---------- ---------
> GINA       DEGENERES
> WALTER     TORN     
> MARY       KEITEL   
@ rows: 3

select first_name, last_name, count(*)
from actor
join film_actor using (actor_id)
group by actor_id, first_name, last_name
order by count(*) desc;
> first_name last_name count
> ---------- --------- -----
> GINA       DEGENERES 42
> WALTER     TORN      41
> MARY       KEITEL    40
@ rows: 3

We can then easily load that file into a class and run queries against it:

import static java.lang.System.out;
import java.sql.*;
import org.jooq.tools.jdbc.*;

public class Mocking {
    public static void main(String[] args) throws Exception {
        MockDataProvider db = new MockFileDatabase(
            Mocking.class.getResourceAsStream("/mocking.txt");

        try (Connection c = new MockConnection(db));
            Statement s = c.createStatement()) {

            out.println("Actors:");
            out.println("-------");
            try (ResultSet rs = s.executeQuery(
                "select first_name, last_name from actor")) {
                while (rs.next())
                    out.println(rs.getString(1) 
                        + " " + rs.getString(2));
            }

            out.println();
            out.println("Actors and their films:");
            out.println("-----------------------");
            try (ResultSet rs = s.executeQuery(
                "select first_name, last_name, count(*)\n" +
                "from actor\n" +
                "join film_actor using (actor_id)\n" +
                "group by actor_id, first_name, last_name\n" +
                "order by count(*) desc")) {
                while (rs.next())
                    out.println(rs.getString(1) 
                        + " " + rs.getString(2) 
                        + " (" + rs.getInt(3) + ")");
            }
        }
    }
}

The above will print:

Actors:
-------
GINA DEGENERES
WALTER TORN
MARY KEITEL

Actors and their films:
-----------------------
GINA DEGENERES (42)
WALTER TORN (41)
MARY KEITEL (40)

Notice how we’re not really connecting to any database at all, but simply running queries against our mock database file, which contains a hard-coded set of SQL string / result pairs. While this obviously shouldn’t be used to implement / mock a full-fledged database, it is certainly very useful to intercept only a few queries and return hard-coded results to any JDBC based caller – regardless if they’re using jOOQ, Hibernate, or vanilla JDBC as in the above API.

Map Reducing a Set of Values Into a Dynamic SQL UNION Query

Sounds fancy, right? But it’s a really nice and reasonable approach to doing dynamic SQL with jOOQ.

This blog post is inspired by a Stack Overflow question, where a user wanted to turn a set of values into a dynamic UNION query like this:

SELECT T.COL1
FROM T
WHERE T.COL2 = 'V1'
UNION
SELECT T.COL1
FROM T
WHERE T.COL2 = 'V2'
...
UNION
SELECT T.COL1
FROM T
WHERE T.COL2 = 'VN'

Note, both the Stack Overflow user and I are well aware of the possibility of using IN predicates :-), let’s just assume for the sake of argument, that the UNION query indeed outperforms the IN predicate in the user’s particular MySQL version and database. If this cannot be accepted, just imagine a more complex use case.

The solution in Java is really very simple:

import static org.jooq.impl.DSL.*;
import java.util.*;
import org.jooq.*;

public class Unions {
    public static void main(String[] args) {
        List<String> list = Arrays.asList("V1", "V2", "V3", "V4");

        System.out.println(
            list.stream()
                .map(Unions::query)
                .reduce(Select::union));
    }

    // Dynamically construct a query from an input string
    private static Select<Record1<String>> query(String s) {
        return select(T.COL1).from(T).where(T.COL2.eq(s));
    }
}

The output is:

Optional[(
  select T.COL1
  from T
  where T.COL2 = 'V1'
)
union (
  select T.COL1
  from T
  where T.COL2 = 'V2'
)
union (
  select T.COL1
  from T
  where T.COL2 = 'V3'
)
union (
  select T.COL1
  from T
  where T.COL2 = 'V4'
)]

If you’re using JDK 9+ (which has Optional.stream()), you can further proceed to running the query fluently as follows:

List<String> list = Arrays.asList("V1", "V2", "V3", "V4");

try (Stream<Record1<String>> stream = list.stream()
    .map(Unions::query)
    .reduce(Select::union))
    .stream() // Optional.stream()!
    .flatMap(Select::fetchStream)) {
    ...
}

This way, if the list is empty, reduce will return an empty optional. Streaming that empty optional will result in not fetching any results from the database.

Top 5 Hidden jOOQ Features

jOOQ’s main value proposition is obvious: Type safe embedded SQL in Java.

People who actively look for such a SQL builder will inevitably stumble upon jOOQ and love it, of course. But a lot of people don’t really need a SQL builder – yet, jOOQ can still be immensely helpful in other situations, through its lesser known features.

Here’s a list of top 5 “hidden” jOOQ features.

1. Working with JDBC ResultSet

Even if you’re otherwise not using jOOQ but JDBC (or Spring JdbcTemplate, etc.) directly, one of the things that’s most annoying is working with ResultSet. A JDBC ResultSet models a database cursor, which is essentially a pointer to a collection on the server, which can be positioned anywhere, e.g. to the 50th record via ResultSet.absolute(50) (remember to start counting at 1).

The JDBC ResultSet is optimised for lazy data processing. This means that we don’t have to materialise the entire data set produced by the server in the client. This is a great feature for large (and even large-ish) data sets, but in many cases, it’s a pain. When we know we’re fetching only 10 rows and we know that we’re going to need them in memory anyway, a List<Record> type would be much more convenient.

jOOQ’s org.jooq.Result is such a List, and fortunately, you can easily import any JDBC ResultSet easily as follows by using DSLContext.fetch(ResultSet):

try (ResultSet rs = stmt.executeQuery()) {
    Result<Record> result = DSL.using(connection).fetch(rs);
    System.out.println(result);
}

With that in mind, you can now access all the nice jOOQ utilities, such as formatting a result, e.g. as TEXT (see The second feature for more details):

+---+---------+-----------+
| ID|AUTHOR_ID|TITLE      |
+---+---------+-----------+
|  1|        1|1984       |
|  2|        1|Animal Farm|
+---+---------+-----------+

Of course, the inverse is always possible as well. Need a JDBC ResultSet from a jOOQ Result? Call Result.intoResultSet() and you can inject dummy results to any application that operates on JDBC ResultSet:

DSLContext ctx = DSL.using(connection);

// Get ready for Java 10 with var!
var result = ctx.newResult(FIRST_NAME, LAST_NAME);
result.add(ctx.newRecord(FIRST_NAME, LAST_NAME)
              .values("John", "Doe"));

// Pretend this is a real ResultSet
try (ResultSet rs = result.intoResultSet()) {
  while (rs.next())
    System.out.println(rs.getString(1) + " " + rs.getString(2));
}

2. Exporting a Result as XML, CSV, JSON, HTML, TEXT, ASCII Chart

As we’ve seen in the previous section, jOOQ Result types have nice formatting features. Instead of just text, you can also format as XML, CSV, JSON, HTML, and again TEXT

The format can usually be adapted to your needs.

For instance, this text format is possible as well:

ID AUTHOR_ID TITLE      
------------------------
 1         1 1984       
 2         1 Animal Farm

When formatting as CSV, you’ll get:

ID,AUTHOR_ID,TITLE
1,1,1984
2,1,Animal Farm

When formatting as JSON, you might get:

[{"ID":1,"AUTHOR_ID":1,"TITLE":"1984"},
 {"ID":2,"AUTHOR_ID":1,"TITLE":"Animal Farm"}]

Or, depending on your specified formatting options, perhaps you’ll prefer the more compact array of array style?

[[1,1,"1984"],[2,1,"Animal Farm"]]

Or XML, again with various common formatting styles, among which:

<result>
  <record>
    <ID>1</ID>
    <AUTHOR_ID>1</AUTHOR_ID>
    <TITLE>1984</TITLE>
  </record>
  <record>
    <ID>2</ID>
    <AUTHOR_ID>1</AUTHOR_ID>
    <TITLE>Animal Farm</TITLE>
  </record>
</result>

HTML seems kind of obvious. You’ll get:

ID AUTHOR_ID TITLE
1 1 1984
2 1 Animal Farm

Or, in code:

<table>
<tr><th>ID</th><th>AUTHOR_ID</th><th>TITLE</th></tr>
<tr><td>1</td><td>1</td><td>1984</td></tr>
<tr><td>2</td><td>1</td><td>Animal Farm</td></tr>
</table>

As a bonus, you could even export the Result as an ASCII chart:

These features are obvious additions to ordinary jOOQ queries, but as I’ve shown in Section 1, you can get free exports from JDBC results as well!

3. Importing these text formats again

After the previous section’s export capabilities, it’s natural to think about how to import such data again back into a more usable format. For instance, when you write integration tests, you might expect a database query to return a result like this:

ID AUTHOR_ID TITLE      
-- --------- -----------
 1         1 1984       
 2         1 Animal Farm

Simply import the above textual representation of your result set into an actual jOOQ Result using Result.fetchFromTXT(String) and you can continue operating on a jOOQ Result (or as illustrated in Section 1, with a JDBC ResultSet!).

Most of the other export formats (except charts, of course) can be imported as well.

Now, don’t you wish for a second that Java has multi-line strings (in case of which this would be very nice looking):

Result<?> result = ctx.fetchFromTXT(
    "ID AUTHOR_ID TITLE      \n" +
    "-- --------- -----------\n" +
    " 1         1 1984       \n" +
    " 2         1 Animal Farm\n"
);
ResultSet rs = result.intoResultSet();

These types can now be injected anywhere where a service or DAO produces a jOOQ Result or a JDBC ResultSet. The most obvious application for this is mocking. The second most obvious application is testing. You can easily test that a service produces an expected result of the above form.

Let’s talk about mocking:

4. Mocking JDBC

Sometimes, mocking is cool. With the above tools, it’s only natural for jOOQ to provide a full-fledged, JDBC-based mocking SPI. I’ve written about this feature before and again here.

Essentially, you can implement a single FunctionalInterface called MockDataProvider. The simplest way to create one is by using the Mock.of() methods, e.g.:

MockDataProvider provider = Mock.of(ctx.fetchFromTXT(
    "ID AUTHOR_ID TITLE      \n" +
    "-- --------- -----------\n" +
    " 1         1 1984       \n" +
    " 2         1 Animal Farm\n"
));

What this provider does is it simply ignores all the input (queries, bind variables, etc.) and always returns the same simple result set. You can now plug this provider into a MockConnection and use it like any ordinary JDBC connection:

try (Connection c = new MockConnection(provider);
     PreparedStatement s = c.prepareStatement("SELECT foo");
     ResultSet rs = s.executeQuery()) {

    while (rs.next()) {
        System.out.println("ID        : " + rs.getInt(1));
        System.out.println("First name: " + rs.getString(2));
        System.out.println("Last name : " + rs.getString(3));
    }
}

The output being (completely ignoring the SELECT foo statement):

ID        : 1
First name: 1
Last name : 1984
ID        : 2
First name: 1
Last name : Animal Farm

This client code doesn’t even use jOOQ (although it could)! Meaning, you can use jOOQ as a JDBC mocking framework on any JDBC-based application, including a Hibernate based one.

Of course, you don’t always want to return the exact same result. This is why a MockDataProvider offers you an argument with all the query information in it:

try (Connection c = new MockConnection(ctx -> {
    if (ctx.sql().toLowerCase().startsWith("select")) {
        // ...
    }
})) {
    // Do stuff with this connection
}

You can almost implement an entire JDBC driver with a single lambda expression. Read more here. Cool, eh?

Side note: Don’t get me wrong: I don’t think you should mock your entire database layer just because you can. My thoughts are available in this tweet storm:

Speaking of “synthetic JDBC connections”

5. Parsing Connections

jOOQ 3.9 introduced a SQL parser, whose main use case so far is to parse and reverse engineer DDL scripts for the code generator.

Another feature that has not been talked about often yet (because still a bit experimental) is the parsing connection, available through DSLContext.parsingConnection(). Again, this is a JDBC Connection implementation that wraps a physical JDBC connection but runs all SQL queries through the jOOQ parser before generating them again.

What’s the point?

Let’s assume for a moment that we’re using SQL Server, which supports the following SQL standard syntax:

SELECT * FROM (VALUES (1), (2), (3)) t(a)

The result is:

 a
---
 1
 2
 3

Now, let’s assume we are planning to migrate our application to Oracle and we have the following JDBC code that doesn’t work on Oracle, because Oracle doesn’t support the above syntax:

try (Connection c = DriverManager.getConnection("...");
     Statement s = c.createStatement();
     ResultSet rs = s.executeQuery(
         "SELECT * FROM (VALUES (1), (2), (3)) t(a)")) {

    while (rs.next())
        System.out.println(rs.getInt(1));
}

Now, we have three options (hint #1 sucks, #2 and #3 are cool):

  1. Tediously migrate all such manually written JDBC based SQL to Oracle syntax and hope we don’t have to migrate back again
  2. Upgrade our JDBC based application to use jOOQ instead (that’s the best option, of course, but it also takes some time)
  3. Simply use the jOOQ parsing connection as shown below, and a lot of code will work right out of the box! (and then, of course, gradually migrate to jOOQ, see option #2)
try (DSLContext ctx = DSL.using("...");
     Connection c = ctx.parsingConnection(); // Magic here
     Statement s = c.createStatement();
     ResultSet rs = s.executeQuery(
         "SELECT * FROM (VALUES (1), (2), (3)) t(a)")) {

    while (rs.next())
        System.out.println(rs.getInt(1));
}

We haven’t touched any of our JDBC based client logic. We’ve only introduced a proxy JDBC connection that runs every statement through the jOOQ parser prior to re-generating the statement on the wrapped, physical JDBC connection.

What’s really executed on Oracle is this emulation here:

select t.a from (
  (select null a from dual where 1 = 0) union all 
  (select * from (
    (select 1 from dual) union all 
    (select 2 from dual) union all 
    (select 3 from dual)
  ) t)
) t

Looks funky, eh? The rationale for this emulation is described here.

Every SQL feature that jOOQ can represent with its API and that it can emulate between databases will be supported! This includes far more trivial things, like parsing this query:

SELECT substring('abcdefg', 2, 4)

… and running this one on Oracle instead:

select substr('abcdefg', 2, 4) from dual

You’re all thinking

Want to learn more about jOOQ?

There are many more such nice little things in the jOOQ API, which help make you super productive. Some examples include:

jOOQ 3.10 Supports JPA AttributeConverter

One of the cooler hidden features in jOOQ is the JPADatabase, which allows for reverse engineering a pre-existing set of JPA-annotated entities to generate jOOQ code.

For instance, you could write these entities here:

@Entity
public class Actor {

    @Id
    @GeneratedValue(strategy = IDENTITY)
    public Integer actorId;

    @Column
    public String firstName;

    @Column
    public String lastName;

    @ManyToMany(fetch = LAZY, mappedBy = "actors", 
        cascade = CascadeType.ALL)
    public Set<Film> films = new HashSet<>();

    public Actor(String firstName, String lastName) {
        this.firstName = firstName;
        this.lastName = lastName;
    }
}

@Entity
public class Film {

    @Id
    @GeneratedValue(strategy = IDENTITY)
    public Integer filmId;

    @Column
    public String title;

    @Column(name = "RELEASE_YEAR")
    @Convert(converter = YearConverter.class)
    public Year releaseYear;

    @ManyToMany(fetch = LAZY, cascade = CascadeType.ALL)
    public Set<Actor> actors = new HashSet<>();

    public Film(String title, Year releaseYear) {
        this.title = title;
        this.releaseYear = releaseYear;
    }
}

// Imagine also a Language entity here...

(Just a simple example. Let’s not discuss the caveats of @ManyToMany mapping).

For more info, the full example can be found on Github:

Now observe the fact that we’ve gone through all the trouble of mapping the database type INT for the RELEASE_YEAR column to the cool JSR-310 java.time.Year type for convenience. This has been done using a JPA 2.1 AttributeConverter, which simply looks like this:

public class YearConverter 
implements AttributeConverter<Year, Integer> {

    @Override
    public Integer convertToDatabaseColumn(Year attribute) {
        return attribute == null ? null : attribute.getValue();
    }

    @Override
    public Year convertToEntityAttribute(Integer dbData) {
        return dbData == null ? null : Year.of(dbData);
    }
}

Using jOOQ’s JPADatabase

Now, the JPADatabase in jOOQ allows you to simply configure the input entities (e.g. their package names) and generate jOOQ code from it. This works behind the scenes with this algorithm:

  • Spring is used to discover all the annotated entities on the classpath
  • Hibernate is used to generate an in-memory H2 database from those entities
  • jOOQ is used to reverse-engineer this H2 database again to generate jOOQ code

This works pretty well for most use-cases as the JPA annotated entities are already very vendor-agnostic and do not provide access to many vendor-specific features. We can thus perfectly easily write the following kind of query with jOOQ:

ctx.select(
        ACTOR.FIRSTNAME,
        ACTOR.LASTNAME,
        count().as("Total"),
        count().filterWhere(LANGUAGE.NAME.eq("English"))
          .as("English"),
        count().filterWhere(LANGUAGE.NAME.eq("German"))
          .as("German"),
        min(FILM.RELEASE_YEAR),
        max(FILM.RELEASE_YEAR))
   .from(ACTOR)
   .join(FILM_ACTOR)
     .on(ACTOR.ACTORID.eq(FILM_ACTOR.ACTORS_ACTORID))
   .join(FILM)
     .on(FILM.FILMID.eq(FILM_ACTOR.FILMS_FILMID))
   .join(LANGUAGE)
     .on(FILM.LANGUAGE_LANGUAGEID.eq(LANGUAGE.LANGUAGEID))
   .groupBy(
        ACTOR.ACTORID,
        ACTOR.FIRSTNAME,
        ACTOR.LASTNAME)
   .orderBy(ACTOR.FIRSTNAME, ACTOR.LASTNAME, ACTOR.ACTORID)
   .fetch()

(more info about the awesome FILTER clause here)

In this example, we’re also using the LANGUAGE table, which we omitted in the article. The output of the above query is something along the lines of:

+---------+---------+-----+-------+------+----+----+
|FIRSTNAME|LASTNAME |Total|English|German|min |max |
+---------+---------+-----+-------+------+----+----+
|Daryl    |Hannah   |    1|      1|     0|2015|2015|
|David    |Carradine|    1|      1|     0|2015|2015|
|Michael  |Angarano |    1|      0|     1|2017|2017|
|Reece    |Thompson |    1|      0|     1|2017|2017|
|Uma      |Thurman  |    2|      1|     1|2015|2017|
+---------+---------+-----+-------+------+----+----+

As we can see, this is a very suitable combination of jOOQ and JPA. JPA was used to insert the data through JPA’s useful object graph persistence capabilities, whereas jOOQ is used for reporting on the same tables.

Now, since we already wrote this nice AttributeConverter, we certainly want to apply it also to the jOOQ query and get the java.time.Year data type also in jOOQ, without any additional effort.

jOOQ 3.10 auto conversion

In jOOQ 3.10, we don’t have to do anything anymore. The existing JPA converter will automatically mapped to a jOOQ converter as the generated jOOQ code reads:

// Don't worry about this generated code
public final TableField<FilmRecord, Year> RELEASE_YEAR = 
    createField("RELEASE_YEAR", org.jooq.impl.SQLDataType.INTEGER, 
        this, "", new JPAConverter(YearConverter.class));

… which leads to the previous jOOQ query now returning a type:

Record7<String, String, Integer, Integer, Integer, Year, Year>

Luckily, this was rather easy to implement as the Hibernate meta model allows for navigating the binding between entities and tables very conveniently as described in this article here:

How to get the entity mapping to database table binding metadata from Hibernate

More similar features are coming up in jOOQ 3.11, e.g. when we look into reverse engineering JPA @Embedded types as well. See https://github.com/jOOQ/jOOQ/issues/6518

If you want to run this example, do check out our jOOQ/JPA example on GitHub:

How to Execute SQL Batches With JDBC and jOOQ

Some databases (in particular MySQL and T-SQL databases like SQL Server and Sybase) support a very nice feature: They allow for running a “batch” of statements in a single statement. For instance, in SQL Server, you can do something like this:

-- Statement #1
DECLARE @table AS TABLE (id INT);

-- Statement #2
SELECT * FROM @table;

-- Statement #3
INSERT INTO @table VALUES (1),(2),(3);

-- Statement #4
SELECT * FROM @table;

This is a batch of 4 statements, and it can be executed as a single statement both with JDBC and with jOOQ. Let’s see how:

Executing a batch with JDBC

Unfortunately, the term “batch” has several meanings, and in this case, I don’t mean the JDBC Statement.addBatch() method, which is actually a bit clumsy as it doesn’t allow for fetching mixed update counts and result sets.

Instead, what I’ll be doing is this:

String sql =
    "\n  -- Statement #1                              "
  + "\n  DECLARE @table AS TABLE (id INT);            "
  + "\n                                               "
  + "\n  -- Statement #2                              "
  + "\n  SELECT * FROM @table;                        "
  + "\n                                               "
  + "\n  -- Statement #3                              "
  + "\n  INSERT INTO @table VALUES (1),(2),(3);       "
  + "\n                                               "
  + "\n  -- Statement #4                              "
  + "\n  SELECT * FROM @table;                        ";

try (PreparedStatement s = c.prepareStatement(sql)) {
    fetchLoop:
    for (int i = 0, updateCount = 0;; i++) {
        boolean result = (i == 0)
            ? s.execute()
            : s.getMoreResults();

        if (result)
            try (ResultSet rs = s.getResultSet()) {
                System.out.println("\nResult:");

                while (rs.next())
                    System.out.println("  " + rs.getInt(1));
            }
        else if ((updateCount = s.getUpdateCount()) != -1)
            System.out.println("\nUpdate Count: " + updateCount);
        else
            break fetchLoop;
    }
}

The output of the above program being:

Result:

Update Count: 3

Result:
  1
  2
  3

The above API usage is a somewhat “hidden” – or at least not every day usage of the JDBC API. Mostly, you’ll be using Statement.executeQuery() when you’re expecting a ResultSet, or Statement.executeUpdate() otherwise.

But in our case, we don’t really know what’s happening. We’re going to discover the result types on the fly, when executing the statement. Here are the main JDBC API features that we’re using, along with an explanation:

  • Statement.execute(): This method should be used if we don’t know the result type. The method returns a boolean, which is true when the first statement in the batch produced a ResultSet and false otherwise.
  • Statement.getMoreResults(): This method returns the same kind of boolean value as the previous Statement.execute() method, but it does so for the next statement in the batch (i.e. for every statement except the first).
  • If the current result is a ResultSet (the boolean was true), then we’ll obtain that ResultSet through Statement.getResultSet() (we can obviously no longer call the usual Statement.executeQuery() to obtain the ResultSet).
  • If the current result is not a ResultSet (the boolean was true), then we’ll check the update count value through Statement.getUpdateCount().
  • If the update count is -1, then we’ve reached the end of the batch.

What a nice state machine!

The nice thing about this is that a batch may be completely nondeterministic. E.g. there may be triggers, T-SQL blocks (e.g. an IF statement), stored procedures, and many other things that contribute result sets and/or update counts. In some cases, we simply don’t know what we’ll get.

Executing a batch with jOOQ

It’s great that the JDBC API designers have thought of this exotic API usage on a rather low level. This makes JDBC extremely powerful. But who remembers the exact algorithm all the time? After all, the above minimalistic version required around 20 lines of code for something as simple as that.

Compare this to the following jOOQ API usage:

System.out.println(
    DSL.using(c).fetchMany(sql)
);

The result being:

Result set:
+----+
|  id|
+----+
Update count: 3
Result set:
+----+
|  id|
+----+
|   1|
|   2|
|   3|
+----+

Huh! Couldn’t get much simpler than that! Let’s walk through what happens:

The DSLContext.fetchMany() method is intended for use when users know there will be many result sets and/or update counts. Unlike JDBC which reuses ordinary JDBC API, jOOQ has a different API here to clearly distinguish between behaviours. The method then eagerly fetches all the results and update counts in one go (lazy fetching is on the roadmap with issue #4503).

The resulting type is org.jooq.Results, a type that extends List<Result>, which allows for iterating over the results only, for convenience. If a mix of results or update counts need to be consumed, the Results.resultsOrRows() method can be used.

A note on warnings / errors

Note that if your batch raises errors, then the above JDBC algorithm is incomplete. Read more about this in this follow-up post.

A Functional Programming Approach to Dynamic SQL with jOOQ

Typesafe embedded DSLs like jOOQ are extremely powerful for dynamic SQL, because the query you’re constructing with the jOOQ DSL is a dynamic query by nature. You’re constructing a query expression tree using a convenient API (the “DSL”), even if you think your SQL statement is static. For instance:

for (Record rec : ctx.select(ACTOR.FIRST_NAME, ACTOR.LAST_NAME)
                     .from(ACTOR)
                     .where(ACTOR.FIRST_NAME.like("A%")))

    System.out.println(rec.get(ACTOR.FIRST_NAME) 
               + " " + rec.get(ACTOR.LAST_NAME));

The above query looks like a static SQL statement, the way you would write it in PL/SQL, for instance:

FOR rec IN (
  SELECT first_name, last_name
  FROM actor
  WHERE first_name LIKE 'A%'
) LOOP
  dbms_output.put_line(rec.first_name
             || ' ' || rec.last_name);
END LOOP;

The PL/SQL implicit cursor loop runs over the records produced by a pre-compiled SQL statement. That’s not the case with the jOOQ statement, in case of which the Java runtime re-creates the jOOQ statement expression tree every time afresh by dynamically creating an org.jooq.Select object, step by step (more about how the DSL works here).

Using jOOQ for actual dynamic SQL

As we’ve seen before, all jOOQ statements are dynamic statements, even if they “feel” static. Sometimes, you actually want a dynamic SQL query, e.g. when the user is allowed to specify custom predicates. In this case, you could do something like this:

// By default, make the dynamic predicate "TRUE"
Condition condition = DSL.trueCondition();

// If the user entered something in the text search field...
if (hasFirstNameSearch())
    condition = condition.and(FIRST_NAME.like(firstNameSearch()));

// If the user entered something in another text search field...
if (hasLastNameSearch())
    condition = condition.and(LAST_NAME.like(lastNameSearch()));

// The query now uses a dynamically created predicate
for (Record rec : ctx.select(ACTOR.FIRST_NAME, ACTOR.LAST_NAME)
                     .from(ACTOR)
                     .where(condition))

    System.out.println(rec.get(ACTOR.FIRST_NAME) 
               + " " + rec.get(ACTOR.LAST_NAME));

The above is not possible with PL/SQL easily, you’d have to resort to the dynamic SQL API called DBMS_SQL, which is about as verbose (and error-prone) as JDBC, as you’re concatenating SQL strings.

Adding functional programming to the mix

If you’re able to construct the entire query in a local scope, e.g. inside of a method, the above imperative style is quite sufficient. But sometimes, you may have something like a “base” query that you want to re-use all the time, and only sometimes, you want to add a custom predicate, or JOIN operation, etc.

In this case, using a more functional approach is optimal. For instance, you could offer a convenience API that produces a query fetching actor first and last names, with custom predicates:

// Higher order, SQL query producing function:
public static ResultQuery<Record2<String, String>> actors(
    Function<Actor, Condition> where
) {
    return ctx.select(ACTOR.FIRST_NAME, ACTOR.LAST_NAME)
              .from(ACTOR)
              .where(where.apply(ACTOR)));
}

The above utility method doesn’t actually execute the query, it just constructs it and takes a function as an argument. In the old days, this used to be called the “strategy pattern”, which is implemented much more easily with a function, than with an object oriented approach (see also Mario Fusco’s interesting blog series about the Gang of Four design patterns).

How to call the above utility? Easy!

// Get only actors whose first name starts with "A"
for (Record rec : actors(a -> a.FIRST_NAME.like("A%")))
    System.out.println(rec);

Now, this is not versatile enough yet, as we can pass only one function. How about this, instead:

@SafeVarargs
public static ResultQuery<Record2<String, String>> actors(
    Function<Actor, Condition>... where
) {
    return dsl().select(ACTOR.FIRST_NAME, ACTOR.LAST_NAME)
                .from(ACTOR)
                .where(Arrays.stream(where)
                             .map(f -> f.apply(ACTOR))
                             .collect(Collectors.toList()));
}

(notice how we can immediately execute and iterate over the ResultQuery, as it implements Iterable)

We can now call this with any number of input functions to form dynamic predicates. E.g.:

// Get all actors
for (Record rec : actors())
    System.out.println(rec);

// Get only actors whose first name starts with "A"
for (Record rec : actors(a -> a.FIRST_NAME.like("A%"))) {
    System.out.println(rec);

// Get actors whose first/last name matches "A% B%"
for (Record rec : actors(
        a -> a.FIRST_NAME.like("A%"),
        a -> a.LAST_NAME.like("B%"))) {
    System.out.println(rec);

You get the idea.

Conclusion

… the idea is that jOOQ is an extremely powerful SQL expression tree API, which allows you to dynamically construct SQL queries of arbitrary complexity. If you’re running a static query, this just means that all of your SQL expressions are constant every time you execute the query.

There are no limits to how far you can push this. We’ve seen jOOQ users write queries that dynamically assemble dozens of common table expressions with several levels of dynamically nested derived tables, too. If you have a crazy example to share, we’re looking forward to it!