How to add a new column in JOOQ?

Question

I want to translate this SQL query, in JOOQ. I want the new field from calculated value, but here i just add it with constant value.

select
  *,
  100 as "newField"
from author

I tried this

Result<Record> r=create.select(DSL.asterisk(),DSL.val(100).as("newField"))
                    .from(DSL.table("author"))
                    .fetch();

That generates this SQL (which looks fine)

select
  *,
  ? as "newField"
from author

But i get this as result (expected is all table with 1 extra column with value 100)

+--------+
|newField|
+--------+
|1       |
|2       |
+--------+

In pgAdmin, the sql above query does what i need(adds a new field to the table with all values 100). I don't know what i do wrong, the results seems like newField gets as value the row number.

Also in SQL generation how i can print the SQL with the 100 value instead of ?

Related question: https://stackoverflow.com/q/5832803/521799 — Lukas Eder, Nov 25 '22 at 11:12

score 1 · Answer 1 · answered Nov 23 '22 at 14:26

This is a known limitation, the usage of asterisk() with a plain SQL query is limited: https://github.com/jOOQ/jOOQ/issues/7841

Workarounds include:

Using the code generator (which is always a good idea)
Wrapping the query in another derived table, and selecting the asterisk() alone in the outer most query

Also in SQL generation how i can print the SQL with the 100 value instead of ?

That's a different question, not strictly related. There are various ways to generate inline values in jOOQ. In your case, probably Query.getSQL(paramType.INLINED) could help you "print" the SQL. Though, you might have had something else in mind, in case of which I recommend asking a new question with more details about this.

How to add a new column in JOOQ?

1 Answers1