Query with joins works correctly when invoking fetchOptional or fetchOne, gets "ambiguous match found" error when invoking fetchMany

[Will Casper]

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());
    }
}

[Lukas Eder]

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 

--
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 jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/c4617f5e-0abb-4195-9624-328c4ff65a7co%40googlegroups.com.

[Will Casper]

That does indeed fix the issues I've been having. Thank you!