数据库事务
处理事务
-
测验事务代码
-
/** * Tom 给 Jerry 汇款 500 元. * * 关于事务: 1. 如果多个操作, 每个操作使用的是自己的单独的连接, 则无法保证事务. 2. 具体步骤: 1). 事务操作开始前, 开始事务: * 取消 Connection 的默认提交行为. connection.setAutoCommit(false); 2). 如果事务的操作都成功, * 则提交事务: connection.commit(); 3). 回滚事务: 若出现异常, 则在 catch 块中回滚事务: */ @Test public void testTransaction() { Connection connection = null; try { connection = (Connection) JDBCTools.getConnection(); System.out.println(connection.getAutoCommit()); // 开始事务: 取消默认提交. connection.setAutoCommit(false); String sql = "UPDATE users SET balance = " + "balance - 500 WHERE id = 1"; update(connection, sql); //这里设置错误 是测验为了回滚事务 int i = 10 / 0; System.out.println(i); sql = "UPDATE users SET balance = " + "balance + 500 WHERE id = 2"; update(connection, sql); // 提交事务 connection.commit(); } catch (Exception e) { e.printStackTrace(); // 回滚事务 这里设置的是出现异常回滚事务 try { connection.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } } finally { JDBCTools.releaseDB(null, null, connection); } /* * try { * * //开始事务: 取消默认提交. connection.setAutoCommit(false); * * //... * * //提交事务 connection.commit(); } catch (Exception e) { //... * * //回滚事务 try { connection.rollback(); } catch (SQLException e1) { * e1.printStackTrace(); } } finally{ JDBCTools.releaseDB(null, null, * connection); } */ // DAO dao = new DAO(); // // String sql = "UPDATE users SET balance = " + // "balance - 500 WHERE id = 1"; // dao.update(sql); // // int i = 10 / 0; // System.out.println(i); // // sql = "UPDATE users SET balance = " + // "balance + 500 WHERE id = 2"; // dao.update(sql); }
-
update方法
public void update(Connection connection, String sql, Object... args) { PreparedStatement preparedStatement = null; try { preparedStatement = (PreparedStatement) connection.prepareStatement(sql); for (int i = 0; i < args.length; i++) { preparedStatement.setObject(i + 1, args[i]); } preparedStatement.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } finally { JDBCTools.releaseDB(null, preparedStatement, null); } }
-
properties文件中
user=xuefeng password=1234 driverClass=com.mysql.jdbc.Driver jdbcUrl=jdbc:mysql://localhost:3306/xuefeng?useUnicode=true&characterEncoding=utf8
-
MySql中
-
事务的隔离级别
-
代码
-
/** * 测试事务的隔离级别 * 在 JDBC 程序中可以通过 Connection 的 setTransactionIsolation 来设置事务的隔离级别. */ @Test public void testTransactionIsolationUpdate() { Connection connection = null; try { connection = (Connection) JDBCTools.getConnection(); connection.setAutoCommit(false); String sql = "UPDATE users SET balance = " + "balance - 500 WHERE id = 1"; update(connection, sql); connection.commit(); } catch (Exception e) { e.printStackTrace(); } finally { } }
-
// 返回某条记录的某一个字段的值 或 一个统计的值(一共有多少条记录等.) public <E> E getForValue(String sql, Object... args) { // 1. 得到结果集: 该结果集应该只有一行, 且只有一列 Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { // 1. 得到结果集 connection = (Connection) JDBCTools.getConnection(); // System.out.println(connection.getTransactionIsolation()); connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED); // connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); preparedStatement = (PreparedStatement) connection.prepareStatement(sql); for (int i = 0; i < args.length; i++) { preparedStatement.setObject(i + 1, args[i]); } resultSet = preparedStatement.executeQuery(); if (resultSet.next()) { return (E) resultSet.getObject(1); } } catch (Exception ex) { ex.printStackTrace(); } finally { JDBCTools.releaseDB(resultSet, preparedStatement, connection); } // 2. 取得结果 return null; }
-
@Test public void testTransactionIsolationRead() { String sql = "SELECT balance FROM users WHERE id = 1"; Integer balance = getForValue(sql); System.out.println(balance); }
-
注意:如果程序需要经常切换数据库,而且数据库的隔离级别未知,还没办法去设置级别的时候就有必要写**
这样的一行代码
connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
-