CODEWITHSUNDEEP
javajooq
Conner
Conner

Reputation: 677

Fetch Single Table into Nested Pojo

I have the following database table:

catalog
- author
- book_name
- publication_date

Values in the catalog table look something like

author book_name publication_date
Mark Smith How to Read 2024-01-01
Sarah Doe A Book 1826-01-01
Sarah Doe A Second Book 1855-07-15

I want to fetch the table into a Catalog POJO similar to this one:

public class Catalog {
    private String author;
    List < BookEntry > book_entries;
}

public class BookEntry {
    private String bookName;
    private Date publicationDate;
}

I have used the multiset (https://www.jooq.org/doc/latest/manual/sql-building/column-expressions/multiset-value-constructor/) operator for doing similar things across separate tables, but I don't know if it is appropriate for this particular use-case.

Upvotes: 1

Views: 30

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220867

The simplest way seems to be MULTISET_AGG(), in this case:

ctx.select(CATALOG.AUTHOR,
        multisetAgg(CATALOG.BOOK_NAME, CATALOG.PUBLICATION_DATE)
        .convertFrom(r -> r.map(Records.mapping(BookEntry::new))))
   .from(CATALOG)
   .groupBy(CATALOG.AUTHOR)
   .fetch(Records.mapping(Catalog::new))

Assuming you have the appropriate constructors on those classes, of course, and that you can handle the perils of not normalising this schema.

Upvotes: 0

Related Questions