【后端开发】JDBC与数据库事务以及ACID四性

感觉讲ACID特别好的视频：https://www.bilibili.com/video/BV1eJ411c7rf?p=39

JDBC与数据库事务以及隔离性的测试都在代码中了

package com.cls1277.databasesacid;

import com.cls1277.commonquery.User;
import com.cls1277.utils.JDBCutils;
import org.junit.Test;

import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.*;

public class TransTest {
    //数据库的事务
    public void update(Connection conn, String sql, Object ... args) {
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for(int i=0; i<args.length; i++) {
                ps.setObject(i+1, args[i]);
            }
            ps.execute();
            //注意ps.execute返回的bool的意思
            //可以用executeUpdate()
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCutils.closeResource(null, ps);
        }
    }

    @Test
    public void updateTest() {
        Connection conn = null;
        try {
            conn = JDBCutils.getConnection();
            conn.setAutoCommit(false);
            String sql1 = "update user_table set balance = balance - 100 where user = ?";
            update(conn, sql1, "AA");

//            System.out.println(100/0);

            String sql2 = "update user_table set balance = balance + 100 where user = ?";
            update(conn, sql2, "BB");

            System.out.println("success!");
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                if(conn!=null)
                    conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            //数据库连接池的时候记得改回来
            try {
                if(conn!=null)
                    conn.setAutoCommit(true);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            JDBCutils.closeResource(conn, null);
        }
    }

    public <T> T getCommonInstance(Connection conn, Class<T> clazz, String sql, Object ... args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            for(int i=0; i<args.length; i++) {
                ps.setObject(i+1, args[i]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            if(rs.next()) {
                T t = clazz.newInstance();
                for(int i=0; i<columnCount; i++) {
                    Object columnValue = rs.getObject(i+1);
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCutils.closeResource(null, ps, rs);
        }
        return null;
    }

    //演示隔离级别
    @Test
    public void testSelect() throws SQLException, IOException, ClassNotFoundException {
        Connection conn = JDBCutils.getConnection();
        //设置隔离级别：两种方法都可以
//        conn.setTransactionIsolation(1);
//        conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
//        conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        //获取隔离级别
        System.out.println(conn.getTransactionIsolation());
        conn.setAutoCommit(false);
        String sql = "select user,password,balance from user_table where user = ?";
        User user = getCommonInstance(conn, User.class, sql, "CC");
        System.out.println(user);
    }

    @Test
    public void testUpdate() throws SQLException, IOException, ClassNotFoundException, InterruptedException {
        Connection conn = JDBCutils.getConnection();
        conn.setAutoCommit(false);
        String sql = "update user_table set balance = ? where user = ?";
        update(conn, sql, 5000, "CC");
        Thread.sleep(15000);
        System.out.println("success");
    }
}