JDBC 设置 PostgreSQL 查询中 any(?) 的参数

May 1, 2024 --- · 3 min read · JDBC jdbctemplate PostgreSQL ·

Share on:

这段时间都纠缠于 Java 如何操作 PostgreSQL 数据库上，千方百计的为求得更好的性能。为此我们用上了 Batch, 或用 id = any(?) 这种更 PostgreSQL 化的数组参数操作。其实它还有更多数组方面的花样可以玩，毕竟 PostgreSQL 数据库有一种广纳百川的胸怀，总有好的新特性能在 PostgreSQL 中首先体验到。

回到之前的一篇 postgres in (?,?) 和 =any(?) 用法/性能对比，其中关于如何向查询语句中 id = any(?) 占位符传入数组参数的代码是

1Connection conn = datasource.getConnection();
2
3String query = "select * from users where id = any(?)";
4PreparedStatement pstmt = conn.prepareStatement(query);
5Object[] params = new Object[]{1, 2, 3};
6pstmt.setArray(1, conn.createArrayOf("int", params));
7ResultSet rs = pstmt.executeQuery();

在 PreparedStatement(PgPreparedStatement) 中设置数组参数的函数是用

setArray(int i, Array x)

而创建一个 java.sql.Array(PgArray) 对象需要使用到数据库连接对象，在本例中我们直接使用数据库连接是没问题的

pstmt.setArray(1, conn.createArrayOf("int", params);

但对于用到了 JdbcTemplate 隐式使用数据库连接对象时稍微增添些麻烦，似乎只能通过回调函数来使用 Connection 对象来创建 java.sql.Array 对象，如含有 Connection 参数的以下回调

PreparedStatementCreator
ConnectionCallback
PreparedStatementCallback
CallableStatementCallback

比如上面代码换成 JdbcTemplate 就要写成

1List<String> ids = jdbcTemplate.query(con -> {
2    PreparedStatement pstmt = con.prepareStatement("select * from users where id = any(?)");
3    Object[] params = new Object[]{1, 2, 3};
4    pstmt.setArray(1, conn.createArrayOf("int", params));
5     return pstmt;
6 }, (rs, idx) -> rs.getString(1));

上面的代码自然没问题

但是否能不使用连接对象而直接创建 java.sql.Array 对象呢？其间思考过定制 PostgreSQL JDBC 驱动 Driver 类来注入自定义的 PgPreparedStatement, 如构造 jdbc:tryme:postgresql://localhost/postgres 这种方式，或使用 Spring JDBC 时去寻找 JdbcTemplate 的扩展点。其实根本就犯不着，最后绕了一个弯回来发现答案就是问题的起点处放着。提前放送

1jdbcTemplate.queryForList("select * from users where id = any(?)", new int[]{1, 2, 3});

用 JdbcTemplate 给 any(?) 设置参数就这么简单

下面是当前由 JDBC setArray 到 JdbcTemplate 的 setArray 尝试的整个过程

我们查看 PgPreparedStatement 源代码，看到关于设置 Array 参数的方法还有一个私有的

private <A extends @NonNull Object> void setObjectArray(int parameterIndex, A in) throws SQLException

那么是谁在调用它呢？

追寻到以下两个 setObject() 方法

setObject(@Positive int parameterIndex, @Nullable Object in, in targetSqlType, int scale)
setObject(@Positive int parameterIndex, @Nullable Object x)

它们在调用 setArray(index, object) 的分支处代码分别为

#1 setObject(@Positive int parameterIndex, @Nullable Object in, in targetSqlType, int scale)

 1      case Types.ARRAY:
 2        if (in instanceof Array) {
 3          setArray(parameterIndex, (Array) in);
 4        } else {
 5          try {
 6            setObjectArray(parameterIndex, in);
 7          } catch (Exception e) {
 8            throw new PSQLException(
 9                GT.tr("Cannot cast an instance of {0} to type {1}", in.getClass().getName(), "Types.ARRAY"),
10                PSQLState.INVALID_PARAMETER_TYPE, e);
11          }
12        }
13        break;

#2 setObject(@Positive int parameterIndex, @Nullable Object x)

1    } else if (x.getClass().isArray()) {
2      try {
3        setObjectArray(parameterIndex, x);
4      } catch (Exception e) {
5        throw new PSQLException(
6            GT.tr("Cannot cast an instance of {0} to type {1}", x.getClass().getName(), "Types.ARRAY"),
7            PSQLState.INVALID_PARAMETER_TYPE, e);
8      }
9    }

这就给了我们一个启发：我们并不需要直接的用 Connection 对象去创建一个 java.sql.Array 对象，只需要经由 setObject() 方法，传入适当的数组类型就能调用到实际的 setArray() 方法。于是我们尝试

1String query = "select * from users where id = any(?)";
2PreparedStatement pstmt = conn.prepareStatement(query);
3Object[] params = new Object[]{1, 2, 3};
4pstmt.setObject(1, params);
5ResultSet rs = pstmt.executeQuery();
6
7while (rs.next()) {
8    System.out.println(rs.getString(1));
9}

执行后出错

Exception in thread "main" org.postgresql.util.PSQLException: Cannot cast an instance of [Ljava.lang.Object; to type Types.ARRAY

at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:1071)

无法把 new Object[]{1, 2, 3} 转换为 java.sql.Array 对象，即不能从 Object[] 类型推断出要的元素是 int。

关于 PgPreparedStatement 如何根据传入的数组对象推断出相关联的数据库列类型的这里就不细讲了，欲知详情可阅读 PgPreparedStatement 的源代码, 它是开源的。

而把 Object[] 改成 int[] 类型就没问题了

1String query = "select * from users where id = any(?)";
2PreparedStatement pstmt = conn.prepareStatement(query);
3int[] params = new int[]{1, 2, 3};
4pstmt.setObject(1, params);

同样 JdbcTemplate 在执行时动态传入的参数也是通过 setObject() 来设定的，所以也是需要给它提供正确的数组类型参数。等效的代码如下

1List<String> ids = jdbcTemplate.query("select * from users where id = any(?)",
2            (rs, idx) -> rs.getString(1), new int[]{1,2,3});

在 IntelliJ IDEA 中对参数 new int[]{1,2,3} 处有一个警告

Confusing primitive array argument to varargs method

它觉得可以拆开来写成

1jdbcTemplate.query("select * from users where id = any(?)",
2    (rs, idx) -> rs.getString(1), 1, 2, 3);

而实际上这个 new int[]{1, 2, 3} 整体是作为 PreparedStatement 的一个参数，拆开的话被认为会对应 PreparedStatement 的三个 ? 占位符，这是不对的, new int[]{1, 2, 3} 是不可分割的。

在 Mac OS X 平台中按下 Option + Enter, IntelliJ IDEA 提供了方案是

1List<String> ids = jdbcTemplate.query("select * from users where id = any(?)",
2    (rs, idx) -> rs.getString(1), (Object) new int[]{1,2,3})

然后它又说 (Object) 转型是多余的, 去掉 (Object) 的话又回去了，产生了死循环。所以在重构时千万要注意, 轻易信 IntelliJ IDEA, 得不来永生。

为避免 IntelliJ IDEA 执拗的提示，我们可以声明和使用分开来写

1int[] params = new int[]{1, 2, 3};
2List<String> ids = jdbcTemplate.query("select * from users where id = any(?)",
3   (rs, idx) -> rs.getString(1), params);

这下就皆大欢喜了

一点小小的回味，想最初只死心眼要用 setArray(int, Array) 方法来设置, 直接阅读源代码才追溯到用 setObject(int, Object) 是一个更通用的设置参数的方法，最终才找到用 JdbcTemplate 设置 any(?) 参数的简单方法。若是当初保持头脑简单，直接 jdbcTemplate.queryForList("select * from users where id = any(?)", new int[]{1, 2, 3}) 一试所预设的问题就会完美的躲避了，也就不会有上面的那些故事 -- 世上本无事庸人自扰之。

永久链接 https://yanbin.blog/jdbc-postgresql-set-array-for-any-question-mark/, 来自隔叶黄莺 Yanbin's Blog
[版权声明]

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