![The Many Totally different Methods to Fetch Information in jOOQ – Java, SQL and jOOQ. The Many Totally different Methods to Fetch Information in jOOQ – Java, SQL and jOOQ.](https://techtoguide.com/wp-content/uploads/2023/10/A-simple-example-configuration-to-generate-package-private-jOOQ-code.png)
[ad_1]
The jOOQ API is all about comfort, and as such, an essential operation (an important one?) like fetch()
should include comfort, too. The default strategy to fetch knowledge is that this:
Consequence<Record1<String>> outcome =
ctx.choose(BOOK.TITLE)
.from(BOOK)
.fetch();
for (Record1<String> report : outcome) {
// ...
}
It fetches the complete 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 vital. jOOQ’s ResultQuery<R>
sort conveniently extends Iterable<R>
, which signifies that a name to ResultQuery.iterator()
may even execute the question. This may be carried out primarily in two methods:
Exterior iteration:
for (Record1<String> report : ctx
.choose(BOOK.TITLE)
.from(BOOK)
) {
// ...
}
That is notably 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 complete 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.
Inside iteration:
The JDK 8 added Iterable::forEach
, which jOOQ’s ResultQuery
inherits, so you are able to do this simply as nicely:
ctx.choose(BOOK.TITLE)
.from(BOOK)
.forEach(report -> {
// ...
});
The 2 are completely equal.
Single report fetching
For those who’re positive 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 optionally available 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 fashion, is that this:
Optionally available<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 secure!
The r.toString()
name is NullPointerException
secure, as a result of if the report didn’t exist a NoDataFoundException
would have been thrown.
Resourceful fetching
The default is to eagerly fetch all the pieces into reminiscence, as that’s possible extra helpful to most functions than JDBC’s default of managing sources on a regular basis (together with nested collections, lobs, and so on.). As might 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) through jOOQ.
However it isn’t at all times the appropriate alternative, so you’ll be able to alternatively preserve open underlying JDBC sources whereas fetching knowledge, in case your knowledge set is giant. There are 2 most important 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:
strive (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’ll be able to fetch data additionally manually from it, e.g.
Document report;
whereas ((report = cursor.fetchNext()) != null) {
// ...
}
Purposeful:
If the Stream
API is extra such as you need to work with knowledge, simply name ResultQuery.fetchStream()
as an alternative, then (however don’t neglect to wrap that in try-with-resources
, too!):
strive (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 might have been doable to implement the API this fashion, its “escape hatches,” like Stream.iterator()
would nonetheless forestall auto-closing behaviour (no less than until many extra options had been launched, similar to e.g. an AutoCloseableIterator
, or no matter).
So, you’ll have to interrupt your fluent pipeline with the try-with-resources
assertion.
Purposeful, however not resourceful
After all, you’ll be able to at all times name fetch()
first, then stream later, with a purpose to stream the information out of your reminiscence instantly. If resourcefulness isn’t essential (i.e. the efficiency affect is negligible as a result of the outcome set isn’t large), you’ll be able to 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 (outdoors of the Stream
API). For my part, there must be an Iterable::accumulate
methodology, as it might 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 type 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 no less than, it feels fairly comparable.
In case of jOOQ, they’re very highly effective. jOOQ provides a couple of helpful out-of-the-box collectors in Data
. Let me showcase Data.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 varieties of a Record2
sort as the important thing and worth sort of the ensuing map. A easy generic trick to ensure 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(Data.intoMap());
That is fully sort secure. You’ll be able to’t undertaking 3 columns, or the incorrect column sorts because of all these generics. That is extra handy than the equal that’s obtainable on the ResultQuery
API instantly, 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 need to use any arbitrary collector, together with your personal, which is actually very highly effective! Additionally, it removes the necessity for the middleman Consequence
knowledge construction, so it doesn’t should fetch all the pieces into reminiscence (until your Collector
does it anyway, after all).
Collectors are notably helpful when accumulating
MULTISET
nested 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. Which means 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 alternative, 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 once 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 (Consequence<?> outcome : outcomes) {
for (Document 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 nicely.
Conclusion
Comfort and developer person expertise is on the core of jOOQ’s API design. Like every good assortment API, jOOQ provides a wide range of composable primitives that enable for extra successfully integrating SQL into your utility.
SQL is only a description of a knowledge construction. jOOQ helps describe that knowledge construction in a kind secure means on the JVM. It’s pure for additional processing to be doable in an equally sort secure means, as we’re used to from the JDK’s personal assortment APIs, or third events like jOOλ, vavr, streamex, and so on.
[ad_2]