10 Nice Examples of Writing SQL in Kotlin With jOOQ

Kotlin is the next big thing. With Google announcing official support for Kotlin on Android, we’ll see a lot more traction for this lovely language.

We’ve already blogged about the Kotlin language recently: 10 Features I Wish Java Would Steal From the Kotlin Language.

Should you migrate your application to Kotlin? Perhaps – there are many questions. The most important question is (of course): What are you going to do with your awesome SQL code?

The answer is: Use jOOQ! Need convincing? Here are 10 nice examples of writing SQL in Kotlin with jOOQ:

(Note: All the code is available on GitHub)

1. Using Kotlin’s Map Access Literals With jOOQ Records

This is a free feature that you’re getting from Kotlin thanks to their smart move of supporting certain operators on custom types by convention. Check out how easy it is to access a value from a record using a column reference:

val a = AUTHOR
val b = BOOK

ctx.select(a.FIRST_NAME, a.LAST_NAME, b.TITLE)
   .from(a)
   .join(b).on(a.ID.eq(b.AUTHOR_ID))
   .orderBy(1, 2, 3)
   .forEach {
       println("${it[b.TITLE]} by "
             + "${it[a.FIRST_NAME]} ${it[a.LAST_NAME]}")
   }

The output from our sample code is this:

1984 by George Orwell
Animal Farm by George Orwell
Brida by Paulo Coelho
O Alquimista by Paulo Coelho

We’re already using a couple of useful language features here. First off:

val for local variable type inference

We’re renaming AUTHOR to a and BOOK to b in our code to shorten table names a little bit. Using val we don’t even need to explicitly refer to the type of those tables.

As it looks right now, we’re going to get that feature in the Java language as well, so stay tuned.

(More about val in item #3)

More info about this feature here.

String interpolation

As you can see, inside of the String literal, we’re using the ${dollar.notation} to access variables from the context.

More info about this feature here.

(More about string interpolation in item #5)

Implicit lambda parameter “it”

When writing single-parameter lambda expressions, which is typical for loops, mapping, and many other standard library features, we don’t need to actually name that parameter in Kotlin. The default name is “it”. Groovy developers will rejoice, as they can profit from the same feature.

More info about this feature here.

Finally: Operator overloading

Kotlin supports a nice and pragmatic approach to operator overloading. We’re making use of the fact that the following two things mean exactly the same in Kotlin:

val x1 = anything[key];
val x2 = anything.get(key);

anything[key] = value;
anything.set(key, value);

Yeah, why not? In jOOQ, we’ve already seen this coming in version 3.9, and we’ve thus added get() and set() methods on our ubiquitous Record type, so you can access record values by:

  • Field reference (with type safety)
  • Column name (no type safety)
  • Index (no type safety)

Note that operator overloading can be used as well with a couple of arithmetic operators:

ctx.select(TRANSACTION.AMOUNT + 10) // Translates to Field.plus(10)
   .from(TRANSACTION)
   .fetch();

More info about this feature here.

2. Loop Over Record Contents

Another really nice feature of the language is destructuring of “tuples” into several local variables. Quite a few languages support this now, and so does Kotlin, even without first-level language support for real tuples. But often, we don’t actually need tuples, we just need their syntax. This can be done with any Map, for instance. Consider this code:

for (r in ctx.fetch(b))
    for ((k, v) in r.intoMap())
        println("${r[b.ID]}: ${k.padEnd(20)} = $v")

The output from our sample code is this:

1: ID                   = 1
1: AUTHOR_ID            = 1
1: TITLE                = 1984
1: PUBLISHED_IN         = 1948
2: ID                   = 2
2: AUTHOR_ID            = 1
2: TITLE                = Animal Farm
2: PUBLISHED_IN         = 1945
3: ID                   = 3
3: AUTHOR_ID            = 2
3: TITLE                = O Alquimista
3: PUBLISHED_IN         = 1988
4: ID                   = 4
4: AUTHOR_ID            = 2
4: TITLE                = Brida
4: PUBLISHED_IN         = 1990

This is really nice! Every jOOQ record can be represented as a Map using Record.intoMap(), which can be destructured automatically in loops as can be seen above.

More info about this feature here.

3. Local Variable Type Inference

An API that makes use of generics as heavily as jOOQ can leave quite some burden on the user occasionally. Type safety is great for the DSL, e.g. in jOOQ, the Java / Kotlin compilers can type-check things like:

// Type checks: Both sides are of type string
AUTHOR.FIRST_NAME.in("Alice", "Bob");

// Type checks: 
// - Both sides are of type string
// - Both sides are of degree one
AUTHOR.FIRST_NAME.in(
  select(CUSTOMER.FIRST_NAME).from(CUSTOMER)
);

// Doesn't type check: Wrong degree
AUTHOR.FIRST_NAME.in(
  select(CUSTOMER.FIRST_NAME, CUSTOMER.LAST_NAME).from(CUSTOMER)
)

This works through fancy types like:

Select<Record2<String, String>> subselect =
  select(CUSTOMER.FIRST_NAME, CUSTOMER.LAST_NAME).from(CUSTOMER);

These types can get quite hairy as they go up to 22 (higher degrees are supported without type safety). So, in Kotlin, we can simply write:

val subselect =
  select(CUSTOMER.FIRST_NAME, CUSTOMER.LAST_NAME).from(CUSTOMER);

// Still doesn't type check:
AUTHOR.FIRST_NAME.in(subselect)

// This works:
row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).in(subselect)

More info about this feature here.

4. Inline Functions for New jOOQ Features

Are you missing a feature in jOOQ, for instance the PostgreSQL ilike operator? Well, it is supported through the Field.likeIgnoreCase() method, but you might prefer the more native looking ilike operator. No problem with Kotlin. Just write an inline function (which works similar to an extension function but they’re not exactly the same thing):

inline fun <F: Field<String>> F.ilike(field: String): Condition {
    return condition("{0} ilike {1}", this, field)
}

These inline functions make use of jOOQ’s plain SQL API which is heavily used by Java developers as well, but in this case, the keyword inline on the above functions indicates that the function works like an inline function. We can now write a query like this:

println("${ctx.select(b.TITLE)
              .from(b)
              .where(b.TITLE.ilike("%animal%"))
              .fetchOne(b.TITLE)}")

The result being:

Animal Farm

Notice how we can simply write b.TITLE.ilike("..") even if the jOOQ API doesn’t have such a method!

More info about this feature here

5. String Interpolation and Multiline Strings

How many times have we wished for multiline strings in Java? So many other languages have it, even SQL and PL/SQL. When working with string-based embedded SQL, this is just a killer feature. Good for Kotlin! Not only does Kotlin support multiline strings, but also string interpolation.

Here’s how to use the jOOQ plain SQL API with Kotlin:

ctx.resultQuery("""
    SELECT *
    FROM (
      VALUES (1, 'a'),
             (2, 'b')
    ) AS t
    """)
    .fetch()
    .forEach {
        println("${it.intoMap()}")
    }

Just copy-paste any arbitrary SQL statement right from your SQL console (in this case: H2 syntax) and you’re done. The output of the above is:

{C1=1, C2=a}
{C1=2, C2=b}

Bonus feature if you’re using the upcoming jOOQ 3.10 parser: You can standardise on SQL features that are not available from your database:

val colX = field("x")
val colY = field("y")
ctx.parser()
   .parseResultQuery("""
    SELECT *
    FROM (
      VALUES (1, 'a'),
             (2, 'b')
      -- This feature (derived column lists) 
      -- isn't really available in H2 yet it works!
    ) AS t(${colX.name}, ${colY.name})
    """)
   .fetch()
   .forEach {
       println("${it[colX]}, ${it[colY]}")
   }

The above statement makes use of derived column lists (renaming tables and columns in one go). Unfortunately, H2 doesn’t support this feature but jOOQ can emulate it, transparently. Note that we’re again using string interpolation, this time in order to reuse column names.

More info about the feature here.

6. Null Safe Dereferencing

Null is the mother of all bikesheds. Of course, Kotlin is opinionated about null as well

You’re not sure if your query will produce a result? No problem with Kotlin’s various null-related operator:

println("${ctx.fetchOne(b, b.ID.eq(5))?.title ?: "not found"}")

The above will print:

not found

Why? Because fetchOne() won’t return a record from our database, it will return null. We cannot dereference ?.title from null, but the ?. operator won’t throw an exception, it will just return null again. Finally, the Elvis operator will provide a default value if the expression on the left of it is null. Perfect!

More info about the feature here.

7. Getters and setters feel like properties

Remember the last 20 years when we emulated properties in Java using the obnoxious JavaBeans convention? Tired of writing getters and setters? Well, at least, with Kotlin, it pays off. We can syntactically pretend that we have a property where there are only getters and setters. Check this out:

val author1 = ctx.selectFrom(a).where(a.ID.eq(1)).fetchOne();
println("${author1.firstName} ${author1.lastName}")

val author2 = ctx.newRecord(a);
author2.firstName = "Alice"
author2.lastName = "Doe"
author2.store()
println("${author2.firstName} ${author2.lastName}")

Isn’t that awesome? we can just dereference author.firstName, which simply translates to author.getFirstName(). Likewise, we can assign a value to author2.lastName = "Doe", which simply translates to author2.setLastName("Doe")

It seems so obvious, no?

More info about the feature here

8. With to Abbreviate Setter Calls

We can take this feature one step further and use the nice with() inline function (not a keyword!). In the above example, when storing a new author, we have to repeat the author reference all the time. This is no longer true with… with!

val author3 = ctx.newRecord(a);
with (author3) {
    firstName = "Bob"
    lastName = "Doe"
    store()
}

All the code that is inside of the block supplied to the with() function is going to operate on the with() function’s first argument. We already know this interesting feature from JavaScript, but in Kotlin, it’s typesafe!

And notice, the store() call is also affected!

More info about this feature here

9. With to Locally Import Tables

We can use with() also with queries! In jOOQ, columns are instance members inside of tables, and as such, must be dereferenced from a concrete table. That can be tedious (SQL doesn’t have this requirement), especially when selecting only from a single table. You can turn on static member generation in jOOQ’s code generator, but then table aliasing is much less easy.

Or you use Kotlin again! The with() function can help:

with (AUTHOR) {
    ctx.select(FIRST_NAME, LAST_NAME)
       .from(AUTHOR)
       .where(ID.lt(5))
       .orderBy(ID)
       .fetch {
           println("${it[FIRST_NAME]} ${it[LAST_NAME]}")
       }
}

Resulting in

George Orwell
Paulo Coelho

Isn’t that nice? Now, all the FIRST_NAME, LAST_NAME, ID column references are dereferenced from AUTHOR in a much more lenient way.

More info about this feature here

10. Destructuring jOOQ records into sets of local variables

This is so great, we’re going to add more native support for it in jOOQ 3.10 right away.

Kotlin’s type destructuring feature is implemented following convention. This means that any type that has methods with the name component[N] can be destructured. We currently don’t have those methods, but they can be added really easily using inline operators:

operator fun <T, R : Record3<T, *, *>> R.component1() : T {
    return this.value1();
}

operator fun <T, R : Record3<*, T, *>> R.component2() : T {
    return this.value2();
}

operator fun <T, R : Record3<*, *, T>> R.component3() : T {
    return this.value3();
}

These inline functions are added to the R type, which is any subtype of Record3, and then they can be used as such:

for ((first, last, title) in ctx
   .select(a.FIRST_NAME, a.LAST_NAME, b.TITLE)
   .from(a)
   .join(b).on(a.ID.eq(b.AUTHOR_ID))
   .orderBy(1, 2, 3))
       println("$title by $first $last")

The result is again:

1984 by George Orwell
Animal Farm by George Orwell
Brida by Paulo Coelho
O Alquimista by Paulo Coelho

As can be seen, our query returns the type Record3<String, String, String>. Our inline functions will add component1(), component2(), and component3() methods to the type, which are used by the Kotlin language to destructure the record into three local loop variables: (first, last, title).

This is incredibly useful!

(note also that we don’t have to execute the query explicitly in the loop because any jOOQ ResultQuery is Iterable)

More info about this feature here

Conclusion

Kotlin is getting increasingly popular. It contains a lot of smart yet pragmatic features that make daily work with programming on the JVM a lot easier. Kotlin’s high focus on Java interoperability makes it really really powerful, and working with Kotlin and jOOQ is a really productive way of writing SQL on the JVM.

Do give it a shot. The sources are here:

https://github.com/jOOQ/jOOQ/tree/master/jOOQ-examples/jOOQ-kotlin-example

How to Fetch Oracle 12c Implicit Cursors with JDBC and jOOQ

Earlier this week, I’ve blogged about how to execute SQL batches with JDBC and jOOQ. This was useful for the MySQL, SQL Server, and Sybase users among you.

Today, we’ll discuss a slightly more difficult task, how to fetch Oracle 12c implicit cursors – which are essentially the same thing.

What’s an implicit cursor?

Oracle 12c added new procedures to their dynamic SQL API DBMS_SQL. Just run the following query in SQL Developer to see the results:

DECLARE
  c1 sys_refcursor;
  c2 sys_refcursor;
BEGIN
  OPEN c1 FOR SELECT 1 AS a FROM dual;
  dbms_sql.return_result(c1);
  OPEN c2 FOR SELECT 2 AS b FROM dual;
  dbms_sql.return_result(c2);
END;

The anonymous PL/SQL block contains two cursors that are opened and returned to whoever calls this block using DBMS_SQL.RETURN_RESULT. This is kind of magic, as we’re calling a procedure, passing a cursor to it, and somehow, this has a side effect on the client of this program after the program ends.

Not only can you do this in anonymous PL/SQL blocks, you can nest these calls in any procedure, of course. So, in other words, from Oracle 12c onwards, you don’t know for sure if you call a procedure if there will be more results than what you can see. For instance:

BEGIN
  any_procedure();
END;

The above call might just as well yield some implicit cursors. You can’t know for sure.

How to discover implicit cursors with JDBC

With JDBC, if you don’t know for sure what your query will yield as a result, you use the Statement.execute(String), or the PreparedStatement.execute() method to find out. As mentioned in the previous post, this is what you would do:

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;
    }
}

Unfortunately, that won’t work on Oracle as Oracle’s JDBC driver doesn’t implement the JDBC spec correctly. I’ve documented this flaw in length on this Stack Overflow question here.

Using ojdbc, the following “improved” loop needs to be written:

/* Alternatively, use this for non-PreparedStatements:
try (Statement s = cn.createStatement()) {
    Boolean result = s.execute(sql); */
try (PreparedStatement s = cn.prepareStatement(sql)) {
    // Use good old three-valued boolean logic
    Boolean result = s.execute();

    fetchLoop:
    for (int i = 0;; i++) {

        // Check for more results if not already done in 
        // this iteration
        if (i > 0 && result == null)
            result = s.getMoreResults();
        System.out.println(result);

        if (result) {
            result = null;

            try (ResultSet rs = s.getResultSet()) {
                System.out.println("Fetching result " + i);
            }
            catch (SQLException e) {
                // Ignore ORA-17283: No resultset available (1)
                if (e.getErrorCode() == 17283)
                    continue fetchLoop;
                else
                    throw e;
            }
        }
        else if (s.getUpdateCount() == -1)
            // Ignore -1 value if there is one more result! (2)
            if (result = s.getMoreResults())
                continue fetchLoop;
            else
                break fetchLoop;
    }
}

Two elements of the above logic need more explanation:

  1. There’s a possibility of an ORA-17283: No resultset available error being raised when accessing the Statement.getResultSet() despite the previous call to Statement.execute() yielding true. If that happens, we’ll just ignore the error and try fetching another result set
  2. In case we’re using PreparedStatement, the original call to PreparedStatement.execute() will yield false (!) and the Statement.getUpdateCount() value is -1, which would normally mean that we should stop. Not in this case. Let’s just try one more time to get a result set, and tah-dah, here are our implicit result sets.

Note that the algorithm now works with both static Statement and PreparedStatement, which (very unfortunately) behave differently when calling execute().

The above will now work with any SQL statement. In case you’re using the previous SQL statement returning implicit cursors:

String sql =
    "\nDECLARE"
  + "\n  c1 sys_refcursor;"
  + "\n  c2 sys_refcursor;"
  + "\nBEGIN"
  + "\n  OPEN c1 FOR SELECT 1 AS a FROM dual;"
  + "\n  dbms_sql.return_result(c1);"
  + "\n  OPEN c2 FOR SELECT 2 AS a FROM dual;"
  + "\n  dbms_sql.return_result(c2);"
  + "\nEND;";

… you will now be able to fetch all the results:

true
true
Fetching result 1
true
Fetching result 2
false

How to get those cursors with jOOQ?

With jOOQ 3.10 (as always), you don’t need to worry about those low level JDBC details. Just call the following code:

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

And you’ll get a convenient, object oriented representation of your multiple result sets in the form of an org.jooq.Results:

Result set:
+----+
|   A|
+----+
|   1|
+----+
Result set:
+----+
|   A|
+----+
|   2|
+----+

Even better, when you use a code generator to return multiple implicit cursors like this in a stored procedure, just call the generated stored procedure object like this, to get all the cursors automatically:

MyProcedure p = new MyProcedure();
p.setParameter1(x);
p.setParameter2(y);
p.execute(configuration);
Results results = p.getResults();

for (Result<?> result : results)
  for (Record record : result)
    System.out.println(record);

Done!

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!

jOOQ 3.10 will Support SQL Server’s Table Valued Parameters

SQL Server has this nice feature called table-valued parameters (TVP), where users can pass table variables to a stored procedure for bulk data processing. This is particularly nice when the stored procedure is an inline table valued function, i.e. a function that returns a table as well. For instance:

CREATE TYPE numbers AS TABLE (i INTEGER);

CREATE FUNCTION cross_multiply (
  @numbers numbers READONLY
)
RETURNS @result TABLE (
  i1 INTEGER,
  i2 INTEGER,
  product INTEGER
)
AS
BEGIN
  INSERT INTO @result
  SELECT n1.i, n2.i, n1.i * n2.i
  FROM @numbers n1
  CROSS JOIN @numbers n2

  RETURN
END

The above function creates a cross product of a table with itself, and multiplies each possible combination. So, when calling this with the following table argument:

DECLARE @arg NUMBERS;
INSERT INTO @arg VALUES (1),(2),(3),(4);
SELECT * FROM cross_multiply(@arg);

We’re getting the following, nice result:

i1	i2	product
-----------------------
1	1	1
2	1	2
3	1	3
4	1	4
1	2	2
2	2	4
3	2	6
4	2	8
1	3	3
2	3	6
3	3	9
4	3	12
1	4	4
2	4	8
3	4	12
4	4	16

Easy, eh?

Call the above from Java with JDBC

The SQL Server JDBC driver (since recently) supports TVPs if you’re ready to use vendor specific API. If you want to run this T-SQL batch:

DECLARE @arg NUMBERS;
INSERT INTO @arg VALUES (1),(2),(3),(4);
SELECT * FROM cross_multiply(@arg);

In Java, you’d write something along the lines of this:

SQLServerDataTable table = new SQLServerDataTable();
sourceDataTable.addColumnMetadata("i" ,java.sql.Types.INTEGER);
sourceDataTable.addRow(1);  
sourceDataTable.addRow(2);  
sourceDataTable.addRow(3);  
sourceDataTable.addRow(4); 
  
try (SQLServerPreparedStatement stmt=   
    (SQLServerPreparedStatement) connection.prepareStatement(  
       "SELECT * FROM cross_multiply(?)")) {

    // Magic here:
    stmt.setStructured(1, "dbo.numbers", table);  

    try (ResultSet rs = stmt.executeQuery()) {
        ...
    }
}

This is a bit tedious as you have to work through all this API and remember:

  • type names
  • column names
  • column positions

But it works.

Now, call the above from Java, with jOOQ

No problem with jOOQ 3.10. Don’t worry about the boring JDBC data type binding details, as the jOOQ code generator has you covered. As always, all routines are generated classes / methods, and this time, the TABLE type is also a generated type. Let the code speak for itself. Instead of this SQL statement:

DECLARE @arg NUMBERS;
INSERT INTO @arg VALUES (1),(2),(3),(4);
SELECT * FROM cross_multiply(@arg);

You can write the following with jOOQ:

Numbers numbers = new NumbersRecord(
    new NumbersElementTypeRecord(1),
    new NumbersElementTypeRecord(2),
    new NumbersElementTypeRecord(3),
    new NumbersElementTypeRecord(4)
);

// Standalone function call:
Result<CrossMultiplyRecord> r1 = 
    crossMultiply(configuration, numbers);

// Embedded table-valued function call, with predicate
Result<CrossMultiplyRecord> r2 = 
DSL.using(configuration)
   .selectFrom(crossMultiply(numbers))
   .where(F_CROSS_MULTIPLY.PRODUCT.gt(5))
   .fetch();

System.out.println(r1);
System.out.println(r2);

And the nice printed output will be:

+----+----+-------+
|  i1|  i2|product|
+----+----+-------+
|   1|   1|      1|
|   2|   1|      2|
|   3|   1|      3|
|   4|   1|      4|
|   1|   2|      2|
|   2|   2|      4|
|   3|   2|      6|
|   4|   2|      8|
|   1|   3|      3|
|   2|   3|      6|
|   3|   3|      9|
|   4|   3|     12|
|   1|   4|      4|
|   2|   4|      8|
|   3|   4|     12|
|   4|   4|     16|
+----+----+-------+

+----+----+-------+
|  i1|  i2|product|
+----+----+-------+
|   3|   2|      6|
|   4|   2|      8|
|   2|   3|      6|
|   3|   3|      9|
|   4|   3|     12|
|   2|   4|      8|
|   3|   4|     12|
|   4|   4|     16|
+----+----+-------+

Not only does jOOQ understand table-valued parameters, since jOOQ 3.5, we have also supported table-valued functions, which can be used like any ordinary table:

Result<CrossMultiplyRecord> r2 = 
DSL.using(configuration)
   .selectFrom(crossMultiply(numbers))
   .where(F_CROSS_MULTIPLY.PRODUCT.gt(5))
   .fetch();

As you can see, the function call can be embedded in the from clause, it even returns safely-typed CrossMultiplyRecord elements (if you’re not using any projection), and you can form predicates on table columns (i.e. function return values), you can join the table, etc.

Excellent! Let’s start using table-valued parameters!

How to Write a Quick and Dirty Converter in jOOQ

One of jOOQ‘s most powerful features is the capability of introducing custom data types, pretending the database actually understands them. For instance, when working with SQL TIMESTAMP types, users mostly want to use the new JSR-310 LocalDateTime, rather than the JDBC java.sql.Timestamp type.

In jOOQ 3.9+, this is a no brainer, as we’ve finally introduced the <javaTimeTypes> flag to automatically generate JSR 310 types instead of JDBC types. But sometimes, you want some custom conversion behaviour, so you write a Converter.

To the rescue our new jOOQ 3.9+ converter constructors, which essentially take two lambdas to construct a converter for you. For instance:

Converter<Timestamp, LocalDateTime> converter =
Converter.of(
    Timestamp.class,
    LocalDateTime.class,
    t -> t == null ? null : t.toLocalDateTime(),
    u -> u == null ? null : Timestamp.valueOf(u)
);

And you’re set! Even easier, if you don’t need any special null encoding (as above), just write this equivalent converter, instead:

Converter<Timestamp, LocalDateTime> converter =
Converter.ofNullable(
    Timestamp.class,
    LocalDateTime.class,
    Timestamp::toLocalDateTime
    Timestamp::valueOf
);

Where’s that useful? The code generator needs a concrete converter class, so you cannot use that with the code generator, but there are many other places in the jOOQ API where converters are useful, including when you write plain SQL like this:

DSL.field(
    "my_table.my_timestamp", 
    SQLDataType.TIMESTAMP.asConvertedDataType(
        Converter.ofNullable(...)
));

How to Prevent JDBC Resource Leaks with JDBC and with jOOQ

In a recent consulting gig, I was analysing a client’s connection pool issue in a productive system, where during some peak loads, all the Java processes involving database interactions just started queueing up until nothing really worked anymore. No exceptions, though, and when the peak load was gone in the evening, everything returned back to normal. The database load looked pretty healthy at the time, so no actual database problem was involved – the problem had to be a client side problem.

Weblogic operations teams quickly identified the connection pool to be the bottleneck. All the connections were constantly allocated to some client process. The immediate thought was: A resource leak is happeneing, and it didn’t show before because this was an exceptional situation: Around the beginning of the new year when everyone wanted to download their electronic documents from the bank (and some new features introduced many more document related database calls).

The obvious problem

That particular system still runs a lot of legacy code in Java 6 style, which means, there are tons of code elements of the following kind:

Connection connection = null;
try {

  // Get the connection from the pool through JNDI
  connection = JDBCHelper.getConnection();
}
finally {

  // Release the connection
  JDBCHelper.close(connection);  
}

While the above code is perfectly fine, and 99% of all database interactions were of the above type, there was an occasional instance of someone badly copy-pasting some code and doing something like this:

Connection connection = JDBCHelper.getConnection();
PreparedStatement stmt = null;

try {
  stmt = connection.prepareStatement("SELECT ...");
}
finally {

  // Release the statement
  JDBCHelper.close(stmt);
}

// But the connection is never released

Sometimes, things were even more subtle, as a utility method expected a connection like this:

// Utility method doesn't have to close the connection:
public void databaseCalls(Connection connection) {
  try {
    stmt = connection.prepareStatement("SELECT ...");
  }
  finally {

    // Release the statement
    JDBCHelper.close(stmt);
  }
}

public void businessLogic() {
  // Oops, subtle connection leak
  databaseCalls(JDBCHelper.getConnection());
}

Thoroughly fixing these things

There’s a quick fix to all these problems. The easiest fix is to just continue rigorously using the JDBCHelper.close() method (or just call connection.close() with appropriate error handling) every time. But apparently, that’s not easy enough as there will always be a non-vigilant developer (or a junior developer who doesn’t know these things), who will get it wrong, who will simply forget things.

I mean, even the official JDBC tutorial gets it “wrong” on their first page:
https://docs.oracle.com/javase/tutorial/jdbc/overview/index.html

The bad example being:

public void connectToAndQueryDatabase(
    String username, String password) {

    Connection con = DriverManager.getConnection(
                         "jdbc:myDriver:myDatabase",
                         username,
                         password);

    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery(
        "SELECT a, b, c FROM Table1");

    while (rs.next()) {
        int x = rs.getInt("a");
        String s = rs.getString("b");
        float f = rs.getFloat("c");
    }
}

All resources leak in this example!

Of course, it’s just an example, and of course, it’s not a terrible situation, because resources can usually clean up themselves when they go out of scope, i.e. when the GC kicks in. But as software engineers we shouldn’t rely on that, and as the productive issues have shown, there are always edge cases, where precisely this lack of vigilance will cause great harm. After all,

It works on my machine

… is simply not an excuse. We should design our software for productive use.

Fix #1: Use try-with-resources. Always

If you want to stay on the safe side, always follow this rule:

The scope that acquires the resource, closes the resource

As long as you’re working with JDBC, save yourself the trouble of writing those JDBCUtilities classes that close non-null resources and safely catch exceptions that may arise. Just use try-with-resources, all the time. For instance, take the example from the Oracle JDBC tutorial, which should read:

public void connectToAndQueryDatabase(
     String username, String password) {

    // All of these resources are allocated in this method. Thus,
    // this method's responsibility is to also close / free all
    // these resources.
    try (Connection con = DriverManager.getConnection(
            "jdbc:myDriver:myDatabase", username, password);
         Statement stmt = con.createStatement();
         ResultSet rs = stmt.executeQuery(
            "SELECT a, b, c FROM Table1")) {

        while (rs.next()) {
            int x = rs.getInt("a");
            String s = rs.getString("b");
            float f = rs.getFloat("c");
        }
    }
}

This already feels that much better and cleaner, doesn’t it? All the resources are acquired in the above method, and the try-with-resources block will close all of them when they go out of scope. It’s just syntax sugar for something we’ve been doing manually all the time. But now, we will (hopefully) never again forget!

Of course, you could introduce automatic leak detection in your integration tests, because it’s rather easy to proxy the JDBC DataSource and count all connection acquisitions and closings. An example can be seen in this post:
The best way to detect database connection leaks

Fix #2: Use jOOQ, which manages resources for you

Historically, JDBC works on lazy resources that are kept around for a while. The assumption in 1997 (when JDBC was introduced) was that database interactions were relatively slow and it made sense to fetch and process one record at a time, even for moderately sized result sets.

In fact, it was even common to abort fetching records from a cursor when we’ve had enough results and close it eagerly before consuming all the rows.

Today, these assumptions are (mostly) no longer true, and jOOQ (like other, more modern database APIs) invert the lazy/eager API default behaviour. In jOOQ, the JDBC types have the following corresponding counterparts:

  • JDBC DataSource / Connection => jOOQ ConnectionProvider:
    jOOQ doesn’t know the concept of an “open connection” like JDBC. jOOQ only has this ConnectionProvider which works in a similar way to JDBC’s / JavaEE’s DataSource. The semantics here is that the connection / session is “managed” and jOOQ will acquire / release it once per statement. This happens automatically, so users don’t have to worry about any connection resource.
  • JDBC Statement (and subtypes) => jOOQ Query:
    While the JDBC statement (especially the PreparedStatement) is a resource that binds some server-side objects, such as an execution plan, for instance, jOOQ again doesn’t have such a resourceful thing. The Query just wraps the SQL string (or AST) and bind variables. All resources are created lazily only when the query is actually executed – and released immediately after execution. Again, users don’t have to worry about any statement resource.
  • JDBC ResultSet => jOOQ Result:
    The JDBC ResultSet corresponds to a server-side cursor, another object that possibly binds quite a few resources, depending on your fetch mode. Again, in jOOQ no resources are bound / exposed, because jOOQ by default eagerly fetches your entire result set – the assumption being that a low-level optimisation here doesn’t add much value for moderately sized result sets

With the above inverted defaults (from lazy to eager resource allocation / freeing), the jOOQ-ified Oracle JDBC tutorial code would look like this:

Working with a standalone Connection

public void connectToAndQueryDatabase(
    String username, String password) {

    // If you're using a standalone connection, you can pass that
    // one to jOOQ, but you're still responsible of closing it
    // again:
    try (Connection con = DriverManager.getConnection(
            "jdbc:myDriver:myDatabase", username, password)) {

        // There is no statment resource anymore, and the result
        // is fetched eagerly from the database, so you don't have
        // to worry about it
        for (Record record : DSL.using(con).fetch(
                "SELECT a, b, c FROM Table1")) {
            int x = record.get("a", int.class);
            String s = record.get("b", String.class);
            float f = record.get("c", float.class);
        }
    }
}

Working with a connection pool / DataSource

// You probably have some means of injecting / discovering
// a JDBC DataSource, e.g. from Spring, or from your JavaEE
// container, etc.
@Inject
DataSource ds;

public void connectToAndQueryDatabase(
    String username, String password) {

    // With a DataSource, jOOQ will automatically acquire and
    // close the JDBC Connection for you, so the last remaining
    // resource has also disappeared from your client code.
    for (Record record : DSL
           .using(ds, SQLDialect.ORACLE)
           .fetch("SELECT a, b, c FROM Table1")) {
        int x = record.get("a", int.class);
        String s = record.get("b", String.class);
        float f = record.get("c", float.class);
    }
}

With jOOQ, all resource management is automatic, by default, because by default, you don’t want to worry about this low level stuff. It’s not 1997 anymore. The JDBC API really is too low level for most use-cases.

If you do want to optimise resource management and not fetch everything eagerly, you can, of course. jOOQ will allow you to fetch your results lazily, in two ways:

Using a Cursor

@Inject
DataSource ds;

public void connectToAndQueryDatabase(
    String username, String password) {

    // jOOQ's Cursor type is a resource, just like JDBC's
    // ResultSet. It actually keeps a reference to an open
    // ResultSet, internally. This is an opt-in
    // feature, though, only to be used if desired.
    try (Cursor<Record> cursor : DSL
            .using(ds, SQLDialect.ORACLE)
            .fetchLazy("SELECT a, b, c FROM Table1")) {

        for (Record record : cursor) {
            int x = record.get("a", int.class);
            String s = record.get("b", String.class);
            float f = record.get("c", float.class);
        }
    }
}

Using a Java 8 Stream (lazy, resourceful version)

@Inject
DataSource ds;

public void connectToAndQueryDatabase(
    String username, String password) {

    // This can also work with a stream
    try (Stream<Record> stream : DSL
        .using(ds, SQLDialect.ORACLE)
        .fetchStream("SELECT a, b, c FROM Table1")) {

        stream.forEach(record -> {
            int x = record.get("a", int.class);
            String s = record.get("b", String.class);
            float f = record.get("c", float.class);
        });
    }
}

Unfortunately, there are no auto-closing streams in Java, which is why we have to resort to using the try-with-resources statement, breaking the fluency of jOOQ’s API.

Do note though, that you can use the Stream API in an eager fashion:

Using a Java 8 Stream (eager version)

@Inject
DataSource ds;

public void connectToAndQueryDatabase(
    String username, String password) {

    // Fetch the jOOQ Result eagerly into memory, then stream it
    // Again, no resource management
    DSL.using(ds, SQLDialect.ORACLE)
       .fetch()
       .stream("SELECT a, b, c FROM Table1")
       .forEach(record -> {
            int x = record.get("a", int.class);
            String s = record.get("b", String.class);
            float f = record.get("c", float.class);
        });
}

Conclusion

Developers, unfortunately, often suffer from

Works on my machine

This leads to problems that can be discovered only in production, under load. When it comes to resources, it is important to constantly remind ourselves that …

The scope that acquires the resource, closes the resource

JDBC (and the JDK’s IO APIs), “unfortunately”, deal with resources on a very low level. This way, their default behaviour is very resource-efficient. For instance, when you only need to read a file header, you don’t load the entire file into memory through the InputStream. You can explicitly, manually, only load the first few lines.

But in many applications, this default and its low level nature gets in the way of correctness (accidental resource leaks are easy to create), and convenience (a lot of boiler plate code needs to be written).

With database interactions, it’s usually best to migrate your JDBC code towards a more modern API like jOOQ, which abstracts resource handling away in its API and inverts the lazy/eager semantics: Eager by default, lazy on demand.

More information about the differences between jOOQ and JDBC can be seen here, in the manual.