The Many Completely different Methods to Fetch Knowledge in jOOQ – Java, SQL and jOOQ.

The jOOQ API is all about comfort, and as such, an necessary operation (crucial one?) like fetch() should include comfort, too. The default strategy to fetch information is that this:

Consequence<Record1<String>> consequence =
ctx.choose(BOOK.TITLE)
   .from(BOOK)
   .fetch();

for (Record1<String> document : consequence) {
    // ...
}

It fetches your complete consequence set into reminiscence and closes the underlying JDBC sources eagerly. However what different choices do we’ve?

Iterable fetching

Within the above instance, the fetch() name wasn’t strictly obligatory. jOOQ’s ResultQuery<R> sort conveniently extends Iterable<R>, which signifies that a name to ResultQuery.iterator() may also execute the question. This may be completed primarily in two methods:

Exterior iteration:

for (Record1<String> document : ctx
    .choose(BOOK.TITLE)
    .from(BOOK)
) {
    // ...
}

That is significantly good as a result of it feels identical to PL/SQL or PL/pgSQL’s FOR loop for implicit cursors:

FOR rec IN (SELECT e book.title FROM e book) LOOP
  -- ...
END LOOP;

This nonetheless has to fetch your complete consequence 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(document -> {
       // ...
   });

The 2 are completely equal.

Single document 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 document:

This fetches a nullable document, i.e. if the document 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-compulsory document:

The null bikeshed is actual, so why maintain you from bikeshedding additionally when working with jOOQ? Precisely equal to the above, however utilizing a special type, is that this:

Elective<Record1<String>> r = question.fetchOptional();

Fetch a single document:

If you understand your question produces precisely one document, there’s the time period “single” in jOOQ’s API which implies 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 document 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 seemingly extra helpful to most functions than JDBC’s default of managing sources on a regular basis (together with nested collections, lobs, and so forth.). As may very well be seen within the above Iterator fetching instance, it’s usually the one attainable method that doesn’t produce unintended useful resource leaks, on condition that customers can’t even entry the useful resource (by default) through jOOQ.

But it surely isn’t all the time the best alternative, so you’ll be able to alternatively maintain open underlying JDBC sources whereas fetching information, in case your information set is massive. There are 2 predominant 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> document : cursor) {
        // ...
    }
}

The Cursor<R> nonetheless extends Iterable<R>, however you’ll be able to fetch data additionally manually from it, e.g.

File document;

whereas ((document = cursor.fetchNext()) != null) {
    // ...
}

Purposeful:

If the Stream API is extra such as you wish to work with information, simply name ResultQuery.fetchStream() as a substitute, then (however don’t overlook to wrap that in try-with-resources, too!):

strive (Stream<Record1<String>> stream = ctx
    .choose(BOOK.TITLE)
    .from(BOOK)
    .fetchStream()
) {
    stream.forEach(document -> {
        // ...
    });
}

Or, use Stream::map, Stream::scale back, or no matter. Regrettably, the Stream API isn’t auto-closing. Whereas it will have been attainable to implement the API this fashion, its “escape hatches,” like Stream.iterator() would nonetheless stop auto-closing behaviour (no less than except many extra options had been launched, corresponding 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 all the time name fetch() first, then stream later, to be able to stream the info out of your reminiscence instantly. If resourcefulness isn’t necessary (i.e. the efficiency influence is negligible as a result of the consequence set isn’t massive), you’ll be able to write this:

ctx.choose(BOOK.TITLE)
   .from(BOOK)
   .fetch()
   .stream()
   .forEach(document -> {
       // ...
   });

Or use Stream::map, Stream::scale 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). In my view, there must be an Iterable::acquire technique, as it will make sense to re-use Collector sorts on any assortment, e.g.

Set<String> s = Set.of(1, 2, 3);
Record<String> l = s.acquire(Collectors.toList());

Why not? Collector is sort 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 no less than, it feels fairly comparable.

In case of jOOQ, they’re very highly effective. jOOQ affords a couple of helpful out-of-the-box collectors in Data. Let me showcase Data.intoMap(), which has this overload, for instance:

<Ok,V,R extends Record2<Ok,V>> Collector<R,?,Map<Ok,V>> intoMap()

The attention-grabbing 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 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)
   .acquire(Data.intoMap());

That is fully sort protected. You may’t undertaking 3 columns, or the unsuitable column sorts due to all these generics. That is extra handy than the equal that’s obtainable on the ResultQuery API instantly, the place you need to 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::acquire and Cursor::acquire APIs, you need to use any arbitrary collector, together with your individual, which is de facto very highly effective! Additionally, it removes the necessity for the middleman Consequence information construction, so it doesn’t need to fetch all the pieces into reminiscence (except your Collector does it anyway, in fact).

Collectors are significantly helpful when amassing MULTISET nested collections. An instance has been given right here, the place a nested assortment was additionally mapped into such a Map<Ok, 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 Movement API are supported for higher interoperability).

So, simply decide your favorite reactive streams API of alternative, e.g. reactor, and stream jOOQ consequence 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 consequence 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 (Consequence<?> consequence : outcomes) {
    for (File document : consequence) {
        // ...
    }
}

The usual foreach loop will solely iterate outcomes, however you too can entry the interleaved row counts utilizing Outcomes.resultsOrRows() if that’s of curiosity to you as effectively.

Conclusion

Comfort and developer person expertise is on the core of jOOQ’s API design. Like all good assortment API, jOOQ affords a wide range of composable primitives that permit for extra successfully integrating SQL into your software.

SQL is only a description of an information construction. jOOQ helps describe that information construction in a sort protected approach on the JVM. It’s pure for additional processing to be attainable 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.