JdbcTemplate的事务控制

迈不过友情╰ 2021-12-12 15:11 348阅读 0赞

### 前言 ###

JdbcTemplate是spring-jdbc提供的数据库核心操作类，那对JdbcTemplate进行事务控制呢？

我的环境：spring-boot-2.1.3，druid-1.1.3。

## 原生Jdbc的事务控制 ##

即，批处理+自动提交的控制方式，

public static void demo(String[] args) throws SQLException, ClassNotFoundException {
        String url = "jdbc:mysql://10.1.4.16:3306/szhtest";
        String username = "ababab";
        String password = "123456";
        String sql1 = "insert xx";
        String sql2 = "insert xx";
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(url, username, password);
        Statement statement = conn.createStatement();
        // 获取到原本的自动提交状态
        boolean ac = conn.getAutoCommit();
        // 批处理多条sql操作
        statement.addBatch(sql1);
        statement.addBatch(sql2);
        // 关闭自动提交
        conn.setAutoCommit(false);
        try {
            // 提交批处理
            statement.executeBatch();
            // 若批处理无异常，则准备手动commit
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            // 批处理抛异常，则rollback
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            // 恢复到原本的自动提交状态
            conn.setAutoCommit(ac);
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

## Spring的声明式事务控制 ##

Bean的类或方法上加@Transactional，事务控制粒度较大，只能控制在方法级别，不能控制到代码粒度级别。

## 尝试JdbcTemplate的事务控制 ##

采取跟**原生jdbc事务控制**一样的方法试试，在批处理前关闭自动提交，若批处理失败则回滚的思路。

@RequestMapping("/druidData1")
    public String druidData1() throws SQLException {
        String sql1 = "INSERT INTO user_tmp(`id`, `username`) VALUES(22, 222)";
        // id=1的主键冲突插入失败
        String sql2 = "INSERT INTO user_tmp(`id`, `username`) VALUES(1, 111)";
        Connection conn = jdbcTemplate.getDataSource().getConnection();
        LOG.info("1:{}", conn);
        boolean ac = conn.getAutoCommit();
        conn.setAutoCommit(false);
        try {
            int[] rs2 = jdbcTemplate.batchUpdate(new String[]{sql1, sql2});
            conn.commit();
        } catch (Throwable e) {
            LOG.error("Error occured, cause by: {}", e.getMessage());
            conn.rollback();
        } finally {
            conn.setAutoCommit(ac);
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    LOG.error("Error occurred while closing connectin, cause by: {}", e.getMessage());
                }
            }
        }
        return "test";
    }

**期望结果**：id=1的因为主键冲突，所以id=22的也要回滚。

**实际结果**：id=1的插入失败，id=22的插入成功，未回滚。

**原因分析**：自始至终都是同一个connection连接对象，按道理不应该无法控制自动提交，唯一的解释是jdbcTemplate.batchUpdate()中真正使用的连接对象并非代码中的conn，于是一方面把conn打印出来，另一方面准备调试jdbcTemplate.batchUpdate()源码内部，看看是否使用了另外获取到的connection。

**调试流程**：jdbcTemplate.batchUpdate()

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Nvbmd6ZWhhbw_size_16_color_FFFFFF_t_70][]

**→**execute(new BatchUpdateStatementCallback())

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Nvbmd6ZWhhbw_size_16_color_FFFFFF_t_70 1][]

**→**DataSourceUtils.getConnection(obtainDataSource())

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Nvbmd6ZWhhbw_size_16_color_FFFFFF_t_70 2][]

对比两个connection，确非同一对象，因此对我们的conn进行事务的控制不会影响jdbcTemplate内部真正使用的con，

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Nvbmd6ZWhhbw_size_16_color_FFFFFF_t_70 3][]

**→**紧接着进入源码376行，回调函数action.doInStatement(stmt)

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Nvbmd6ZWhhbw_size_16_color_FFFFFF_t_70 4][]

在回调函数中，真正进行数据库操作。至此，便明白了这样的方法为何**不能成功控制****jdbcTemplate****事务的原因**，即我们控制的conn和jdbcTemplate真正使用的con不是同一个对象。那如果Druid数据库连接池里只有1个conn呢，这样的方法会不会成功控制？

于是修改druid配置，将initial-size、max-active、min-idle都设置为1，这样，你jdbcTemplate里获取到的跟我的conn总该是同一对象了吧？然而，方法运行约1min后，抛出异常：

Failed to obtain JDBC Connection; nested exception is com.alibaba.druid.pool.GetConnectionTimeoutException: wait millis 60001, active 1, maxActive 1, creating 0

继续跟了一下源码，原来是池子里最大只有一个连接conn，而它又未被释放，导致jdbcTemplate内部再去从池子里获取con时，一直在等待已有连接conn的释放，一直等不到释放，所以等待了max-wait=60000ms的时间，最后报错。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Nvbmd6ZWhhbw_size_16_color_FFFFFF_t_70 5][]所以这样的控制也是不合理的，那究竟如何控制JdbcTemplate的事务呢？答案就是**TransactionTemplate**。

## TransactionTemplate的编程式事务控制 ##

注册事务相关bean：**TransactionTemplate**，如下：

package com.boot.druid.config;
    
    import com.alibaba.druid.pool.DruidDataSource;
    import com.alibaba.druid.support.http.StatViewServlet;
    import com.alibaba.druid.support.http.WebStatFilter;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.boot.web.servlet.FilterRegistrationBean;
    import org.springframework.boot.web.servlet.ServletRegistrationBean;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.datasource.DataSourceTransactionManager;
    import org.springframework.transaction.support.TransactionTemplate;
    
    /**
     * Druid数据库连接池配置文件
     */
    @Configuration
    public class DruidConfig {
        private static final Logger logger = LoggerFactory.getLogger(DruidConfig.class);
    
        @Value("${spring.datasource.druid.url}")
        private String dbUrl;
    
        @Value("${spring.datasource.druid.username}")
        private String username;
    
        @Value("${spring.datasource.druid.password}")
        private String password;
    
        @Value("${spring.datasource.druid.driverClassName}")
        private String driverClassName;
    
        @Value("${spring.datasource.druid.initial-size}")
        private int initialSize;
    
        @Value("${spring.datasource.druid.max-active}")
        private int maxActive;
    
        @Value("${spring.datasource.druid.min-idle}")
        private int minIdle;
    
        @Value("${spring.datasource.druid.max-wait}")
        private int maxWait;
    
        /**
         * Druid 连接池配置
         */
        @Bean     //声明其为Bean实例
        public DruidDataSource dataSource() {
            DruidDataSource datasource = new DruidDataSource();
            datasource.setUrl(dbUrl);
            datasource.setUsername(username);
            datasource.setPassword(password);
            datasource.setDriverClassName(driverClassName);
            datasource.setInitialSize(initialSize);
            datasource.setMinIdle(minIdle);
            datasource.setMaxActive(maxActive);
            datasource.setMaxWait(maxWait);
            datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
            try {
                datasource.setFilters(filters);
            } catch (Exception e) {
                logger.error("druid configuration initialization filter", e);
            }
            datasource.setConnectionProperties(connectionProperties);
            return datasource;
        }
        /**
         * JDBC操作配置
         */
        @Bean(name = "dataOneTemplate")
        public JdbcTemplate jdbcTemplate (@Autowired DruidDataSource dataSource){
            return new JdbcTemplate(dataSource) ;
        }
        /**
         * 装配事务管理器
         */
        @Bean(name="transactionManager")
        public DataSourceTransactionManager transactionManager(@Autowired DruidDataSource dataSource) {
            return new DataSourceTransactionManager(dataSource);
        }
    
        /**
         * JDBC事务操作配置
         */
        @Bean(name = "txTemplate")
        public TransactionTemplate transactionTemplate (@Autowired DataSourceTransactionManager transactionManager){
            return new TransactionTemplate(transactionManager);
        }
    
        /**
         * 配置 Druid 监控界面
         */
        @Bean
        public ServletRegistrationBean statViewServlet(){
            ServletRegistrationBean srb =
                    new ServletRegistrationBean(new StatViewServlet(),"/druid/*");
            //设置控制台管理用户
            srb.addInitParameter("loginUsername","root");
            srb.addInitParameter("loginPassword","root");
            //是否可以重置数据
            srb.addInitParameter("resetEnable","false");
            return srb;
        }
        @Bean
        public FilterRegistrationBean statFilter(){
            //创建过滤器
            FilterRegistrationBean frb =
                    new FilterRegistrationBean(new WebStatFilter());
            //设置过滤器过滤路径
            frb.addUrlPatterns("/*");
            //忽略过滤的形式
            frb.addInitParameter("exclusions",
                    "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
            return frb;
        }
    }

然后注入TransactionTemplate，使用transactionTemplate.execute(new TransactionCallback<> action)或者transactionTemplate.execute(new TransactionCallbackWithoutResult<> action)执行多条sql，最后可以通过**transactionStatus**的**setRollbackOnly**()或**rollbackToSavepoint**(savepoint) 控制事务，如下：

@RequestMapping("/druidData2")
    public String runTransactionSamples() {
        String sql1 = "INSERT INTO user_tmp(`id`, `username`) VALUES(22, 222)";
        String sql2 = "INSERT INTO user_tmp(`id`, `username`) VALUES(1, 111)";
        txTemplate.execute(new TransactionCallback<Object>() {
            @Override
            public Object doInTransaction(TransactionStatus transactionStatus) {
                Object savepoint = transactionStatus.createSavepoint();
                // DML执行
                try {
                    int[] rs2 = jdbcTemplate.batchUpdate(new String[]{sql1, sql2});
                } catch (Throwable e) {
                    LOG.error("Error occured, cause by: {}", e.getMessage());
                    transactionStatus.setRollbackOnly();
                    // transactionStatus.rollbackToSavepoint(savepoint);
                }
                return null;
            }
        });
        return "test2";
    }

上面是不带参数的多条sql的事务执行，若是带参数的多条sql，可以实现如下：

@RequestMapping("/druidData3")
    public String runTransactionSamples2() {
        String sql1 = "INSERT INTO user_tmp(`id`, `username`) VALUES(?, ?)";
        Object[] args1 = new Object[] {22, 222};
        String sql2 = "INSERT INTO user_tmp(`id`, `username`) VALUES(?, ?)";
        Object[] args2 = new Object[] {1, 111};
        txTemplate.execute(new TransactionCallback<Object>() {
            @Override
            public Object doInTransaction(TransactionStatus transactionStatus) {
                Object savepoint = transactionStatus.createSavepoint();
                // DML执行
                try {
                    int rs1 = jdbcTemplate.update(sql1, args1);
                    int rs2 = jdbcTemplate.update(sql2, args2);
                } catch (Throwable e) {
                    LOG.error("Error occured, cause by: {}", e.getMessage());
                    transactionStatus.setRollbackOnly();
                    // transactionStatus.rollbackToSavepoint(savepoint);
                }
                return null;
            }
        });
        return "test2";
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Nvbmd6ZWhhbw_size_16_color_FFFFFF_t_70]: /images/20211210/486400f944a7467e9d7e9741146137e7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Nvbmd6ZWhhbw_size_16_color_FFFFFF_t_70 1]: /images/20211210/e13f4b27344e4722b87a2338adfdc2df.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Nvbmd6ZWhhbw_size_16_color_FFFFFF_t_70 2]: /images/20211210/4048a635253e4671b2e6febe1b36c19c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Nvbmd6ZWhhbw_size_16_color_FFFFFF_t_70 3]: /images/20211210/f60b7eac3f3b43f5bdcb194d8c2cd048.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Nvbmd6ZWhhbw_size_16_color_FFFFFF_t_70 4]: /images/20211210/882ad94261384b63bdf094a7c8e07e98.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Nvbmd6ZWhhbw_size_16_color_FFFFFF_t_70 5]: /images/20211210/0a63e6a438634f5cb3024ceedfbb8995.png