JdbcUtils工具类

JdbcUtils工具类，方便用得时候copy

本文连接：https://www.cnblogs.com/muphy/p/15346775.html

JdbcUtils.java

//package me.muphy.util

import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;

/**
 * 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) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 增删改
     *
     * @param sql    预编译SQL语句
     * @param params 参数
     * @return 受影响的记录数目
     */
    public static int executeUpdate(String sql, List<Object> params) {
        int result = -1;
        if (sql == null || sql.isEmpty()) {
            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) {
            e.printStackTrace();
        } finally {
            release(ps, connection);
        }
        return result;  // 更新数据失败
    }

    /**
     * 查
     *
     * @param sql    预编译SQL语句
     * @param params 参数
     */
    public static ResultSet executeQuery(String sql, List<Object> params) {
        if (sql == null || sql.isEmpty()) {
            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) {
            e.printStackTrace();
        } 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 (sql == null || sql.isEmpty()) {
            return ts;
        }
        ResultSet rs = executeQuery(sql, params);
        if (rs == null) {
            return ts;
        }
        List<Field> list = 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 = getLowerCamelCase(colName);

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

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

    public static List<Field> getAllFieldList(Class<?> tClass) {
        return getFieldList(tClass, true, null);
    }

    public static List<Field> getFieldList(Class<?> tClass, boolean superClass, List<Field> fieldSet) {
        if (fieldSet == null) {
            fieldSet = new ArrayList<>();
        }
        if (tClass == null) {
            return fieldSet;
        }
        fieldSet.addAll(Arrays.asList(tClass.getFields()));
        fieldSet.addAll(Arrays.asList(tClass.getDeclaredFields()));

        if (superClass) {
            Class supperClass = tClass.getSuperclass();
            if (!Object.class.equals(supperClass)) {
                return getFieldList(supperClass, superClass, fieldSet);
            }
        }
        fieldSet = new ArrayList<>(new HashSet<>(fieldSet));//去重
        return fieldSet;
    }

    private static <T> void setData(T t, Field field, Object val) {
        boolean temp = field.isAccessible();
        field.setAccessible(true);
        try {
            field.set(t, val);
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        field.setAccessible(temp);
    }

    public static String getLowerCamelCase(String name) {
        name = getUpperCamelCase(name);
        return name.substring(0, 1).toLowerCase() + name.substring(1);
    }

    public static String getUpperCamelCase(String name) {
        if (name == null || "".equals(name)) {
            return "";
        }
        String[] strings = name.split("[^a-zA-Z0-9]+");
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < strings.length; i++) {
            sb.append(name.substring(0, 1).toUpperCase()).append(name.substring(1).toLowerCase());
        }
        return sb.toString();
    }


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

    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);
    }

}