JOOQ - selection of extraneous objects such as JPA

Question

There are two tables in my database:

CREATE TABLE `AUTHOR` (
  `ID` varchar(255) NOT NULL,
  `NAME` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`)
)

CREATE TABLE `BOOK` (
  `ID` varchar(255) NOT NULL,
  `TITLE` varchar(255) NOT NULL,
  `AUTHOR_ID` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`),
  FOREIGN KEY (`AUTHOR_ID`) REFERENCES `AUTHOR` (`ID`)
)

As you can see, there is a connection between the author and books: the author can have many books. In my application I want to get all the authors, each of which contains a collection of their books. Now I implemented this with the code:

public List<Author> findAll() {
        List<Author> authors = dsl.selectFrom(AUTHOR)
                .fetchInto(Author.class);

        return authors.stream()
                .map(author -> new Author(author.getId(), author.getName(), getBooksForAuthor(author.getId())))
                .collect(Collectors.toList());
    }

    private List<Book> getBooksForAuthor(String authorId) {
        return dsl.select(BOOK.ID, BOOK.TITLE)
                .from(BOOK)
                .where(BOOK.AUTHOR_ID.eq(authorId))
                .fetchInto(Book.class);
    }

Unfortunately, this requires a lot of database queries. One chooses to retrieve authors and an additional one query to retrieve books for each author. I tried to join the tables, but I don't know how to parse the results correctly using jooq. Any ideas?

Answer 1

In terms of efficiency, I would do this in two steps:

List<Author> authors = dsl.selectFrom(AUTHOR)
                          .fetchInto(Author.class);

Map<String, List<Book>> booksByAuthorId = dsl.selectFrom(BOOK)
                                             .fetchMap(BOOK.AUTHOR_ID, Book.class);

return authors.stream()
    .map(author -> new Author(author.getId(),
                              author.getName(),
                              booksByAuthorId.getOrDefault(author.getId(), emptyList())))
    .collect(Collectors.toList());