JDBC批处理Select语句

JDBC 批处理 Select 语句

在网络开销中，成本最高的部分往往是客户端与服务器之间的往返请求（Round-Trips）。在 JDBC 操作中，这种情况尤为明显。对于数据插入、更新或删除操作，我们可以使用 executeBatch() 方法来减少数据库调用次数，例如：

Statement pstmt = conn.createStatement();
pstmt.addBatch("insert into settings values(3,'liu')");
pstmt.addBatch("insert into settings values(4,'zhi')");
pstmt.addBatch("insert into settings values(5,'jun')");
pstmt.executeBatch();

但不幸的是，对于批量查询（Batch Select），JDBC 并没有内建（built-in）的支持方法。此外，JDBC 在执行批处理时不允许包含 SELECT 语句。例如以下代码：

Statement pstmt = conn.createStatement();
pstmt.addBatch("select * from settings");
pstmt.executeBatch();

将会抛出异常：

Exception in thread "main" java.sql.BatchUpdateException: Can not issue SELECT via executeUpdate().
    at com.mysql.jdbc.Statement.executeBatch(Statement.java:961)
    at test.SelectBatchTest.test2(SelectBatchTest.java:49)
    at test.SelectBatchTest.main(SelectBatchTest.java:12)

假设你想从一系列指定的 ID 列表中获取对应的名字，逻辑上我们可能期望这样做：

PreparedStatement stmt = conn.prepareStatement(
    "select id, name from users where id in (?)");
stmt.setString(1, "1,2,3"); // 错误示范

但这样做并不能得到预期的结果。JDBC 只允许你用单个字面值来替换 ? 占位符。JDBC 之所以这样设计是有必要的：如果 SQL 结构自身可以随意改变，JDBC 驱动就无法预编译（Pre-compile）SQL 语句；另一方面，严格限制参数类型也能有效防止 SQL 注入攻击。

针对批量查询的需求，主要有四种可替代的实现方法可供选择：

分别对每个 ID 做查询
一个查询做完所有事
使用存储过程
分批处理

方法一：分别对每个 ID 做查询

假设有 100 个 ID，那么就需要进行 100 次数据库调用：

PreparedStatement stmt = conn.prepareStatement(
    "select id, name from users where id = ?");
for (int i = 0; i < 100; i++) {
    stmt.setInt(1, ids[i]);  // 设置具体的参数值
 
    // 执行语句并获取结果
}

这种方法写起来非常简单，但是性能非常慢，数据库往返需要处理 100 次，网络开销巨大。

方法二：一个查询完成所有事

在运行时，你可以使用循环来构建如下 SQL 语句：

PreparedStatement stmt = conn.prepareStatement(
    "select id, name from users where id in (?, ?, ?)");
stmt.setInt(1, id1);
stmt.setInt(2, id2);
stmt.setInt(3, id3);

这种方案从代码复杂度来看，相比第一种方法算是第二简单的。它解决了来回多次请求数据库的问题。但是，如果每次请求参数的个数不一样，预处理语句（PreparedStatement）就必须重新编译。由于每次 SQL 字面值不匹配（例如分别用 10 个 ID、3 个 ID、100 个 ID），这样会在数据库缓存中产生三个不同的预处理语句。

除了重新编译预处理语句之外，先前缓存池中的预处理语句将被移除（受限于缓存池大小），进而导致后续重复查询时再次重新编译。最后，这种查询方式在内存溢出或触发磁盘分页操作时，查询会占用很长时间。

该方案的另一种变体就是在 SQL 语句中硬编码：

PreparedStatement stmt = conn.prepareStatement(
    "select id, name from users where id in (1, 2, 3)");

这种方式甚至更差，没有任何机会对 SQL 语句进行重用。至少使用 ? 占位符还可以对使用相同数量参数的 SQL 语句进行重用。

还有一种尝试是使用分号间隔多个查询：

PreparedStatement stmt = conn.prepareStatement(
   "select id, name from users where id in (?) ; "  
   + "select id, name from users where id in (?); "
   + "select id, name from users where id in (?)");
stmt.setInt(1, id1);
stmt.setInt(2, id2);
stmt.setInt(3, id3);

这种方法的优点就是每次查询模版语句都一样，数据库不需要每次计算执行路径。然而，从数据库驱动的角度来说，SQL 每次都不一样，预处理语句每次必须预处理并保存在缓存中。而且，不是所有数据库系统都支持分号间隔的多个 SQL 语句执行。

方法三：使用存储过程

存储过程执行在数据库系统内部，因此它可以做很多查询而不需要太多网络负载，存储过程可以收集所有结果一次性返回。这是一种速度很快的解决方案。

但是，它对数据库的依赖比较强，不能随意地切换数据库系统，否则需要重写存储过程，而且需要你分离应用服务器与数据库服务器之间的逻辑。如果应用架构已经使用了存储过程，无疑这是最佳方案。

方法四：分批处理

批量查询是方案一和方案二的折衷选择。它预先确定一批查询参数的常量，然后用这些参数构建一批查询。因为这只会涉及到有限个查询，所以它有预处理语句的优势（预编译不会与缓存中的预处理发生碰撞）。批处理多个值在相同的查询保留了服务器来回请求最小化的优势。最后，你可以通过控制批处理的上限来避免大查询的内存问题。

如果你有很关键的查询对性能方面有要求，又不想用存储过程，那么这是一种很好的解决办法。现在我们通过一个例子说明：

public static final int SINGLE_BATCH = 1;
public static final int SMALL_BATCH = 4;
public static final int MEDIUM_BATCH = 11;
public static final int LARGE_BATCH = 51;

第一件要做的事是你要衡量有多少批处理以及每个批处理的大小。（注意：在真实的代码中，这些值应该写在一个配置文件中而不是采取硬编码的形式，也就是说，你可以在运行时试验并改变批处理的大小）。不管真正的批处理大小是多大，你总需要一个单个的批处理——大小为 1 的批处理（SINGLE_BATCH）。这样如果有人请求的就是一个值，或者在一个很大的查询中最后有遗留下来的单个值，都能派上用场。

对于批处理的大小，使用素数会更好些。换句话说，大小不应该可以相互整除或者被相同的数整除。请求数的最大值将有最少的服务器往返。批处理的大小的数量和真正的大小是基于配置变化的。需要注意的是：大的批处理大小不应该太大，否则你将遇到内存麻烦；同时最小批处理的大小应该很小，你可能会使用这个来做很多次的查询。

while (totalNumberOfValuesLeftToBatch > 0) {
    // 按如下方式重复操作直到退出循环
    int batchSize = SINGLE_BATCH;
    if (totalNumberOfValuesLeftToBatch >= LARGE_BATCH) {
        batchSize = LARGE_BATCH;
    } else if (totalNumberOfValuesLeftToBatch >= MEDIUM_BATCH) {
        batchSize = MEDIUM_BATCH;
    } else if (totalNumberOfValuesLeftToBatch >= SMALL_BATCH) {
        batchSize = SMALL_BATCH;
    }
    totalNumberOfValuesLeftToBatch -= batchSize;
    
    // 构建查询逻辑...
}

这种方案在这里是查找到最大的批处理大小，可能这个最大值比我们实际要查询的值稍大。举例说明：假设查询有 75 个参数，那么首先选择 51 个元素（LARGE_BATCH），现在还剩 24 个待查询；然后接着用 11 个元素的查询（MEDIUM_BATCH）。现在还有 13 个值，因为仍然大于 11，再做一次 11 个元素的查询，现在只剩下 2 个值，它少于那个最小的批处理 4（SMALL_BATCH），所以做两次单查询。总共 5 次往返用了 3 次预处理在缓存中。这是一个很重要的改进，比单独地做 75 次单查询效率更高。

接下来构建具体的预处理语句：

StringBuilder inClause = new StringBuilder();
boolean firstValue = true;
for (int i = 0; i < batchSize; i++) {
    if (!firstValue) {
        inClause.append(',');
    } else {
        firstValue = false;
    }
    inClause.append('?');
}

PreparedStatement stmt = conn.prepareStatement(
    "select id, name from users where id in (" + inClause.toString() + ')');

现在已经构建了一个真实的预处理语句，由于一直用相同的方式构建查询，驱动会注意到 SQL 是相同的。（注意：如果你还没有用 Java 5，使用 StringBuffer 替换 StringBuilder 才能正常编译）。返回 ID 很重要，这样有利于查找哪个名字对应哪个 ID。

设置合适的值数量去查询，包括其他搜索条件查询：

for (int i = 0; i < batchSize; i++) {
    stmt.setInt(i + 1, values[i]);  // 设置具体的参数值
}

仅仅只要把这些参数在占据参数之后，在这种情况你可以最终当前的索引。从这点来看，你仅仅只是执行查询返回了结果。在第一次尝试的时候，你应该关注一下性能的提升，根据具体情况调整优化批处理的大小（batch size）。

正如那句名言所说：“过早的优化是万恶之源”，批处理应该是用于解决性能问题。

说明：本文涉及的部分 Java 语法（如 StringBuilder 与 StringBuffer 的区别）基于较早期的 Java 版本（Java 5 之前）。在现代 Java 开发中，StringBuilder 已是标准选择。此外，具体的异常信息可能因 JDBC 驱动版本不同而略有差异，但核心原理通用。

本文地址：https://1diff.fun/archives/jdbc-pi-chu-li-select-yu-ju.html

如果对本文有什么问题或疑问都可以在评论区留言，我看到后会尽量解答。

JDBC批处理Select语句