I have the following database table:

catalog
- author
- book_name
- publication_date

Values in the catalog table look something like

I have the following database table:

catalog
- author
- book_name
- publication_date

Values in the catalog table look something like

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./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.

• java
• jooq

1 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.