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

教程

上一教程 中介绍了如何使用 jOOQ 检索一对一（*One-to-One）关系的记录。本文将带你了解如何使用 jOOQ 检索一对多（One-to-Many）关系的记录。

你可以在 Github 获取完整的源码。

在示例数据库中，有 users（用户）表和 bookmarks（书签）表。每个用户可以创建多个书签，因此 users 表和 bookmarks 表之间是一对多的关系。

让我们看看如何获取用户详细信息以及用户创建的书签。

首先，创建 UserWithBookmarks Record。

package com.sivalabs.bookmarks.models;

import java.util.List;

public record UserWithBookmarks(Long id, String name, String email, List<BookmarkInfo> bookmarks) {
    public record BookmarkInfo (Long id, String title, String url){}
}

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

使用 jOOQ 的 MULTISET Value 构造函数来获取用户创建的书签列表。有关 MULTISET Value 构造函数的更多详情，请访问：https://www.jooq.org/doc/latest/manual/sql-building/column-expressions/multiset-value-constructor/。

此外，强烈推荐你阅读《jOOQ 3.15 的新 Multiset Operator 将如何改变你对 SQL 的看法》一文。

实现获取用户详细信息以及该用户创建的书签。

@Repository
public class UserRepository {
    ...
    ...
    public Optional<UserWithBookmarks> getUserWithBookmarksById(Long userId) {
        return dsl
                .select(
                        USERS.ID, USERS.NAME, USERS.EMAIL,
                        multiset(
                                select(BOOKMARKS.ID, BOOKMARKS.TITLE, BOOKMARKS.URL)
                                        .from(BOOKMARKS)
                                        .where(BOOKMARKS.CREATED_BY.eq(USERS.ID))
                        ).as("bookmarks").convertFrom(r -> r.map(mapping(UserWithBookmarks.BookmarkInfo::new)))
                )
                .from(USERS)
                .where(USERS.ID.eq(userId))
                .fetchOptional()
                .map(mapping(UserWithBookmarks::new));
    }
}

测试数据

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

# OMITTING OTHER INSERT STATEMENTS FOR BREVITY

INSERT INTO users (id, email, password, name, preferences_id)
VALUES (1, 'admin@gmail.com', 'admin', 'Admin', 2),
       (2, 'siva@gmail.com', 'siva', 'Siva', 1)
;
  
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)
;

测试加载一对多关系记录

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

package com.sivalabs.bookmarks.repositories;

import com.sivalabs.bookmarks.models.User;
import com.sivalabs.bookmarks.models.UserWithBookmarks;
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 java.util.Optional;
import java.util.UUID;

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

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

    @Autowired
    UserRepository userRepository;

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

    @Test
    void getUserWithBookmarks() {
        Optional<UserWithBookmarks> userOptional = userRepository.getUserWithBookmarksById(2L);
        assertThat(userOptional).isPresent();
        UserWithBookmarks user = userOptional.get();
        assertThat(user.id()).isEqualTo(2L);
        assertThat(user.name()).isEqualTo("Siva");
        assertThat(user.email()).isEqualTo("siva@gmail.com");

        assertThat(user.bookmarks()).hasSize(2);

        var bookmark1 = new UserWithBookmarks.BookmarkInfo(2L, "Spring Initializr", "https://start.spring.io");
        var bookmark2 = new UserWithBookmarks.BookmarkInfo(3L, "Spring Blog", "https://spring.io/blog");

        assertThat(user.bookmarks()).contains(bookmark1, bookmark2);
    }
}

运行测试，测试通过。

总结

本文介绍了如何在 jOOQ 中通过 MULTISET Value Operator 检索一对多关系的记录。

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