不开启事务
- 当程序异常时,可能两条语句一条成果一条失败,导致数据库错误的被操作
- 如下代码,sql1被成功执行,但sql2由于异常未被执行
- 因为connection默认情况下是自动提交
private Connection connection = null;
@Test
public void notTransaction() {
String sql1 = "update account set money = money + 100 where id = 1";
String sql2 = "update account set money = money - 100 where id = 2";
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql1);
preparedStatement.executeUpdate();
int i = 1 / 0;
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtils.close(null, preparedStatement, connection);
}
开启事务
- 事务可以确保语句要么都被执行,要么都不执行(回滚),或者选择执行哪一部分(回滚一部分)
- 这样子可以确保sql1和sql2都被执行或者都不执行
@Test
public void useTransaction(){
String sql1 = "update account set money = money + 100 where id = 1";
String sql2 = "update account set money = money - 100 where id = 2";
PreparedStatement preparedStatement = null;
Savepoint savepoint = null;
try {
connection = JDBCUtils.getConnection();
connection.setAutoCommit(false);
preparedStatement = connection.prepareStatement(sql1);
preparedStatement.executeUpdate();
int i = 1 / 0;
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
connection.commit();
} catch (SQLException e) {
try {
connection.rollback();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
throw new RuntimeException(e);
} finally {
JDBCUtils.close(null, preparedStatement, connection);
}
}