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

The jOOQ API is all about comfort, and as such, an vital operation (a very powerful one?) like fetch() should include comfort, too. The default method to fetch knowledge is that this:

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

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

It fetches your complete consequence 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 vital. jOOQ’s ResultQuery<R> kind conveniently extends Iterable<R>, which signifies that a name to ResultQuery.iterator() will even execute the question. This may be executed 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 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(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 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 fashion, is that this:

Elective<Record1<String>> 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<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 the whole lot into reminiscence, as that’s possible extra helpful to most functions than JDBC’s default of managing assets on a regular basis (together with nested collections, lobs, and so on.). As might be seen within the above Iterator fetching instance, it’s typically the one potential strategy 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.

But it surely isn’t all the time the proper alternative, so you may alternatively preserve open underlying JDBC assets whereas fetching knowledge, in case your knowledge set is giant. There are 2 primary methods:

Crucial:

By calling ResultQuery.fetchLazy(), you’re making a Cursor<R>, which wraps the underlying JDBC ResultSet, and thus, needs to 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) {
    // ...
}

Practical:

If the Stream API is extra such as you wish to work with knowledge, simply name ResultQuery.fetchStream() as a substitute, 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 might have been potential to implement the API this manner, its “escape hatches,” like Stream.iterator() would nonetheless forestall auto-closing behaviour (at the very least except many extra options had been launched, akin to e.g. an AutoCloseableIterator, or no matter).

So, you’ll have to interrupt your fluent pipeline with the try-with-resources assertion.

Practical, however not resourceful

After all, you may all the time name fetch() first, then stream later, so as to stream the info out of your reminiscence straight. If resourcefulness isn’t vital (i.e. the efficiency impression is negligible as a result of the consequence set isn’t massive), 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::gather and Cursor::gather 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 needs to be an Iterable::gather methodology, as it might make sense to re-use Collector sorts on any assortment, e.g.

Set<String> s = Set.of(1, 2, 3);
Checklist<String> l = s.gather(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. Apart 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 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 attention-grabbing bit right here is that it captures the kinds of a Record2 kind as the important thing and worth kind of the ensuing map. A easy generic trick to ensure it really works provided that you challenge precisely 2 columns, for instance:

Map<Integer, String> books =
ctx.choose(BOOK.ID, BOOK.TITLE)
   .from(BOOK)
   .gather(Information.intoMap());

That is utterly kind secure. You’ll be able to’t challenge 3 columns, or the unsuitable column sorts due to all these generics. That is extra handy than the equal that’s out there 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::gather and Cursor::gather APIs, you should use any arbitrary collector, together with your individual, which is actually very highly effective! Additionally, it removes the necessity for the middleman Outcome knowledge construction, so it doesn’t must fetch the whole lot into reminiscence (except your Collector does it anyway, in fact).

Collectors are notably 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<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 Circulate API are supported for higher interoperability).

So, simply choose 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 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 (Outcome<?> consequence : outcomes) {
    for (File report : consequence) {
        // ...
    }
}

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 several good assortment API, jOOQ provides a wide range of composable primitives that permit for extra successfully integrating SQL into your utility.

SQL is only a description of an information construction. jOOQ helps describe that knowledge construction in a sort secure means on the JVM. It’s pure for additional processing to be potential in an equally kind secure means, as we’re used to from the JDK’s personal assortment APIs, or third events like jOOλ, vavr, streamex, and so on.