Spring Boot + jOOQ 教程 - 2：实现 CRUD 操作

教程

在 上一教程 中，介绍了如何使用 testcontainers-jooq-codegen-maven-plugin 生成 jOOQ 代码，以及如何使用 jOOQ DSL 执行 SQL 查询。

本文将带你了解如何使用 jOOQ 对 USERS 表执行基本的 CRUD（创建、读取、更新、删除）操作。

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

findAllUsers()

首先，从 USERS 表中获取所有用户。假设只检索 USERS 表中的 id、name、email 和 password 列。

创建 User record，如下：

package com.sivalabs.bookmarks.models;

public record User(Long id, String name, String email, String password) { }

在 UserRepository 类中实现 findAllUsers() 方法，如下：

package com.sivalabs.bookmarks.repositories;

import com.sivalabs.bookmarks.models.User;
import org.jooq.DSLContext;
import org.jooq.impl.SQLDataType;
import org.springframework.stereotype.Repository;

import java.util.List;

import static com.sivalabs.bookmarks.jooq.tables.Users.USERS;
import static org.jooq.impl.DSL.inline;

@Repository
class UserRepository {
    private final DSLContext dsl;

    UserRepository(DSLContext dsl) {
        this.dsl = dsl;
    }

    public List<User> findAllUsers() {
        return dsl
                .select(USERS.ID, USERS.NAME, USERS.EMAIL,USERS.PASSWORD)
                .from(USERS)
                .fetch(r -> new User(
                    r.get(USERS.ID),
                    r.get(USERS.NAME),
                    r.get(USERS.EMAIL),
                    r.get(USERS.PASSWORD))
                );
    }
}
  • 查询 USERS 表，只返回 id、name、email 和 password 列。
  • fetch() 方法会返回一个 UsersRecord 对象列表，逐个将它们转换为 User 对象。

如果 User record 有一个构造函数，该构造函数将所有选定的列值类型作为参数（本例中为 Long、String、String、String），那么可以使用以下语法：

public List<User> findAllUsers() {
    return dsl
        .select(USERS.ID, USERS.NAME, USERS.EMAIL, USERS.PASSWORD)
        .from(USERS)
        .fetch(r -> r.into(User.class));
}

如上，使用反射将 UsersRecord 字段映射到 User 对象中。

你也可以使用 org.jooq.Records.mapping() 将 UsersRecord 字段映射到 User 对象，如下所示：

import static org.jooq.Records.mapping;

public List<User> findAllUsers(){
    return dsl.select(USERS.ID,USERS.NAME,USERS.EMAIL,USERS.PASSWORD)
            .from(USERS)
            //使用 lambda
            .fetch(mapping((id,name,email,password) -> new User(id,name,email,password)));
    
            //使用构造函数引用
            //.fetch(mapping(User::new));
        
            //使用工厂方法引用
            //.fetch(mapping(User::create));
}

// User 类中的工厂方法
public record User (Long id, String name, String email, String password
) {
    public static User create(Long id, String name, String email, String password) {
        return new User(id, name, email, password);
    }
}

如果要选择 USERS 表中的所有列，并且有一个相应的构造函数，那么可以使用下面的语法：

public record User (Long id, String name, String email, String password,
                    Long userPreferencesId, LocalDateTime createdAt, LocalDateTime updatedAt) { }

public List<User> findAllUsers(){
    return dsl.selectFrom(USERS).fetch(r->r.into(User.class));
}

但最好的做法是只选择用例所需的列/数据（不要无脑 SELECT *），并将结果映射到 DTO/Record 中。

findUserById()

在 UserRepository 类中实现 findUserById() 方法如下：

public Optional<User> findUserById(Long id) {
    return dsl
            .select(USERS.ID, USERS.NAME, USERS.EMAIL, USERS.PASSWORD)
            .from(USERS)
            .where(USERS.ID.eq(id))
        .fetchOptional()
        // 使用反射
        //.map(r -> r.into(User.class));
        // 使用工厂方法
        .map(mapping(User::create));
}

除了使用 fetchOptional() 方法返回一个 Optional 对象外，该方法的实现与 findAllUsers() 方法非常相似。

如果要加载 USERS 表中的所有列，并且有相应的 User 构造函数，则可以使用以下语法：

public Optional<User> findUserById(Long id){
    return dsl.fetchOptional(USERS,USERS.ID.eq(id))
            .map(r -> r.into(User.class));
}

如果你有许多 findByXXX() 方法，它们具有相同的选择列和相同的映射逻辑，那么你可以按如下方式将其抽象到一个方法中：

public Optional<User> findUserById(Long id) {
    return getSelectUserSpec()
            .where(USERS.ID.eq(id))
            .fetchOptional(new UserRecordMapper());
}
        
public Optional<User> findUserByEmail(String email) {
    return getSelectUserSpec()
            .where(USERS.EMAIL.equalIgnoreCase(email))
            .fetchOptional(new UserRecordMapper());
}

private SelectJoinStep<Record4<Long, String, String, String>> getSelectUserSpec() {
        return dsl.select(USERS.ID, USERS.NAME, USERS.EMAIL, USERS.PASSWORD)
        .from(USERS);
}

static class UserRecordMapper
        implements RecordMapper<Record4<Long, String, String, String>, User> {
    @Override
    public User map(Record4<Long, String, String, String> userRecord) {
        return new User(
                userRecord.get(USERS.ID),
                userRecord.get(USERS.NAME),
                userRecord.get(USERS.EMAIL),
                userRecord.get(USERS.PASSWORD)
        );
    }
}

createUser()

在 UserRepository 类中实现 createUser() 方法，如下：

public User createUser(User user) {
    return dsl.insertInto(USERS)
            .set(USERS.NAME, user.name())
            .set(USERS.EMAIL, user.email())
            .set(USERS.PASSWORD, user.password())
            .returning()
            .fetchOne(record -> new User(
                    record.getId(),
                    record.getName(),
                    record.getEmail(),
                    record.getPassword()));
}

使用 dsl.insertInto(USERS) 开始插入记录，然后使用 set() 方法为不同列设置值。这是插入新记录的一种方法，使用 jOOQ 还有其他几种方式可以实现。

使用 returning() 方法返回新插入记录的详细信息，并将其映射到 User 对象。

你也可以使用稍有不同的方法/语法，如下所示：

public User createUser(User user){
    return dsl.insertInto(USERS,USERS.NAME,USERS.EMAIL,USERS.PASSWORD)
            .values(user.name(),user.email(),user.password())
            .returning()
            .fetchOne(record -> new User(
                        record.getId(),
                        record.getName(),
                        record.getEmail(),
                        record.getPassword()));
}

另一种方法是使用 UsersRecord（UpdatableRecord） 对象：

public User createUser(User user){
    UsersRecord record=dsl.newRecord(USERS);
    record.setName(user.name());
    record.setEmail(user.email());
    record.setPassword(user.password());
    record.store();
    
    return new User(record.getId(),
                    record.getName(),
                    record.getEmail(),
                    record.getPassword());
}

另一种方法是使用 UsersRecord 从 User 对象中填充数据：

public User createUser(User user){
    UsersRecord record = dsl.newRecord(USERS,user);
    record.store();
    return new User(record.getId(),
                    record.getName(),
                    record.getEmail(),
                    record.getPassword());
}

请查阅 org.jooq.Record.from(Object source)（在内部调用 dsl.newRecord(USERS, user)）、API 文档以了解映射规则。

关联对象的持久化

与 JPA 不同，jOOQ 不支持自动持久化关联对象。

例如，如果 User 有一个关联对象 UserPreferences，则需要单独持久化 UserPreferences。

updateUser()

在 UserRepository 类中实现 updateUser() 方法，如下：

public void updateUser(User user) {
    dsl.update(USERS)
        .set(USERS.NAME, user.name())
        .where(USERS.ID.eq(user.id()))
        .execute();
    
    //另一种方法是在更新前检查记录是否存在
    /*
    dsl.fetchOptional(USERS, USERS.ID.eq(user.id()))
            .ifPresent(record -> {
                record.setName(user.name());
                record.store();
            });
    */
}

这里，只更新 USERS 表中的 name 列。execute() 方法会返回更新的行数。

deleteUser()

在 UserRepository 类中实现 deleteUser() 方法，如下：

public void deleteUser(Long id) {
    dsl.deleteFrom(USERS)
        .where(USERS.ID.eq(id))
        .execute();
}

使用 Testcontainers 进行测试

编写 UserRepositoryTest 来测试上述 CRUD 操作。

package com.sivalabs.bookmarks.repositories;

import com.sivalabs.bookmarks.models.User;
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 findAllUsers() {
        List<User> users = userRepository.findAllUsers();
        assertThat(users).hasSize(2);
        // 更多断言
    }

    @Test
    void findUserById() {
        Optional<User> userOptional = userRepository.findUserById(1L);
        assertThat(userOptional).isPresent();
        assertThat(userOptional.get().id()).isEqualTo(1L);
        assertThat(userOptional.get().name()).isEqualTo("Admin");
        assertThat(userOptional.get().email()).isEqualTo("admin@gmail.com");
        assertThat(userOptional.get().password()).isEqualTo("admin");
    }

    @Test
    void createUser() {
        User user = new User(null, "SivaLabs", "sivalabs@gmail.com", "siva1234");

        User savedUser = userRepository.createUser(user);
        assertThat(savedUser.id()).isNotNull();
        assertThat(savedUser.name()).isEqualTo("SivaLabs");
        assertThat(savedUser.email()).isEqualTo("sivalabs@gmail.com");
        assertThat(savedUser.password()).isEqualTo("siva1234");
    }

    @Test
    void updateUser() {
        User user = createTestUser();
        User updateUser = new User(user.id(), "TestName1", user.email(), user.password());
        userRepository.updateUser(updateUser);

        User updatedUser = userRepository.findUserById(updateUser.id()).orElseThrow();

        assertThat(updatedUser.id()).isEqualTo(updateUser.id());
        assertThat(updatedUser.name()).isEqualTo("TestName1");
        assertThat(updatedUser.email()).isEqualTo(user.email());
        assertThat(updatedUser.password()).isEqualTo(user.password());
    }

    @Test
    void deleteUser() {
        User user = createTestUser();
        userRepository.deleteUser(user.id());

        Optional<User> optionalUser = userRepository.findUserById(user.id());
        assertThat(optionalUser).isEmpty();
    }

    private User createTestUser() {
        String uuid = UUID.randomUUID().toString();
        User user = new User(null, uuid, uuid+"@gmail.com", "Secret");
        return userRepository.createUser(user);
    }
}

总结

本文介绍了如何在 Spring Boot 中使用 jOOQ 实现基本的 CRUD 操作。

Ref：https://www.sivalabs.in/spring-boot-jooq-tutorial-crud-operations/