JDBC考虑事务的实现

package JDBCTest;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @className: transactiontest
 * @description: 考虑数据库事务的代码实现
 * @author: CCQ
 * @date: 2021/9/24
 **/
public class transactiontest {
    public static void main(String[] args){
        Connection connection =null;
        try {
            InputStream resourceAsStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
            Properties properties = new Properties();
            properties.load(resourceAsStream);
            connection = DriverManager.getConnection(properties.getProperty("url"), properties.getProperty("user"), properties.getProperty("password"));
            connection.setAutoCommit(false);
            String sql ="update pro set sal =sal-1000  where bid =?";
            String sql1 ="update sc set score =score+10  where id =?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,1);
            preparedStatement.execute();
            preparedStatement.close();
            PreparedStatement preparedStatement1 = connection.prepareStatement(sql1);
            preparedStatement1.setInt(1,1350);
            preparedStatement1.execute();
            preparedStatement1.close();
            System.out.println(10/0);
            connection.commit();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }
}

package JDBCTest;

import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;

/**
 * @className: transaction2test
 * @description: 代码演示事务的隔离级别
 * @author: CCQ
 * @date: 2021/9/25
 **/
public class transaction2test {

    @Test
    public void test01(){
        Connection connection =null;
        try {

            InputStream resourceAsStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
            Properties properties = new Properties();
            properties.load(resourceAsStream);
            connection = DriverManager.getConnection(properties.getProperty("url"),properties.getProperty("user"),properties.getProperty("password"));
            System.out.println(connection.getTransactionIsolation());
            connection.setAutoCommit(false);
            connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
            System.out.println(connection.getTransactionIsolation());
            String sql ="select * from l where id =?";
            ArrayList<Selectdata> arrayList = selectany3(connection, Selectdata.class, sql, 13);
            System.out.println(arrayList);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } finally {
        }
    }

    @Test
    public void test02() throws InterruptedException, SQLException {
        Connection connection =null;
        try {
            InputStream resourceAsStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
            Properties properties = new Properties();
            properties.load(resourceAsStream);
            connection = DriverManager.getConnection(properties.getProperty("url"),properties.getProperty("user"),properties.getProperty("password"));
            System.out.println(connection.getTransactionIsolation());
            connection.setAutoCommit(false);
            String sql ="update l set name =? where id =?";
            update(connection,sql,"ak471",13);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            Thread.sleep(15000);
            connection.rollback();
        }
    }

    void update(Connection connection,String sql, Object... args) throws IOException, SQLException, ClassNotFoundException {
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            preparedStatement.setObject(i + 1, args[i]);
        }
        preparedStatement.execute();
        preparedStatement.close();
    }

   static  <T> ArrayList<T> selectany3(Connection connection,Class<T> clazz, String sql, Object... args) throws IOException, SQLException, NoSuchFieldException, IllegalAccessException, InstantiationException {
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            preparedStatement.setObject(i + 1, args[i]);
        }
        ResultSet resultSet = preparedStatement.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        ArrayList<T> arrayList = new ArrayList<>();
        while (resultSet.next()) {
            T t = clazz.newInstance();
            for (int i = 0; i < columnCount; i++) {
                String string = resultSet.getString(i + 1);
                String columnLabel = metaData.getColumnLabel(i + 1);
                Field declaredField = clazz.getDeclaredField(columnLabel);
                declaredField.setAccessible(true);
                declaredField.set(t, string);
            }
            arrayList.add(t);
        }
        preparedStatement.close();
        return arrayList;
    }
}