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块中进行事务的回滚，也就是我们平常所知的抛出异常进行事务回滚