如何用Java高效执行数千条MySQL SELECT查询并分文件存储结果？

针对你每日要运行数千条SELECT查询、每条结果单独存文件的需求，结合你提到的「连接成本高」「调用Shell效果不好」的痛点，我整理了几个实用的方案：

1. 核心优化：用连接池复用数据库连接 #

这是解决「每个查询开连接成本高」的关键——连接池会预先创建并维护一批数据库连接，每次查询直接复用现有连接，用完放回池里，彻底避免频繁创建/销毁连接的开销。

推荐用轻量高效的HikariCP（Spring Boot默认的连接池），或者阿里的Druid。下面是一个简单的可落地示例：

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.util.ArrayList;
import java.util.List;

public class QueryProcessor {
    private static HikariDataSource dataSource;

    // 连接池只初始化一次，放在静态代码块里
    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://<host>:3306/<schema>?useUnicode=true&characterEncoding=utf8");
        config.setUsername("<usr_name>");
        config.setPassword("<password>");
        config.setMaximumPoolSize(10); // 根据数据库最大连接数调整，别设太满
        config.setConnectionTimeout(30000);
        dataSource = new HikariDataSource(config);
    }

    // 执行单条查询并保存结果到文件
    public static void executeQueryAndSave(String query, String outputFilePath) {
        // 用try-with-resources自动关闭资源，不用手动写close()
        try (Connection conn = dataSource.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(query);
             BufferedWriter writer = new BufferedWriter(new FileWriter(outputFilePath))) {

            // 按行写入结果，和你原来Shell脚本的-N参数一致（不输出表头）
            while (rs.next()) {
                String cartId = rs.getString(1);
                String emptyStr = rs.getString(2);
                String userId = rs.getString(3);
                writer.write(String.join("\t", cartId, emptyStr, userId)); // 用制表符分隔，和mysql客户端输出格式对齐
                writer.newLine();
            }
        } catch (Exception e) {
            e.printStackTrace();
            // 这里可以加日志记录、失败重试逻辑，比如某条查询失败了标记下来后续处理
        }
    }

    public static void main(String[] args) {
        // 模拟读取所有查询任务，实际你可以从配置文件/数据库/本地文件加载
        List<QueryTask> tasks = getQueryTasks();
        for (QueryTask task : tasks) {
            executeQueryAndSave(task.getQuery(), task.getOutputPath());
        }
        // 任务完成后关闭连接池
        dataSource.close();
    }

    // 封装查询语句和输出路径的内部类
    static class QueryTask {
        private String query;
        private String outputPath;

        public QueryTask(String query, String outputPath) {
            this.query = query;
            this.outputPath = outputPath;
        }

        public String getQuery() { return query; }
        public String getOutputPath() { return outputPath; }
    }

    // 模拟获取任务列表的方法
    private static List<QueryTask> getQueryTasks() {
        List<QueryTask> tasks = new ArrayList<>();
        tasks.add(new QueryTask(
            "select (CART_ID), '', (USER_ID) from shopping_cart where DATE_MODIFIED >= '2018-01-08 00:00:00' and DATE_MODIFIED < '2018-01-10 00:00:00'",
            "/Users/selumalai/Desktop/520_250_493991738_shopping_cart_2018_01_12_00_00_00.dat"
        ));
        // 这里可以添加更多查询任务...
        return tasks;
    }
}

2. 进一步提速：预编译相似查询+批量处理 #

如果你的上千条查询结构相似（只是日期、筛选参数不同），可以用PreparedStatement预编译SQL模板，替换参数后执行，减少数据库的SQL解析开销：

// 预编译通用SQL模板
String sqlTemplate = "select (CART_ID), '', (USER_ID) from shopping_cart where DATE_MODIFIED >= ? and DATE_MODIFIED < ?";

try (Connection conn = dataSource.getConnection();
     PreparedStatement pstmt = conn.prepareStatement(sqlTemplate)) {

    // 循环处理不同的日期范围
    for (DateRange range : getDateRanges()) {
        pstmt.setString(1, range.getStartDate());
        pstmt.setString(2, range.getEndDate());
        // 执行查询并写入文件
        try (ResultSet rs = pstmt.executeQuery();
             BufferedWriter writer = new BufferedWriter(new FileWriter(range.getOutputPath()))) {
            while (rs.next()) {
                writer.write(String.join("\t", rs.getString(1), rs.getString(2), rs.getString(3)));
                writer.newLine();
            }
        }
    }
} catch (Exception e) {
    e.printStackTrace();
}

// 日期范围封装类
class DateRange {
    private String startDate;
    private String endDate;
    private String outputPath;

    // 构造器、getter方法...
}

3. 并行处理：用线程池加快任务完成速度 #

如果想更快跑完所有查询，可以用Java线程池并行执行任务，但要注意：

• 线程池大小不要超过连接池的最大连接数，避免连接不够用
• 控制并行度，别给数据库造成太大压力（比如对应你原来的5个Shell脚本，线程池设为5就很合适）

示例：

import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class AsyncQueryProcessor {
    private static final ExecutorService executor = Executors.newFixedThreadPool(5); // 对应原5个Shell脚本的并行度

    public static void main(String[] args) {
        List<QueryTask> tasks = getQueryTasks();
        for (QueryTask task : tasks) {
            executor.submit(() -> executeQueryAndSave(task.getQuery(), task.getOutputPath()));
        }
        executor.shutdown(); // 任务提交完后关闭线程池
    }
}

4. 过渡方案：优化Java调用Shell的方式 #

如果暂时不想完全重写Java逻辑，可以优化Shell调用：不要为每个查询启动一个mysql进程，而是用一个mysql客户端批量执行所有查询，同时在结果中加入文件标记，Java读取输出后分割成单独文件。

比如Shell脚本可以这么写：

mysql -u <usr_name> -p<password> -h <host> -P 3306 -D <schema> -C -N --default-character-set=utf8 << EOF
-- 标记第一个文件开始
SELECT '===FILE_START:/path/to/file1.dat===';
select (CART_ID), '', (USER_ID) from shopping_cart where ...;
-- 标记文件结束
SELECT '===FILE_END===';

-- 第二个查询
SELECT '===FILE_START:/path/to/file2.dat===';
select ...;
SELECT '===FILE_END===';
EOF

然后Java用ProcessBuilder调用这个脚本，读取输出流，根据===FILE_START===和===FILE_END===标记把内容写入对应的文件。这个方案效率不如纯Java，但可以作为过渡。

关键注意事项 #

• 设置Fetch Size：如果查询结果量大，记得加stmt.setFetchSize(1000);（或合适的数值），避免一次性把所有结果加载到内存导致OOM
• 字符集一致性：JDBC URL里务必加上useUnicode=true&characterEncoding=utf8，和你原来的Shell脚本保持一致，避免乱码
• 异常处理：添加日志记录、失败重试逻辑，确保每日任务稳定执行，比如某条查询失败了可以标记下来后续人工核查
• 资源关闭：一定要用try-with-resources语法，自动关闭连接、结果集、文件流，避免资源泄漏

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