How to Use PostgreSQL JSONB Operators (`?`, `?|`, `?&`, `/`) with JDBC Prepared Statements?

Using PostgreSQL JSON(B) Operators with Question Marks ("?") in JDBC

PostgreSQL offers various operators, including ?/?|/ and ?&, which facilitate JSON handling. However, the PostgreSQL JDBC driver encounters difficulties parsing SQL strings containing these operators. This article explores two workarounds for utilizing these operators in JDBC.

Solution 1: Static Statements

One approach is to employ static statements instead of prepared statements. While this method eliminates prepared statement benefits, it effectively handles operators.

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

Solution 2: Function Substitution

Operators are mere syntactic constructs that correspond to functions in the pg_catalog. By identifying these functions, you can directly invoke them without using operators. This method may impact indexing efficiency.

To find the corresponding function, 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 = '?|';

The resulting function, "jsonb_exists_any(jsonb, text[])", can be used in prepared statements:

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 JDBC Prepared Statements?. For more information, please follow other related articles on the PHP Chinese website!