How to Handle PostgreSQL JSONB Operators Containing '?' in JDBC?

Using PostgreSQL JSON(B) Operators Containing Question Mark "?" in JDBC

JDBC integration with PostgreSQL poses a challenge when dealing with JSON operators featuring the question mark symbol, such as ? and ?|. The PostgreSQL JDBC driver interprets these characters as ordinary bind variables rather than operators.

Workarounds

Two solutions are available to address this issue:

Static Statements

This straightforward approach avoids the use of prepared statements. While it compromises performance and security, it solves the problem:

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

Functions

Operators in PostgreSQL are often aliases for functions in the pg_catalog. To find the underlying function for an operator, use the following query:

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

This query will return a result similar to:

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

With this information, you can use the underlying function instead of the operator:

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 Handle PostgreSQL JSONB Operators Containing '?' in JDBC?. For more information, please follow other related articles on the PHP Chinese website!