【DB系列】Jooq之事务

在SpringBoot中使用事务比较简单，大部分场景下，直接借助@Transactional注解即可，本文将介绍一下Jooq中使用事务的两种方式

# I. 项目搭建

本项目借助SpringBoot 2.2.1.RELEASE + maven 3.5.3 + IDEA进行开发

# 1. 项目依赖

关于如何创建一个SpringBoot的项目工程，不再本文的描述范围内，如有兴趣可以到文末的个人站点获取

在这个示例工程中，我们的选用h2dabase作为数据库（方便有兴趣的小伙伴直接获取工程源码之后，直接测试体验），因此对应的pom核心依赖如下

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jooq</artifactId>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
    </dependency>
</dependencies>

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 2. 数据准备

本文对应的项目工程，和前面介绍增加删除的一致，所以这里直接使用之前新增的数据

# II. 使用姿势

# 0. 前期准备

@Repository
public class PoetryTransactionRepository {
    private static final PoetTB poetTable = PoetTB.POET;
    private static final PoetryTB poetryTable = PoetryTB.POETRY;

    @Autowired
    private DSLContext dsl;
}

1
2
3
4
5
6
7
8

# 1. @Transactional 注解用法

直接借助Spring的@Transactional注解来实现事务相关业务逻辑，在实际的时候需要注意一下，使用姿势不当，可能导致事务不生效，对此有疑问的小伙伴，可以查看博文【SpringBoot DB系列】事务不生效的几种case (opens new window)

@Transactional(rollbackFor = Exception.class)
public void transaction() {
    PoetryPO poetryPO = dsl.newRecord(poetryTable);
    poetryPO.setId(10);
    poetryPO.setPoetId(1);
    poetryPO.setTitle("事务插入的标题");
    poetryPO.setContent("事务插入的内容");
    poetryPO.setCreateAt(new Timestamp(System.currentTimeMillis()));
    boolean ans = poetryPO.insert() > 0;
    System.out.println(ans);

    // 这个会插入失败，长度超限
    PoetPO record = dsl.newRecord(poetTable);
    record.setId(20);
    record.setName("123456789100918237645412738123");
    boolean ans2 = record.insert() > 0;
    System.out.println(ans2);
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

# 2. jooq声明式事务

一般使用Spring的@Transactional注解时，需要注意不能是同类的内部方法调用，因为这样不会走代理，导致事务不生效；

如果确实存在事务的业务需求，但是又不想拆分到另外一个对象类，那么可以考虑一下声明式事务的用法，如下

public void trans2() {
    boolean ans = dsl.transactionResult(new TransactionalCallable<Boolean>() {
        @Override
        public Boolean run(Configuration configuration) throws Throwable {
            final DSLContext inner = DSL.using(configuration);

            PoetryPO poetryPO = inner.newRecord(poetryTable);
            poetryPO.setId(11);
            poetryPO.setPoetId(1);
            poetryPO.setTitle("事务插入的标题2");
            poetryPO.setContent("事务插入的内容2");
            poetryPO.setCreateAt(new Timestamp(System.currentTimeMillis()));
            boolean ans = poetryPO.insert() > 0;
            System.out.println(ans);

            // 这个会插入失败，长度超限
            PoetPO record = inner.newRecord(poetTable);
            record.setId(20);
            record.setName("123456789100918237645412738123");
            boolean ans2 = record.insert() > 0;
            System.out.println(ans2);

            return null;
        }
    });
    System.out.println(ans);
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

# 3. 测试

@RestController
public class TestRest {
    @Autowired
    private PoetryTransactionRepository poetryTransactionRepository;

    @GetMapping(path = "trans")
    public String transaction() {
        try {
            poetryTransactionRepository.transaction();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    
        try {
            poetryTransactionRepository.trans2();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    
        return "over";
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

实际输出如下

true
jOOQ; SQL [insert into "PUBLIC"."POET" ("ID", "NAME") values (cast(? as int), cast(? as varchar))]; Value too long for column "NAME VARCHAR(20)": "'123456789100918237645412738123' (30)"; SQL statement:
insert into "PUBLIC"."POET" ("ID", "NAME") values (cast(? as int), cast(? as varchar)) [22001-200]; nested exception is org.h2.jdbc.JdbcSQLDataException: Value too long for column "NAME VARCHAR(20)": "'123456789100918237645412738123' (30)"; SQL statement:
insert into "PUBLIC"."POET" ("ID", "NAME") values (cast(? as int), cast(? as varchar)) [22001-200]
true
jOOQ; SQL [insert into "PUBLIC"."POET" ("ID", "NAME") values (cast(? as int), cast(? as varchar))]; Value too long for column "NAME VARCHAR(20)": "'123456789100918237645412738123' (30)"; SQL statement:
insert into "PUBLIC"."POET" ("ID", "NAME") values (cast(? as int), cast(? as varchar)) [22001-200]; nested exception is org.h2.jdbc.JdbcSQLDataException: Value too long for column "NAME VARCHAR(20)": "'123456789100918237645412738123' (30)"; SQL statement:
insert into "PUBLIC"."POET" ("ID", "NAME") values (cast(? as int), cast(? as varchar)) [22001-200]