How dop I get a temp table column reference in Jooq?

Question

I'm creating a temp table to manipulate some data in MySQL. All the code runs in a TransactionCallable.

-- Create the temp table
Name tmpTableName = DSL.name(TMP_TABLE_NAME);
dslContext.createTemporaryTable(tmpTableName)        
.as(dslContext.select(TABLE_A.fields()).from(TABLE_A)).withNoData().execute();

Table<Record> tmpTable = DSL.table(tmpTableName);

-- The insert...select here seems to be ok

dslContext.update(tmpTable)
      .set(tmpTable.field("COL_1", String.class), "replacement text")
      .where(tmpTable.field("COL_2", String.class).startsWith("prefix"))
      .execute();

The update statement is where there is an error I can't get past. The message is tmpTable.field("COL_2", String.class) is null. It seems like the tmpTable object isn't actually referencing the temp table.

I thought the problem was in how I'm trying to get the table object with DSL.table which sounds like that is for constructing a table from a SQL string.

I tried getting a reference through meta() but the List came back empty.

List<Table<?>> tables = dslContext.meta().getTables(tmpTableName);

How do I get a reference to the columns/fields of the temp table so I can use them in the update statement?

Answer 1

How to create column references

The identifier based table expression DSL.table(tmpTableName) doesn't know anything about its columns, so when you write tmpTable.field("COL_1"), that can't dereference any columns. This only works if you're using Meta (only if the database reports temp tables, not all do), or generated code.

However, why not just construct standalone field references based on the identifier, like you did with the table:

// You don't really have to qualify your field references in this particular case
Field<String> col1Unqualified = field(name("COL_1"), VARCHAR);

// .. but if you do want to qualify them, here's how:
Field<String> col1Qualified = field(name(TMP_TABLE_NAME, "COL_1"), VARCHAR);

The usual static imports are assumed here:

import static org.jooq.impl.DSL.*;
import static org.jooq.impl.SQLDataType.*;

Alternative approaches using generated code

Since your temp table TMP_TABLE_NAME has the exact same structure as your TABLE_A, you can just call TABLE_A.rename("TMP_TABLE_NAME") to get a type safe reference of the temp table with all its columns:

TableA tmp = TABLE_A.rename("TMP_TABLE_NAME");

ctx.update(tmp)
   .set(tmp.COL1, "replacement text")
   .where(tmp.COL2.startsWith("prefix"))
   .execute();