How to Use PostgreSQL's JSON(B) Operators with '?' in JDBC?

Understanding PostgreSQL JSON(B) Operators with Question Mark ""?" in JDBC

PostgreSQL offers unique JSON operators that include a question mark character in their names, such as ? for key existence and ?| for array string existence. However, the official PostgreSQL JDBC driver encounters difficulties parsing SQL strings containing these operators, mistakenly interpreting them as JDBC bind variables.

Workarounds for Using Such Operators via JDBC

To successfully utilize these operators in JDBC, consider the following workarounds:

Utilize Static Statements

This basic workaround eliminates the use of prepared statements, allowing the SQL string to be executed as a static statement. However, it sacrifices the advantages of prepared statements.

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

Employ Functions

Operators are essentially syntactic aliases for backing functions in the pg_catalog. Identifying the function name allows you to use it directly. For example:

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

This yields the following result:

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

The workaround involves replacing the operator with its 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's JSON(B) Operators with '?' in JDBC?. For more information, please follow other related articles on the PHP Chinese website!