A Hidden jOOQ Gem: Foreach Loop Over ResultQuery

Posted on by lukaseder
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!

Published by lukaseder

I made jOOQ

7 thoughts on “A Hidden jOOQ Gem: Foreach Loop Over ResultQuery”

  1. > there’s no AutoCloseableIterable

    I don’t understand… Is this the real problem? I guess, you could write one, just forget about checked exceptions. Actually, the cursor is such a thing, isn’t it?

    The problem seems to be java lacking a construct like

    try-for (MyTableRecord rec : DSL.....) {
    doThingsWithRecord(rec);
}
    Reply

    1. We’re saying the same thing. The Java language doesn’t have any Iterable semantics on AutoCloseable, thus the Java language doesn’t have a for-with-resources. Flip the two sides around the “thus” and the statement is equally true.

      Note: Stay tuned for an upcoming blog post saying why try-with-resources is nice but it could have been done much better :)

      Reply

      1. OK, I was confused by various discussions why there’s no such thing like `CloseableIterator` (and the only “sane” reason were checked exceptions; which is insane given that how often they stay in the way).

        —

        I’m looking forward to reading about try-with-resources. My only concern with the it is the syntax. I find

        @Cleanup Cursor cursor = DSL…;

        much cleaner (no new syntax, no additional indentation). With some relaxation on annotation usage constrains, it’d make this possible:

        for (MyTableRecord rec : @Cleanup DSL…..) {…}

        Reply

        1. Yes, checked exceptions do get in the way, although that problem is orthogonal to auto closing resources, I guess. I do hope it will be addressed in some future Java, e.g. by adding a flag to disable exception checking, making them all unchecked. (which would effectively but gracefully kill them from the language)

          Interesting feedback about try-with-resources. Curiously enough, I find the syntax to be some of the main assets. But the discussion will be about what features should go into the language (JLS) and what features should go into the libraries. Your suggestion is an alternative to what I’ll suggest. What you’re doing is already possible to some extent. At least, you can already put the annotation on a type declaration thanks to JSR-308 in Java 8, but not on an expression.There would be many open questions, though. What is the scope of this @Cleanup thing? I.e. when is “cleanup” invoked?

          Reply

          1. checked exceptions … that problem is orthogonal to…

            Quite possible, they simply always get in the way. Nice idea, except for it doesn’t work. Just kill’em all.

            What you’re doing

            It wasn’t me, except for placing it on an expression, which is currently illegal.

            What is the scope of this @Cleanup thing?

            The same as the scope of `i` in

            for (Item i : ...) {...}

            i.e., the smallest one which makes it work. Put differently: Transform my illegal statement

            for (MyTableRecord rec : @Cleanup DSL...) {...}

            over this illegal statement

            for (MyTableRecord rec : @Cleanup final tempIterable = DSL...) {...}

            to this legal one

            {
    @Cleanup final tempIterable = DSL...;
    for (MyTableRecord rec : tempIterable) {...}
}

            It’s pretty straightforward and I guess, we agree that the scope shouldn’t be bigger and can’t be smaller, don’t we?

            Reply

            1. Oh wow, I wasn’t aware of this! Interesting. Very interesting! So, when the variable goes out of scope, it is closed. That’s excellent! Maybe, we could ask the Lombok team to add support for @Cleanup on methods in order to look for all the AutoCloseable local variables within the method (and which aren’t closed by the end of the method) and then to close them all at the end of their respective scopes…

              As far as your final example is concerned, though, I think it would be as verbose as the existing:

              try (Cursor tempIterable = DSL...) {
    for (MyTableRecord rec : tempIterable) { ... }
}
              Reply

  2. Maybe, we could ask the Lombok team to add support for @Cleanup on methods in order to look for all the AutoCloseable

    I’m afraid, this is hard/impossible to do, because of the way Lombok works. It can’t find all the implemented interfaces as it hacks into the compile process at a point when this information isn’t always available. That’s a big showstopper even for basic features like generating constructors calling non-trivial `super(…)`.

    look for all the AutoCloseable local variables within the method (and which aren’t closed by the end of the method) and then to close them

    This would be dangerous as sometimes you want to return them, possibly wrapped (BuffereReader over InputStreamReader over Connection). Recognizing this would require a lot of analysis. But it could be solved by something like @Cleanup(false) and requiring @Cleanup (with false or true) on all AutoCloseables. A bit verbose, but very forget-proof.

    As far as your final example is concerned…

    It was just a rewrite of the illegal construct. Using @Cleanup doesn’t make code shorter, except by the line containing the closing brace only, which is needed in the example. But it makes it cleaner by no requiring no additional indentation. It’s much better than try-finally because of it being less verbose and not separating the cleanup from the allocation. It’s only slightly better than try with resources.

    With Java 20 allowing notations on expressions, it’ll get an advantage again. ;)

    Reply

Leave a Reply