(1) Java实现JDBC连接及事务的方式
许多数据库的auto-commit默认是ON的，比如MySQL，PostgresSQL等。当然也有默认是OFF的，比如Oracle（Oracle里面执行DML语句是需要手动commit的）。
这里我们以MySQL为例，先写一个基本的JDBC连接的例子：
package com.mycloud.demo.connection;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;public class ConncetionTest1 { public static void main(String[] args) { String jdbcUrl = "jdbc:mysql://localhost:3306/test_db"; String username = "xxx"; String password = "xxx"; // Basic sample // Connection -> Statement -> ResultSet: try with resource try (Connection con = DriverManager.getConnection(jdbcUrl, username, password); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("select id, account from test")) { String result = getResultSetAsString(rs); System.out.println(result); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException("Error occurred!"); } } private static String getResultSetAsString(ResultSet rs) throws SQLException { ResultSetMetaData rsmd = rs.getMetaData(); int numCols = rsmd.getColumnCount(); StringBuilder sb = new StringBuilder(); while (rs.next()) { for (int i = 1; i <= numCols; i++) { String elem = rs.getString(i); if (rs.wasNull()) sb.append("NULL"); else sb.append(elem); if (i != numCols) sb.append("|"); } sb.append(System.lineSeparator()); } return sb.toString(); }}
在auto-commit默认是ON的情况下，每一条sql都是一个独立的事务，运行完直接commit。但是如果需要实现事务，比如我们执行一组DML，如果某一条失败，就全部rollback。这种方式就不行了：
try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password); Statement stmt = conn.createStatement()) { stmt.execute("INSERT INTO test(id, account) VALUES (1, 100)"); stmt.execute("INSERT INTO test(id, account) VALUES (2, 200)"); stmt.execute("INSERT INTO test(id, account) VALUES (2, 201)"); // Exception: duplicate pk } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException("Error occurred!"); }
我们查询数据库发现，成功插入了两条数据：
| id | account |
| 1 | 100 |
| 2 | 200 |
在这种情况下，我们首先要通过设置connection.setAutoCommit为OFF来开启事务，再通过connection.commit/connection.rollback来提交/回滚事务。
try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password); Statement stmt = conn.createStatement()) { conn.setAutoCommit(false); try { stmt.execute("INSERT INTO test(id, account) VALUES (1, 100)"); stmt.execute("INSERT INTO test(id, account) VALUES (2, 200)"); stmt.execute("INSERT INTO test(id, account) VALUES (3, 300)"); } catch (SQLException e) { e.printStackTrace(); conn.rollback(); // rollback throw e; } conn.commit(); // commit } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException("Error occurred!"); }
我们查询数据库发现，成功插入了3条数据：
| id | account |
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
当然，如果我们去掉conn.commit()这一句，数据就不会插入了。
如果这一组DML中某一条失败，则会被SQLException捕获，从而抛出异常并回滚。
try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password); Statement stmt = conn.createStatement()) { conn.setAutoCommit(false); try { stmt.execute("INSERT INTO test(id, account) VALUES (1, 100)"); stmt.execute("INSERT INTO test(id, account) VALUES (2, 200)"); stmt.execute("INSERT INTO test(id, account) VALUES (2, 201)"); // Exception: duplicate pk stmt.execute("INSERT INTO test(id, account) VALUES (3, 300)"); } catch (SQLException e) { e.printStackTrace(); conn.rollback(); // rollback throw e; } conn.commit(); // commit } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException("Error occurred!"); }
我们再查询数据库发现，并没有数据插入。
赞 (0)