CODEWITHSUNDEEP
javasqljooq
jonesy
jonesy

Reputation: 656

Build up dynamic table joins in JOOQ

I am using JOOQ as a SQL builder tool to then execute in a jdbcTemplate. I need to dynamically add table joins to the from clause. At the moment I am doing it like this..

SelectJoinStep<?> selectClause = select(field("ColumnA"), field("ColumnB"))
.from(table("TableA"));

if(condition) {
  selectClause.join(table("TableB), JoinType.JOIN).on(field("columnA").eq(field("columnB")));
}

Then I combine the select clause with the where clause..

org.jooq.Query ps = selectClause.where(whereClause);

This works ok but now I want to introduce paging so I need to swap out the selected fields for a simple count and it would be good if I could reuse the from clause in this count query but I can't because the select and from clause are in the same variable.

Update.. Why can I not build up the from clause dynamically like so...

    List<Field<?>> fields = new ArrayList<Field<?>>();
    fields.add(...);
    Table<?> from = table("TableA");
    if(condition) {
      from.join(table("TableB), JoinType.JOIN).on(field("columnA").eq(field("columnB")));
    }
org.jooq.Query ps = select(fields).from(from);

If condiiton is true table join does not appear in generated SQL?

Upvotes: 1

Views: 1866

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221210

The best approach to using jOOQ for dynamic SQL is a more functional approach. Don't think in terms of "swapping out" parts of your query under certain conditions. Think in terms of what parts of your query are provided by whom through functions? For example:

ResultQuery<?> query(
    Supplier<Collection<SelectField<?>>> select,
    boolean condition,
    boolean paginate
) {
    Condition whereClause = ...;
    Table<?> from = table("TableA");
    if (condition)
        from = from.join(table("TableB")).on(field("ColumnA").eq(field("ColumnB")));

    return select(select.get())
          .from(from)
          .where(whereClause)
          .limit(paginate ? 10 : Integer.MAX_VALUE); // Could be done differently
}

Now call the above:

query(() -> Arrays.asList(field("ColumnA"), field("ColumnB")), true, true);
query(() -> Arrays.asList(count()), true, false);

Of course, another option would be to use a window function to calculate that count value, instead of running two queries:

count().over()

Upvotes: 3

Related Questions