目录大纲

Blog

SQL的事务控制以及JdbcTemplate

2021-04-05 03:04:24 · 创建时间 2025-08-10 14:06:01 · 更新时间

Java Sql

本文内容将不定期修订与完善，请以最新版本为准。转载或引用本文内容时，请明确注明作者及原文链接。

摘要

学习Spring的JdbcTemplate，以及Spring基于XML或者注解的声明式事务控制

正文

一、JdbcTemplate

1.1 概述

JdbcTemplate是spring框架中提供的一个对象，是对原始Jdbc API对象的简单封装。

Spring框架提供的工具类

关系型数据库
- JdbcTemplate
- HibernateTemplate
非关系型数据库
- RedisTemplate
操作消息队列
- JmsTemplate

JdbcTemplate的作用：它就是与数据库交互的，实现对表的CRUD操作

1.2 使用

1.2.1 简单使用

可以直接用

java
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
public class JdbcTemplateDemo01 {
    public static void main(String[] args) {
        //准备数据源：Spring的内置数据源
        DriverManagerDataSource ds = new DriverManagerDataSource();
        ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
        ds.setUrl("jdbc:mysql://localhost:3306/spring?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai");
        ds.setUsername("root");
        ds.setPassword("root");
        //1.创建JdbcTemplate对象
        JdbcTemplate jt=new JdbcTemplate();
        jt.setDataSource(ds);
        //2. 执行操作
        jt.execute("insert into account(name,money)values('ccc',1000)");
    }
}

1.2.2 Spring注解使用

Config

java
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
@Configuration
@ComponentScan("top.meethigher.demo19")
@EnableAspectJAutoProxy
public class Config {
    @Bean("ds")
    public DataSource createDataSource(){
        DriverManagerDataSource ds = new DriverManagerDataSource();
        ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
        ds.setUrl("jdbc:mysql://localhost:3306/spring?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai");
        ds.setUsername("root");
        ds.setPassword("root");
        return ds;
    }
    @Bean("jdbcTemplate")
    public JdbcTemplate createJdbcTemplate(@Qualifier("ds") DataSource ds){
        return new JdbcTemplate(ds);
    }
}

Account

java
 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
28
29
30
31
32
33
34
35
36
37
38
public class Account implements Serializable {
    private Integer id;
    private String name;
    private Float money;

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", money=" + money +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Float getMoney() {
        return money;
    }

    public void setMoney(Float money) {
        this.money = money;
    }
}

测试

java
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
public class JdbcTemplateDemo02 {
    public static void main(String[] args) {
        AnnotationConfigApplicationContext aac = new AnnotationConfigApplicationContext(Config.class);
//        JdbcTemplate jdbcTemplate = (JdbcTemplate) aac.getBean("jdbcTemplate");
        JdbcTemplate jdbcTemplate = aac.getBean("jdbcTemplate", JdbcTemplate.class);
        //插入
//        jdbcTemplate.execute("insert into account(name,money)values('ddd',2222)");
        //修改
//        jdbcTemplate.update("update account set name=?,money=? where id=?","bbb",111,13);
        //删除
//        jdbcTemplate.update("delete from account where id=?",13);
        //查询所有
//        List<Account> query = jdbcTemplate.query("select * from account", new BeanPropertyRowMapper<>(Account.class));
//        System.out.println(query);
        //查询一个
//        List<Account> query = jdbcTemplate.query("select * from account where id=?", new BeanPropertyRowMapper<>(Account.class), 1);
//        System.out.println(query.get(0));
        //查询总记录数
        Long aLong = jdbcTemplate.queryForObject("select count(*) from account where money>?", Long.class, 1000);
        System.out.println(aLong);
    }
}

1.2.3 Spring与Dao的联合使用的两种方式

使用注解的dao层来实现

java
 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
28
29
30
@Component("accoundDao")
public class AccountDaoImpl implements IAccountDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public Account findAccountById(Integer accountId) {
        List<Account> query = jdbcTemplate.query("select * from account where id=?", new BeanPropertyRowMapper<>(Account.class), accountId);
        if(query.isEmpty()){
            return null;
        }
        return query.get(0);
    }

    @Override
    public Account findAccountByName(String name) {
        List<Account> query = jdbcTemplate.query("select * from account where name=?", new BeanPropertyRowMapper<>(Account.class), name);
        if(query.isEmpty()){
            return null;
        }
        if(query.size()>1){
            throw new RuntimeException("结果不唯一");
        }
        return query.get(0);
    }

    @Override
    public void updateAccount(Account account) {
        jdbcTemplate.update("update account set name=?,money=? where id=?",account.getName(),account.getMoney(),account.getId());
    }
}

使用xml的dao层来实现

但是如果有很多个dao的话，就会有重复代码了，那么如何解决？

我们可以通过创建一个dao的共有父类，让其他子类继承。

而spring中，提供了这种个父类JdbcDaoSupport

AccountDaoImpl

java
 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
public class AccountDaoImpl extends JdbcDaoSupport implements IAccountDao {
    @Override
    public Account findAccountById(Integer accountId) {
        List<Account> query = super.getJdbcTemplate().query("select * from account where id=?", new BeanPropertyRowMapper<>(Account.class), accountId);
        if(query.isEmpty()){
            return null;
        }
        return query.get(0);
    }

    @Override
    public Account findAccountByName(String name) {
        List<Account> query = super.getJdbcTemplate().query("select * from account where name=?", new BeanPropertyRowMapper<>(Account.class), name);
        if(query.isEmpty()){
            return null;
        }
        if(query.size()>1){
            throw new RuntimeException("结果不唯一");
        }
        return query.get(0);
    }

    @Override
    public void updateAccount(Account account) {
        super.getJdbcTemplate().update("update account set name=?,money=? where id=?",account.getName(),account.getMoney(),account.getId());
    }
}

spring.xml

xml
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        https://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/aop
        https://www.springframework.org/schema/aop/spring-aop.xsd
        http://www.springframework.org/schema/context
        https://www.springframework.org/schema/context/spring-context.xsd">
    <bean id="accountDao" class="top.meethigher.demo20.dao.impl.AccountDaoImpl">
        <property name="dataSource" ref="ds"/>
    </bean>
    <bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/spring?useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=Asia/Shanghai&amp;useSSL=false"></property>
        <property name="username" value="root"></property>
        <property name="password" value="root"></property>
    </bean>
</beans>

测试类

java
1
2
3
4
5
6
7
8
9
public class JdbcTemplateDemo03 {
    public static void main(String[] args) {
        ClassPathXmlApplicationContext aac = new ClassPathXmlApplicationContext("spring.xml");
        AccountDaoImpl accoundDao = aac.getBean("accountDao", AccountDaoImpl.class);
        System.out.println(accoundDao.findAccountById(1));
//        System.out.println(accoundDao.findAccountByName("水冰儿"));
        System.out.println(accoundDao.findAccountByName("小舞"));
    }
}

二、事务控制

2.1 转账案例再次优化

如果使用环绕通知，只需要修改如下即可。会自动将impl包下的类作为切入点，进行增强

java
 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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
@Component("tsManager")
@Aspect
public class TransactionManager {
    @Autowired
    private ConnectionUtils connectionUtils;
    @Pointcut("execution(* top.meethigher.demo21.service.impl.*.*(..))")
    private void pt(){}

    /**
     * 开启手动提交事务
     */
//    @Before("pt()")
    public void beginTransaction(){
        try {
            connectionUtils.getThreadConnection().setAutoCommit(false);
            System.out.println("开启手动提交事务");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 提交事务
     */
//    @AfterReturning("pt()")
    public void commitTransaction(){
        try {
            connectionUtils.getThreadConnection().commit();
            System.out.println("提交事务");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 回滚事务
     */
//    @AfterThrowing("pt()")
    public void rollbackTransaction(){
        try {
            connectionUtils.getThreadConnection().rollback();
            System.out.println("回滚事务");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 释放连接
     */
//    @After("pt()")
    public void release(){
        try {
            connectionUtils.getThreadConnection().close();//归还连接
            System.out.println("归还连接");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 环绕通知实现增强
     * @param proceedingJoinPoint
     * @return
     */
    @Around("pt()")
    public Object around(ProceedingJoinPoint proceedingJoinPoint){
        Object returnValue=null;
        try{
            beginTransaction();
            System.out.println("前置通知->开启事务");
            returnValue = proceedingJoinPoint.proceed(proceedingJoinPoint.getArgs());
            commitTransaction();
            System.out.println("后置通知->提交事务");
            return returnValue;
        }catch (Throwable t){
            rollbackTransaction();
            System.out.println("异常通知->回滚事务");
            throw new RuntimeException(t);
        }finally {
            release();
            System.out.println("最终通知->释放连接");
        }
    }
}

此时我们就需要思考了，这是使用QueryRunner实现的，如果是JdbcTemplate，如何实现事务控制呢？

2.2 spring声明式事务

2.2.1 基于xml的声明式事务

步骤

配置事务管理器
- spring提供的DataSourceTransactionManager
配置事务通知
- 需要导入事务的约束。官方文档DataAccess，搜索xmlns:tx（同时也需要aop的）
- 使用tx:advice标签配置事务通知
  - id：事务通知的唯一标志
  - transaction-manager：给事务通知提供一个事务管理器引用
配置aop中的通用切入点表达式：aop:pointcut
建立事务通知和切入点表达式的对应关系：aop:advisor
配置事务的属性：在tx:advice的内部下的tx:method
- name：方法名称。可以使用通配符*表示所有。如果名称规范的话，findxxx，像jpa那样，可以这么统配find*。*与find*，后者优先级更高
- isolation：用于指定事务的隔离级别，默认级别DEFAULT，表示使用数据库的默认隔离级别
- no-rollback-for：用于指定一个异常，当产生该异常时，事务不回滚；产生其他异常时，事务回滚。没有默认值，表示任何异常都回滚。
- rollback-for：用于指定一个异常，当产生该异常时，事务回滚；产生其他异常时，不回滚。没有默认值，表示任何异常都回滚
- propagation：用于指定事务的传播行为。默认值是REQUIRED，表示一定会有事务，增删改时选择。查询方法可以选择SUPPORTS
- read-only：用于指定事务事务是否只读，只有查询方法才能设置为true，默认值是false，表示读写
- timeout：用于指定事务的超时时间，默认值是-1，表示永不超时。如果指定了数值，以秒为单位

配置文件

xml
 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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        https://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/tx
        https://www.springframework.org/schema/tx/spring-tx.xsd
        http://www.springframework.org/schema/aop
        https://www.springframework.org/schema/aop/spring-aop.xsd">
    <bean id="accountDao" class="top.meethigher.demo21.dao.impl.AccountDaoImpl">
        <property name="dataSource" ref="ds"/>
    </bean>
    <bean id="accountService" class="top.meethigher.demo21.service.impl.AccountServiceImpl">
        <property name="accountDao" ref="accountDao"/>
    </bean>
    <bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
        <property name="url"
                  value="jdbc:mysql://localhost:3306/spring?useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=Asia/Shanghai&amp;useSSL=false"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </bean>
    <!--配置事务-->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="ds"/>
    </bean>
    <!--配置事务通知-->
    <tx:advice id="txAdvice" transaction-manager="transactionManager">
        <!--配置事务属性-->
        <tx:attributes>
            <tx:method name="transfer" propagation="REQUIRED" read-only="false"/>
        </tx:attributes>
    </tx:advice>
    <!--配置aop-->
    <aop:config>
        <aop:pointcut id="pt" expression="execution(* top.meethigher.demo21.service.impl.*.*(..))"/>
        <!--建立切入点表达式和事务通知的对应关系-->
        <aop:advisor advice-ref="txAdvice" pointcut-ref="pt"/>
    </aop:config>
</beans>

AccountDaoImpl

java
 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
28
29
30
31
32
33
public class AccountDaoImpl extends JdbcDaoSupport implements AccountDao {

    @Override
    public Account findAccountByName(String name) {
        List<Account> query = super.getJdbcTemplate().query(
                "select * from account where name=?",
                new BeanPropertyRowMapper<>(Account.class),
                name);
        if (!query.isEmpty()) {
            return query.get(0);
        }
        return null;
    }

    @Override
    public void transfer(String sourceName, String targetName, Float money) {
        Account account1 = findAccountByName(sourceName);
        Account account2 = findAccountByName(targetName);
        account1.setMoney(account1.getMoney()-money);
        account2.setMoney(account2.getMoney()+money);
        update(account1);
        int a=2/0;
        update(account2);
    }

    @Override
    public void update(Account account) {
        super.getJdbcTemplate().update(
                "update account set money=? where id=?",
                account.getMoney(), account.getId()
        );
    }
}

测试类

java
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:spring.xml")
public class Test21 {
    @Autowired
    private AccountService accountService;

    @Test
    public void testTransfer() {
        accountService.transfer("白沉香","小舞",500f);
    }
}

2.2.2 基于注解与xml的声明式事务

步骤

配置事务管理器
开启spring对**注解事务**的支持
在需要事务支持的地方，使用@Transactional注解

配置文件

xml
 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
28
29
30
31
32
33
34
35
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        https://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/tx
        https://www.springframework.org/schema/tx/spring-tx.xsd
        http://www.springframework.org/schema/aop
        https://www.springframework.org/schema/aop/spring-aop.xsd
        http://www.springframework.org/schema/context
        https://www.springframework.org/schema/context/spring-context.xsd">
    <context:component-scan base-package="top.meethigher.demo22"/>
    <bean id="accountDao" class="top.meethigher.demo22.dao.impl.AccountDaoImpl"/>
    <bean id="accountService" class="top.meethigher.demo22.service.impl.AccountServiceImpl"/>
    <bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
        <property name="url"
                  value="jdbc:mysql://localhost:3306/spring?useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=Asia/Shanghai&amp;useSSL=false"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </bean>
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="ds"/>
    </bean>
    <!--配置事务-->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="ds"/>
    </bean>
    <!--开启spring对注解事务的支持-->
    <tx:annotation-driven transaction-manager="transactionManager"/>
</beans>

AccountDaoImpl

java
 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
28
29
30
31
32
33
34
35
36
37
38
@Repository("accountDao")
@Transactional(readOnly = true)//配置为只读事务，也就是查询
public class AccountDaoImpl implements AccountDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public Account findAccountByName(String name) {
        List<Account> query = jdbcTemplate.query(
                "select * from account where name=?",
                new BeanPropertyRowMapper<>(Account.class),
                name);
        if (!query.isEmpty()) {
            return query.get(0);
        }
        return null;
    }

    @Transactional(readOnly = false)//修改为读写事务，也就是增删改
    @Override
    public void transfer(String sourceName, String targetName, Float money) {
        Account account1 = findAccountByName(sourceName);
        Account account2 = findAccountByName(targetName);
        account1.setMoney(account1.getMoney()-money);
        account2.setMoney(account2.getMoney()+money);
        update(account1);
        int a=2/0;
        update(account2);
    }

    @Override
    public void update(Account account) {
        jdbcTemplate.update(
                "update account set money=? where id=?",
                account.getMoney(), account.getId()
        );
    }
}

AccountServiceImpl

java
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
@Service("accountService")
public class AccountServiceImpl implements AccountService {
    @Autowired
    private AccountDao accountDao;

    @Override
    public void transfer(String sourceName, String targetName, float money) {
        accountDao.transfer(sourceName,targetName,money);
    }
}

测试类

java
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:spring.xml")
public class Test22 {
    @Autowired
    private AccountService accountService;

    @Test
    public void testTransfer() {
        accountService.transfer("白沉香","小舞",500f);
    }
}

2.2.3 基于注解的声明式事务

替代xml中的开启spring对**注解事务**的支持

@EnableTransactionManagement

jdbc.properties

properties
1
2
3
4
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/spring?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
u=root
pw=1050121804

配置类

JdbcConfig

java
 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
@Configuration
@PropertySource("classpath:jdbc.properties")
public class JdbcConfig {
    @Value("${u}")
    private String USER;
    @Value("${pw}")
    private String PASS;
    @Value("${driver}")
    private String DRIVER;
    @Value("${url}")
    private String URL;
    @Bean("jdbcTemplate")
    public JdbcTemplate createJdbcTemplate(@Qualifier("ds") DataSource ds){
        return new JdbcTemplate(ds);
    }
    @Bean("ds")
    public DataSource createDataSource(){
        DriverManagerDataSource ds = new DriverManagerDataSource();
        ds.setUsername(USER);
        ds.setPassword(PASS);
        ds.setDriverClassName(DRIVER);
        ds.setUrl(URL);
        return ds;
    }
}

TransactionManagerConfig

java
1
2
3
4
5
6
7
@Configuration
public class TransactionManagerConfig {
    @Bean("transactionManager")
    public PlatformTransactionManager createTransactionManager(@Qualifier("ds") DataSource ds){
        return new DataSourceTransactionManager(ds);
    }
}

SpringConfig

java
1
2
3
4
5
6
@Configuration
@ComponentScan(basePackages = "top.meethigher.demo23")
@Import({JdbcConfig.class,TransactionManagerConfig.class})
@EnableTransactionManagement//开启对事务的支持
public class SpringConfig {
}

测试类

java
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = SpringConfig.class)
public class Test23 {
    @Autowired
    private AccountService accountService;

    @Test
    public void testTransfer() {
        accountService.transfer("白沉香","小舞",500f);
    }
}

其他的跟上一节的一样。

2.3 spring编程式事务

需要在配置里添加事务模板对象，用于对指定方法进行添加事务

xml
 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
28
29
30
31
32
33
34
35
36
37
38
39
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        https://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/tx
        https://www.springframework.org/schema/tx/spring-tx.xsd
        http://www.springframework.org/schema/aop
        https://www.springframework.org/schema/aop/spring-aop.xsd
        http://www.springframework.org/schema/context
        https://www.springframework.org/schema/context/spring-context.xsd">
    <context:component-scan base-package="top.meethigher.demo24"/>
    <bean id="accountDao" class="top.meethigher.demo24.dao.impl.AccountDaoImpl">
        <property name="dataSource" ref="ds"/>
    </bean>
    <bean id="accountService" class="top.meethigher.demo24.service.impl.AccountServiceImpl">
        <property name="accountDao" ref="accountDao"/>
        <property name="transactionTemplate" ref="transactionTemplate"/>
     </bean>
    <bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
        <property name="url"
                  value="jdbc:mysql://localhost:3306/spring?useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=Asia/Shanghai&amp;useSSL=false"/>
        <property name="username" value="root"/>
        <property name="password" value="1050121804"/>
    </bean>
   <!--配置事务管理器-->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="ds"/>
    </bean>
    <!--事务模板对象-->
    <bean id="transactionTemplate" class="org.springframework.transaction.support.TransactionTemplate">
        <property name="transactionManager" ref="transactionManager"/>
    </bean>
</beans>

其他类都一样，唯一不同的是对要方法进行添加事务

java
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
public class AccountServiceImpl implements AccountService {
    private TransactionTemplate transactionTemplate;
    private AccountDao accountDao;

    public void setTransactionTemplate(TransactionTemplate transactionTemplate) {
        this.transactionTemplate = transactionTemplate;
    }

    public void setAccountDao(AccountDao accountDao) {
        this.accountDao = accountDao;
    }

    @Override
    public void transfer(String sourceName, String targetName, float money) {
        transactionTemplate.execute(new TransactionCallback<Object>() {
            @Override
            public Object doInTransaction(TransactionStatus transactionStatus) {
                accountDao.transfer(sourceName, targetName, money);
                return null;
            }
        });

    }
}

缺点：让方法实现事务功能时，都需要写这么多，太麻烦了。还是注解的声明式事务用的多一点，只需要配置事务并添加@Transaction注解即可。

三、多数据库事务控制

数据库的事务是基于连接 Connection 来管理的。如果操作多个数据库或者多个连接，只要保证连接一起 commit 或者一起 rollback 即可。源码meethigher/jdbc-transaction-spring-boot: JDBC多事务使用示例

但是这个操作在应用层实现，会存在一种问题。伪代码如下

java
1
2
3
4
5
6
7
8
9
try {
    ...
        
    conn1.commit();
    conn2.commit();
} catch (Exception e) {
    conn1.rollback();
    conn2.rollback();
}

conn1成功提交事务后，conn2如果没有提交成功。这时候conn1应该要回滚的，但是此时已经无法回滚了。

有两种解决方案

业务补偿方案：较简单
- 业务层捕获到异常后，主动执行补偿逻辑，修正数据。
使用分布式事务管理器：较麻烦

3.1 原生JDBC

示例代码

java
 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
28
29
30
31
32
33
34
35
36
37
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.concurrent.locks.LockSupport;

public class App {


    public static Connection getConnection(String jdbcUrl, String username, String password) throws Exception {
        Class.forName("org.postgresql.Driver");
        return DriverManager.getConnection(jdbcUrl, username, password);
    }

    public static void main(String[] args) throws Exception {
        Connection conn1 = getConnection("jdbc:postgresql://10.0.0.20:5432/first", "postgres", "postgres");
        Connection conn2 = getConnection("jdbc:postgresql://10.0.0.20:5432/second", "postgres", "postgres");


        conn1.setAutoCommit(false);
        conn2.setAutoCommit(false);

        Statement stat1 = conn1.createStatement();
        Statement stat2 = conn2.createStatement();
        stat1.execute("create table conn(id varchar)");
        stat2.execute("create table conn(id varchar)");

        // jdbc的事务，是针对单个Connection。如果需要复杂的事务，比如分布式，需要自己维护多个Connection的事务
        conn1.commit();
        conn2.commit();

        // conn1.rollback();
        // conn2.rollback();


        LockSupport.park();
    }
}

3.2 Spring JdbcTemplate

依然保持 @Transactional 的用法

java
1
2
3
4
5
6
7
@Override
@Transactional(rollbackFor = Exception.class)
public void run(String... args) throws Exception {
    firstJdbcTemplate.update("create table record(id varchar)");
    int i = 1 / 0;
    secondJdbcTemplate.update("create table record(id varchar)");
}

但是在配置时，需要将多个数据源的事务通过org.springframework.data.transaction.ChainedTransactionManager配置到一起。

java
 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
28
29
30
31
32
33
34
35
36
37
@Bean
public DataSource firstDataSource() {
    HikariConfig config = new HikariConfig();
    config.setJdbcUrl("jdbc:postgresql://10.0.0.20:5432/first");
    config.setUsername("postgres");
    config.setPassword("postgres");
    return new HikariDataSource(config);
}

@Bean
public JdbcTemplate firstJdbcTemplate(@Qualifier("firstDataSource") DataSource dataSource) {
    return new JdbcTemplate(dataSource);
}


@Bean
public DataSource secondDataSource() {
    HikariConfig config = new HikariConfig();
    config.setJdbcUrl("jdbc:postgresql://10.0.0.20:5432/second");
    config.setUsername("postgres");
    config.setPassword("postgres");
    return new HikariDataSource(config);
}

@Bean
public JdbcTemplate secondJdbcTemplate(@Qualifier("secondDataSource") DataSource dataSource) {
    return new JdbcTemplate(dataSource);
}


@Bean
public PlatformTransactionManager chainedTransactionManager(
        @Qualifier("firstDataSource") DataSource d1,
        @Qualifier("secondDataSource") DataSource d2
) {
    return new ChainedTransactionManager(new DataSourceTransactionManager(d1), new DataSourceTransactionManager(d2));
}

JPA换个角度实现持久层

Apache安装证书细节