package transaction;

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

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

/**
 * 数据库事务：
 * 事务：一组逻辑操作单元，使事务从一种状态转换为另一种状态
 * 数据一旦提交，就无法回滚
 * 关注的问题：哪些操作会自动提交？
 *      >DDL操作一旦执行，就会自动提交
 *      >DML操作默认情况下，一旦执行就会自动提交，但是可以修改set_autocommit=false取消DML的自动提交
 *      >默认在关闭连接的时候，会自动提交数据
 */
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");

    }
    /*
    针对不同的表格进行查询，返回表的一条记录
    考虑了事务，version2.0
     */
    public<T> T getInstance(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();
                //要给customer赋值为取得的某个属性
                //处理结果集一行数据中的每一个列
                for(int i=0;i<columncount;i++){
                    Object value = rs.getObject(i + 1);
                    //获取列名
                    //String columnName = rsmd.getColumnName(i + 1);
                    //给customer列名为columnName的属性赋值为value：通过反射
                    String columnLabel=rsmd.getColumnLabel(i+1);
                    Field field = clazz.getDeclaredField(columnLabel);
                    //变为可以访问的属性
                    field.setAccessible(true);
                    field.set(t,value);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}

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 +
                '}';
    }
}