如何通过JDBC使用包含问号“？”的PostgreSQL JSON(B)操作符

Question

PostgreSQL知道几个在名称中使用问号字符的时髦ASCII-art运算符，例如这些JSON运算符：

• ？字符串是否作为JSON值中的顶级键存在？
• ？这些数组字符串中是否有任何作为顶级键存在？
• ？&是否所有这些数组字符串都作为顶级键存在？

问题是官方的PostgreSQL JDBC驱动程序似乎不能正确解析包含此类操作符的SQL字符串。它假设问号是一个普通的JDBC绑定变量。以下代码...

try (PreparedStatement s = c.prepareStatement("select '{}'::jsonb ?| array['a', 'b']");
     ResultSet rs = s.executeQuery()) {
     ...
}

...引发异常：

org.postgresql.util.PSQLException: Für den Parameter 1 wurde kein Wert angegeben.
    at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:225)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:190)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:424)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:161)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)

我如何使用这个操作符？

Answer 1

有两种可能的解决办法：

这是最简单的变通方法，但您失去了从准备好的语句中获得的所有好处（性能、SQL注入保护等）。然而，这将起作用

try (Statement s = c.createStatement();
     ResultSet rs = s.executeQuery("select '{}'::jsonb ?| array['a', 'b']")) {
     ...
}

运算符只是pg_catalog中支持函数的语法糖。下面是如何找到这些函数的名称：

SELECT 
  oprname, 
  oprcode || '(' || format_type(oprleft,  NULL::integer) || ', ' 
                 || format_type(oprright, NULL::integer) || ')' AS function
FROM pg_operator 
WHERE oprname = '?|';

oprname  function
----------------------------------------------------------------------------------
?|       point_vert(point, point)
?|       lseg_vertical(-, lseg)
?|       line_vertical(-, line)
?|       jsonb_exists_any(jsonb, text[])    <--- this is the one we're looking for
?|       exists_any(hstore, text[])

try (PreparedStatement s = c.prepareStatement(
         "select jsonb_exists_any('{}'::jsonb, array['a', 'b']");
     ResultSet rs = s.executeQuery()) {
     ...
}