Home HowTo insert into tableName with select and specifying insert columns at Jooq?

Questions

HowTo insert into tableName with select and specifying insert columns at Jooq?

November 29, 2022

I’m using Jooq to generate SQL
Here is resulting query

insert into MY_TABLE -- I want INSERT INTO(firstField,secondField)
select 
  ?, 
  ?
where not exists (
  select 1
  from MY_TABLE
  where (
    firstField = ?
  )
)
returning id

MY_TABLE DDL:

create table IF NOT EXISTS MY_TABLE
(
    id              SERIAL PRIMARY KEY,
    firstField         int          not null,
    secondField        int          not null
)

I can’t make Jooq add field names next to insert into MY_TABLE

My builder:

    JooqBuilder.default()
      .insertInto(table("MY_TABLE")) 
      .select(
        select(
          param(classOf[Int]), // 1 
          param(classOf[Int]), // 2 
        
        )
          .whereNotExists(select(inline(1))
            .from(table("MY_TABLE"))
            .where(
              DSL.noCondition()
                .and(field("firstField", classOf[Long]).eq(0L))
                
            )
          ) 
    ).returning(field("id")).getSQL

I’ve tried

.insertInto(table("MY_TABLE"),field("firstField"), field("secondField"))

UPD:
I was confused by compiler exception.
The right solution is

```scala
    JooqBuilder.default()
      .insertInto(table("MY_TABLE"), 
          field("firstField",classOf[Int]),
          field("secondField",classOf[Int])
      ) 
      .select(
        select(
          param(classOf[Int]), 
          param(classOf[Int]) 
        
        )
          .whereNotExists(select(inline(1))
            .from(table("MY_TABLE"))
            .where(
              DSL.noCondition()
                .and(field("firstField", classOf[Long]).eq(0L))
                
            )
          ) 
    ).returning(field("id")).getSQL

The thing is that Jooq takes field types from insertInto and doesn’t compile if select field types don’t match.

I’ve tried

.insertInto(table("MY_TABLE"), 
          field("firstField"),
          field("secondField")
      )

and it didn’t compile since no match with

.select(
        select(
          param(classOf[Int]), // 1 
          param(classOf[Int]) // 2 
        
        )

I’ve added types to insertInto fields and got match, two ints in insert, two ints in select.

Jooq generated expected query

insert into MY_TABLE -- I want INSERT INTO(firstField,secondField)
select 
  ?, 
  ?
where not exists (
  select 1
  from MY_TABLE
  where (
    firstField = ?
  )
)

>Solution :

jOOQ just generates exactly the SQL you tell it to generate. You’re not listing firstField,secondField in jOOQ, so jOOQ doesn’t list them in SQL. To list them in jOOQ, just add:

  // ...
  .insertInto(table("MY_TABLE"), field("firstField", classOf[Long]), ...)
  // ...

Obviously, even without using the code generator, you can reuse expressions by assigning them to local variables:

val t = table("MY_TABLE")
val f1 = field("firstField", classOf[Long])
val f2 = field("secondField", classOf[Long])

And then:

  // ...
  .insertInto(t, f1, f2)
  // ...

Using the code generator

Note that if you were using the code generator, which jOOQ recommends, your query would be much simpler:

ctx.insertInto(MY_TABLE, MY_TABLE.FIRST_FIELD, MY_TABLE.SECOND_FIELD)
   .values(v1, v2)
   .onDuplicateKeyIgnore()
   .returningResult(MY_TABLE.ID)
   .fetch();