数据库访问 BaseDao的封装

package com.fengmy.javaweb02.base;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.Properties;


/**
 * 数据库连接工具类
 *
 * @since 2022/3/11
 */
public abstract class BaseDao<T> {
    public final String DRIVER = "com.mysql.cj.jdbc.Driver";
    public final String URL = "jdbc:mysql://localhost:3306/fruitdb?useUnicode=true&useCharacterEncoding=utf-8";
    public final String USER = "root";
    public final String PWD = "root";

    protected Connection conn;
    protected PreparedStatement psmt;
    protected ResultSet res;

    // T 的Class对象
    private Class entityClass;

    public BaseDao() {
        Type genericSuperclass = getClass().getGenericSuperclass();
        Type[] actualTypeArguments = ((ParameterizedType) genericSuperclass).getActualTypeArguments();
        Type actualType = actualTypeArguments[0];
        System.out.println(actualType.getTypeName());
        try {
            entityClass = Class.forName(actualType.getTypeName());
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接
     *
     * @return 数据库连接
     */
    protected Connection getConn() {
        try {


            DruidDataSource dds=new DruidDataSource();
            dds.setDriverClassName(DRIVER);
            dds.setUrl(URL);
            dds.setUsername(USER);
            dds.setPassword(PWD);
            dds.setInitialSize(5);
            dds.setMaxActive(10);
            dds.setMaxActive(3000);

            conn=dds.getConnection();
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 释放资源
     *
     * @param res  数据集
     * @param psmt 预处理
     * @param conn 数据库连接
     */
    protected void close(ResultSet res, PreparedStatement psmt, Connection conn) {
        try {
            if (res != null) {
                res.close();
            }
            if (psmt != null) {
                psmt.close();
            }
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 对数据库更新操作的抽取
     *
     * @param sql    sql语句
     * @param params 预处理数据参数
     * @return 返回受影响行数
     */
    protected int executeUpdate(String sql, Object... params) {
        boolean insertFlag = false;
        insertFlag = sql.trim().toUpperCase(Locale.ROOT).startsWith("INSERT");
        try {
            conn = getConn();
            if (insertFlag) {
                psmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            } else {
                psmt = conn.prepareStatement(sql);
            }
            setParams(psmt, params);
            int count = psmt.executeUpdate();
            if (insertFlag) {
                res = psmt.getGeneratedKeys();
                if (res.next()) {
                    return ((Long) res.getLong(1)).intValue();
                }
            }
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(res, psmt, conn);
        }
        return 0;
    }

    /**
     * 设置预处理参数
     */
    private void setParams(PreparedStatement psmt, Object... params) throws SQLException {
        if (params != null && params.length > 0) {
            for (int i = 0; i < params.length; i++) {
                psmt.setObject(i + 1, params[i]);
            }
        }
    }


    /**
     * 通过反射技术给object对象的属性赋值
     */
    private void setValue(Object obj, String property, Object propertyValue) {
        Class clazz = obj.getClass();
        try {
            Field field = clazz.getDeclaredField(property);
            if (field != null) {
                field.setAccessible(true);
                field.set(obj, propertyValue);

            }
        } catch (NoSuchFieldException | IllegalAccessException e) {
            e.printStackTrace();
        }

    }

    /**
     * 对数据库查询操作的抽取
     *
     * @param sql    sql语句
     * @param params 预处理数据参数
     * @return 返回T类型数据列表
     */
    protected List<T> executeQuery(String sql, Object... params) {
        List<T> list = new ArrayList<>();
        try {
            conn = getConn();
            psmt = conn.prepareStatement(sql);
            setParams(psmt, params);
            ResultSet res = psmt.executeQuery();
            ResultSetMetaData rsmd = res.getMetaData();
            int columnCount = rsmd.getColumnCount();
            while (res.next()) {
                T entity = getT(columnCount, rsmd, res);
                list.add(entity);
            }
        } catch (SQLException | IllegalAccessException | InstantiationException e) {
            e.printStackTrace();
        } finally {
            close(res, psmt, conn);
        }
        return list;
    }

    /**
     * 获取T对象
     *
     * @param columnCount 列数量
     * @param rsmd        元数据
     * @param res         数据集
     * @return T类型
     * @throws InstantiationException 异常
     * @throws IllegalAccessException 异常
     * @throws SQLException           异常
     */
    private T getT(int columnCount, ResultSetMetaData rsmd, ResultSet res) throws InstantiationException, IllegalAccessException, SQLException {

        if (entityClass != null) {
            T entity = (T) entityClass.newInstance();
            for (int i = 0; i < columnCount; i++) {
                String columnName = rsmd.getColumnName(i + 1);
                Object columnValue = res.getObject(i + 1);
                setValue(entity, columnName, columnValue);
            }
            return entity;
        }
        return null;
    }

    /**
     * 对数据库查询操作的抽取
     *
     * @param sql    sql语句
     * @param params 预处理数据参数
     * @return 返回T类型
     */
    protected T load(String sql, Object... params) {
        try {
            conn = getConn();
            psmt = conn.prepareStatement(sql);
            setParams(psmt, params);
            ResultSet res = psmt.executeQuery();
            ResultSetMetaData rsmd = res.getMetaData();
            int columnCount = rsmd.getColumnCount();
            if (res.next()) {
                return getT(columnCount, rsmd, res);
            }
        } catch (SQLException | IllegalAccessException | InstantiationException e) {
            e.printStackTrace();
        } finally {
            close(res, psmt, conn);
        }
        return null;
    }


    /**
     * 对数据库复杂查询操作的抽取
     *
     * @param sql    sql语句
     * @param params 预处理数据参数
     * @return 返回T类型
     */
    protected Object[] executeComplexQuery(String sql, Object... params) {
        try {
            conn = getConn();
            psmt = conn.prepareStatement(sql);
            setParams(psmt, params);
            ResultSet res = psmt.executeQuery();
            ResultSetMetaData rsmd = res.getMetaData();
            int columnCount = rsmd.getColumnCount();
            Object[] columnValueArr = new Object[columnCount];
            if (res.next()) {
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = res.getObject(i + 1);
                    columnValueArr[i] = columnValue;
                }
                return columnValueArr;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(res, psmt, conn);
        }
        return null;
    }
}