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

查看:179
本文介绍了如何使用包含问号“?”的PostgreSQL JSON(B)运算符。通过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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆