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.
