当前 SWMS 和 WMS 系统在导出数十万级数据时存在性能瓶颈，导致导出失败频发，建议优化导出机制。

解决方案

1、初期简易版

核心功能开发预计耗时约3天，后续新增报表可通过配置快速实现，仅需少量开发支持。

2、多数据源具体实现步骤

使用 Spring Boot + 多数据源配置

步骤 1：添加依赖

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <dependency>
        <groupId>com.microsoft.sqlserver</groupId>
        <artifactId>mssql-jdbc</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
    </dependency>
</dependencies>

spring:
  datasource:
    mysql:
      url: jdbc:mysql://localhost:3306/db_mysql?useSSL=false&serverTimezone=UTC
      username: root
      password: root
      driver-class-name: com.mysql.cj.jdbc.Driver

    postgres:
      url: jdbc:postgresql://localhost:5432/db_postgres
      username: postgres
      password: postgres
      driver-class-name: org.postgresql.Driver

    sqlserver:
      url: jdbc:sqlserver://localhost:1433;databaseName=db_sqlserver;encrypt=false;trustServerCertificate=true;
      username: sa
      password: YourStrong@Passw0rd
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver

# 可选：启用 Hikari 连接池（Spring Boot 默认）
spring.datasource.hikari.maximum-pool-size: 10

@Configuration
public class DataSourceConfig {

    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.mysql")
    public DataSource mysqlDataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.postgres")
    public DataSource postgresDataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.sqlserver")
    public DataSource sqlServerDataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }
}

@Configuration
public class JdbcTemplateConfig {

    @Autowired
    @Qualifier("mysqlDataSource")
    private DataSource mysqlDataSource;

    @Autowired
    @Qualifier("postgresDataSource")
    private DataSource postgresDataSource;

    @Autowired
    @Qualifier("sqlServerDataSource")
    private DataSource sqlServerDataSource;

    @Bean
    @Primary
    public JdbcTemplate mysqlJdbcTemplate() {
        return new JdbcTemplate(mysqlDataSource);
    }

    @Bean
    public JdbcTemplate postgresJdbcTemplate() {
        return new JdbcTemplate(postgresDataSource);
    }

    @Bean
    public JdbcTemplate sqlServerJdbcTemplate() {
        return new JdbcTemplate(sqlServerDataSource);
    }
}

@Service
public class MultiDatabaseService {

    private final JdbcTemplate mysqlTemplate;
    private final JdbcTemplate postgresTemplate;
    private final JdbcTemplate sqlServerTemplate;

    public MultiDatabaseService(
            @Qualifier("mysqlJdbcTemplate") JdbcTemplate mysqlTemplate,
            @Qualifier("postgresJdbcTemplate") JdbcTemplate postgresTemplate,
            @Qualifier("sqlServerJdbcTemplate") JdbcTemplate sqlServerTemplate) {
        this.mysqlTemplate = mysqlTemplate;
        this.postgresTemplate = postgresTemplate;
        this.sqlServerTemplate = sqlServerTemplate;
    }

    // MySQL
    public List<User> getUsersFromMysql() {
        return mysqlTemplate.query(
            "SELECT id, name, email FROM users",
            (rs, rowNum) -> new User(rs.getLong("id"), rs.getString("name"), rs.getString("email"))
        );
    }

    // PostgreSQL
    public void saveOrderToPostgres(Order order) {
        postgresTemplate.update(
            "INSERT INTO orders (order_id, amount, created_at) VALUES (?, ?, ?)",
            order.getOrderId(), order.getAmount(), order.getCreatedAt()
        );
    }

    // SQL Server
    public List<Customer> getCustomersFromSqlServer() {
        return sqlServerTemplate.query(
            "SELECT CustomerID, CompanyName, ContactName FROM Customers",
            (rs, rowNum) -> new Customer(
                rs.getInt("CustomerID"),
                rs.getString("CompanyName"),
                rs.getString("ContactName")
            )
        );
    }
}

CSV例子：

package io.renren.common.service.impl;

import java.io.*;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.nio.file.StandardOpenOption;
import java.util.*;

public class DynamicCsvExport {

    // 临时 CSV 文件路径
    private static final String OUTPUT_FILE = "E://dynamic_report.csv";

    public static void main(String[] args) throws Exception {
        // 删除旧文件
        Files.deleteIfExists(Paths.get(OUTPUT_FILE));

        // 定义动态列名（可从配置、数据库获取）
        List<String> headers = Arrays.asList("用户ID", "姓名", "金额", "状态", "注册时间");

        // 第一次写入：包含表头
        writeCsvWithHeaders(OUTPUT_FILE, headers);

        // 模拟分批写入数据（如从数据库分页查询）
        for (int i = 0; i < 3; i++) {
            List<List<Object>> pageData = generateDataPage(i * 1000 + 1, (i + 1) * 1000);
            appendCsvData(OUTPUT_FILE, pageData);
            System.out.println("✅ 已追加写入第 " + (i + 1) + " 批数据");
        }

        System.out.println("🎉 CSV 报表已生成：" + OUTPUT_FILE);
    }

    /**
     * 第一次写入：包含表头（UTF-8 with BOM，确保 Excel 不乱码）
     */
    public static void writeCsvWithHeaders(String filePath, List<String> headers) throws IOException {
        Path path = Paths.get(filePath);

        // 写入 BOM（\uFEFF）以确保 Excel 正确识别 UTF-8
        try (BufferedWriter writer = Files.newBufferedWriter(path, StandardCharsets.UTF_8, StandardOpenOption.CREATE, StandardOpenOption.WRITE)) {
            writer.write("\uFEFF"); // UTF-8 BOM
            writer.write(String.join(",", escapeCsvFields(headers)));
            writer.newLine();
        }
    }

    /**
     * 追加写入数据（不包含表头）
     */
    public static void appendCsvData(String filePath, List<List<Object>> data) throws IOException {
        Path path = Paths.get(filePath);

        try (BufferedWriter writer = Files.newBufferedWriter(path, StandardCharsets.UTF_8, StandardOpenOption.WRITE, StandardOpenOption.APPEND)) {
            for (List<Object> row : data) {
                List<String> escapedRow = escapeCsvFields(convertToStringList(row));
                writer.write(String.join(",", escapedRow));
                writer.newLine();
            }
        }
    }

    /**
     * 转换 Object 列表为 String 列表
     */
    private static List<String> convertToStringList(List<Object> row) {
        List<String> result = new ArrayList<>();
        for (Object cell : row) {
            if (cell == null) {
                result.add("");
            } else if (cell instanceof Date) {
                result.add(new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cell));
            } else {
                result.add(cell.toString());
            }
        }
        return result;
    }

    /**
     * 转义 CSV 特殊字符：包含逗号、引号、换行符的字段需用双引号包围
     */
    private static List<String> escapeCsvFields(List<String> fields) {
        List<String> escaped = new ArrayList<>();
        for (String field : fields) {
            if (field == null) {
                escaped.add("");
            } else if (field.contains(",") || field.contains("\"") || field.contains("\n") || field.contains("\r")) {
                // 转义双引号：""，并用双引号包围
                escaped.add("\"" + field.replace("\"", "\"\"") + "\"");
            } else {
                escaped.add(field);
            }
        }
        return escaped;
    }

    /**
     * 模拟生成一页数据
     */
    private static List<List<Object>> generateDataPage(int startId, int endId) {
        List<List<Object>> data = new ArrayList<>();
        Random random = new Random();
        for (int i = startId; i <= endId; i++) {
            List<Object> row = Arrays.asList(
                    i,
                    "用户_" + i,
                    String.format("%.2f", random.nextDouble() * 1000),
                    random.nextBoolean() ? "启用" : "禁用",
                    new Date()
            );
            data.add(row);
        }
        return data;
    }
}

package io.renren.common.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;

import java.io.File;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

public class EasyExcelTest {

    public static void main(String[] args) {
        String filePath = "E://dynamic_report.xlsx";
        File file = new File(filePath);

        try (ExcelWriter excelWriter = EasyExcel.write(file).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet("动态数据").build();

            // 表头
            List<List<String>> headers = Arrays.asList(
//                    Arrays.asList("ID", "姓名", "状态", "金额", "时间")
                    Arrays.asList("ID"),
                    Arrays.asList("姓名"),
                    Arrays.asList("状态"),
                    Arrays.asList("金额"),
                    Arrays.asList("时间")
            );

            // 第一批数据
            List<List<Object>> data1 = Arrays.asList(
                    Arrays.asList(1L, "张三", "启用", "999.99", new Date()),
                    Arrays.asList(2L, "李四", "禁用", "888.88", new Date())
            );

            excelWriter.write(data1, writeSheet, EasyExcel.writerTable(0).head(headers).build());

            // 追加第二批数据（不带表头）
            List<List<Object>> data2 = Arrays.asList(
                    Arrays.asList(3L, "王五", "启用", "777.77", new Date())
            );
            excelWriter.write(data2, writeSheet, EasyExcel.writerTable(1).needHead(false).build());
        }

        System.out.println("✅ 动态报表已生成：" + filePath);
    }
}