如何使用包含问号“?"的 PostgreSQL JSON(B) 运算符?通过 JDBC [英] How do I use PostgreSQL JSON(B) operators containing a question mark "?" via JDBC

问题描述

PostgreSQL 知道几个时髦的 ASCII 艺术运算符，它们在名称中使用问号字符，例如 这些 JSON 运算符:

PostgreSQL knows a couple of funky ASCII-art operators that use the question mark character in their names, for instance these JSON operators:

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

• ? does the string exist as a top-level key within the JSON value?
• ?| Do any of these array strings exist as top-level keys?
• ?& Do all of these array strings exist as top-level keys?

问题在于官方的 PostgreSQL JDBC 驱动程序似乎无法正确解析包含此类运算符的 SQL 字符串.它假定问号是一个普通的 JDBC 绑定变量.下面的代码...

The problem is that the official PostgreSQL JDBC driver does not seem to correctly parse SQL strings containing such operators. It assumes that the question mark is an ordinary JDBC bind variable. The following code...

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)

如何使用此运算符?

推荐答案

有两种可能的解决方法:

There are two possible workarounds:

这是最简单的解决方法，但您将失去准备好的语句的所有好处(性能、SQL 注入保护等).但是，这将起作用

This is the simplest workaround, but you lose all the benefits from prepared statements (performance, SQL injection protection, etc.). However, this will work

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

避开运营商.改用函数(注意:可能不使用索引)

运算符只是存在于 pg_catalog 中的支持函数的语法糖.以下是查找这些函数名称的方法:

Avoid the operator. Use a function instead (note: indexes might not be used)

Operators are just syntax sugar for a backing function that exists in the pg_catalog. Here's how to find the name of these functions:

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[])

因此，最简单的解决方法是不使用运算符，而是使用相应的函数:

So, the simplest workaround is to just not use the operator, but the corresponding function instead:

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

这篇关于如何使用包含问号“?"的 PostgreSQL JSON(B) 运算符?通过 JDBC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！