res1 res1 - 2 months ago 13
Java Question

Jooq - Order results from select with UpdatableRecord

How can i execute a select with UpdatableRecord ordering the results of the query?

Looking in the manual, for example,

create.fetch(BOOK, BOOK.PUBLISHED_IN.equal(1948))


how can i add an order by clause?

I am using Jooq 3.6.4

Thanks for the help

UPDATE:

I see i can use, for example:

for(UtenteRecord utenteRecord :
create.selectFrom(UTENTE).orderBy(UTENTE.LOGIN)){
}


but it seems to work with and without the
fetch()
at the end, why?

Answer

Sorting in the database

Simply write out the SQL query and use selectFrom() to return UpdatableRecord:

DSL.using(configuration)
   .selectFrom(UTENTE)
   .orderBy(UTENTE.LOGIN)
   .fetch();

Side-note: Why is fetch() not needed in foreach loops?

You've noticed that you don't need to write fetch() in a foreach loop:

for (UtenteRecord record : DSL.using(configuration)
                              .selectFrom(UTENTE)
                              .orderBy(UTENTE.LOGIN)) {
    ...
}

This is because ResultQuery extends Iterable and when the foreach loop calls Iterable.iterator(), jOOQ will execute the query and return the Result.iterator

I've just written a blog post to illustrate this more in detail

Using client side sorting

Do note that you can always sort results also in the client, although this is probably not recommended as you should push as many operations as possible into the database to help the optimiser:

DSL.using(configuration)
   .fetch(UTENTE, somePredicate)
   .sortAsc(UTENTE.LOGIN);
Comments