How to Handle PostgreSQL JSON(B) Operators with Question Marks Using JDBC?

Handling PostgreSQL JSON(B) Operators with Question Mark Character via JDBC

PostgreSQL utilizes ASCII-art operators containing the question mark ""?" character for JSON operations. However, the official PostgreSQL JDBC driver interprets these question marks as ordinary JDBC bind variables, resulting in errors when using them.

Workarounds

To use these operators via JDBC, two workarounds are applicable:

Static Statements:

Use static statements instead of prepared statements. This approach simplifies usage but eliminates the advantages of prepared statements, including performance gains and SQL injection protection.

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

Function-Based Approach:

Avoid the operators by employing the underlying functions available in pg_catalog. To identify the function name associated with a specific operator, execute the following SQL query:

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

For the "?|"" operator, this yields:

oprname  function
----------------------------------------------------------------------------------
?|       jsonb_exists_any(jsonb, text[])

Substitute the operator with the corresponding function in the JDBC statement:

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 JSON(B) Operators with Question Marks Using JDBC?. For more information, please follow other related articles on the PHP Chinese website!