100. Tackling records in jOOQ
The more you learn JPA more you’ll love jOOQ. Why? Because jOOQ represents the best way to write SQL in Java. Flexibility, versatility, dialect agnostic, rock-solid SQL support, a small learning curve, and high performance are just a few of the attributes that make jOOQ the most appealing persistence technology for modern applications.Being part of the modern technology stack, jOOQ is the new persistence trend that respects all standards of a mature, robust, and well-documented technology.If you are not familiar with jOOQ then please consider my book jOOQ Masterclass.That being said, let’s assume that we have a database schema consisting of two tables Productline and Product. A product line contains multiple products, so we can shape this one-to-many relationship via two records as follows:
public record RecordProduct(String productName,
String productVendor, Integer quantityInStock) {}
public record RecordProductLine(String productLine,
String textDescription, List<RecordProduct> products) {}
In jOOQ, we can populate this model via a simple query based on the MULTISET operator:
List<RecordProductLine> resultRecord = ctx.select(
PRODUCTLINE.PRODUCT_LINE, PRODUCTLINE.TEXT_DESCRIPTION,
multiset(
select(
PRODUCT.PRODUCT_NAME, PRODUCT.PRODUCT_VENDOR,
PRODUCT.QUANTITY_IN_STOCK)
.from(PRODUCT)
.where(PRODUCTLINE.PRODUCT_LINE.eq(
PRODUCT.PRODUCT_LINE))
).as(“products”).convertFrom(
r -> r.map(mapping(RecordProduct::new))))
.from(PRODUCTLINE)
.orderBy(PRODUCTLINE.PRODUCT_LINE)
.fetch(mapping(RecordProductLine::new));
How cool is this? Really, how cool is this? jOOQ can produce any nested collection value of jOOQ Record or DTO (POJO/Java records) in a fully type-safe manner, with 0 reflections, no N+1 risks, no de-duplications, and no accidental Cartesian Products. This allows the database to perform nesting and optimize the query execution plan.In the bundled code, you can see another example that fetches in a record-model a many-to-many relationship. Moreover, in the bundled code, you can find an example that relies on the jOOQ MULTISET_AGG() function. This is a synthetic aggregate function that can be used as an alternative to MULTISET.