<!-- 添加UCanAccess依賴 -->
<dependency>
    <groupId>net.sf.ucanaccess</groupId>
    <artifactId>ucanaccess</artifactId>
    <version>5.0.1</version>
</dependency>

import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

public class MdbJdbcUtil {
    private static final String DRIVER_CLASS = "net.ucanaccess.jdbc.UcanaccessDriver";
    private static final String URL_PREFIX = "jdbc:ucanaccess://";
    private static final String URL_MEMORY = ";memory=false";

    /**
     * 私有構(gòu)造方法，防止實(shí)例化
     */
    private MdbJdbcUtil() {
        throw new UnsupportedOperationException("This is a utility class and cannot be instantiated");
    }
    /**
     * 靜態(tài)初始化塊，加載數(shù)據(jù)庫驅(qū)動
     */
    static {
        try {
            Class.forName(DRIVER_CLASS);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("Failed to load UCanAccess JDBC driver", e);
        }
    }

    /**
     * 創(chuàng)建數(shù)據(jù)庫連接
     *
     * @param mdbPath MDB文件路徑
     * @return 數(shù)據(jù)庫連接
     * @throws SQLException 如果連接失敗
     */
    public static Connection getConnection(String mdbPath) throws SQLException {
        if (mdbPath == null || mdbPath.trim().isEmpty()) {
            throw new IllegalArgumentException("MDB file path cannot be null or empty");
        }

        Properties props = new Properties();
        props.put("charSet", "UTF-8");

        String dbUrl = URL_PREFIX + mdbPath + URL_MEMORY;
        return DriverManager.getConnection(dbUrl, props);
    }
    /**
     * 執(zhí)行帶參數(shù)的查詢并返回List<Map<String, Object>>結(jié)果
     *
     * @param mdbPath MDB文件路徑
     * @param sql SQL查詢語句
     * @param params 查詢參數(shù)列表
     * @return 查詢結(jié)果列表，每個Map代表一行數(shù)據(jù)
     * @throws SQLException 如果查詢失敗
     */
    public static List<Map<String, Object>> queryForList(String mdbPath, String sql, Object... params) throws SQLException {
        validateParameters(mdbPath, sql);

        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            conn = getConnection(mdbPath);
            stmt = conn.prepareStatement(sql);

            // 設(shè)置查詢參數(shù)
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    stmt.setObject(i + 1, params[i]);
                }
            }

            rs = stmt.executeQuery();
            return convertResultSetToList(rs);

        } finally {
            closeResources(rs, stmt, conn);
        }
    }

    /**
     * 驗(yàn)證參數(shù)有效性
     *
     * @param mdbPath MDB文件路徑
     * @param sql SQL語句
     */
    private static void validateParameters(String mdbPath, String sql) {
        if (mdbPath == null || mdbPath.trim().isEmpty()) {
            throw new IllegalArgumentException("MDB file path cannot be null or empty");
        }
        if (sql == null || sql.trim().isEmpty()) {
            throw new IllegalArgumentException("SQL statement cannot be null or empty");
        }
    }

    /**
     * 關(guān)閉數(shù)據(jù)庫資源
     *
     * @param rs ResultSet對象
     * @param stmt Statement對象
     * @param conn Connection對象
     */
    private static void closeResources(ResultSet rs, Statement stmt, Connection conn) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            // 記錄日志但不拋出異常
            System.err.println("Failed to close ResultSet: " + e.getMessage());
        }

        try {
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException e) {
            System.err.println("Failed to close Statement: " + e.getMessage());
        }

        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            System.err.println("Failed to close Connection: " + e.getMessage());
        }
    }

    /**
     * 將ResultSet轉(zhuǎn)換為List<Map<String, Object>>
     *
     * @param rs ResultSet對象
     * @return 轉(zhuǎn)換后的列表
     * @throws SQLException 如果轉(zhuǎn)換失敗
     */
    private static List<Map<String, Object>> convertResultSetToList(ResultSet rs) throws SQLException {
        List<Map<String, Object>> resultList = new ArrayList<>();

        if (rs == null) {
            return resultList;
        }

        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();

        // 獲取列名列表
        List<String> columnNames = IntStream.rangeClosed(1, columnCount)
                .mapToObj(i -> {
                    try {
                        return metaData.getColumnLabel(i);
                    } catch (SQLException e) {
                        throw new RuntimeException("Failed to get column name", e);
                    }
                })
                .collect(Collectors.toList());

        // 遍歷結(jié)果集
        while (rs.next()) {
            Map<String, Object> row = new LinkedHashMap<>();
            for (String columnName : columnNames) {
                row.put(columnName, rs.getObject(columnName));
            }
            resultList.add(row);
        }

        return resultList;
    }
}

public class Example {
    public static void main(String[] args) throws SQLException {
        String sql = "select * from UserInfo";
        String mdbPath = "D:\mdb\mdbtest.mdb";
        List<Map<String, Object>> resultList = MdbJdbcUtil.queryForList(mdbPath,sql);
        System.out.println("查詢結(jié)果數(shù)量"+resultList.size());
        for (Map<String, Object> row : resultList) {
            System.out.println(row);
        }
    }
}