PostgreSQL JDBC 基于游标的分批查询结果返回

JDBC 查询 PostgreSQL 数据库,如果表数据量小,可以一次性全部查询返回,并且这种方式速度也是最快的,而对于大数据量的表,一次性查询返回所有结果,会占用大量的内存,导致 OOM,程序崩溃。此时使用 JDBC 基于 ResultSets 的游标特性,将结果集分批的返回,就能很好的解决这个问题。

基于 ResultSets 的分批特性,其原理 是将一个批次的行记录缓存在连接的客户端侧,当这一批次处理完成后,通过游标来检索下一批记录。

示例代码:

import java.sql.*;
import java.util.Properties;

public class Main{
    public static void main(String[] args)
    {
        String url = "jdbc:postgresql://127.0.0.1:36099/postgres";
        try {
                Connection conn = DriverManager.getConnection(url, "admin", "123456");
                conn.setAutoCommit(false);
                Statement st = conn.createStatement();
                st.setFetchSize(50);
                ResultSet rs = st.executeQuery("select * from tb_test");
                while(rs.next()){
                    int id = rs.getInt(1);
                    String name = rs.getString(2);
                    System.out.println("id:" + id + " name:" + name);
                }
                rs.close();
                st.close();
                conn.close();
        }catch (SQLException e) {
                e.printStackTrace();
        }
    }
}
  • conn.setAutoCommit(false) 表示关闭自动提交
  • st.setFetchSize(50) 表示设置客户端本地一个批次获取 50 条记录。setFetchSize()最主要是为了减少网络交互次数设计的。访问 ResultSet 时,如果它每次只从服务器上取一行数据,则会产生大量的开销。setFetchSize()的意思是当调用 rs.next() 时,ResultSet 会一次性从服务器上取得多少行数据回来,这样在下次 rs.next() 时,它可以直接从内存中获取出数据而不需要网络交互,提高了效率。 这个设置可能会被某些 JDBC 驱动忽略的,而且设置过大也会造成内存使用增大。
  • 如果想关闭游标批量查询,将 setFetchSize()参数设置为 0,即 st.setFetchSize(0)

下面演示代码执行,构造数据库表 tb_test 数据如下:

postgres=# select * from tb_test;
 id | name
----+------
  1 | a
  2 | b
  3 | c
  4 | d
  5 | e
(5 rows)

代码执行结果如下,setFetchSize(50) 只是改变了 jdbc 内部的查询结果获取与缓存机制,对于上层调用者来说是无感知的,它返回的结果与 setFetchSize(0) 完全一致。

[zhang@localhost java]$ javac -Djava.ext.dirs=jdbc Main.java
[zhang@localhost java]$ java -Djava.ext.dirs=jdbc Main
id:1 name:a
id:2 name:b
id:3 name:c
id:4 name:d
id:5 name:e

ResultSets 的游标特性并不是在所有场景下都能使用,它有一些使用限制,如下:

  • 连接数据库必须是 V3 协议,PostgreSQL 数据库版本必须是 7.4 及以上
  • 连接不能是自动提交模式
  • 查询只能包含一个 SQL 语句,不能多个 SQL 语句放到一个查询里
  • Statement 被创建时,其 ResultSet 类型必须是 ResultSet.TYPE_FORWARD_ONLY,即只能向前移动,这个也是默认值。

文章评论

0条评论