Generate Dynamic Query using JOOQ

Question

I want to generate a SQL query where tables name is stored in the tables array. and the corresponding columns name stored in a 2-D array.

example:-

Tables array
[T1,T2,T3]
Columns array
[
    [C1,C2],   // T1 columns
    [C1,C2],   // T2 columns
    [C1,C2]    // T3 columns
]

QUERY:-
select T1.C1,T2.C1,T3.C1 from T1
inner join T2 ON T2.C2=T1.C2;
inner join T3 ON T3.C2=T1.C2

select first column of every table in the array 
if they have a match in the second column

[assuming every table has 2 columns]

I don't want to execute this query. I just want to print it using JOOQ. Can someone pls help me out on this.

score 1 · Accepted Answer · answered Jun 07 '21 at 07:01

The phrasing of your question gives room to some interpretation. I'm assuming you have these specific array types:

Table<?>[] tables = ...
Field<?>[][] fields = ...

I'm assuming that your requirement is to match all tables by common column names (i.e. names that are the same as the name of the second column of the first table), in order to join them. Since you do not specify what happens if consecutive tables don't have such a match, I'm assuming you'd like to default to excluding such tables, and their columns.

In any case, I guess this is more of a question about an idea on how to do dynamic SQL with jOOQ in general, not necessarily how to solve your particular problem.

In that case, write:

Field<?> match = fields[0][1];

List<Field<?>> select = new ArrayList<>();
Table<?> from = tables[0];

select.add(fields[0][0]);
for (int i = 1; i < fields.length && i < tables.length; i++) {
    if (match.getName().equals(fields[i][1].getName())) {
        select.add(fields[i][0]);
        from = from.join(tables[i]).on(match.eq((Field) fields[i][1]));
    }
}

ctx.select(select)
   .from(from)
   .fetch();

If your actual requirements are very different to these assumptions, you can still ask a new question.

Generate Dynamic Query using JOOQ

1 Answers1

Linked