java - JOOQ - 我如何使用字段列表将数据获取到 pojo

Question

我有一个包含三个表的 DDBB:loan、person 和 Loan_person_rel 以及每个表各自的 POJO。

贷款

private int line;
private Double balance;
private Double expired;
private int state;
private int defaultDays;
private Branch branch;
private String balanceHistory;
private String expiredHistory;
private Long loanCode;
private List<Person> persons;
private String extraInfo;
private LoanTypes loanType;
private String nomPro;
//The class contains the getters and setters :)

人

private String name;
private String documentNumber;
private String workEnterprise;
private String workCity;
private String workAddress;
private String workNeighborhood;
private String workPhone;
private String occupation;
private String homePhone;
private String cellPhone;
private String phone3;
private String phone4;
private String homeAddress;
private String homeCity;
private String homeNeighborhood;
private String email;
private String relationship;
private List<Loan> loans;
//The class contains the getters and setters :)

Loan_person_rel

private String personId;
private String loanId;
private int type;
//The class contains the getters and setters :)

我如何构建 JOOQ 选择或某种方法来检索数据并使用填充的字段 persons 填充类贷款？

Answer 1

使用 MULTISET 的 jOOQ 3.15 解决方案

开始 with jOOQ 3.15, the standard SQL MULTISET operator was introduced ，如果需要，可以使用 SQL/XML 或 SQL/JSON 进行模拟。为简单起见，我假设您的 Loan 和 Person 类是 Java 16 记录:

List<Loan> result =
ctx.select(
      // Project the loan columns you need
      LOAN.LINE,
      LOAN.BALANCE,
      ..
      multiset(
        select(PERSON.NAME, PERSON.DOCUMENT_NUMBER, ...)
        .from(PERSON)
        .join(LOAN_PERSON_REL)
        .on(PERSON.PERSON_ID.eq(LOAN.PERSON_REL.PERSON_ID))
        .where(LOAN_PERSON_REL.LOAN_ID.eq(LOAN.LOAN_ID))
      ).as("persons").convertFrom(r -> r.map(Records.mapping(Person::new)))
   )
   .from(LOAN)
   .fetch(Records.mapping(Loan::new));

使用 new jOOQ 3.15 ad-hoc converter feature 映射到 Loan 和 Person 构造函数引用是类型安全且无反射的。 .

与基于 JPA 的 ORM 不同，jOOQ 不提供对象图持久性，即您的 Person 对象不能包含返回到 Loan< 的基于身份的引用 对象。相反，这种方法以树形式投影数据，这可能适合您的用例。

使用 SQL/XML 或 SQL/JSON 的 jOOQ 3.14 解决方案

从 jOOQ 3.14 开始，这里的首选方法是 nest your collections directly in SQL using SQL/XML or SQL/JSON 。您可以编写如下查询:

List<Loan> result =
ctx.select(
      // Project the loan columns you need, or all of them using LOAN.asterisk()
      LOAN.LINE,
      LOAN.BALANCE,
      ...
      field(select(
        jsonArrayAgg(jsonObject(
          key("name").value(PERSON.NAME),
          key("documentNumber").value(PERSON.DOCUMENT_NUMBER),
          ...
        ))
        .from(PERSON)
        .join(LOAN_PERSON_REL)
        .on(PERSON.PERSON_ID.eq(LOAN.PERSON_REL.PERSON_ID))
        .where(LOAN_PERSON_REL.LOAN_ID.eq(LOAN.LOAN_ID))
      )).as("persons")
    )
   .from(LOAN)
   .fetchInto(Loan.class);

关于获取树而不是图的相同限制适用。

请注意，JSON_ARRAYAGG() 将空集聚合到 NULL 中，而不是聚合到空的 [] 中。 If that's a problem, use COALESCE()