如何使用JDBI批量读取数据？解决大表查询超时问题

我需要读取某张包含400万行数据的表，每行有25列。我设置了fetch size为1_000来避免JVM负载过高，但查询仍触发超时异常。请问JDBI是否提供可批量读取数据的“cursor”来避免语句超时？还有其他JDBI方案解决该超时问题吗？

代码示例 #

var handle = jdbi.open()
handle
  .createQuery("SELECT * FROM TEST_TABLE")
  .setFetchSize(1_000)
  .mapToMap()

异常信息 #

query execution canceled due to statement timeout [statement:"SELECT * FROM TEST_TABLE", arguments:{positional:{}, named:{}, finder:[]}]
    at org.jdbi.v3.core.statement.SqlStatement.internalExecute(SqlStatement.java:1796)
    at org.jdbi.v3.core.result.ResultProducers.lambda$getResultSet$2(ResultProducers.java:64)
    at org.jdbi.v3.core.result.ResultIterable.lambda$of$0(ResultIterable.java:57)
    at org.jdbi.v3.core.result.ResultIterable.iterator(ResultIterable.java:43)

1. 使用JDBI游标（Cursor）分批读取 #

JDBI支持通过useCursor方法直接操作JDBC游标，逐批获取数据，避免数据库端因查询耗时过长触发超时，同时严格控制内存占用。

示例代码：

try (var handle = jdbi.open()) {
    handle.createQuery("SELECT * FROM TEST_TABLE")
          .setFetchSize(1_000)
          .useCursor(cursor -> {
              while (cursor.hasNext()) {
                  Map<String, Object> row = cursor.next();
                  // 处理单条数据逻辑
              }
          });
}

该方式会让JDBC驱动以游标模式工作，每次仅从数据库拉取fetchSize指定的行数，数据库不会一次性执行全量查询，从根源上避免超时。

2. 延长查询超时时间 #

如果数据库允许，可通过setTimeout方法延长查询的超时阈值，给分批读取足够的时间：

try (var handle = jdbi.open()) {
    handle.createQuery("SELECT * FROM TEST_TABLE")
          .setFetchSize(1_000)
          .setTimeout(300) // 单位：秒，根据实际场景调整
          .mapToMap()
          .forEach(row -> {
              // 处理数据逻辑
          });
}

3. 拆分成分页查询 #

将全量查询拆分为多个分页查询，通过数据库的分页语法（如LIMIT/OFFSET、范围查询）分批获取数据：

基于OFFSET的分页（适合中小规模数据） #

int batchSize = 1_000;
// 先获取总条数
int totalRows = handle.createQuery("SELECT COUNT(*) FROM TEST_TABLE")
                      .mapTo(Integer.class)
                      .one();

for (int offset = 0; offset < totalRows; offset += batchSize) {
    List<Map<String, Object>> batch = handle.createQuery("SELECT * FROM TEST_TABLE LIMIT ? OFFSET ?")
                                            .bind(0, batchSize)
                                            .bind(1, offset)
                                            .mapToMap()
                                            .list();
    // 处理当前批次数据
}

基于主键范围的分页（适合超大规模数据） #

如果表有自增主键或有序字段，用范围查询替代OFFSET可避免大偏移量导致的性能下降：

int batchSize = 1_000;
Long lastId = 0L;

while (true) {
    List<Map<String, Object>> batch = handle.createQuery("SELECT * FROM TEST_TABLE WHERE id > ? ORDER BY id LIMIT ?")
                                            .bind(0, lastId)
                                            .bind(1, batchSize)
                                            .mapToMap()
                                            .list();
    if (batch.isEmpty()) {
        break;
    }
    // 处理当前批次数据
    lastId = (Long) batch.get(batch.size() - 1).get("id");
}

4. 调整JDBC驱动参数 #

部分JDBC驱动需要额外配置才能让fetchSize生效，比如PostgreSQL驱动需在连接URL中添加defaultRowFetchSize=1000，确保游标模式被正确启用。

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