In my previous post about Open Session in View, I explored how this default Spring Boot setting can hide serious performance problems. The N+1 query problem sits behind a system that looks correct in development, but breaks down under real production load. Suddenly, you see 701 queries where 1 would be enough.
Today, I want to show a fundamentally different approach.
This is not about tuning Hibernate or adding more fetch joins. It is about choosing a data access model where performance is explicit by design.
Instead of fighting lazy loading and runtime surprises, we can use SQL directly. This is where jOOQ and its MULTISET feature come in.
The Problem Revisited
Consider a typical e-commerce domain:
Customer (1) <-- (*) PurchaseOrder (1) <-- (*) OrderItem (*) --> (1) Product
With JPA and Open Session in View enabled, a simple orderRepository.findAll() can trigger:
- 1 query for orders
- N queries for customers (one per order)
- N queries for order item collections
- N × M queries for products (one per item)
For 100 orders with 5 items each, this results in 701 database round trips.
That is:
- 1 query for orders
- 100 queries for customers
- 100 queries for order items
- 500 queries for products
The dangerous part is that everything works fine in development. Only when real traffic hits production does the problem become visible.
A Different Philosophy: SQL-First with jOOQ
jOOQ follows a different philosophy.
Instead of hiding SQL behind an ORM and hoping the generated queries are efficient, jOOQ treats SQL as a first-class citizen and keeps it fully type-safe.
With JPA, you model object graphs and hope the SQL behaves.
With jOOQ, you model queries and deliberately shape the object graph.
The MULTISET feature, introduced in jOOQ 3.15, allows fetching hierarchical data in a single query, while mapping directly to immutable Java records.
The DTOs: Simple Java Records
First, define the DTOs as Java records:
public record ProductDTO(Long id, String name, double price) {
}
public record CustomerDTO(Long id, String firstName, String lastName,
String street, String postalCode, String city) {
}
public record OrderItemDTO(Long id, int quantity, ProductDTO product) {
}
public record PurchaseOrderDTO(Long id, LocalDateTime orderDate,
CustomerDTO customer, List<OrderItemDTO> items) {
}
OrderItemDTO contains a nested ProductDTO.PurchaseOrderDTO contains a CustomerDTO and a list of OrderItemDTO.
This is the exact object graph we want to return. And we will fetch it with one SQL query.
The Repository: One Query to Rule Them All
Here is the complete repository method:
@Repository
public class PurchaseOrderRepository {
private final DSLContext ctx;
public PurchaseOrderRepository(DSLContext ctx) {
this.ctx = ctx;
}
public List<PurchaseOrderDTO> findAll() {
return ctx.select(
PURCHASE_ORDER.ID,
PURCHASE_ORDER.ORDER_DATE,
// Customer (1:1)
row(
PURCHASE_ORDER.customer().ID,
PURCHASE_ORDER.customer().FIRST_NAME,
PURCHASE_ORDER.customer().LAST_NAME,
PURCHASE_ORDER.customer().STREET,
PURCHASE_ORDER.customer().POSTAL_CODE,
PURCHASE_ORDER.customer().CITY
).mapping(CustomerDTO::new),
// Order items with nested product (1:N)
multiset(
select(
ORDER_ITEM.ID,
ORDER_ITEM.QUANTITY,
row(
ORDER_ITEM.product().ID,
ORDER_ITEM.product().NAME,
ORDER_ITEM.product().PRICE
).mapping(ProductDTO::new)
)
.from(ORDER_ITEM)
.where(ORDER_ITEM.PURCHASE_ORDER_ID.eq(PURCHASE_ORDER.ID))
.orderBy(ORDER_ITEM.ID)
).convertFrom(r -> r.map(mapping(OrderItemDTO::new)))
)
.from(PURCHASE_ORDER)
.orderBy(PURCHASE_ORDER.ORDER_DATE)
.fetch(mapping(PurchaseOrderDTO::new));
}
}
Key Patterns Explained
1. row(...).mapping(DTO::new) for 1:1 Relationships
For the relationship between PurchaseOrder and Customer:
row(
PURCHASE_ORDER.customer().ID,
PURCHASE_ORDER.customer().FIRST_NAME,
PURCHASE_ORDER.customer().LAST_NAME,
PURCHASE_ORDER.customer().STREET,
PURCHASE_ORDER.customer().POSTAL_CODE,
PURCHASE_ORDER.customer().CITY
).mapping(CustomerDTO::new)
This creates a row value expression and maps it directly to the CustomerDTO constructor.
The PURCHASE_ORDER.customer() call uses jOOQ’s implicit join, based on the foreign key defined in the database schema.
2. multiset(...).convertFrom(...) for 1:N Relationships
For order items:
multiset(
select(ORDER_ITEM.ID, ORDER_ITEM.QUANTITY, /* nested product */)
.from(ORDER_ITEM)
.where(ORDER_ITEM.PURCHASE_ORDER_ID.eq(PURCHASE_ORDER.ID))
)
.convertFrom(r -> r.map(mapping(OrderItemDTO::new)))
multiset() executes a correlated subquery and aggregates the result into a nested collection.convertFrom() maps the result into OrderItemDTO instances.
3. Nested Mapping Three Levels Deep
Inside the multiset, the product is mapped using another row().mapping():
row(
ORDER_ITEM.product().ID,
ORDER_ITEM.product().NAME,
ORDER_ITEM.product().PRICE
).mapping(ProductDTO::new)
This produces a full hierarchy:
Order → Items → Product
All fetched with a single database call.
What Does the Generated SQL Look Like?
Enable debug logging:
logging.level.org.jooq=DEBUG
Example output for PostgreSQL:
SELECT purchase_order.id,
purchase_order.order_date,
(customer.id, customer.first_name, customer.last_name,
customer.street, customer.postal_code, customer.city),
(SELECT COALESCE(
JSONB_AGG(
JSONB_BUILD_ARRAY(
t.id, t.quantity,
JSONB_BUILD_ARRAY(product.id, product.name, product.price)
)
),
JSONB_BUILD_ARRAY()
)
FROM order_item t
JOIN product ON t.product_id = product.id
WHERE t.purchase_order_id = purchase_order.id
ORDER BY t.id)
FROM purchase_order
JOIN customer ON purchase_order.customer_id = customer.id
ORDER BY purchase_order.order_date;
One query. All data. Aggregated where it belongs: in the database.
Note: The exact SQL depends on the database dialect. PostgreSQL uses JSON aggregation. Other databases may use ARRAY, MULTISET, or nested SELECTs. jOOQ handles this transparently while keeping everything type-safe.
Benefits
1. Predictable Performance
The query count is always 1, independent of data size.
No surprises under production load.
2. Full Type Safety
Everything is checked at compile time:
- Tables and columns
- Foreign key navigation
- DTO constructor signatures
Rename a column and the build fails immediately.
3. Explicit Data Loading
There is no lazy loading.
What you select is what you get.
4. Immutable DTOs
Java records are immutable.
No proxies. No detached entity problems. No accidental state changes.
5. Full SQL Power
Window functions, CTEs, lateral joins, vendor-specific features.
All available, all type-safe.
Project Setup: Testcontainers, Flyway, jOOQ CodeGen
One challenge with jOOQ is generating the type-safe classes. This project uses an elegant setup:
- Flyway manages schema migrations
- Testcontainers starts PostgreSQL during the Maven build
- jOOQ CodeGen generates Java classes from that schema
The database schema is the single source of truth.
Change the schema, run mvn compile, and the Java code is updated automatically.
Running the Demo
./mvnw compile ./mvnw spring-boot:test-run curl http://localhost:8080/orders
Result:
[
{
"id": 897,
"orderDate": "2023-11-14T14:38:40",
"customer": {
"id": 945,
"firstName": "John",
"lastName": "Doe",
"street": "123 Main St",
"postalCode": "12345",
"city": "Boston"
},
"items": [
{
"id": 1,
"quantity": 4,
"product": {
"id": 395,
"name": "Sobe - Berry Energy",
"price": 24.96
}
}
]
}
]
When to Consider jOOQ Over JPA
jOOQ is a strong choice when:
- Complex queries are common
- Performance predictability matters
- SQL control is required
- The team is comfortable with SQL
- Existing schemas must be respected
JPA may still fit better when:
- Simple CRUD dominates
- Entity-level caching is important
- Automatic dirty checking is needed
- The team prefers object-centric abstractions
MULTISET-based DTO queries are especially powerful for read models and APIs. They do not replace transactional write models.
Conclusion
Open Session in View does not solve the N+1 problem. It hides it until production.
Fetch joins help, but they are string-based, fragile, and limited.
jOOQ MULTISET offers a different path.
Use SQL deliberately. Keep type safety. Fetch exactly the data you need in one query.
No lazy loading surprises.
No proxy objects.
No hidden database calls during JSON serialization.
Sometimes, the best abstraction over SQL is simply writing good SQL.
The complete source code is available on GitHub: https://github.com/simasch/jooq-nested-dtos
