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

        }

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

巴特西

(1) Java实现JDBC连接及事务的方式

最新文章

热门文章