I have the following database tables:
order line
- id - parentid
- sub - linenum
- approved - total
- product
And the following POJOs with @Column
annotations:
public class Order {
@Column(name = "id")
private Integer orderId;
@Column(name = "sub")
private Double subtotal;
@Column(name = "approved")
private Boolean approved;
private List<OrderLine> orderLines;
}
public class OrderLine {
@Column(name = "parentid")
private Integer parentId;
@Column(name = "linenum")
private Integer lineNumber;
@Column(name = "total")
private Double lineTotal;
@Column(name = "product")
private String product;
}
I write a query like the
create.select(
ORDER.ID, ORDER.SUB, ORDER.APPROVED,
multiset(
select(LINE.PARENTID, LINE.LINENUM, LINE.TOTAL, LINE.PRODUCT)
.from(LINE)
.where(LINE.PARENTID.eq(ORDER.ID))
.as("order_lines")
).from(ORDER)
.where(ORDER.ID.eq(100));
I'd like to fetch the results of this into the Order POJO.
Doing the following
fetchInto(Order.class)
works for Order fields, but I am unsure of how to handle the multiset and OrderLines. Is it possible to jOOQ to use the JPA @Column
annotations?
The DefaultRecordMapper
, which implements the fetchInto(Order.class)
style methods, has only limited support for nested collections. This is mainly because of generic type erasure on your Order
class, where List<OrderLine>
doesn't tell jOOQ much at runtime, because it's just List
(the raw type).
You could nest your DefaultRecordMapper
usage and map your multiset()
directly with an ad-hoc converter:
multiset(
select(LINE.PARENTID, LINE.LINENUM, LINE.TOTAL, LINE.PRODUCT)
.from(LINE)
.where(LINE.PARENTID.eq(ORDER.ID))
.convertFrom(r -> r.into(OrderLine.class))
.as("order_lines")
At this point, I'd like to point out that from the jOOQ perspective, it's better to use type safe mapping using constructor references because: