json values not supported by jooq

user_5667911 0
Viewed 403

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?

jooq-codegen-maven jooq postgresql java
1 Answers

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();
user_5217990
Related