JOOQ - fetch foreign objects like in JPA

Question

i have 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 relation between author and books: author can have many books. In my application I want to fetch all authors, each one with collection of their books. Now I implemented this with 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 it requires many queries to database. One select to fetch authors and additional one query to fetch books for every author. I tried to join tables, but i don't know how to parse results correctly with jooq. Any ideas?

See this for a way to use `join`: http://stackoverflow.com/questions/38222957/jooq-can-i-fetch-a-join-of-two-tables-into-the-respective-pojos — toddkaufmann, May 08 '17 at 14:01

assylias · Accepted Answer · 2015-09-16T10:56:41.600

5

From an efficiency point of view, I would probably do it 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());

edited Sep 16 '15 at 10:56

answered Sep 16 '15 at 08:24

assylias

321,522
82
660
783

This solution will be a lot more elegant than one select sql option because there is not easy way to map your result after join. You probably will have to map the result on your own. In my opinion, go with this one. – SerhatCan Sep 16 '15 at 11:00

JOOQ - fetch foreign objects like in JPA

1 Answers1