Jooq bind the same parameter multiple times

Question

I have an SQL query that uses the same parameter in several places (the table name changed between the environments prod, stg, dev):

String SQL =
select *
from :a_table a
join :b_table b
on a.x != b.x
where a.y not in (select b1.y
                  from :b_table b1
                  where b1.x = a.x)

I run this query with Jooq:

return dslContext.fetch(sql, param("a_table", ATable.getName()), param("b_table", BTable.getName()));

but the parameters are replaced only once, and the query that Jooq runs is (assuming ATable.getName() = "A" and BTable,getName() = "B"):

select *
from A a
join B b
on a.x != b.x
where a.y not in (select b1.y
                  from :b_table b1
                  where b1.x = a.x)

is it possible to replace the params in all places without putting it more than once in the fetch calli?

Answer 1

Bind values

A bind value cannot be a table in SQL. A bind value is a parameter to the SQL query, representing a scalar value, not some syntactic element, e.g.

SELECT :b1, :b2 + 1 -- Scalar values
FROM t              -- "Syntactic element"
WHERE t.x = :b3     -- Scalar values

This isn't jOOQ specific. You (probably) won't find any SQL dialect out there that allows for bind values to be used as tables.

Templates

If you wish to implement dynamic text based SQL like that, use "plain SQL templates", e.g.

String sql = 
    """
    select *
    from {0} a
    join {1} b
    on a.x != b.x
    where a.y not in (select b1.y
                      from {1} b1
                      where b1.x = a.x)
    """;

// And then
return dslContext.fetch(sql, ATable, BTable);

Assuming ATable and BTable are of type org.jooq.Table<?>. Templates are purely a jOOQ feature. They will be replaced when jOOQ generates SQL prior to passing the generated SQL to the JDBC driver.

See also this blog post for more information on using plain SQL templates with text blocks