The jOOQ API is all about comfort, and as such, an vital operation (crucial one?) like fetch() should include comfort, too. The default technique to fetch information is that this:
Outcome> end result =
ctx.choose(BOOK.TITLE)
.from(BOOK)
.fetch();
for (Record1 report : end result) {
// ...
}
It fetches your complete end result set into reminiscence and closes the underlying JDBC assets eagerly. However what different choices do we’ve got?
Iterable fetching
Within the above instance, the fetch() name wasn’t strictly obligatory. jOOQ’s ResultQuery sort conveniently extends Iterable, which signifies that a name to ResultQuery.iterator() may even execute the question. This may be executed primarily in two methods:
Exterior iteration:
for (Record1 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 ebook.title FROM ebook) LOOP
-- ...
END LOOP;
This nonetheless has to fetch your complete end result 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.
Inside iteration:
The JDK 8 added Iterable::forEach, which jOOQ’s ResultQuery inherits, so you are able to do this simply as properly:
ctx.choose(BOOK.TITLE)
.from(BOOK)
.forEach(report -> {
// ...
});
The 2 are completely equal.
Single report fetching
In the event you’re positive you’re going to fetch solely a single worth, no must materialise a listing. Simply use one of many following strategies. Given this question:
ResultQuery> 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 multiple information, a TooManyRowsException is thrown.
Record1 r = question.fetchOne();
Fetch an non-compulsory report:
The null bikeshed is actual, so why hold you from bikeshedding additionally when working with jOOQ? Precisely equal to the above, however utilizing a distinct fashion, is that this:
Elective> r = question.fetchOptional();
Fetch a single report:
If you recognize your question produces precisely one report, there’s the time period “single” in jOOQ’s API which suggests precisely one:
Record1 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 probably extra helpful to most purposes than JDBC’s default of managing assets on a regular basis (together with nested collections, lobs, and so forth.). As could possibly be seen within the above Iterator fetching instance, it’s typically the one potential method that doesn’t produce unintended 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 possibly can alternatively hold open underlying JDBC assets whereas fetching information, in case your information set is giant. There are 2 essential methods:
Crucial:
By calling ResultQuery.fetchLazy(), you’re making a Cursor, which wraps the underlying JDBC ResultSet, and thus, ought to be contained in a try-with-resources assertion:
attempt (Cursor> cursor = ctx
.choose(BOOK.TITLE)
.from(BOOK)
.fetchLazy()
) {
for (Record1 report : cursor) {
// ...
}
}
The Cursor nonetheless extends Iterable, however you possibly can fetch information additionally manually from it, e.g.
File report;
whereas ((report = cursor.fetchNext()) != null) {
// ...
}
Useful:
If the Stream API is extra such as you need to work with information, simply name ResultQuery.fetchStream() as an alternative, then (however don’t overlook to wrap that in try-with-resources, too!):
attempt (Stream> 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 will have been potential to implement the API this fashion, its “escape hatches,” like Stream.iterator() would nonetheless forestall auto-closing behaviour (at the very least until many extra options had been launched, reminiscent of 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 possibly can at all times name fetch() first, then stream later, with a purpose to stream the info out of your reminiscence immediately. If resourcefulness isn’t vital (i.e. the efficiency impression is negligible as a result of the end result set isn’t large), you possibly can 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::acquire and Cursor::acquire 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 ought to be an Iterable::acquire technique, as it will make sense to re-use Collector sorts on any assortment, e.g.
Set s = Set.of(1, 2, 3);
Checklist l = s.acquire(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. Aside from that, to me at the very least, it feels fairly related.
In case of jOOQ, they’re very highly effective. jOOQ provides just a few helpful out-of-the-box collectors in Data. Let me showcase Data.intoMap(), which has this overload, for instance:
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 venture precisely 2 columns, for instance:
Map books =
ctx.choose(BOOK.ID, BOOK.TITLE)
.from(BOOK)
.acquire(Data.intoMap());
That is utterly sort protected. You possibly can’t venture 3 columns, or the incorrect column sorts due to all these generics. That is extra handy than the equal that’s out there on the ResultQuery API immediately, the place it’s a must to repeat the projected column expressions:
Map books =
ctx.choose(BOOK.ID, BOOK.TITLE)
.from(BOOK)
.fetchMap(BOOK.ID, BOOK.TITLE);
With the ResultQuery::acquire and Cursor::acquire APIs, you should utilize any arbitrary collector, together with your personal, which is actually very highly effective! Additionally, it removes the necessity for the middleman Outcome information construction, so it doesn’t need to fetch every part into reminiscence (until your Collector does it anyway, in fact).
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.
Reactive fetching
Ranging from jOOQ 3.15, R2DBC has been supported. Which means ResultQuery is now additionally a reactive streams Writer (each the reactive-streams API and the JDK 9 Circulation API are supported for higher interoperability).
So, simply decide your favorite reactive streams API of selection, e.g. reactor, and stream jOOQ end result units reactively like this:
Flux> flux = Flux.from(ctx
.choose(BOOK.TITLE)
.from(BOOK)
);
Many fetching
Final however not least, there are uncommon circumstances when your question produces multiple end result set. This was once fairly en vogue in SQL Server and associated RDBMS, the place saved procedures might produce cursors. MySQL and Oracle even have the characteristic. For instance:
Outcomes outcomes = ctx.fetch("sp_help");
for (Outcome> end result : outcomes) {
for (File report : end result) {
// ...
}
}
The usual foreach loop will solely iterate outcomes, however you can too entry the interleaved row counts utilizing Outcomes.resultsOrRows() if that’s of curiosity to you as properly.
Conclusion
Comfort and developer consumer expertise is on the core of jOOQ’s API design. Like all good assortment API, jOOQ provides quite a lot of composable primitives that permit for extra successfully integrating SQL into your utility.
SQL is only a description of a knowledge construction. jOOQ helps describe that information construction in a kind protected approach on the JVM. It’s pure for additional processing to be potential in an equally sort protected approach, as we’re used to from the JDK’s personal assortment APIs, or third events like jOOλ, vavr, streamex, and so forth.
