Jdbc手动实现事务管理

序言

我们平常工作中用到的spring框架中的事务的管理是通过@Transcational注解来控制的声明式事务管理，其底层是通过关闭事务自动提交，手动提交事务来实现事务控制。

JdbcTranscational.java

import java.sql.*;

public class JdbcTranscational {

    /**
     * mysql连接url
     */
    private static final String URL = "jdbc:mysql://127.0.0.1:3306/test";
    /**
     * 数据库连接账号名
     */
    private static final String USER = "root";
    /**
     * 数据库连接密码
     */
    private static final String PASSWORD = "123456";
    /**
     * mysql数据库驱动类名
     */
    private static final String DRIVER_NAME = "com.mysql.jdbc.Driver";

    /**
     * @return java.sql.Connection
     * @Author cuizx
     * @Description 获取数据库连接
     * @Date 2019/8/23 21:39
     * @Param []
     **/
    private Connection getConnection() {
        Connection conn = null;
        try {
            //加载mysql驱动类
            Class.forName(DRIVER_NAME);
            //获取数据库连接
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * @return void
     * @Author cuizx
     * @Description 按照连接方式倒序关闭所有连接
     * @Date 2019/8/23 21:42
     * @Param [conn, stmt, resultSet]
     **/
    private void closeConnection(Connection conn, Statement stmt, ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }

            if (stmt != null) {
                stmt.close();
            }

            if (conn != null) {
                conn.close();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * @Author cuizx
     * @Description 执行sql
     * @Date 2020/11/21
     * @Param [sql]
     * @return void
     **/
    public void executeSql(String sql) {
        Statement stmt = null;
        int result = 0;
        Connection connection = getConnection();
        try {
            //JDBC中默认是true，自动提交事务（这里手动成false）
            connection.setAutoCommit(false);
            //创建声明
            stmt = connection.createStatement();
            //执行sql
            result = stmt.executeUpdate(sql);
            System.out.println("执行结果：" + result);
            //提交事务
            connection.commit();
        } catch (Exception e) {
            try {
                //抛错后回滚
                connection.rollback();
            } catch (SQLException e1) {
            }
        } finally {
            closeConnection(connection, stmt, null);
        }
    }

    public static void main(String[] args) {
        String sql = "select * from user";
        String insertSql = "insert into user (id,userName) values('123','cuizx')";
        String updateSql = "update user set userName ='更新后' where id ='123'";
        String deleteSql = "delete from user where id ='123'";

        JdbcTranscational jdbcTranscational = new JdbcTranscational();
        jdbcTranscational.executeSql(insertSql);
        jdbcTranscational.executeSql(updateSql);
        jdbcTranscational.executeSql(deleteSql);
    }
}

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.jdbctranscational</groupId>
    <artifactId>jdbctranscational</artifactId>
    <version>1.0-SNAPSHOT</version>


    <dependencies>
        <!-- 导入Mysql数据库链接jar包 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.30</version>
        </dependency>
    </dependencies>
</project>

以上便是事务控制的手动实现代码，其中最核心的部分是关闭事务自动提交和异常后事务回滚

1：jdbc中autocommit默认为true，每次sql执行都会自动提交事务，而我们如果要自己来控制事务的话，就必须将autocommit设置为false

2：connection.rollback()，在catch块中进行事务的回滚，也就是我们平常所知的抛出异常进行事务回滚