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?
Iterable fetching
In the above example, the fetch() call wasn’t 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
.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 isn’t 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::forEach, which jOOQ’s 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 materialise 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, i.e. if the record hasn’t 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 didn’t 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 doesn’t produce accidental resource leaks, given that users can’t even access the resource (by default) via jOOQ.
But it isn’t 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:
Imperative:
By calling ResultQuery.fetchLazy(), you’re creating a Cursor<R>, which wraps the underlying JDBC ResultSet, and thus, should be contained in a try-with-resources statement:
try (Cursor<Record1<String>> cursor = ctx
.select(BOOK.TITLE)
.from(BOOK)
.fetchLazy()
) {
for (Record1<String> record : cursor) {
// ...
}
}
The Cursor<R> still extends Iterable<R>, but you can fetch records also manually from it, e.g.
Record record;
while ((record = cursor.fetchNext()) != null) {
// ...
}
Functional:
If the Stream API is more like you want to work with data, just call ResultQuery.fetchStream() instead, then (but don’t forget to wrap that in try-with-resources, too!):
try (Stream<Record1<String>> stream = ctx
.select(BOOK.TITLE)
.from(BOOK)
.fetchStream()
) {
stream.forEach(record -> {
// ...
});
}
Or, use Stream::map, Stream::reduce, or whatever. Regrettably, the Stream API isn’t 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 behaviour (at least unless many more features had been introduced, such as e.g. an AutoCloseableIterator, or 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 isn’t important (i.e. the performance impact is negligible because the result set isn’t big), you can write this:
ctx.select(BOOK.TITLE)
.from(BOOK)
.fetch()
.stream()
.forEach(record -> {
// ...
});
Or use Stream::map, Stream::reduce, or 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 doesn’t 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, e.g.
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 =
ctx.select(BOOK.ID, BOOK.TITLE)
.from(BOOK)
.collect(Records.intoMap());
This is completely type safe. You can’t 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);
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 doesn’t 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 aMap<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 API are supported for better interoperability).
So, just pick your favourite reactive streams API of choice, e.g. reactor, and stream jOOQ result sets reactively like this:
Flux<Record1<String>> flux = Flux.from(ctx
.select(BOOK.TITLE)
.from(BOOK)
);
Many fetching
Last but not least, there are rare cases when your query produces more than one result set. This used to be quite en 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.
Conclusion
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, streamex, etc.
