JdbcUtils工具类

//me.muphy.util

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

/**
 * JdbcUtils
 *
 * @className: JdbcUtils
 * @author: 若非
 * @date: 2021-09-24 14:57
 */
public class JdbcUtils {
    public static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql:///dbpipe", "root", "root");
        } catch (Exception e) {
            LogUtils.e(JdbcUtils.class.getSimpleName(), e);
        }
        return conn;
    }

    /**
     * 增删改
     *
     * @param sql    预编译SQL语句
     * @param params 参数
     * @return 受影响的记录数目
     */
    public static int executeUpdate(String sql, List<Object> params) {
        int result = -1;
        if (StringUtils.isEmpty(sql)) {
            return result;
        }
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = getConnection();
            ps = connection.prepareStatement(sql);
            if (params != null) {
                for (int i = 0; i < params.size(); i++) {
                    ps.setObject(i + 1, params.get(i));
                }
            }
            result = ps.executeUpdate();
        } catch (SQLException e) {
            LogUtils.e(JdbcUtils.class.getSimpleName(), e);
        } finally {
            release(ps, connection);
        }
        return result;  // 更新数据失败
    }

    /**
     * 查
     *
     * @param sql    预编译SQL语句
     * @param params 参数
     */
    public static ResultSet executeQuery(String sql, List<Object> params) {
        if (StringUtils.isEmpty(sql)) {
            return null;
        }
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = getConnection();
            ps = connection.prepareStatement(sql);
            if (params != null) {
                for (int i = 0; i < params.size(); i++) {
                    ps.setObject(i + 1, params.get(i));
                }
            }
            ResultSet resultSet = ps.executeQuery();
            return resultSet;
        } catch (SQLException e) {
            LogUtils.e(JdbcUtils.class.getSimpleName(), e);
        } finally {
            release(ps, connection);
        }
        return null;  // 更新数据失败
    }

    /**
     * 查
     *
     * @param sql    预编译SQL语句
     * @param params 参数
     */
    public static <T> List<T> executeQuery(String sql, List<Object> params, Class<T> tClass) {
        List<T> ts = new ArrayList<>();
        if (StringUtils.isEmpty(sql)) {
            return ts;
        }
        ResultSet rs = executeQuery(sql, params);
        if (rs == null) {
            return ts;
        }
        List<Field> list = ReflectUtils.getAllFieldList(tClass);
        Map<String, Field> fieldMap = toMap(list, field -> field.getName());
        //for (Field field : list) {
            //Column annotation = field.getAnnotation(Column.class);
            //if (annotation == null || StringUtils.isEmpty(annotation.name())) {
                //continue;
            }
            //String camelCase = StringUtils.getLowerCamelCase(annotation.name());
            //fieldMap.put(camelCase, field);
        //}
        // 获取数据库表结构
        ResultSetMetaData meta;
        try {
            meta = rs.getMetaData();
            while (rs.next()) {
                try {
                    T t = tClass.newInstance();
                    // 循环获取指定行的每一列的信息
                    for (int i = 1; i <= meta.getColumnCount(); i++) {
                        // 当前列名
                        String colName = meta.getColumnLabel(i);
                        colName = StringUtils.getLowerCamelCase(colName);

                        // 获取当前位置的值，返回Object类型
                        Object value = rs.getObject(i);
                        ReflectUtils.setData(fieldMap.get(colName), t, value);
                    }
                    ts.add(t);
                } catch (Exception e) {
                    LogUtils.e(JdbcUtils.class.getSimpleName(), e);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return ts;  // 更新数据失败
    }


    public static void release(Statement stmt, Connection conn) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                LogUtils.w(JdbcUtils.class.getSimpleName(), e);
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                LogUtils.w(JdbcUtils.class.getSimpleName(), e);
            }
        }
    }

    public static <K, T> Map<K, T> toMap(Collection<T> collection, Callback<K, T> callback) {
        Map<K, T> map = new HashMap<>();
        if (collection == null) {
            return map;
        }
        for (T t : collection) {
            K k = callback.call(t);
            map.put(k, t);
        }
        return map;
    }

    interface Callback<K, T> {
        K call(T t);
    }

}