What is the equivalent for coalesce in the new jOOQ 3.14 SQL/JSON supporting version (in PostgreSQL)? The following unfortunately fails with the error “COALESCE types json and uuid[] cannot be matched”. Answer JSON.json() or JSONB.jsonb(), e.g.: But you can also use the jsonArray() or jsonbArray()…
Tag: jooq
NoSuchMethod in JOOQ 3.14 InsertQuery with Postgresql
I’m trying to use Jooq’s InsertQuery to insert a record into PostgresQL database, but I’m running into following error: The query class I’m using looks like this: The user database table looks like this. It has a unique key on the username column and a primary key on the id column. If …
jOOQ JSON query results in ORA-00979
I’m trying to execute this query to an Oracle 19c database: But I get Does jOOQs JSON feature not work with Oracle? Answer This isn’t related to your JSON usage. The same thing would have happened if you removed all of it and wrote this query instead: Your query would work in MySQL, PostgreSQL or …
JOOQ materialized views
is it possible to create materialized views using the JOOQ builder? Something like this Answer In jOOQ 3.14, not yet: https://github.com/jOOQ/jOOQ/issues/9483 But you can use plain SQL templating to work around this limitation, to at least get some type safety in your statement:
Is there a way to query ltrees in JOOQ?
Is there any sort of extension that would allow me to use Postgres ltrees in JOOQ without having to use raw SQL? Any way to make one myself maybe? Answer Starting from jOOQ 3.17: With #13188, jOOQ has added native support for LTREE types (and similar) via the jooq-postgres-extensions module: Just add that mod…
In Jooq, how to make the “delete” statement return the deleted records?
In my project, there are 2 tables, orders and order_payments. The latter is a join table with 2 columns, order_id and payment_id. I didn’t use FK for efficiency sake. When I delete some orders from orders table, I need to delete the related records from payments table based on the order_id field. So, af…
Jooq Java CTE wrong render
I have CTE that is been creating from DSL.values: Then i am trying use it to get needed values from DB But i have only the next error I suppose it happens because of (select * from (values(cast(? as varchar)) How to fix it? Other methods without cte work fine. Answer The problem was there: “Id” is…
JOOQ – inline Converter not being applied
In my build.gradle I use a converter in my forcedTypes. This works fine where i need it. However, I am trying to convert a comma separated string into a list of enums for a specificuse case: This throws an exception This is the model I am fetching into: Am I missing something? UPDATE: I am able to convert inl…
jooq query for sql query with analytical function
i have sql query like i need to write in jOOQ, any help. I tried the below Also,is there any way to avoid listing all columns in select. Instead of Something like Answer A derived table can be constructed like this: Also, is there any way to avoid listing all columns in select You’re looking for Table.a…
JOOQ: How to resolve foreign keys as objects?
Say I have a table that references another table, in this case “TestScenarios” references “TestSchemas”. So each TestScenario HAS-A TestSchema. I autogenerated DAOs, however, when fetching TestScenario instance via the DAO the TestSchema field is an integer, not a TestSchema-object. Ho…