A 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 is 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!