Fetch Single Table into Nested Pojo

Question

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.

Answer 1

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.