Java中数据库操作中的事务实现(JDBC)

Leave a reply

基本实现先参照：Java简单完整数据库操作全过程(JDBC)

事务的支持其实只需要在Statement对象执行SQL之前开启事务，执行之后提交事务，出现异常情况回滚数据即可，分别对应三个方法：

  • setAutoCommit(boolean)：参数为false则开启事务
  • commit()：提交事务
  • rollback()：回滚事务

这三个方法是数据库连接类Connection的对象方法。

实现参考：

package com.yusian.transaction;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TransactionDemo {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt1 = null;
        PreparedStatement pstmt2 = null;
        try {
            conn = Utils.getConnection();
            pstmt1 = conn.prepareStatement("update account set balance = balance - ? where id = ?");
            pstmt2 = conn.prepareStatement("update account set balance = balance + ? where id = ?");
            pstmt1.setDouble(1, 500);
            pstmt1.setInt(2, 1);
            pstmt2.setDouble(1, 500);
            pstmt2.setInt(2, 2);
            // 重点来了，开启事务和不开启事务就只有这一点差别
            conn.setAutoCommit(false);  // 开启事务
            pstmt1.executeUpdate();
            pstmt2.executeUpdate();     // 提交事务
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                // 事务回滚，注意：回滚应该在所有的异常捕获中执行，因为无论发生何种异常，都应该回滚。
                conn.rollback();
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            }
        }finally {
            // 资源释放
            Utils.releaseCloseableObject(pstmt1);
            Utils.releaseCloseableObject(pstmt2);
            Utils.releaseCloseableObject(conn);
        }
    }
}

Utils工具类：

package com.yusian.transaction;

import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;

public class Utils {
    private static String url;
    private static String user;
    private static String pass;
    private static String driver;

    static {
        ClassLoader loader = Utils.class.getClassLoader();
        URL res = loader.getResource("jdbc.properties");
        try {
            Properties prop = new Properties();
            prop.load(new FileReader(res.getPath()));
            url = prop.getProperty("url");
            user = prop.getProperty("user");
            pass = prop.getProperty("pass");
            driver = prop.getProperty("driver");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    public static Connection getConnection() throws SQLException {
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return DriverManager.getConnection(url, user, pass);
    }


    /**
     * 释放查询类SQL语句的相关资源
     * @param conn SQL连接对象
     * @param stmt SQL执行对象
     * @param retSet 查询结果
     */
    public static void close(Connection conn, Statement stmt, ResultSet retSet) {
        releaseCloseableObject(retSet);
        close(conn, stmt);
    }

    public static void close(Connection conn, Statement stmt) {
        releaseCloseableObject(stmt);
        releaseCloseableObject(conn);
    }

    public static void releaseCloseableObject(AutoCloseable object) {
        if (object == null) return;
        try {
            object.close();
        } catch (Exception e) {
            e.printStackTrace();;
        }
    }
}

Leave a Reply