JDBC API 事务的实践

使用了持久化框架几乎没有使用过原生的jdbc API ,发现原来使用jdbc API来实现事务也是很简单的。

数据库的链接connection具有一个属性autocommit,这个属性默认是true,作用是控制是否把执行的命令提交给数据库。一旦命令被提交就无法回滚数据库。

而我们实现事务的方式也是很简单,就是手动设置属性autocommit的值为false,等执行完全部命令之后再手动提交所有命令就可以了。

try (Connection connection = DriverManager.getConnection(url, username, password)) {
System.out.println("数据库链接成功!"); try (Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
String command = "insert into a_dept(name) values('1'),('2')";
int count = statement.executeUpdate(command);
System.out.println("受影响行数:" + count); String selectSql = "select * from a_dept";
String updateSql = "update a_dept set name=? where id=?";
String insertSql = "insert into a_dept(name) values(?)";
try (ResultSet resultSet = statement.executeQuery(selectSql)) {
while (resultSet.next()) {
System.out.print("修改前id " + resultSet.getInt("id") + "的值是: ");
System.out.println(resultSet.getString("name"));
}
resultSet.beforeFirst();
connection.setAutoCommit(false);
while (resultSet.next()) {
PreparedStatement updateState = connection.prepareStatement(updateSql);
updateState.setString(1, "I am new" + resultSet.getInt("id"));
updateState.setInt(2, resultSet.getInt("id"));
updateState.executeUpdate();
} // Savepoint savepoint = connection.setSavepoint();
PreparedStatement updateState = connection.prepareStatement(insertSql);
updateState.setString(1, "我是插入的");
updateState.executeUpdate();
try {
PreparedStatement updateStateFail = connection.prepareStatement(insertSql);
updateStateFail.setString(1, "我是插入的,但是我太长了,所以我是插不进去的,会报错!");
updateStateFail.executeUpdate(); } catch (Exception e) {
connection.rollback();
System.err.println(e.getMessage());
}
connection.commit();
Statement newStatement = connection.createStatement();
ResultSet newSet = newStatement.executeQuery(selectSql);
System.out.println("打印出所有的值:");
while (newSet.next()) {
System.out.print(newSet.getInt("id") + ": ");
System.out.println(newSet.getString("name"));
}
}
}
}

  贴运行结果:

JDBC API 事务的实践

红色字体就是报错的信息。我们可以发现,在这个结果集当中没有进行修改,也没有进行插入,所有的修改和插入命令因为插入操作的错误都被回滚了。

但是我们发现我们的修改操作和插入操作都被回滚了,这在一些情况下我们不希望所有的操作都被回滚,那么我们可以设置回滚的节点,设置回滚的节点也很简单,在想要的地方添加如下代码,

Savepoint savepoint = connection.setSavepoint();

那么就会在该地生成一个节点,回滚把这个节点作为参数传递给回滚的方法 rollback() ,那么回滚的时候会放过该节点之前的操作,需要注意的是节点使用完之后必须释放它。

结果如下(不要在意Id亲,我清了数据库了):
Connected to the target VM, address: '127.0.0.1:17576', transport: 'socket'
数据库链接成功!
受影响行数:2
修改前id 479的值是: 1
修改前id 480的值是: 2
Data truncation: Data too long for column 'name' at row 1
打印出所有的值:
479: I am new479
480: I am new480
Disconnected from the target VM, address: '127.0.0.1:17576', transport: 'socket'

简单吧:

最后附上使用节点回滚的全部代码,可以不看~:

        try (Connection connection = DriverManager.getConnection(url, username, password)) {
System.out.println("数据库链接成功!"); try (Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
String command = "insert into a_dept(name) values('1'),('2')";
int count = statement.executeUpdate(command);
System.out.println("受影响行数:" + count); String selectSql = "select * from a_dept";
String updateSql = "update a_dept set name=? where id=?";
String insertSql = "insert into a_dept(name) values(?)";
try (ResultSet resultSet = statement.executeQuery(selectSql)) {
while (resultSet.next()) {
System.out.print("修改前id " + resultSet.getInt("id") + "的值是: ");
System.out.println(resultSet.getString("name"));
}
resultSet.beforeFirst();
connection.setAutoCommit(false);
while (resultSet.next()) {
PreparedStatement updateState = connection.prepareStatement(updateSql);
updateState.setString(1, "I am new" + resultSet.getInt("id"));
updateState.setInt(2, resultSet.getInt("id"));
updateState.executeUpdate();
} Savepoint savepoint = connection.setSavepoint();
PreparedStatement updateState = connection.prepareStatement(insertSql);
updateState.setString(1, "我是插入的");
updateState.executeUpdate();
try {
PreparedStatement updateStateFail = connection.prepareStatement(insertSql);
updateStateFail.setString(1, "我是插入的,但是我太长了,所以我是插不进去的,会报错!");
updateStateFail.executeUpdate(); } catch (Exception e) {
connection.rollback(savepoint);
               connection.releaseSavepoint(savepoint);
System.err.println(e.getMessage());
}
connection.commit();
Statement newStatement = connection.createStatement();
ResultSet newSet = newStatement.executeQuery(selectSql);
System.out.println("打印出所有的值:");
while (newSet.next()) {
System.out.print(newSet.getInt("id") + ": ");
System.out.println(newSet.getString("name"));
}
}
}
}

  

上一篇:看你的门-攻击服务器(4)-HTTP参数注入攻击


下一篇:Spring 代理对象,cglib,jdk的问题思考,AOP 配置注解拦截 的一些问题.为什么不要注解在接口,以及抽象方法.