java jdbc提交回滚,java持久化 jdbc开始事务、提交事务、回滚事务、设置savepoint...

迷南。 2023-01-15 08:19 162阅读 0赞

学海无涯

这次我们来学习基于jdbc的原生事务操作，不要小看jdbc喔~ mybatis、jpa等持久化框架都是在它上面的封装。

准备数据

SET NAMES utf8mb4;

SET FOREIGN\_KEY\_CHECKS = 0;

\-- ----------------------------

\-- Table structure for users

\-- ----------------------------

DROP TABLE IF EXISTS \`users\`;

CREATE TABLE \`users\` (

\`id\` bigint(20) NOT NULL AUTO\_INCREMENT COMMENT '主键ID',

\`name\` varchar(30) CHARACTER SET utf8 COLLATE utf8\_general\_ci NULL DEFAULT NULL COMMENT '姓名',

\`age\` int(11) NULL DEFAULT NULL COMMENT '年龄',

\`email\` varchar(50) CHARACTER SET utf8 COLLATE utf8\_general\_ci NULL DEFAULT NULL COMMENT '邮箱',

PRIMARY KEY (\`id\`) USING BTREE

) ENGINE = InnoDB AUTO\_INCREMENT = 103 CHARACTER SET = utf8 COLLATE = utf8\_general\_ci ROW\_FORMAT = Dynamic;

SET FOREIGN\_KEY\_CHECKS = 1;

上代码

下面程序使用jdbc 发出了两个insert语句，并在insert语句之间设置了一个savepoint，在发出第二条insert之后直接回滚到指定savepoint了。所以最后提交事务后，只有第二个insert生效

使用connection.setAutoCommit(false); 手动提交事务，让mysql隐式为我们开一个事务

使用 Savepoint point\_1 = connection.setSavepoint("point\_1"); 创建名为point\_1的保存点

使用 connection.rollback(point\_1); 回滚至特定的保存点；当然可以不指定savepoint参数，那么语义为直接回滚当前的事务到事务开始时

使用 connection.commit(); 即可提交当前事务

import java.sql.\*;

class JDBCUtils \{

public static void main(String\[\] args) throws SQLException, ClassNotFoundException \{

//加载驱动

Class.forName("com.mysql.jdbc.Driver");

//获得连接

Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbcstudy?characterEncoding=UTF-8", "root", "yk123");

//手动提交事务

connection.setAutoCommit(false);

//PreparedStatement预编译sql，插入一条记录的sql

PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO \`jdbcstudy\`.\`users\`( \`name\`, \`age\`, \`email\`) VALUES ('hh', 22, 'hh@qq.com');");

//执行insert

preparedStatement.executeUpdate();

//创建一个savepoint命名为 point\_1

Savepoint point\_1 = connection.setSavepoint("point\_1");

//再次插入

preparedStatement = connection.prepareStatement("INSERT INTO \`jdbcstudy\`.\`users\`( \`name\`, \`age\`, \`email\`) VALUES ( 'yk', 23, 'yk@qq.com');");

//回滚至指定savepoint

connection.rollback(point\_1);

//查询最新结果

ResultSet resultSet = preparedStatement.executeQuery("select \* from users");

//解析ResultSet

while (resultSet != null && resultSet.next()) \{

System.out.println(resultSet.getInt("id"));

System.out.println(resultSet.getString("name"));

System.out.println(resultSet.getInt("age"));

System.out.println(resultSet.getString("email"));

System.out.println("记录===>" + resultSet.getInt("id") );

//提交事务