如何通过Java-Spring将SQL数据库导出为MDB（MS Access）文件

1. 引入依赖 #

在pom.xml中添加UCanAccess相关依赖（Java操作Access数据库的主流JDBC驱动）：

<dependencies>
    <!-- UCanAccess JDBC驱动 -->
    <dependency>
        <groupId>net.sf.ucanaccess</groupId>
        <artifactId>ucanaccess</artifactId>
        <version>5.0.1</version>
    </dependency>
    <!-- 依赖的辅助库 -->
    <dependency>
        <groupId>commons-lang</groupId>
        <artifactId>commons-lang</artifactId>
        <version>2.6</version>
    </dependency>
    <dependency>
        <groupId>com.healthmarketscience.jackcess</groupId>
        <artifactId>jackcess</artifactId>
        <version>3.0.1</version>
    </dependency>
</dependencies>

2. 服务层实现（生成MDB文件） #

创建服务类，负责执行SQL查询、生成MDB文件并写入数据：

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.io.File;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Service
public class MdbExportService {

    private final JdbcTemplate jdbcTemplate;

    public MdbExportService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public File generateMdbFromQuery(String sql, String tableName) throws Exception {
        // 创建临时MDB文件，JVM退出时自动清理
        File tempMdb = File.createTempFile("export_", ".mdb");
        tempMdb.deleteOnExit();

        // 连接临时MDB文件，指定生成Access 2003格式
        String accessUrl = "jdbc:ucanaccess://" + tempMdb.getAbsolutePath() + ";newdatabaseversion=V2003";
        try (Connection accessConn = DriverManager.getConnection(accessUrl);
             Statement accessStmt = accessConn.createStatement();
             // 执行SQL查询获取源数据结果集
             ResultSet rs = jdbcTemplate.getDataSource().getConnection().createStatement().executeQuery(sql);
             ResultSetMetaData metaData = rs.getMetaData()) {

            // 根据结果集元数据创建MDB表结构
            int columnCount = metaData.getColumnCount();
            List<String> columnDefs = new ArrayList<>();
            for (int i = 1; i <= columnCount; i++) {
                String colName = metaData.getColumnName(i);
                String colType = mapSqlTypeToAccessType(metaData.getColumnType(i));
                columnDefs.add(colName + " " + colType);
            }
            String createTableSql = "CREATE TABLE " + tableName + " (" + String.join(", ", columnDefs) + ")";
            accessStmt.execute(createTableSql);

            // 插入结果集数据到MDB表
            String insertSql = "INSERT INTO " + tableName + " VALUES (" + String.join(", ", new String[columnCount]).replace("null", "?") + ")";
            try (PreparedStatement pstmt = accessConn.prepareStatement(insertSql)) {
                while (rs.next()) {
                    for (int i = 1; i <= columnCount; i++) {
                        pstmt.setObject(i, rs.getObject(i));
                    }
                    pstmt.executeUpdate();
                }
            }
        }

        return tempMdb;
    }

    // 映射SQL数据类型到Access支持的类型
    private String mapSqlTypeToAccessType(int sqlType) {
        return switch (sqlType) {
            case Types.INTEGER, Types.SMALLINT -> "INT";
            case Types.BIGINT -> "LONG";
            case Types.FLOAT, Types.DOUBLE, Types.REAL -> "DOUBLE";
            case Types.DECIMAL, Types.NUMERIC -> "DECIMAL";
            case Types.VARCHAR, Types.CHAR -> "TEXT(255)";
            case Types.LONGVARCHAR -> "MEMO";
            case Types.DATE, Types.TIMESTAMP -> "DATETIME";
            case Types.BOOLEAN -> "YESNO";
            default -> "TEXT(255)";
        };
    }
}

3. 控制器层实现（触发下载） #

创建控制器端点，接收请求后调用服务生成MDB，返回文件流供浏览器下载：

import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

@RestController
public class MdbExportController {

    private final MdbExportService exportService;

    public MdbExportController(MdbExportService exportService) {
        this.exportService = exportService;
    }

    @GetMapping("/export/mdb")
    public ResponseEntity<byte[]> exportToMdb(@RequestParam String sql, @RequestParam String tableName) throws Exception {
        // 生成MDB文件
        File mdbFile = exportService.generateMdbFromQuery(sql, tableName);

        // 读取文件字节
        byte[] fileBytes = new byte[(int) mdbFile.length()];
        try (FileInputStream fis = new FileInputStream(mdbFile)) {
            fis.read(fileBytes);
        } catch (IOException e) {
            throw new RuntimeException("读取MDB文件失败", e);
        }

        // 设置响应头，触发浏览器下载
        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        headers.setContentDispositionFormData("attachment", "data_export.mdb");

        return ResponseEntity.ok()
                .headers(headers)
                .body(fileBytes);
    }
}

关键注意事项 #

• 临时文件处理：使用deleteOnExit()确保临时文件在应用退出时自动清理，也可在返回文件后手动删除。
• 数据类型映射：根据实际使用的SQL数据库类型，调整mapSqlTypeToAccessType方法的映射逻辑，避免类型不兼容。
• UCanAccess版本：选择稳定版本，如需生成ACCDB格式文件，可去掉连接URL中的;newdatabaseversion=V2003参数。

内容的提问来源于stack exchange，提问作者StzEZ