jooq

Adhoc Converts with Multiset & JPA

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?

Solution

  • 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: