ODBC桥的快捷操作方法

ODBC桥在jdk1.8被移除后似乎springboot2.7.14的HikariCP连接池不支持保管odbc桥的连接了.故而研究了一下怎么快速执行access内的sql.单数据源,没有把具体路径配置为入参.如有bug,欢迎指正.

@Service
public class AccessDBOperate {
    private Connection connection;  // 数据库连接对象

    // 构造函数，通过数据库名称建立ODBC连接
    {
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String url = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=path:\\to\\access;uid=;pwd=pwd;charSet=GBK";
            connection = DriverManager.getConnection(url);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 构造函数，通过数据库驱动、用户名、密码和数据库路径建立连接


    // 私有构造函数，防止外部直接创建实例
    private AccessDBOperate() {
    }

    public <T> List<T> executeQuery(String querySql, Class<T> entityType) {
        // 检查数据库连接是否存在
        if (null != this.connection) {
            Statement statement = null;

            try {
                // 创建SQL语句执行对象
                statement = this.connection.createStatement();
                // 确保查询SQL不为空
                if (!StrUtil.isEmpty(querySql)) {
                    // 执行查询并获取结果集
                    ResultSet resultSet = statement.executeQuery(querySql);
                    // 将结果集转换为实体类对象列表
                    List<T> entities = this.transResultSetToEntities(entityType, resultSet);
                    return entities;
                }
            } catch (Exception exception) {
                // 打印并记录异常
                exception.printStackTrace();
            } finally {
                // 在finally块中关闭statement，确保资源正常释放
                if (null != statement) {
                    try {
                        statement.close();
                    } catch (SQLException sqlException) {
                        // 处理关闭statement时可能出现的异常
                        sqlException.printStackTrace();
                    }
                }
            }
        }

        // 如果连接不存在或者查询SQL为空，返回空列表
        return Collections.emptyList();
    }

    private <T> List<T> transResultSetToEntities(Class<T> entityClass, ResultSet resultSet) throws Exception {
        // 获取实体类的所有字段
        Field[] fields = entityClass.getDeclaredFields();
        Map<String, String> columnToFieldMap = new HashMap<>(fields.length);
        Map<String, Class<?>> columnToTypeMap = new HashMap<>(fields.length);

        // 遍历字段，构建列名到字段名的映射
        for (Field field : fields) {
            Column columnAnnotation = field.getAnnotation(Column.class);
            if (columnAnnotation != null) {
                String fieldName = field.getName();
                String columnName = columnAnnotation.name();
                Class<?> fieldType = field.getType();

                // 处理列名为空的情况
                if (!StringUtils.isEmpty(columnName)) {
                    columnToFieldMap.put(columnName.toLowerCase(), fieldName);
                    columnToTypeMap.put(columnName.toLowerCase(), fieldType);
                } else {
                    columnToFieldMap.put(fieldName.toLowerCase(), fieldName);
                    columnToTypeMap.put(fieldName.toLowerCase(), fieldType);
                }
            }
        }

        // 获取所有的set方法
        Method[] methods = entityClass.getDeclaredMethods();
        List<String> setMethodNames = new ArrayList<>(fields.length);
        for (Method method : methods) {
            if (method.getName().startsWith("set")) {
                setMethodNames.add(method.getName());
            }
        }

        List<T> entities = new ArrayList<>(columnToFieldMap.size());
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        List<String> availableColumns = new ArrayList<>();

        // 获取结果集中所有列的名称
        for (int i = 0; i < columnCount; ++i) {
            availableColumns.add(metaData.getColumnName(i + 1).toLowerCase());
        }

        // 遍历结果集
        while (resultSet.next()) {
            T entity = entityClass.getDeclaredConstructor().newInstance();

            for (String columnName : availableColumns) {
                String fieldName = columnToFieldMap.get(columnName);
                if (!StringUtils.isEmpty(fieldName)) {
                    String setterMethodName = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                    Class<?> fieldType = columnToTypeMap.get(columnName);
                    Object columnValue = getColumnValue(resultSet, columnName, fieldType);

                    // 如果实体类包含相应的set方法，则调用该方法设置值
                    if (setMethodNames.contains(setterMethodName)) {
                        Method setterMethod = entityClass.getDeclaredMethod(setterMethodName, fieldType);
                        if (columnValue != null) {
                            setterMethod.invoke(entity, columnValue);
                        }
                    }
                }
            }

            entities.add(entity);
        }

        return entities;
    }

    @SneakyThrows
    private Object getColumnValue(ResultSet resultSet, String columnName, Class<?> type) throws SQLException {
        String typeName = type.getSimpleName().toLowerCase();
        switch (typeName) {
            case "double":
                return resultSet.getDouble(columnName);
            case "integer":
            case "int":
                return resultSet.getInt(columnName);
            case "long":
                return resultSet.getLong(columnName);
            case "float":
                return resultSet.getFloat(columnName);
            case "date":
                return resultSet.getDate(columnName);
            case "string":
                // 处理字符串类型，考虑到编码问题
                byte[] bts = resultSet.getBytes(columnName);
                if (bts == null) {
                    return null;
                }
                try {
                    return new String(bts, "GBK");
                } catch (Exception e) {
                    return null;
                }
            default:
                // 默认处理为二进制数据
                return resultSet.getBytes(columnName);

        }
    }

    public <T> List<T> executeQuery(String querySql, Class<T> entityClass, Object[] params) {
        // 检查数据库连接是否存在
        if (this.connection != null) {
            PreparedStatement preparedStatement = null;

            try {
                // 准备预编译的SQL语句
                preparedStatement = this.connection.prepareStatement(querySql);
                // 如果参数为空，则返回空列表
                if (params == null) {
                    return Collections.emptyList();
                }

                // 初始化预编译语句的参数
                this.initParamToPreparedStatement(params, preparedStatement);
                // 执行查询并获取结果集
                ResultSet resultSet = preparedStatement.executeQuery();
                // 将结果集转换为实体类对象列表
                return this.transResultSetToEntities(entityClass, resultSet);
            } catch (Exception e) {
                // 异常处理：打印并记录异常信息
                e.printStackTrace();
                return Collections.emptyList();
            } finally {
                // 确保预编译语句资源被释放
                if (preparedStatement != null) {
                    try {
                        preparedStatement.close();
                    } catch (SQLException sqlException) {
                        // 处理关闭预编译语句时的异常
                        sqlException.printStackTrace();
                    }
                }
            }
        } else {
            // 如果数据库连接不存在，则返回空列表
            return Collections.emptyList();
        }
    }

    private void initParamToPreparedStatement(Object[] params, PreparedStatement preparedStatement) throws SQLException {
        int paramCount = params.length;

        for (int i = 0; i < paramCount; i++) {
            Object param = params[i];

            if (param != null) {
                Class<?> paramClass = param.getClass();

                // 根据参数类型设置PreparedStatement的相应值
                if (Integer.class.equals(paramClass)) {
                    preparedStatement.setInt(i + 1, (Integer) param);
                } else if (Double.class.equals(paramClass)) {
                    preparedStatement.setDouble(i + 1, (Double) param);
                } else if (Float.class.equals(paramClass)) {
                    preparedStatement.setFloat(i + 1, (Float) param);
                } else if (Long.class.equals(paramClass)) {
                    preparedStatement.setLong(i + 1, (Long) param);
                } else if (String.class.equals(paramClass)) {
                    preparedStatement.setString(i + 1, (String) param);
                } else if (Date.class.equals(paramClass)) {
                    preparedStatement.setDate(i + 1, new java.sql.Date(((Date) param).getTime()));
                } else if (java.sql.Date.class.equals(paramClass)) {
                    preparedStatement.setDate(i + 1, (java.sql.Date) param);
                } else {
                    // 如果类型未知，设置为null
                    preparedStatement.setNull(i + 1, Types.NULL);
                }
            } else {
                // 如果参数为null，设置SQL类型为NULL
                preparedStatement.setNull(i + 1, Types.NULL);
            }
        }
    }

}

上为服务类.

@Repository
public class ObjectDao {
    public List<Object> findAll(AccessDBOperate accessDBOperate) {
        String sql = "select * from piece";
        return accessDBOperate.executeQuery(sql, Object.class);
    }

    public Object getBigTime(Double datTim, AccessDBOperate accessDBOperate) {
        String sql = "SELECT TOP 1 * from Piece where DatTim > ? order by DatTim asc";
        List<Object> objects = accessDBOperate.executeQuery(sql, Object.class, new Object[]{datTim});
        return !objects.isEmpty() ? objects.get(0) : null;
    }
}

上为使用样例