How to Use PostgreSQL JSONB Operators with Question Marks '?' in JDBC?

How to use the PostgreSQL JSON(B) operator containing question mark "?" in JDBC

PostgreSQL provides several methods that use question marks as names Part of the clever ASCII operators, such as these JSON Operators:

• ?: Does a string exist as a top-level key in a JSON value?
• ?|: Does any of these array strings exist as top-level keys?
• ?&: Do these array strings exist as top-level keys?

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

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

... throws an exception:

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)

How to use this operator?

There are two possible solutions:

1. Use static statements instead of prepared statements

This is the simplest workaround, but you will lose all the benefits of 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']")) {
    ...
}

2. Avoid using operators. Use a function instead (note: indexing may not be used) The

operator is just syntactic sugar for the supported function, which exists in pg_catalog. Here's how to find the names of these functions:

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

The above operation produces:

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 solution is not to use the operator, but the corresponding Function:

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

The above is the detailed content of How to Use PostgreSQL JSONB Operators with Question Marks '?' in JDBC?. For more information, please follow other related articles on the PHP Chinese website!