Spring Boot + jOOQ 教程 - 5：多对多关系检索

教程

上一教程 介绍了如何使用 jOOQ 检索一对多关系的记录。本文将带你了解如何使用 jOOQ 检索多对多关系的记录。

你可以通过 Github 获取到完整的源码。

在示例数据库中，有 bookmarks（书签）表和 tags（标签）表。每个书签可以关联多个标签，反之亦然，因此 bookmarks 表和 tags 表之间存在多对多的关系。

让我们看看如何获取书签列表以及与之关联的标签

首先，创建 BookmarkWithTags record。

package com.sivalabs.bookmarks.models;

import java.util.List;

public record BookmarkWithTags(Long id, String title, String url, List<TagInfo> tags) {
    public record TagInfo (Long id, String name){}
}

使用 MULTISET Value 构造器获取多对多关系

使用 jOOQ 的 MULTISET Value Constructor 来获取书签列表和标签。

实现获取书签和标签，如下：

package com.sivalabs.bookmarks.repositories;

import com.sivalabs.bookmarks.models.BookmarkWithTags;
import org.jooq.DSLContext;
import org.springframework.stereotype.Repository;

import java.util.List;

import static com.sivalabs.bookmarks.jooq.Tables.BOOKMARK_TAG;
import static com.sivalabs.bookmarks.jooq.tables.Bookmarks.BOOKMARKS;
import static com.sivalabs.bookmarks.jooq.tables.Tags.TAGS;
import static org.jooq.Records.mapping;
import static org.jooq.impl.DSL.multiset;
import static org.jooq.impl.DSL.select;

@Repository
public class BookmarkRepository {
    private final DSLContext dsl;

    public BookmarkRepository(DSLContext dsl) {
        this.dsl = dsl;
    }

    public List<BookmarkWithTags> getBookmarksWithTags() {
        return dsl
                .select(
                    BOOKMARKS.ID, BOOKMARKS.TITLE, BOOKMARKS.URL,
                    multiset(
                            select(TAGS.ID, TAGS.NAME)
                                .from(TAGS)
                                .join(BOOKMARK_TAG)
                                .on(BOOKMARK_TAG.TAG_ID.eq(TAGS.ID))
                                .where(BOOKMARK_TAG.BOOKMARK_ID.eq(BOOKMARKS.ID))
                    ).as("tags").convertFrom(r -> r.map(mapping(BookmarkWithTags.TagInfo::new)))
                )
                .from(BOOKMARKS)
                .fetch(mapping(BookmarkWithTags::new));
    }
}

测试数据

项目中有以下 src/test/resources/test-data.sql 文件，用于将测试数据插入数据库。

# OMITTING OTHER INSERT STATEMENTS FOR BREVITY

INSERT INTO tags(id, name)
VALUES (1, 'java'),
       (2, 'spring-boot'),
       (3, 'spring-cloud'),
       (4, 'devops'),
       (5, 'security')
;

INSERT INTO bookmarks(id, title, url, created_by, created_at)
VALUES (1, 'SivaLabs', 'https://sivalabs.in', 1, CURRENT_TIMESTAMP),
       (2, 'Spring Initializr', 'https://start.spring.io', 2, CURRENT_TIMESTAMP),
       (3, 'Spring Blog', 'https://spring.io/blog', 2, CURRENT_TIMESTAMP)
;

insert into bookmark_tag(bookmark_id, tag_id)
VALUES (1, 1),
       (1, 2),
       (1, 3),
       (2, 2),
       (3, 2),
       (3, 3),
       (3, 4)
;

测试加载多对多关系记录

编写一个测试用例来验证上述方法。

package com.sivalabs.bookmarks.repositories;

import com.sivalabs.bookmarks.models.BookmarkWithTags;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.jooq.JooqTest;
import org.springframework.boot.testcontainers.service.connection.ServiceConnection;
import org.springframework.context.annotation.Import;
import org.springframework.test.context.jdbc.Sql;
import org.testcontainers.containers.PostgreSQLContainer;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;

import java.util.List;

import static org.assertj.core.api.Assertions.assertThat;

@JooqTest
@Import({BookmarkRepository.class})
@Testcontainers
@Sql("classpath:/test-data.sql")
class BookmarkRepositoryTest {

    @Autowired
    BookmarkRepository bookmarkRepository;

    @Container
    @ServiceConnection
    static final PostgreSQLContainer<?> postgres =
            new PostgreSQLContainer<>("postgres:16-alpine");

    @Test
    void getBookmarksWithTags() {
        var bookmarksWithTags = bookmarkRepository.getBookmarksWithTags();
        assertThat(bookmarksWithTags).hasSize(3);

        var javaTag = new BookmarkWithTags.TagInfo(1L, "java");
        var springBootTag = new BookmarkWithTags.TagInfo(2L, "spring-boot");
        var springCloudTag = new BookmarkWithTags.TagInfo(3L, "spring-cloud");
        var devopsTag = new BookmarkWithTags.TagInfo(4L, "devops");

        var bookmark1 = new BookmarkWithTags(1L, "SivaLabs", "https://sivalabs.in",
                List.of(javaTag, springBootTag, springCloudTag));
        var bookmark2 = new BookmarkWithTags(2L, "Spring Initializr", "https://start.spring.io",
                List.of(springBootTag));
        var bookmark3 = new BookmarkWithTags(3L, "Spring Blog", "https://spring.io/blog",
                List.of(springBootTag, springCloudTag, devopsTag));

        assertThat(bookmarksWithTags).contains(bookmark1, bookmark2, bookmark3);
    }
}

运行测试，测试通过。

总结

本文介绍了如何在 jOOQ 中使用 MULTISET Value Operator 来检索多对多关系的记录。

Ref：https://www.sivalabs.in/spring-boot-jooq-tutorial-fetching-many-to-many-associations/