Thanks for your message. Just use fetch() instead of fetchMany(). You hardly ever need to use fetchMany(). The "Many" corresponds to "many results", not "many records", see: https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/ResultQuery.html#fetchMany()
The reason you're seeing the warnings is because with fetchMany(), the assumption is that there might be a trigger, stored procedure, side/effect, etc that produces interleaving result sets (or update counts, or exceptions), so the type safety of the query is meaningless. There's no guarantee that your type safe query will be the one producing the first results, hence mapping works a bit differently. I hope this helps, Lukas On Thu, Jul 9, 2020 at 6:20 AM Will Casper <[email protected]> wrote: > Hi, was hoping someone could point me in the right direction and show me > what I'm doing wrong. I'll just put up some example code that can > illustrate what I'm trying to do. Using Spring, so a functioning DSLContext > gets injected into BookRepository. Also using the code generation > (obviously). > > I have some tests written in BookRepositoryIT. From my viewpoint, the > getAllBooksTest should be passing, seeing as the bookOne and bookTwo tests > are passing. But when I try to map the records retrieved from fetchMany, I > get a bunch of "ambiguous match found" errors. I do not get them when > mapping a record retrieved using fetchOne or fetchOptional. > > Any tips as to what I'm doing wrong? > > JOOQ Version: 3.13.2 > > DDL (I'm using MySQL / MariaDB) > CREATE TABLE PERSON > ( > id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, > created DATETIME NOT NULL, > name VARCHAR(50) NOT NULL > ) CHARACTER SET utf8mb4, COLLATE utf8mb4_unicode_ci; > > CREATE TABLE BOOK > ( > id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, > created DATETIME NOT NULL, > name VARCHAR(50) NOT NULL, > author_id INT UNSIGNED NOT NULL, > coauthor_id INT UNSIGNED NULL, > editor_id INT UNSIGNED NULL, > CONSTRAINT book__person__author_id_fk FOREIGN KEY (author_id) > REFERENCES PERSON (id), > CONSTRAINT book__person__coauthor_id_fk FOREIGN KEY (coauthor_id) > REFERENCES PERSON (id), > CONSTRAINT book__person__editor_id_fk FOREIGN KEY (editor_id) > REFERENCES PERSON (id) > ) CHARACTER SET utf8mb4, COLLATE utf8mb4_unicode_ci; > > INSERT INTO PERSON (id, created, name) VALUES > (1, NOW(), 'Mike Author'), > (2, NOW(), 'Sally Coauthor'), > (3, NOW(), 'Bob Editor'); > INSERT INTO BOOK (id, created, name, author_id, coauthor_id, editor_id) > VALUES > (1, NOW(), 'Mike With Help', 1, 2, 3), > (2, NOW(), 'Mike Solo', 1, NULL, NULL); > > BookRepository.java > package com.allianceip.abs.repository; > > import com.allianceip.abs.db.Keys; > import com.allianceip.abs.db.tables.Book; > import com.allianceip.abs.db.tables.Person; > import lombok.AllArgsConstructor; > import lombok.Data; > import org.jooq.DSLContext; > import org.jooq.Record; > import org.jooq.SelectQuery; > import org.springframework.stereotype.Repository; > > import java.time.LocalDateTime; > import java.util.Collection; > import java.util.List; > import java.util.Optional; > import java.util.stream.Collectors; > > @Repository > @AllArgsConstructor > public class BookRepository { > > private final DSLContext dslContext; > > @Data > public static class PersonDto { > private Long id; > private LocalDateTime created; > private String name; > } > > @Data > public static class BookDto { > private Long id; > private LocalDateTime created; > private String name; > private Long authorId; > private PersonDto author; > private Long coauthorId; > private PersonDto coauthor; > private Long editorId; > private PersonDto editor; > } > > private static Person AUTHOR_ALIAS = Person.PERSON.as("a"); > private static Person COAUTHOR_ALIAS = Person.PERSON.as("c"); > private static Person EDITOR_ALIAS = Person.PERSON.as("e"); > > public Optional<BookDto> getBook(long id) { > SelectQuery<?> query = selectQuery(); > query.addConditions(Book.BOOK.ID.eq(id)); > return query.fetchOptional().map(this::mapRecord); > } > > public List<BookDto> getBooks() { > SelectQuery<?> query = selectQuery(); > return query.fetchMany().stream() > .flatMap(Collection::stream) > .map(this::mapRecord) > .collect(Collectors.toList()); > } > > private SelectQuery<?> selectQuery() { > return dslContext.selectQuery(Book.BOOK > .leftJoin(AUTHOR_ALIAS).onKey(Keys.BOOK__PERSON__AUTHOR_ID_FK) > .leftJoin(COAUTHOR_ALIAS).onKey(Keys. > BOOK__PERSON__COAUTHOR_ID_FK) > .leftJoin(EDITOR_ALIAS).onKey(Keys.BOOK__PERSON__EDITOR_ID_FK) > ); > } > > private BookDto mapRecord(Record record) { > BookDto book = record.into(Book.BOOK).into(BookDto.class); > PersonDto author = record.into(AUTHOR_ALIAS).into(PersonDto.class > ); > PersonDto coauthor = record.into(COAUTHOR_ALIAS).into(PersonDto. > class); > PersonDto editor = record.into(EDITOR_ALIAS).into(PersonDto.class > ); > if (author.getName() != null) book.setAuthor(author); > if (coauthor.getName() != null) book.setCoauthor(coauthor); > if (editor.getName() != null) book.setEditor(editor); > return book; > } > } > > BookRepositoryIT.java > package com.allianceip.abs.repository; > > import com.allianceip.abs.context.ITWithMariaDB4JContext; > import org.junit.Test; > import org.springframework.beans.factory.annotation.Autowired; > > import java.util.List; > > import static org.junit.Assert.*; > > public class BookRepositoryTest extends ITWithMariaDB4JContext { > > @Autowired > private BookRepository bookRepository; > > @Test // test passes > public void getNoneTest() { > assertFalse(bookRepository.getBook(0).isPresent()); > } > > @Test // test passes > public void getBookOneTest() { > BookRepository.BookDto book = bookRepository.getBook(1). > orElseThrow(RuntimeException::new); > checkBookOne(book); > } > > @Test // test passes > public void getBookTwoTest() { > BookRepository.BookDto book = bookRepository.getBook(2). > orElseThrow(RuntimeException::new); > checkBookTwo(book); > } > > @Test // test fails > public void getAllBooksTest() { > List<BookRepository.BookDto> books = bookRepository.getBooks(); > > BookRepository.BookDto bookOne = books.stream().filter(b -> b. > getId() == 1).findFirst().orElseThrow(RuntimeException::new); > checkBookOne(bookOne); > > BookRepository.BookDto bookTwo = books.stream().filter(b -> b. > getId() == 2).findFirst().orElseThrow(RuntimeException::new); > checkBookTwo(bookTwo); > } > > private void checkBookOne(BookRepository.BookDto bookOne) { > assertEquals("Mike With Help", bookOne.getName()); > assertEquals("Mike Author", bookOne.getAuthor().getName()); > assertEquals("Sally Coauthor", bookOne.getCoauthor().getName()); > assertEquals("Bob Editor", bookOne.getEditor().getName()); > } > > private void checkBookTwo(BookRepository.BookDto bookTwo) { > assertEquals("Mike Solo", bookTwo.getName()); > assertEquals("Mike Author", bookTwo.getAuthor().getName()); > assertNull(bookTwo.getCoauthor()); > assertNull(bookTwo.getEditor()); > } > } > > > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/jooq-user/c4617f5e-0abb-4195-9624-328c4ff65a7co%40googlegroups.com > <https://groups.google.com/d/msgid/jooq-user/c4617f5e-0abb-4195-9624-328c4ff65a7co%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO5o0M7L_297dKXwmJmRxibTvrtBMR3D8Q6HEGf-4LnYoA%40mail.gmail.com.
