A Hidden jOOQ Gem: Foreach Loop Over ResultQuery
See how Lukas Eder breaks down and explains how and why a couple of loops work in jOOQ and how to take advantage of them.
Join the DZone community and get the full member experience.
Join For FreeA recent question on Stack Overflow about jOOQ caught my attention. The question essentially asked:
Why do both of these loops work?
// With fetch()
for (MyTableRecord rec : DSL
.using(configuration)
.selectFrom(MY_TABLE)
.orderBy(MY_TABLE.COLUMN)
.fetch()) { // fetch() here
doThingsWithRecord(rec);
}
// Without fetch()
for (MyTableRecord rec : DSL
.using(configuration)
.selectFrom(MY_TABLE)
.orderBy(MY_TABLE.COLUMN)) { // No fetch() here
doThingsWithRecord(rec);
}
And indeed, just like in PL/SQL, you can use any jOOQ ResultQuery
as a Java 5 Iterable
, because that’s what it is. An Iterable<R>
where R extends Record
.
The semantics are simple. When Iterable.iterator()
is invoked, the query is executed and the Result.iterator()
is returned. So, the result is materialised in the client memory just like if I called fetch()
. Unsurprisingly, this is the implementation of AbstractResultQuery.iterator()
:
@Override
public final Iterator<R> iterator() {
return fetch().iterator();
}
No magic. But it’s great that this works like PL/SQL:
FOR rec IN (SELECT * FROM my_table ORDER BY my_table.column)
LOOP
doThingsWithRecord(rec);
END LOOP;
Note, unfortunately, there’s no easy way to manage resources through Iterable
, i.e. there’s no AutoCloseableIterable
returning an AutoCloseableIterator
, which could be used in an auto-closing try-with-resources style loop. This is why the entire result set needs to be fetched at the beginning of the loop. For lazy fetching, you can still use ResultQuery.fetchLazy()
try (Cursor<MyTableRecord> cursor = DSL
.using(configuration)
.selectFrom(MY_TABLE)
.orderBy(MY_TABLE.COLUMN)
.fetchLazy()) {
for (MyTableRecord rec : cursor)
doThingsWithRecord(rec);
}
Happy coding!
Published at DZone with permission of Lukas Eder. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments