JDBC工具类

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

结果集封装，借鉴了一些博客资料，注意事项，返回字段的别名需要与javaBean字段名保持一致，如有其它需求可以适当调整

一、使用步骤

import org.apache.commons.lang3.time.DateUtils;
import org.springframework.util.StopWatch;

import javax.annotation.PostConstruct;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.*;
import java.text.ParseException;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.util.LinkedList;
import java.util.List;
import java.util.Properties;
public class JdbcUtils {
    private static final String URL = "jdbc:mysql://localhost:8080/test?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true";
    private static final String USER = "admin";
    private static final String PWD = "admin";

    private final Properties info = new Properties();

    @PostConstruct
    public void initPro() {
        info.setProperty("user", USER);
        info.setProperty("password", PWD);
        info.setProperty("initialSize", "2");
        info.setProperty("maxActive", "8");
        info.setProperty("minIdle", "4");
        info.setProperty("timeBetweenEvictionRunsMillis", "300000");
        info.setProperty("minEvictableIdleTimeMillis", "360000");
        info.setProperty("numTestsPerEvictionRun", "60000");
        info.setProperty("validationQuery", "SELECT 1 FROM DUAL");
        info.setProperty("testWhileIdle", "true");
        info.setProperty("testOnBorrow", "false");
        info.setProperty("testOnReturn", "false");
        info.setProperty("poolPreparedStatements", "true");
        info.setProperty("connectTimeout", "100000");
        info.setProperty("socketTimeout", "100000");
        info.setProperty("maxPoolPreparedStatementPerConnectionSize", "50");
    }

    /**
     * 数据处理
     *
     * @param sqlStr sql
     * @param start  参数1
     * @param end    参数2
     */
    public void dataHandle(String sqlStr, LocalDateTime start, LocalDateTime end) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {

            return;
        }
        try (
                Connection con = DriverManager.getConnection(URL, info);
                PreparedStatement statement = con.prepareStatement(sqlStr);
        ) {
            Timestamp stamp1 = new Timestamp(start.toInstant(ZoneOffset.of("+8")).toEpochMilli());
            Timestamp stamp2 = new Timestamp(end.toInstant(ZoneOffset.of("+8")).toEpochMilli());
            statement.setTimestamp(1, stamp1);
            statement.setTimestamp(2, stamp2);
            StopWatch stopWatch = new StopWatch();
            stopWatch.start();
            ResultSet resultSet = statement.executeQuery();
            stopWatch.stop();
            double totalTimeMillis = stopWatch.getTotalTimeMillis();
            System.out.println("sql耗时" + totalTimeMillis + "毫秒");
            resultSet.last();
            System.out.println("resultSetLength：" + resultSet.getRow());
            resultSet.beforeFirst();
            List<Object> dataList = new LinkedList<>();
            Object tmp = null;
            ResultSetMetaData metaData = resultSet.getMetaData();
            while (resultSet.next()) {
                try {
                    tmp = putResult(resultSet, Object.class, metaData);
                    dataList.add(tmp);
                } catch (Exception e) {
                    System.out.println("resultSet转Java对象异常：" + e.getMessage());
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * resultSet 转javaBean
     *
     * @param rs       ResultSet
     * @param obj      Java类型
     * @param metaData 结果集属性
     * @param <T>      返回类型
     * @throws NoSuchMethodException     NoSuchMethodException
     * @throws SQLException              SQLException
     * @throws InvocationTargetException InvocationTargetException
     * @throws IllegalAccessException    IllegalAccessException
     * @throws InstantiationException    InstantiationException
     */
    public static <T> T putResult(ResultSet rs, Class<T> obj, ResultSetMetaData metaData)
            throws NoSuchMethodException,
            SQLException,
            InvocationTargetException,
            IllegalAccessException, InstantiationException {
        T newInstance = obj.newInstance();
        int count = metaData.getColumnCount();
        metaData = rs.getMetaData();
        for (int i = 1; i <= count; i++) {
            // 获取字段别名
            String name = metaData.getColumnLabel(i);
            // 首字母大写
            String substring = name.substring(0, 1);
            String replace = name.replaceFirst(substring, substring.toUpperCase());
            Class<?> type = null;
            try {
                // 获取字段类型
                type = obj.getDeclaredField(name).getType();
            } catch (NoSuchFieldException e) {
                // Class对象未定义该字段时,跳过
                continue;
            }
            Method method = obj.getMethod("set" + replace, type);
            if (type.isAssignableFrom(String.class)) {
                method.invoke(newInstance, rs.getString(i));
            } else if (type.isAssignableFrom(byte.class) || type.isAssignableFrom(Byte.class)) {
                method.invoke(newInstance, rs.getByte(i));// byte 数据类型是8位、有符号的，以二进制补码表示的整数
            } else if (type.isAssignableFrom(short.class) || type.isAssignableFrom(Short.class)) {
                method.invoke(newInstance, rs.getShort(i));// short 数据类型是 16 位、有符号的以二进制补码表示的整数
            } else if (type.isAssignableFrom(int.class) || type.isAssignableFrom(Integer.class)) {
                method.invoke(newInstance, rs.getInt(i));// int 数据类型是32位、有符号的以二进制补码表示的整数
            } else if (type.isAssignableFrom(long.class) || type.isAssignableFrom(Long.class)) {
                method.invoke(newInstance, rs.getLong(i));// long 数据类型是 64 位、有符号的以二进制补码表示的整数
            } else if (type.isAssignableFrom(float.class) || type.isAssignableFrom(Float.class)) {
                method.invoke(newInstance, rs.getFloat(i));// float 数据类型是单精度、32位、符合IEEE 754标准的浮点数
            } else if (type.isAssignableFrom(double.class) || type.isAssignableFrom(Double.class)) {
                method.invoke(newInstance, rs.getDouble(i));// double 数据类型是双精度、64 位、符合IEEE 754标准的浮点数
            } else if (type.isAssignableFrom(BigDecimal.class)) {
                method.invoke(newInstance, rs.getBigDecimal(i));
            } else if (type.isAssignableFrom(boolean.class) || type.isAssignableFrom(Boolean.class)) {
                method.invoke(newInstance, rs.getBoolean(i));// boolean数据类型表示一位的信息
            } else if (type.isAssignableFrom(Date.class)) {
                Timestamp timestamp = rs.getTimestamp(i);
                method.invoke(newInstance, new Date(timestamp.getTime()));
            }
        }
        return newInstance;
    }
}

总结

还可以