【Java】一个JDBC的工具类

package com.imut.utils;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.ResourceBundle;

/**
 * @author djs
 * @create 2022-09-25 14:30
 */
public class JDBCUtils {


    /**
     * ResourceBundle 是资源绑定器 只能获取.properties的文件（配置文件）
     * 注意事项：
     *   1. .properties的文件必须放在类路径下(Src目录下)
     *   2. 文件名称一定不能写.properties后缀名
     */
    private static ResourceBundle bundle = ResourceBundle.getBundle("resources/jdbc");
    private static String driver = bundle.getString("driver");
    private static String url = bundle.getString("url");
    private static String user = bundle.getString("user");
    private static String password = bundle.getString("password");

	// 构造器私有化：避免工具类可以创建对象
    private JDBCUtils(){}

    static {
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取一个连接
     * @return
     * @throws Exception
     */
    public static Connection getConnection() throws Exception {
        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;
    }

    /**
     * 关闭连接
     * @param connection
     * @param statement
     */
    public static void closeConnection(Connection connection, Statement statement) {
        if (connection !=  null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement != null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    /**
     * 关闭连接
     * @param connection
     * @param statement
     */
    public static void closeConnection(Connection connection, Statement statement, ResultSet resultSet) {
        if (resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (connection !=  null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement != null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }




    //查询表中的数据，以集合的形式返回
    public static  <T> List <T> query(String sql, Class<T> clazz, Object ...args) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<T> list = new ArrayList<>();

        try {
            // 1.获取一个连接
            connection = getConnection();

            // 2.预编译一个sql语句，返回一个PrepareStatement对象
            preparedStatement = connection.prepareStatement(sql);

            // 3.填充占位符
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i+1, args[i]);
            }

            // 4。执行sql,得到结果集
            resultSet = preparedStatement.executeQuery();

            // 5.获取元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            // 6.获取一个类的反射实例
            T t = null;
            // 7.遍历得到每一行数据
            while (resultSet.next()){
                t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    // 7.1获取列值
                    Object object = resultSet.getObject(i + 1);
                    // 7.2获取列别名
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    // 7.3获取属性并设置属性的值
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, object);
                }
                list.add(t);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection, preparedStatement, resultSet);
        }

        return list;
    }
    

    //新增，修改，删除
    public static int update(String sql, Object ...obj) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 1.获取连接
            connection = getConnection();
            // 2.预编译sql，返回一个PrepareStatement实例
            preparedStatement = connection.prepareStatement(sql);
            // 3.填充占位符
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i+1, obj[i]);
            }
            // 4.执行
            int result = preparedStatement.executeUpdate();
            connection.commit();
            return result;
        } catch (Exception e) {
            if(connection != null){
                try {
                    connection.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            // 5.关闭资源
            closeConnection(connection, preparedStatement);
        }
        return 0;
    }


}

Web工程目录结构

介绍ThreadLocal
每次使用完threadLocal完后，都要删除里面存的值
根本原因：

添加了事务版本：

package com.imut.utils;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.ResourceBundle;

/**
 * @author djs
 * @create 2022-09-25 14:30
 */
public class JDBCUtils {


    private static ResourceBundle bundle = ResourceBundle.getBundle("resources.jdbc");
    private static String driver = bundle.getString("driver");
    private static String url = bundle.getString("url");
    private static String user = bundle.getString("user");
    private static String password = bundle.getString("password");

    //避免创建对象
    private JDBCUtils(){}

    static {
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }


    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();

    /**
     * 获取一个连接
     * @return
     * @throws Exception
     */
    public static Connection getConnection() throws Exception {
        Connection connection = threadLocal.get();
        System.out.println("connection = " + connection);
        if(connection == null){
            connection = DriverManager.getConnection(url, user, password);
            threadLocal.set(connection);
        }
        return connection;
    }

    /**
     * 关闭连接
     * @param connection
     * @param statement
     */
    public static void closeConnection(Connection connection, Statement statement) {
        if (connection !=  null){
            try {
                connection.close();
                //从threadlocal中删除，因为tomcat是支持线程池的
                // 如果不删除，可能下次获得的还是这个线程，会获得这个的connection
                threadLocal.remove();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement != null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    /**
     * 关闭连接
     * @param connection
     * @param statement
     */
    public static void closeConnection(Connection connection, Statement statement, ResultSet resultSet) {
        if (resultSet != null){
            try {
                resultSet.close();
                //从threadlocal中删除，因为tomcat是支持线程池的
                // 如果不删除，可能下次获得的还是这个线程，会获得这个的connection
                threadLocal.remove();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (connection !=  null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement != null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }




    //查询表中的数据，以集合的形式返回
    public static  <T> List <T> query(String sql, Class<T> clazz, Object ...args) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<T> list = new ArrayList<>();

        try {
            // 1.获取一个连接
            connection = getConnection();

            // 2.预编译一个sql语句，返回一个PrepareStatement对象
            preparedStatement = connection.prepareStatement(sql);

            // 3.填充占位符
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i+1, args[i]);
            }

            // 4。执行sql,得到结果集
            resultSet = preparedStatement.executeQuery();

            // 5.获取元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            // 6.获取一个类的反射实例
            T t = null;
            // 7.遍历得到每一行数据
            while (resultSet.next()){
                t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    // 7.1获取列值
                    Object object = resultSet.getObject(i + 1);
                    // 7.2获取列别名
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    // 7.3获取属性并设置属性的值
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, object);
                }
                list.add(t);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConnection(null, preparedStatement, resultSet);
        }

        return list;
    }
    

    //新增，修改，删除
    public static int update(String sql, Object ...obj) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 1.获取连接
            connection = getConnection();
            // 2.预编译sql，返回一个PrepareStatement实例
            preparedStatement = connection.prepareStatement(sql);
            // 3.填充占位符
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i+1, obj[i]);
            }
            // 4.执行
            int result = preparedStatement.executeUpdate();
            return result;
        } catch (Exception e) {
            if(connection != null){
                try {
                    connection.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            // 5.关闭资源
            closeConnection(null, preparedStatement);
        }
        return 0;
    }


}

测试

 @Test
    public void test5(){
        BankDao bankDao = new BankDaoImpl();
        Bank bank1 = bankDao.seleteById("b001");
        Bank bank2 = bankDao.seleteById("b002");

        try {
            Connection connection = JDBCUtils.getConnection();
            connection.setAutoCommit(false);


            double money = 1000;
            if(bank1.getBalance() < money){
                System.out.println("钱不够!");
                return;
            }
            bank1.setBalance(bank1.getBalance()-money);
            bank2.setBalance(bank2.getBalance()+money);
            bankDao.update(bank1);
            int a = 1 /0 ;
            bankDao.update(bank2);
            System.out.println("转账成功!");
            connection.commit();
            //关闭JDBC的各个资源
            JDBCUtils.closeConnection(connection,null);
        } catch (Exception e) {
            e.printStackTrace();
        }



    }