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

Question

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?

What you asking here looks like you need a full-blown ORM solution, not JOOQ. JOOQ is relatively simple SQL mapper/generator library, not a model graph query tool. Or at least this was always my impression. — M. Prokhorov, Mar 20 '18 at 16:48
If for some reason you really *must not* use a JPA-based entity graph persistence framework, you might be able to pull this off using jOOQ and something like http://simpleflatmapper.org/0106-getting-started-jooq.html. But usually, it's much simple to embrace SQL and work with what the JPA folks call "projections" (i.e. just plain SQL) and not worry about nesting collections too much. — Lukas Eder, Mar 21 '18 at 09:07

Lukas Eder · Accepted Answer · 2021-07-07T11:10:45.633

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

score 1 · Answer 2 · answered Apr 23 '18 at 16:55

It must be so way:

List<Loan*> list = dsl.selectFrom(Loan).fetch(this::recordToPojo);

private Loan recordToPojo(final LoanRecord record) {
    return new Loan(
            record.getLine(),
            record.getBalance(),
            ....
    );
}

*Loan - name of pojo!

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

2 Answers2

jOOQ 3.15 solutoin using MULTISET

jOOQ 3.14 solution using SQL/XML or SQL/JSON

jOOQ 3.15 solutoin using `MULTISET`