【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>
|
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. @Transactional 注解用法
直接借助Spring的@Transactional注解来实现事务相关业务逻辑，在实际的时候需要注意一下，使用姿势不当，可能导致事务不生效，对此有疑问的小伙伴，可以查看博文【SpringBoot DB系列】事务不生效的几种case
| @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);
}
|
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);
}
|
| @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";
}
}
|
实际输出如下
| 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]
|
II. 其他
系列博文
项目源码
1. 一灰灰Blog
尽信书则不如，以上内容，纯属一家之言，因个人能力有限，难免有疏漏和错误之处，如发现bug或者有更好的建议，欢迎批评指正，不吝感激
下面一灰灰的个人博客，记录所有学习和工作中的博文，欢迎大家前去逛逛
一灰灰blog
分享到
