The jOOQ API is all about comfort, and as such, an vital operation (an important one?) like fetch() should include comfort, too. The default method to fetch information is that this:
End result<Record1<String>> outcome =
ctx.choose(BOOK.TITLE)
.from(BOOK)
.fetch();
for (Record1<String> report : outcome) {
// ...
}
It fetches the whole outcome set into reminiscence and closes the underlying JDBC sources eagerly. However what different choices do we have now?
Iterable fetching
Within the above instance, the fetch() name wasn’t strictly essential. jOOQ’s ResultQuery<R> sort conveniently extends Iterable<R>, which implies that a name to ResultQuery.iterator() may also execute the question. This may be performed primarily in two methods:
Exterior iteration:
for (Record1<String> report : ctx
.choose(BOOK.TITLE)
.from(BOOK)
) {
// ...
}
That is significantly good as a result of it feels similar to PL/SQL or PL/pgSQL’s FOR loop for implicit cursors:
FOR rec IN (SELECT guide.title FROM guide) LOOP
-- ...
END LOOP;
This nonetheless has to fetch the whole outcome set into reminiscence, although, as a result of there isn’t a for-with-resources syntax in Java that mixes the foreach syntax with a try-with-resources syntax.
Inner iteration:
The JDK 8 added Iterable::forEach, which jOOQ’s ResultQuery inherits, so you are able to do this simply as effectively:
ctx.choose(BOOK.TITLE)
.from(BOOK)
.forEach(report -> {
// ...
});
The 2 are completely equal.
Single report fetching
In case you’re certain you’re going to fetch solely a single worth, no must materialise an inventory. Simply use one of many following strategies. Given this question:
ResultQuery<Record1<String>> question = ctx
.choose(BOOK.TITLE)
.from(BOOK)
.the place(BOOK.ID.eq(1));
Now you can:
Fetch a nullable report:
This fetches a nullable report, i.e. if the report hasn’t been discovered, null is produced. If there are a couple of data, a TooManyRowsException is thrown.
Record1<String> r = question.fetchOne();
Fetch an non-obligatory report:
The null bikeshed is actual, so why preserve you from bikeshedding additionally when working with jOOQ? Precisely equal to the above, however utilizing a unique model, is that this:
Non-obligatory<Record1<String>> r = question.fetchOptional();
Fetch a single report:
If you understand your question produces precisely one report, there’s the time period “single” in jOOQ’s API which suggests precisely one:
Record1<String> r = question.fetchSingle();
println(r.toString()); // NPE protected!
The r.toString() name is NullPointerException protected, as a result of if the report didn’t exist a NoDataFoundException would have been thrown.
Resourceful fetching
The default is to eagerly fetch every part into reminiscence, as that’s doubtless extra helpful to most purposes than JDBC’s default of managing sources on a regular basis (together with nested collections, lobs, and many others.). As may very well be seen within the above Iterator fetching instance, it’s usually the one doable method that doesn’t produce unintentional useful resource leaks, on condition that customers can’t even entry the useful resource (by default) by way of jOOQ.
However it isn’t at all times the fitting selection, so you may alternatively preserve open underlying JDBC sources whereas fetching information, in case your information set is giant. There are 2 fundamental methods:
Crucial:
By calling ResultQuery.fetchLazy(), you’re making a Cursor<R>, which wraps the underlying JDBC ResultSet, and thus, must be contained in a try-with-resources assertion:
attempt (Cursor<Record1<String>> cursor = ctx
.choose(BOOK.TITLE)
.from(BOOK)
.fetchLazy()
) {
for (Record1<String> report : cursor) {
// ...
}
}
The Cursor<R> nonetheless extends Iterable<R>, however you may fetch data additionally manually from it, e.g.
File report;
whereas ((report = cursor.fetchNext()) != null) {
// ...
}
Useful:
If the Stream API is extra such as you wish to work with information, simply name ResultQuery.fetchStream() as an alternative, then (however don’t neglect to wrap that in try-with-resources, too!):
attempt (Stream<Record1<String>> stream = ctx
.choose(BOOK.TITLE)
.from(BOOK)
.fetchStream()
) {
stream.forEach(report -> {
// ...
});
}
Or, use Stream::map, Stream::cut back, or no matter. Regrettably, the Stream API isn’t auto-closing. Whereas it could have been doable to implement the API this fashion, its “escape hatches,” like Stream.iterator() would nonetheless stop auto-closing behaviour (at the very least until many extra options had been launched, resembling e.g. an AutoCloseableIterator, or no matter).
So, you’ll have to interrupt your fluent pipeline with the try-with-resources assertion.
Useful, however not resourceful
In fact, you may at all times name fetch() first, then stream later, as a way to stream the information out of your reminiscence straight. If resourcefulness isn’t vital (i.e. the efficiency influence is negligible as a result of the outcome set isn’t huge), you may write this:
ctx.choose(BOOK.TITLE)
.from(BOOK)
.fetch()
.stream()
.forEach(report -> {
// ...
});
Or use Stream::map, Stream::cut back, or no matter
Collector fetching
Beginning with jOOQ 3.11, each ResultQuery::accumulate and Cursor::accumulate had been added. The JDK Collector API is extraordinarily poweful. It doesn’t get the eye it deserves (exterior of the Stream API). For my part, there must be an Iterable::accumulate technique, as it could make sense to re-use Collector sorts on any assortment, e.g.
Set<String> s = Set.of(1, 2, 3);
Record<String> l = s.accumulate(Collectors.toList());
Why not? Collector is form of a twin to the Stream API itself. The operations aren’t composed in a pipelined syntax, however in a nested syntax. Apart from that, to me at the very least, it feels fairly related.
In case of jOOQ, they’re very highly effective. jOOQ presents just a few helpful out-of-the-box collectors in Information. Let me showcase Information.intoMap(), which has this overload, for instance:
<Okay,V,R extends Record2<Okay,V>> Collector<R,?,Map<Okay,V>> intoMap()
The fascinating bit right here is that it captures the kinds of a Record2 sort as the important thing and worth sort of the ensuing map. A easy generic trick to verify it really works provided that you undertaking precisely 2 columns, for instance:
Map<Integer, String> books =
ctx.choose(BOOK.ID, BOOK.TITLE)
.from(BOOK)
.accumulate(Information.intoMap());
That is utterly sort protected. You may’t undertaking 3 columns, or the mistaken column sorts because of all these generics. That is extra handy than the equal that’s obtainable on the ResultQuery API straight, the place you must repeat the projected column expressions:
Map<Integer, String> books =
ctx.choose(BOOK.ID, BOOK.TITLE)
.from(BOOK)
.fetchMap(BOOK.ID, BOOK.TITLE);
With the ResultQuery::accumulate and Cursor::accumulate APIs, you should utilize any arbitrary collector, together with your individual, which is admittedly very highly effective! Additionally, it removes the necessity for the middleman End result information construction, so it doesn’t need to fetch every part into reminiscence (until your Collector does it anyway, after all).
Collectors are significantly helpful when gathering
MULTISETnested collections. An instance has been given right here, the place a nested assortment was additionally mapped into such aMap<Okay, V>.
Reactive fetching
Ranging from jOOQ 3.15, R2DBC has been supported. Because of this ResultQuery<R> is now additionally a reactive streams Writer<R> (each the reactive-streams API and the JDK 9 Move API are supported for higher interoperability).
So, simply decide your favorite reactive streams API of selection, e.g. reactor, and stream jOOQ outcome units reactively like this:
Flux<Record1<String>> flux = Flux.from(ctx
.choose(BOOK.TITLE)
.from(BOOK)
);
Many fetching
Final however not least, there are uncommon instances when your question produces a couple of outcome set. This was fairly en vogue in SQL Server and associated RDBMS, the place saved procedures may produce cursors. MySQL and Oracle even have the characteristic. For instance:
Outcomes outcomes = ctx.fetch("sp_help");
for (End result<?> outcome : outcomes) {
for (File report : outcome) {
// ...
}
}
The usual foreach loop will solely iterate outcomes, however you may also entry the interleaved row counts utilizing Outcomes.resultsOrRows() if that’s of curiosity to you as effectively.
Conclusion
Comfort and developer consumer expertise is on the core of jOOQ’s API design. Like all good assortment API, jOOQ presents quite a lot of composable primitives that enable for extra successfully integrating SQL into your software.
SQL is only a description of a knowledge construction. jOOQ helps describe that information construction in a kind protected manner on the JVM. It’s pure for additional processing to be doable in an equally sort protected manner, as we’re used to from the JDK’s personal assortment APIs, or third events like jOOλ, vavr, streamex, and many others.
