I have this struct in a JSONB column named services of my PostgreSQL database: [ { "status": "ACTIVE", <another attributes> }, <another JSON objetcs> ] I want to make this select: select * from entity en where en.owner_id = '???'…
Let's consider the database table for currency conversion looks as below: fromCurrency toCurrency effective date conversion rate USD INR 1-Mar-2024 80 USD INR 1-Jan-2024 85 USD GBP 1-Oct-2023 .80 USD GBP 1-Mar-2024 .85 USD AUD 1-Mar-2023 1.55 USD AUD 1-Mar-2024…
The values for some columns are only meant to be inserted and never modified (effectively complex foreign keys but not mapped as such for various reasons). In JPA, this can be modeled via @Column(updatable=false). Is there a way to systematically…
Using jOOQ and PostgreSQL, is there a way to know what operation was executed for a MERGE, INSERT or UPDATE? E.g. using the example from jOOQ's documentation: create.mergeInto(AUTHOR) .using(create.selectOne()) // Suppose the last name is unique (natural key) in my…
I have three tables: SchoolTable, SchoolOrgTable, and SchoolDetailsTable. The relationships between these tables are as follows: SchoolTable to SchoolOrgTable is a one-to-many relationship. SchoolOrgTable to SchoolDetailsTable is a many-to-one relationship. I'm currently retrieving data from these tables using a JOOQ…
I have a scala case class case class MyRecord( id: String, dimensions: List[String], // postgres text[] dimensionCodes: List[Long] // postgres integer[] ) and a Jooq query JooqBuilder .default(conn) .select( field("id", classOf[String]), field("dimensions", classOf[List[String]]), field("dimensionCodes", classOf[List[Long]]), ) .from(table(MY_TABLE)) .fetchInto(classOf[MyRecord]) .asScala .toList…
I use Springboot3 and Jooq to create my project and define a custom delete method deleteByUsername follow jooq delete guide @Repository public class UserRepository extends UserDao { @Autowired public UserRepository(Configuration configuration) { super(configuration); } /* --- some query method can…
The rough layout I have for some complex queries follows this table structure in Postgres: CREATE TABLE primari ( id BIGINT NOT NULL PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE secondary ( primary_id BIGINT NOT NULL references primari…
I have the same problem as the user in this issue https://github.com/jOOQ/jOOQ/issues/8080. When our generated jOOQ code does not exactly match our DB schema, queries using select(alias.asterisk()) throw errors because columns do not line up correctly. org.postgresql.util.PSQLException: Bad value for…
I am trying to fetch complex structure from the database using jOOQ. I will try to explain on an example where the problem is. So, let's imagine we have classes Foo, Bar and Bazz: public class Foo { private String…