JDBC 批量调用数据库 SQL, 函数与存储过程

Apr 26, 2024 --- · 7 min read · PostgreSQL ·

Share on:

继续上一篇数据库相关操作的话题，在有大量的数据操作时(如增删改，甚至调用函数或存储过程)，我们应该尽可能的采用批量化操作(先摆下结论，后面我们会看到原由)。想像一下我们要向数据库插入 10 万条记录，如果逐条插入的话，客户端与数据库之间将会有 10 万网络请求响应来回; 而假如以 1000 条记录为一个 batch, 客户端与数据库之间的网络请求响应次数将缩小到 100。业务数据的内容总量未变，但 Batch 操作除了可重用预编译的 Statement 外还, 可避免每次请求中重复的元数据，所以从 100,000 到 100 的缩减在时效上的表现是非常可观的，有时就是 60 分钟与 1 分钟的区别(在最后面测试结果显示这一差异更为恐怖)。

当然, JDBC 的批处理功能具体还要相应驱动的支持，通过数据库连接的 conn.getMetaData().supportsBatchUpdates() 可探知是否支持批量操作。

API 方面, 在 Statement 接口中定义了如下 batch 相关的操作方法

void addBatch(String sql): 将显式的 SQL 语句编入到当前 Batch 中
void clearBatch(): 清除当前 Batch 列表，以便于建立新的 Batch
int[] executeBatch(): 执行当前 Batch 列表中的语句，返回每条语句受影响行数组成的数组。0 可能表示执行语句无法确知受影响的行
long[] executeLargeBatch(): 当 Batch 中语句受影响行数可能会超过整数最大值时用这个

另外在 Statement 的子接口 PreparedStatement 中定义了

void addBatch(): 当动态 SQL 语句，函数和存储过程也支持 Batch 操作

而 CallableStatement 是 PreparedStatement 的子接口，所以数据库函数和存储过程也能 Batch 操作

执行 Batch 时只有 executeBatch() 或 executeLargeBatch()，它们只用来更新，没有像 executeQuery() 这样的方法或获得查询数据。如果简单的用 Statement 同时执行多条语句的话，可用分号对多条语句进行分隔，然后用 getMoreResults() 去遍历 ResultSet.

下面开始各种演示

我们要使用的数据库是 PostgreSQL, 预先创建一个表并插入几条记录

1create table users(
2   id serial,
3   name varchar(32),
4   age int
5);
6
7
8insert into users(name, age) values ('Scott', 20), ('Tiger', 18), ('Rose', 5);

Statement 执行多条语句获得多个 ResultSet

 1    private static void testReturnMultipleResultSets() throws SQLException {
 2        Connection conn = dataSource.getConnection();
 3        Statement stmt = conn.createStatement();
 4
 5        stmt.execute("select * from users; select * from users where id=1");
 6
 7        System.out.println("--- first result ---");
 8        ResultSet rs = stmt.getResultSet();
 9        while (rs.next()) {
10            System.out.println(rs.getString("name"));
11        }
12        System.out.println("--- more results ---");
13        while (stmt.getMoreResults()) {
14            rs = stmt.getResultSet();
15            while (rs.next()) {
16                System.out.println(rs.getString("name"));
17            }
18        }
19    }

注：连接从一个连接池中，另外本文所有代码中未处理资源的关闭，实际应用中必须正确的关闭数据 ResultSet, Statement, 和 Connection 资源。这里只是为了演示同时执行分号隔开的多条语句，当然，同质的 SQL 语句写成 UNION 查询就行了。

以上代码执行得到了两个 ResultSet, 输出如下

--- first result ---
Scott
Tiger
Rose
--- more results ---
Scott

用 Statement 也可以批量执行像 update/delete 等操作，但如何获得 ResultSet 就像多加验证了。例如我们可以执行

1stmt.execute("delete from users where id=1; update users set age=1 where id=2; select * from users");

addBatch(String sql), executeBatch() 的批量操作

接下来开始体验 Statement 的 addBatch(String sql), executeBatch() 那样的批量操作

 1    private static void executeBatch() throws SQLException {
 2        Connection conn = dataSource.getConnection();
 3
 4        Statement stmt = conn.createStatement();
 5        for (int i = 1; i <= 5; i++) {
 6           stmt.addBatch("insert into users(name, age) values('%s', 10)".formatted("user" + i));
 7        }
 8        int[] affected = stmt.executeBatch();
 9        System.out.println(Arrays.toString(affected));
10    }

输出为每条语句所影响的行数

[1, 1, 1, 1, 1]

若要重用 Statement 进行多次 Batch 操作的代码是

 1stmt.addBatch(sql1);
 2stmt.addBatch(sql2);
 3......
 4stmt.executeBatch();
 5
 6stmt.clearBatch();
 7
 8stmt.addBatch(sql3);
 9stmt.addBatch(sql4);
10......
11stmt.executeBatch();

Batch 和非 Batch 时，数据库客户端与服务端间的交互我们可以观察通讯数据包。

如与执行单条语句时

1stmt.execute("insert into users(name, age) values('user1', 10)");

的网络数据报文对比就是

执行单条语句：

executeBatch() 执行多条语句时

addBatch(String sql) 只是减少了网络请求次数，但在报文中的 SQL 语句仍然是简单重复。如果用 PreparedStatement 的方式进行 Batch 将只会传送一次 SQL 语句, 再往下看

更高效的 PreparedStatement 批量操作

如果使用 PreparedStatement 则只需要发送 SQL 语句一次，然后伴随着每次执行需要不同的参数组，

 1    private static void executePrepareStatementBatch() throws SQLException {
 2        Connection conn = dataSource.getConnection();
 3
 4        PreparedStatement pstmt = conn.prepareStatement("insert into users(name, age) values(?, ?)");
 5        for (int i = 1; i <= 5; i++) {
 6            pstmt.setString(1, "user" + i);
 7            pstmt.setInt(2, 10);
 8            pstmt.addBatch();
 9        }
10        int[] affected = pstmt.executeBatch();
11        System.out.println(Arrays.toString(affected));
12    }

执行的输出自然还是一样的

[1, 1, 1, 1, 1]

此时我们再来看看数据报文，有两个基本的报文

发送 SQL 语句到数据库，将得到一个预编译语句的句柄

最后发送所有批量操作时的 Statement ID, 这里是 (S_1) 和参数组，如 (user1, 10), (user2, 10), (user3, 10), (user4, 10), (user5, 10)

所以当我们使用 JDBC 时希望能采用 Batch 操作改善性能时十分有必要考虑 PreparedStatement 的 Batch 操作，而非 addBatch(String sql) 的简单堆叠 -- 从后面的测试来看这两种方法性能上差异产不大。

Batch 操作调用数据函数与存储过程

如果是以 select func1(?, ?) 的方式调用函数，那和普通 insert/delete/update 等操作没什么分别，这里只尝试 {? = call func1(?, ?)} 或 call proc1(?, ?) (用 Spring JdbcTemplate 调用存储过程也要写成 {call proc1(?, ?)}) 调用的批量操作。由于暂时未找到 executeBatch() 返回每条语句结果(集)的办法，所以 Batch 操作函数时只能忽略掉返回值，剩下要做的就是如何用 Batch 调用存储过程了, 或者说把数据库函数当成存储过程来调用。

我们创建一个数据库函数

 1create or replace function insert_user_fn(p_name character varying, p_age int) 
 2    returns integer
 3    language plpgsql
 4    as $$
 5declare var_count integer;
 6begin
 7    insert into users(name, age) values(p_name, p_age);
 8    select lastval() into var_count;
 9    return var_count;
10end;
11$$;

把上面的 function 替换成 procedure, 再去掉 returns void, return var_count 就是一个存储过程了

其实和 PreparedStatement 没什么区别，见代码如下

 1    private static void batchCall() throws SQLException {
 2        Connection conn = dataSource.getConnection();
 3        CallableStatement call = conn.prepareCall("{call insert_user_fn(?, ?)}");
 4
 5        for (int i = 1; i <= 5; i++) {
 6            call.setString(1, "user" + i);
 7            call.setInt(2, 10);
 8            call.addBatch();
 9        }
10        int[] affected = call.executeBatch();
11        System.out.println(Arrays.toString(affected));<br/><br/>
12    }

唯一的不同就是 conn.prepareCall() 中的语句不一样，然后就是打印出的 affected 不同

[0, 0, 0, 0, 0]

全为零并非每次执行时未产生效果，而是调用函数或存储过程时无法获知明确的受影响行数

对于 PostgreSQL 虽然写成了用 {call insert_user_fn(?, ?)} 来调用函数，但驱动会转换为 select 语句

select * from insert_user_fn($1, $1) as result

而后的套入参数批量操作与 PreparedStatement 是一样的，只是无法获得每次操作的返回值

在 PostgreSQL 中如果操作一个真正的存储过程

1create or replace procedure insert_user_pr(p_name character varying, p_age int) 
2    language plpgsql
3    as $$
4begin
5    insert into users(name, age) values(p_name, p_age);
6end;
7$$;

直接用 JDBC 操作存储过程时，语句的写法不能用大括号括起来(Spring 的 JdbcTemplate 需以一致的方式 {call insert_user_pr(?, ?)} 来调用存储过程)

 1    private static void batchCall() throws SQLException {
 2        Connection conn = dataSource.getConnection();
 3        CallableStatement call = conn.prepareCall("call insert_user_pr(?, ?)");
 4
 5        for (int i = 1; i <= 5; i++) {
 6            call.setString(1, "user" + i);
 7            call.setInt(2, 10);
 8            call.addBatch();
 9        }
10        int[] affected = call.executeBatch();
11        System.out.println(Arrays.toString(affected));
12    }

发送到数据库端的语句就不是 select * from insert_user_pr($1, $2) 了，而是 call insert_user_pr($1, $2)

输出也是一样无法判定每次执行影响的行数

[0, 0, 0, 0, 0]

文中小节一下 JDBC 如何进行批量操作

判断数据库或 JDBC 驱动能不能支持批量操作，可通过 conn.getMetaData().supportsBatchUpdates() 获知
简单的批量操作可由 Statement 的 execute(), executeQuery(), executeUpdate() 执行分号分隔的多条 SQL 语句
Statement 的 addBatch(String sql) 可进行静态 SQL 的批量操作
PreparedStatement 的 addBatch() 可对带参数的 SQL, 函数和存储过程进行批量操作

逐条语句与批量操作的性能对比

我们为什么要选择 JDBC 批量操作，关键就是性能，前面只是从理论上分析了批量操作可获得较好的性能，但这个好有多好，有没有一个量级的差别，还需要有一个感性的对比数据。为此我们用以下三个方法来看每次往一个新创建的空表中插入 10 万条记录的各自时长

为体现出实际应用中的网络因素，测试用的是一个远程数据库

 1public class TestBatchPerformance {
 2
 3    public static void main(String[] args) throws SQLException {
 4        Connection conn = dataSource.getConnection();
 5        long start = System.currentTimeMillis();
 6        insertOneByOne(conn);
 7        System.out.println("timeMs: " + (System.currentTimeMillis() - start));
 8    }
 9
10    private static void insertOneByOne(Connection conn) throws SQLException {
11        Statement stmt = conn.createStatement();
12        for (int i = 1; i <= 100_000; i++) {
13            stmt.execute("insert into users(name, age) values('%s', 10)".formatted("user" + i));
14        }
15    }
16
17    private static void executeBatch1(Connection conn, int batchSize) throws SQLException {
18        Statement stmt = conn.createStatement();
19        for (int i = 1; i <= 100_100; i++) {
20            stmt.addBatch("insert into users(name, age) values('%s', 10)".formatted("user" + i));
21            if(i % batchSize == 0) {
22                stmt.executeBatch();
23                stmt.clearBatch();
24            }
25        }
26    }
27
28    private static void executeBatch2(Connection conn, int batchSize) throws SQLException {
29        PreparedStatement stmt = conn.prepareStatement("insert into users(name, age) values(?, ?)");
30        for (int i = 1; i <= 100_100; i++) {
31            stmt.setString(1, "user" + i);
32            stmt.setInt(2, 10);
33            stmt.addBatch();
34            if(i % batchSize == 0) {
35                stmt.executeBatch();
36                stmt.clearBatch();
37            }
38        }
39    }
40
41}

下面是不同调用时统计的时长(单位为毫秒)

方法调用	BatchSize	批处理方式	耗时(毫秒)	说明
insertOneByOne	N/A	N/A	6186544	1 小时 43 分 6 秒, 16.17 记录/s
executeBatch1	1000	Statement.addBatch(sql)	31243	31 秒, 3.2 batch/s, 3200 记录/s
executeBatch2	1000	PreparedStatement.addBatch()	30684	30 秒
executeBatch1	2000	Statement.addBatch(sql)	31700	31 秒
executeBatch2	2000	PreparedStatement.addBatch()	30590	30 秒

以上的测试结果应该够冲击力吧，还有什么理由在进行大批量操作时不采用 Batch 吗？至于是用 Statement.addBatch(sql) 还是 PreparedStatement.addBatch() 性能上并不没本质上的差别。现实在多为执行动态的 SQL 语句(含函数与存储过程)，为防止可能的 SQL Injection, 多考虑用 PreparedStatement.addBatch() 的方式。

最后一个遗留问题: 是否能在批量调用函数时获得每次的返回值？

从本人目前所找到的资料来看，executeBatch() 可用来批量执行 insert/update/delete 语句，以及函数和存储过程，但无法获得函数的返回值和 OUT 参数。

下方的代码调在执行 cstmt.executeBatch() 时会报错

 1    private static void batchCallFunctionGetReturn(Connection conn) throws SQLException {
 2        CallableStatement cstmt = conn.prepareCall("{? = call insert_user_fn(?, ?)}");
 3
 4        cstmt.registerOutParameter(1, Types.INTEGER);
 5        cstmt.setString(2, "yy");
 6        cstmt.setInt(3, 3);
 7        cstmt.addBatch();
 8
 9        cstmt.executeBatch();
10    }

错误信息

Exception in thread "main" java.lang.IllegalArgumentException: can't getV3Length() on a null parameter
at org.postgresql.core.v3.SimpleParameterList.getV3Length(SimpleParameterList.java:434)
at org.postgresql.core.v3.QueryExecutorImpl.sendBind(QueryExecutorImpl.java:1690)
at org.postgresql.core.v3.QueryExecutorImpl.sendOneQuery(QueryExecutorImpl.java:2014)
at org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:1534)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:565)
at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:912)
at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:936)

注册了 OUT parameter 的函数或存储过程不能用 executeBatch() 执行，那么直接 select 函数不注册 OUT parameter 会怎么样呢？

下面的代码试图 executeBatch 调用函数，获得结果

 1    private static void callFunctionGetReturn(Connection conn) throws SQLException {
 2        PreparedStatement pstmt = conn.prepareStatement("select insert_user_fn(?, ?)");
 3        pstmt.setString(1, "yy");
 4        pstmt.setInt(2, 3);
 5        pstmt.addBatch();
 6        pstmt.executeBatch();
 7
 8        ResultSet rs = pstmt.getResultSet();
 9        if(rs.next()) {
10            System.out.println(rs.getObject(1));
11        }
12    }

pstmt.executeBatch() 执行没问题，但是 pstmt.getResultSet() 是 null

Exception in thread "main" java.lang.NullPointerException: Cannot invoke "java.sql.ResultSet.next()" because "rs" is null

同样走不通。既要用 Batch, 又想从 Batch 中每条语句中获得返回结果的念头该断绝了。

附一段执行 insert 语句后如何获得自增 ID 值的代码

1    private static void insertGetGeneratedKey(Connection conn) throws SQLException {
2        PreparedStatement pstmt = conn.prepareStatement(
3            "insert into users(name, age) values('xx', 5)", Statement.RETURN_GENERATED_KEYS);
4        pstmt.execute(); // or pstmt.executeUpdate();
5        ResultSet rs = pstmt.getGeneratedKeys();
6        if (rs.next()) {
7            System.out.println(rs.getInt(1));
8        }
9    }

或者用

1    private static void insertGetGeneratedKey(Connection conn) throws SQLException {
2        Statement stmt = conn.createStatement();
3        stmt.executeUpdate("insert into users(name, age) values('xx', 5)", Statement.RETURN_GENERATED_KEYS);
4        ResultSet rs = stmt.getGeneratedKeys();
5        if (rs.next()) {
6            System.out.println(rs.getInt(1));
7        }
8    }

上面两段代码可用来替代之前的 returning id 写法

1    private static void insertGetGeneratedKey(Connection conn) throws SQLException {
2        Statement stmt = conn.createStatement();
3        ResultSet rs = stmt.executeQuery("insert into users(name, age) values('xx', 5) returning id");
4
5        if (rs.next()) {
6            System.out.println(rs.getInt(1));
7        }
8    }

Statement.RETURN_GENERATED_KEYS 只能用于返回自动产生的 ID 上，而 returning 可返回当前插入记录的任意字段，如

insert into users(name, age) values('xx', 5) returning name

看来只能打消 executeBatch() 时获得函数返回值或存储过程 OUT 的参数了。

对于试图 Batch 方式调用函数或存储过程，还希望得到每次调用的返回值时，我们必须转换一下思维，有必要改造函数或存储过程，使之能处理 Table 或对象数组输入，输出 Table 或 Cursor, 也就是说让函数或存储过程自身具体批处理的能力。永久链接 https://yanbin.blog/jdbc-batch-sql-function-procedure/, 来自隔叶黄莺 Yanbin's Blog
[版权声明]

本文采用署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 进行许可。