The Many Different Ways to Fetch Data in jOOQ – Java, SQL and jOOQ.


The jOOQ API is all about convenience, and as such, an important operation (the most important one?) Like fetch() must come with convenience, too. The default way to fetch data is this:

Result<Record1<String>> result =

for (Record1<String> record : result) 
    // ...

It fetches the entire result set into memory and closes the underlying JDBC resources eagerly. But what other options do we have?

Iterable fetching

In the above example, the fetch() call was not strictly necessary. jOOQ’s ResultQuery<R> type conveniently extends Iterable<R>which means that a call to ResultQuery.iterator() will also execute the query. This can be done mainly in two ways:

External iteration:

for (Record1<String> record : ctx
    // ...

This is particularly nice because it feels just like PL / SQL or PL / pgSQL’s FOR loop for implicit cursors:

FOR rec IN (SELECT book.title FROM book) LOOP
  -- ...

This still has to fetch the entire result set into memory, though, because there is not a for-with-resources syntax in Java that combines the foreach syntax with a try-with-resources syntax.

Internal iteration:

The JDK 8 added Iterable::forEachwhich jOOQ’s ResultQuery inherits, so you can do this just as well:
   .forEach(record -> 
       // ...

The two are perfectly equivalent.

Single record fetching

If you’re sure you’re going to fetch only a single value, no need to materialize a list. Just use one of the following methods. Given this query:

ResultQuery<Record1<String>> query = ctx

You can now:

Fetch a nullable record:

This fetches a nullable record, ie if the record has not been found, null is produced. If there are more than one records, a TooManyRowsException is thrown.

Record1<String> r = query.fetchOne();

Fetch an optional record:

The null bikeshed is real, so why keep you from bikeshedding also when working with jOOQ? Exactly equivalent to the above, but using a different style, is this:

Optional<Record1<String>> r = query.fetchOptional();

Fetch a single record:

If you know your query produces exactly one record, there’s the term “single” in jOOQ’s API which means exactly one:

Record1<String> r = query.fetchSingle();
println(r.toString()); // NPE safe!

The r.toString() call is NullPointerException safe, because if the record did not exist a NoDataFoundException would have been thrown.

Resourceful fetching

The default is to eagerly fetch everything into memory, as that is likely more useful to most applications than JDBC’s default of managing resources all the time (including nested collections, lobs, etc.). As could be seen in the above Iterator fetching example, it is often the only possible approach that does not produce accidental resource leaks, given that users can not even access the resource (by default) via jOOQ.

But it is not always the right choice, so you can alternatively keep open underlying JDBC resources while fetching data, if your data set is large. There are 2 main ways:


By calling ResultQuery.fetchLazy()you’re creating a Cursor<R>which wraps the underlying JDBC ResultSetand thus, should be contained in a try-with-resources statement:

try (Cursor<Record1<String>> cursor = ctx
    for (Record1<String> record : cursor) 
        // ...

The Cursor<R> still extends Iterable<R>but you can fetch records also manually from it, eg

Record record;

while ((record = cursor.fetchNext()) != null) 
    // ...


If the Stream API is more like you want to work with data, just call ResultQuery.fetchStream() instead, then (but do not forget to wrap that in try-with-resourcestoo!):

try (Stream<Record1<String>> stream = ctx
    stream.forEach(record -> 
        // ...

Or, use Stream::map, Stream::reduce, or whatever. Regrettably, the Stream API is not auto-closing. While it would have been possible to implement the API this way, its “escape hatches,” like Stream.iterator() would still prevent auto-closing behavior (at least unless many more features had been introduced, such as eg an AutoCloseableIteratoror whatever).

So, you’ll have to break your fluent pipeline with the try-with-resources statement.

Functional, but not resourceful

Of course, you can always call fetch() first, then stream later, in order to stream the data from your memory directly. If resourcefulness is not important (ie the performance impact is negligible because the result set is not big), you can write this:
   .forEach(record -> 
       // ...

Or use Stream::map, Stream::reduceor whatever

Collector fetching

Starting with jOOQ 3.11, both ResultQuery::collect and Cursor::collect had been added. The JDK Collector API is extremely poweful. It does not get the attention it deserves (outside of the Stream API). In my opinion, there should be an Iterable::collect method, as it would make sense to re-use Collector types on any collection, eg

Set<String> s = Set.of(1, 2, 3);
List<String> l = s.collect(Collectors.toList());

Why not? Collector is kind of a dual to the Stream API itself. The operations aren’t composed in a pipelined syntax, but in a nested syntax. Other than that, to me at least, it feels quite similar.

In case of jOOQ, they’re very powerful. jOOQ offers a few useful out-of-the-box collectors in Records. Let me showcase Records.intoMap()which has this overload, for example:

<K,V,R extends Record2<K,V>> Collector<R,?,Map<K,V>> intoMap()

The interesting bit here is that it captures the types of a Record2 type as the key and value type of the resulting map. A simple generic trick to make sure it works only if you project exactly 2 columns, for example:

Map<Integer, String> books =, BOOK.TITLE)

This is completely type safe. You can not project 3 columns, or the wrong column types thanks to all those generics. This is more convenient than the equivalent that is available on the ResultQuery API directly, where you have to repeat the projected column expressions:

Map<Integer, String> books =, BOOK.TITLE)
   .fetchMap(BOOK.ID, BOOK.TITLE);

With the ResultQuery::collect and Cursor::collect APIs, you can use any arbitrary collector, including your own, which is really very powerful! Also, it removes the need for the intermediary Result data structure, so it does not have to fetch everything into memory (unless your Collector does it anyway, of course).

Collectors are particularly useful when collecting MULTISET nested collections. An example has been given here, where a nested collection was also mapped into such a Map<K, V>.

Reactive fetching

Starting from jOOQ 3.15, R2DBC has been supported. This means that ResultQuery<R> is now also a reactive streams Publisher<R> (both the reactive-streams API and the JDK 9 Flow APIs are supported for better interoperability).

So, just pick your favorite reactive streams API of choice, eg reactor, and stream jOOQ result sets reactively like this:

Flux<Record1<String>> flux = Flux.from(ctx

Many fetching

Last but not least, there are rare cases when your query produces more than one result set. This used to be quite in vogue in SQL Server and related RDBMS, where stored procedures could produce cursors. MySQL and Oracle also have the feature. For example:

Results results = ctx.fetch("sp_help");

for (Result<?> result : results) 
    for (Record record : result) 
        // ...

The standard foreach loop will only iterate results, but you can also access the interleaved row counts using Results.resultsOrRows() if that is of interest to you as well.


Convenience and developer user experience is at the core of jOOQ’s API design. Like any good collection API, jOOQ offers a variety of composable primitives that allow for more effectively integrating SQL into your application.

SQL is just a description of a data structure. jOOQ helps describe that data structure in a type safe way on the JVM. It is natural for further processing to be possible in an equally type safe way, as we’re used to from the JDK’s own collection APIs, or third parties like jOOλ, vavr, streamexetc.



Please enter your comment!
Please enter your name here