CODEWITHSUNDEEP
javasqljooq
Alejandro Abdelnur
Alejandro Abdelnur

Reputation: 31

Is it possible to alter the SELECT/WHERE of jOOQ DSL query

I would like manipulate a jOOQ DSL query changing its SELECT columns and WHERE conditions.

For example:

DSLContext ctx = ...;
SelectHavingStep query = ctx.select(MyEntity.MY_ENTITY.ZIP, DSL.count(MyEntity.MY_ENTITY.ZIP))
   .from(MyEntity.MY_ENTITY)
   .where(MyEntity.MY_ENTITY.ID.gt("P"))
   .groupBy(MyEntity.MY_ENTITY.ZIP);

Use case 1:

I would like to pass the above query to a utility class that will produce the same query just with with a different SELECT, for example:

ctx.select(DSL.count())
   .from(MyEntity.MY_ENTITY)
   .where(MyEntity.MY_ENTITY.ID.gt("P"))
   .groupBy(MyEntity.MY_ENTITY.ZIP);

This particular example is to be able to create paginated results showing the total number of rows of the query.

Use case 2:

I would like to pass the above query to a utility class that will produce the same query just with with a modified WHERE clause, for example:

SelectHavingStep query = 
  ctx.select(MyEntity.MY_ENTITY.ZIP, DSL.count(MyEntity.MY_ENTITY.ZIP))
     .from(MyEntity.MY_ENTITY)
     .where(
        MyEntity.MY_ENTITY.ID.gt("P")
        .and(MyEntity.MY_ENTITY.ZIP.in("100", "200", "300"))
      )
     .groupBy(MyEntity.MY_ENTITY.ZIP);

This particular example is to further restrict a query based on some criteria (i.e. data visibility based on the user doing the query).

Is this possible?

Currently I'm using helper classes to do this at query construction time in the application code. I would like to move the responsibility to a library so it can be enforced transparently to the app.

Thanks.

Upvotes: 1

Views: 1242

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220762

You shouldn't try to alter jOOQ objects, instead you should try to create them dynamically in a functional way. There are different ways to achieve your use-cases, e.g.

Use case 1:

An approach to generic paginated querying can be seen here: https://blog.jooq.org/calculating-pagination-metadata-without-extra-roundtrips-in-sql/

Ideally, you would avoid the extra round trip for the COUNT(*) query and use a COUNT(*) OVER () window function. If that's not available in your SQL dialect, then you could do this, instead:

public ResultQuery<Record> mySelect(
    boolean count,
    Supplier<List<Field<?>>> select,
    Function<? super SelectFromStep<Record>, ? extends ResultQuery<Record>> f
) {
    return f.apply(count ? ctx.select(count()) : ctx.select(select.get()));
}

And then use it like this:

mySelect(false, 
    () -> List.of(MY_ENTITY.ZIP),
    q -> q.from(MY_ENTITY)
          .where(MY_ENTITY.ID.gt("P"))
          .groupBy(MY_ENTITY.ZIP)
).fetch();

This is just one way to do it. There are many others, see the below link.

Use case 2:

Just take the above example one step further and extract the logic used to create the WHERE clause in yet another function, e.g.

public Condition myWhere(Function<? super Condition, ? extends Condition> f) {
    return f.apply(MY_ENTITY.ID.gt("P"));
}

And now use it as follows:

mySelect(false, 
    () -> List.of(MY_ENTITY.ZIP),
    q -> q.from(MY_ENTITY)
          .where(myWhere(c -> c.and(MY_ENTITY.ZIP.in("100", "200", "300")))
          .groupBy(MY_ENTITY.ZIP)
).fetch();

Again, there are many different ways to solve this, depending on what is the "common part", and what is the "user-defined part". You can also abstract over your MY_ENTITY table and pass around functions that produce the actual table.

More information

See also these resources:

Upvotes: 2

Related Questions