Как использовать операторы PostgreSQL JSON(B), содержащие знак вопроса "?" через JDBC

Question

PostgreSQL знает несколько классных операторов ASCII-art, которые используют символ вопросительного знака в своих именах, например эти операторы JSON:

• ? существует ли строка как ключ верхнего уровня в значении JSON?
• ?| Существуют ли какие-либо из этих строк массива в качестве ключей верхнего уровня?
• ?& Все ли эти строки массива существуют как ключи верхнего уровня?

Проблема в том, что официальный драйвер PostgreSQL JDBC, похоже, неправильно анализирует строки SQL, содержащие такие операторы. Предполагается, что вопросительный знак является обычной переменной связывания JDBC. Следующий код...

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

... выдает исключение:

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)

Как я могу использовать этот оператор?

Answer 1

Есть два возможных обходных пути:

Используйте статические операторы вместо подготовленных

Это самый простой обходной путь, но вы теряете все преимущества подготовленных операторов (производительность, защита от внедрения SQL и т. Д.). Тем не менее, это будет работать

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

Избегайте оператора. Вместо этого используйте функцию (примечание: индексы могут не использоваться)

Операторы являются просто синтаксическим сахаром для вспомогательной функции, которая существует в pg_catalog, Вот как найти имя этих функций:

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

Вышеуказанные выходы:

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

Итак, самый простой обходной путь - просто не использовать оператор, а вместо этого соответствующую функцию:

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

Answer 2

Документация JDBC описывает, как использовать операторы, содержащие вопросительный знак:

В JDBC знак вопроса (?) является заполнителем для позиционных параметровPreparedStatement. Однако существует ряд операторов PostgreSQL® ^, которые содержат вопросительный знак. Чтобы такие вопросительные знаки в операторе SQL не интерпретировались как позиционные параметры, используйте два вопросительных знака (??) как escape-последовательность. Вы также можете использовать эту escape-последовательность вStatement, но это не обязательно.