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 = ctx.select(BOOK.TITLE) .from(BOOK) .fetch(); 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?
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:
for (Record1<String> record : ctx .select(BOOK.TITLE) .from(BOOK) ) // ...
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 -- ... END 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
The JDK 8 added
ResultQuery inherits, so you can do this just as well:
ctx.select(BOOK.TITLE) .from(BOOK) .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 .select(BOOK.TITLE) .from(BOOK) .where(BOOK.ID.eq(1));
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:
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!
r.toString() call is
NullPointerException safe, because if the record did not exist a
NoDataFoundException would have been thrown.
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:
try (Cursor<Record1<String>> cursor = ctx .select(BOOK.TITLE) .from(BOOK) .fetchLazy() ) for (Record1<String> record : cursor) // ...
Cursor<R> still extends
Iterable<R>but you can fetch records also manually from it, eg
Record record; while ((record = cursor.fetchNext()) != null) // ...
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 (Stream<Record1<String>> stream = ctx .select(BOOK.TITLE) .from(BOOK) .fetchStream() ) stream.forEach(record -> // ... );
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
So, you’ll have to break your fluent pipeline with the
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:
ctx.select(BOOK.TITLE) .from(BOOK) .fetch() .stream() .forEach(record -> // ... );
Starting with jOOQ 3.11, both
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());
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.
<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 = ctx.select(BOOK.ID, BOOK.TITLE) .from(BOOK) .collect(Records.intoMap());
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 = ctx.select(BOOK.ID, BOOK.TITLE) .from(BOOK) .fetchMap(BOOK.ID, BOOK.TITLE);
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
MULTISETnested collections. An example has been given here, where a nested collection was also mapped into such a
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 .select(BOOK.TITLE) .from(BOOK) );
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) // ...
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.