CODEWITHSUNDEEP
javamysqlsqljooq
Troy
Troy

Reputation: 1639

Qualifying a temporary table column name in jOOQ

I am using jOOQ with a temporary table:

Table<Record> TMP = DSL.table("tmp");
Field<String> TYPE = DSL.field("type", String.class);
Field<String> TOKEN = DSL.field("token", String.class);

This allows me to write simple queries: DSL.select(TYPE, TOKEN).from(TMP)...

However, when I try to join against another table, it creates ambiguities because the column names TYPE and TOKEN are not qualified with a table name (i.e. I need the generated code to look like SELECT tmp.type, tmp.token ...). Is there a way to make this happen, either by making Jooq understand that the temp table has certain columns, or by making a Field with a qualified name?

Of course I can always drop to raw SQL for these parts of the query, which is what I've been doing so far.

Upvotes: 5

Views: 3652

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221145

There are two ways to interact with tables / columns dynamically (i.e. without using the code generator) in jOOQ:

Using plain SQL (org.jooq.SQL)

That's what you're doing. You can obviously qualify the columns directly in your plain SQL Field references in two ways:

By repeating the "tmp" string in each field:

Table<Record> TMP = DSL.table("tmp");
Field<String> TYPE = DSL.field("tmp.type", String.class);
Field<String> TOKEN = DSL.field("tmp.token", String.class);

By embedding the "tmp" reference in the plain SQL template:

Table<Record> TMP = DSL.table("tmp");
Field<String> TYPE = DSL.field("{0}.type", String.class, TMP);
Field<String> TOKEN = DSL.field("{0}.token", String.class, TMP);

The plain SQL functionality is documented here in the manual

Using qualified references (org.jooq.Name)

That's probably what you want to be doing instead. You'll write:

Table<Record> TMP = DSL.table(DSL.name("tmp"));
Field<String> TYPE = DSL.field(DSL.name("tmp", "type"), String.class);
Field<String> TOKEN = DSL.field(DSL.name("tmp", "token"), String.class);

The naming functionality is described here in the manual.

The advantages of this approach are:

Upvotes: 6

Related Questions