Access库工具类

package com.jiyang.common.utils;

import com.jiyang.common.constant.ErrorMessage;
import com.jiyang.common.exception.CustomException;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * access库工具类
 */
public class AccessUtil {

    public static void main(String[] args) throws SQLException {
        int offset = 0;
        getResultData("bdcaj_k", 10, offset, "C:\\Users\\Administrator\\Desktop\\76.mdb");
        System.out.println(offset);
    }

    /**
     * 连接Access库
     *
     * @return
     */
    public static Connection getAccessConnection(String mdbFilePath) {

        try {
            Class.forName(ACCESS_DRIVER_CLASS);
            Connection conn = DriverManager.getConnection(ACCESS_ADDRESS_PREFIX + mdbFilePath);
            return conn;
        } catch (Exception e1) {
            e1.printStackTrace();
            throw new CustomException(ErrorMessage.ACCESS_CONNECT_ERROR);
        }
    }


    /**
     * 获取查询结果集
     */
    public static List<HashMap<String, Object>> getResultData(String tableName, int offset, int size, String mdbFilePath) throws SQLException {
        String sql = "select * from " + tableName + " where id > " + offset + " order by ID " + " limit " + size;
        Connection connection = getAccessConnection(mdbFilePath);
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet rs = preparedStatement.executeQuery();
        ResultSetMetaData resultSetMetaData = rs.getMetaData();//取数据库的列名
        int numberOfColumns = resultSetMetaData.getColumnCount();
        List<HashMap<String, Object>> dataList = new ArrayList<>();
        while (rs.next()) {
            HashMap<String, Object> rsTree = new HashMap<>();
            for (int r = 1; r < numberOfColumns + 1; r++) {
                rsTree.put(CATALOG_NO.equals(resultSetMetaData.getColumnName(r)) ? CATALOG_NAME : resultSetMetaData.getColumnName(r), rs.getObject(r));
            }
            dataList.add(rsTree);
        }
        closeDB(rs, preparedStatement, connection);
        return dataList;
    }

    /**
     * 获取表列名集合
     */
    public static List<String> listTableColumns(String sql, String mdbFilePath) throws SQLException {
        Connection connection = getAccessConnection(mdbFilePath);
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet rs = preparedStatement.executeQuery();
        ResultSetMetaData resultSetMetaData = rs.getMetaData();//取数据库的列名
        int numberOfColumns = resultSetMetaData.getColumnCount();
        List<String> columns = new ArrayList<>();
        for (int i = 1; i < numberOfColumns + 1; i++) {
            columns.add(resultSetMetaData.getColumnName(i));
        }
        closeDB(rs, preparedStatement, connection);
        return columns;
    }


    /**
     * 关闭连接
     *
     * @param rs
     * @param preparedStatement
     * @param connection
     * @throws SQLException
     */

    private static void closeDB(ResultSet rs, PreparedStatement preparedStatement, Connection connection) throws SQLException {
        if (rs != null) {
            rs.close();
        }
        if (preparedStatement != null) {
            preparedStatement.close();
        }
        if (connection != null) {
            connection.close();
        }

    }

}