Search code examples
javajooq

JOOQ - How i can fetch data into pojo with a field list

I have a DDBB with three tables: loan, person and loan_person_rel and the respective POJO for each table.

Loan

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

Person

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

How i can build a JOOQ select or some method for retrieve the data and fill the class loan with the field persons populated?

Solution

  • jOOQ 3.15 solutoin using MULTISET

    Starting with jOOQ 3.15, the standard SQL MULTISET operator was introduced, which is emulated using SQL/XML or SQL/JSON if needed. For simplicity, I'm assuming your Loan and Person classes are Java 16 records:

    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));

    The mapping into the Loan and Person constructor references is type safe and reflection free, using the new jOOQ 3.15 ad-hoc converter feature.

    Unlike JPA based ORMs, jOOQ doesn't offer object graph persistence, i.e. your Person objects can't contain identity-based references back to Loan objects. Instead, this approach projects data in tree form, which may be fine for your use-cases.

    jOOQ 3.14 solution using SQL/XML or SQL/JSON

    Starting with jOOQ 3.14, the preferred approach here is to nest your collections directly in SQL using SQL/XML or SQL/JSON. You could write a query like this:

    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);

    The same restriction about this fetching trees instead of graphs applies.

    Note that JSON_ARRAYAGG() aggregates empty sets into NULL, not into an empty []. If that's a problem, use COALESCE()