(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!");        }

我们再查询数据库发现，并没有数据插入。