【JDBC-3】关于数据库事务的一些 *** 作

【JDBC-3】关于数据库事务的一些 *** 作

package transaction;

import JDBC_Utils.JDBCUtils;
import org.junit.Test;

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


public class TransactionTest {

    //考虑数据库事务的转账 *** 作：

    @Test
    public void testUpdateWithTx() {
        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");

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

            System.out.println("转账成功！");
            conn.commit();//再次提交数据
        } catch (Exception e) {
            e.printStackTrace();
            //回滚数据
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        finally {
            try {
                //修改回，主要针对使用数据库连接池的时候
                conn.setAutoCommit(true);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            JDBCUtils.CloseResource(conn,null);
        }
    }
    //通用的增删改 *** 作（考虑事务）
    public void update(Connection conn,String sql,Object ...args){//使用可变形参
        PreparedStatement ps = null;
        try {
            //sql当中占位符的个数应该与可变形参的长度一致
            //1.获取数据库的连接
            ps = conn.prepareStatement(sql);
            //2.填充占位符
            for(int i=0;i< args.length;i++)
            {
                ps.setObject(i+1,args[i]);
            }
            //3.执行操作
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //4.资源关闭
            JDBCUtils.CloseResource(null,ps);
        }
    }

    @Test
    public void testTransactionSelect() throws SQLException, IOException, ClassNotFoundException {

        Connection conn = JDBCUtils.getConnection();

        System.out.println(conn.getTransactionIsolation());
        conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

        conn.setAutoCommit(false);
        //设置数据库的隔离级别

        String sql="select user,password,balance from user_table where user=?";
        User user = getInstance(conn, User.class, sql, "CC");
        JDBCUtils.CloseResource(conn,null);
        System.out.println(user);
    }


    @Test
    public void testTransactionUpdate() throws SQLException, IOException, ClassNotFoundException {

        Connection conn = JDBCUtils.getConnection();
        conn.setAutoCommit(false);
        String sql="update user_table set balance=? where user=?";
        update(conn,sql,5000,"CC");

    }
    
    public T getInstance(Connection conn,Class clazz,String sql,Object...args){
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            for(int i=0;i

package transaction;

public class User {
    private String user;
    private String password;
    private int balance;

    public User(String user, String password, int balance) {
        this.user = user;
        this.password = password;
        this.balance = balance;
    }

    public User() {
    }

    public String getUser() {
        return user;
    }

    public String getPassword() {
        return password;
    }

    public int getBalance() {
        return balance;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public void setBalance(int balance) {
        this.balance = balance;
    }

    @Override
    public String toString() {
        return "User{" +
                "user='" + user + ''' +
                ", password='" + password + ''' +
                ", balance=" + balance +
                '}';
    }
}