2

I am trying to insert into books table using jooq in java. books table has two columns book_id integer,jsonb_column jsonb. However the final query formed is not correct. The values for jsonb is not populated. This is my query formed.

insert into book (book_id, jsoncolumn) values (902, );

Below is my java code.

    Field[] columnNames= new Field[2];
    columnNames[0]=field("book_id");
    columnNames[1]= field("jsoncolumn");
    
    Field[] columnValues= new Field[2];
    columnValues[0]=field("902");
    columnValues[1]=field("{}");
    Query query= create.insertInto(table("book"), 
            columnNames)
            .values(columnValues);
    
    String sql = query.getSQL();
    System.out.println(sql);

How to go about this?

Abhishek
  • 650
  • 1
  • 8
  • 31

1 Answers1

2

If you were using the code generator (which I highly recommend!), then most of jOOQ works out of the box. Your query would simply read:

ctx.insertInto(BOOK, BOOK.BOOK_ID, BOOK.JSONCOLUMN)
   .values(902, JSONB.jsonb("{}"))
   .execute();

If for some reason you cannot use the code generator, then you have to do what the code generator does for you, manually. Here are the mistakes you made:

  1. You didn't specify data types for your field expressions
  2. You used plain SQL templating to define the values of your columns, not bind values

Here's a fixed version of your code:

Table<?> book = table(name("book"));
Field<Long> bookId = field(name("book_id"), SQLDataType.BIGINT);
Field<JSONB> jsoncolumn = field(name("jsoncolumn"), SQLDataType.JSONB);

ctx.insertInto(book, bookId, jsoncolumn)
   .values(902L, JSONB.jsonb("{}"))
   .execute();
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • what exactly is BOO,BOOK.BOOK_ID... is it a string? also this is giving me compilation error JSONB.jsonb – Abhishek Feb 24 '21 at 08:03
  • also the fixed version of the code also gives compilation error for me in JSONB.jsonb. this is what i am trying to do org.jooq.JSONB.jsonb("{}") – Abhishek Feb 24 '21 at 08:05
  • @Abhishek: *"what exactly is BOO,BOOK.BOOK_ID"* - Well, I recommended using the code generator, so that's generated code. [See here for examples](https://www.jooq.org/doc/latest/manual/getting-started/use-cases/jooq-as-a-sql-builder-with-code-generation/). Seriously. Use the code generator if you can (i.e. if your schema isn't dynamic). You'll have a *much better* jOOQ experience, not just a good one. :) "*also this is giving me compilation error JSONB.jsonb*" - I'm assuming you're using the latest version of jOOQ. Otherwise, use `JSONB.valueOf()`, which does the same. – Lukas Eder Feb 24 '21 at 08:27