Update jsonb value in PostgreSQL using JOOQ

Question

In my Java project I'm usinq JOOQ and I want update JSONB value. But my build failure.

I wrote a code:

DSL.using(connection).update(USER_SETTINGS)
                .set(USER_SETTINGS.SETTING_VALUE, field("{\"filtersValue\":{\"page\":0,\"size\":100}, \"visibleCount\":100}", JSONB.class))
                .where(USER_SETTINGS.USER_ID.eq(98383L))
                .execute();

and I got a next message:

org.jooq.exception.DataAccessException: 
SQL [update "sbcm_sys"."user_settings" set "setting_value" = "filtersValue":{"page":0,"size":100, "visibleCount":100} where "sbcm_sys"."user_settings"."user_id" = ?]; ERROR: syntax err
or at or near ":"
  Position: 71
Caused by: org.postgresql.util.PSQLException: 
ERROR: syntax error at or near ":"
  Position: 71

Where I made a mistake?

I changed field() to val() and it's work!

Oleksii Hlovatskyi
– Oleksii Hlovatskyi

2021-11-22 10:33:54 +00:00
Commented Nov 22, 2021 at 10:33 — Oleksii Hlovatskyi
– Oleksii Hlovatskyi, Commented Nov 22, 2021 at 10:33

Lukas Eder · Accepted Answer · 2021-11-22 10:29:00Z

2

You're using DSL.field(String), which is used for plain SQL templating, when you meant to use DSL.val(String), which is used for bind values.

See also this blog post for further clarifications: https://blog.jooq.org/whats-a-string-in-the-jooq-api/

answered Nov 22, 2021 at 10:29

Lukas Eder

224k138 gold badges731 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update jsonb value in PostgreSQL using JOOQ

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related