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: