PostgreSQL jsonb、`?` 和 JDBC

Question

我正在使用PostgreSQL 9.4和强大的JSONB字段类型。 我正在尝试针对文档中的字段进行查询。以下内容在psql CLI中有效：

SELECT id FROM program WHERE document -> 'dept' ? 'CS'

当我尝试通过我的Scala应用程序运行相同的查询时，出现了以下错误。我正在使用Play框架和Anorm，因此查询看起来像这样：

SQL(s"SELECT id FROM program WHERE document -> 'dept' ? {dept}")
.on('dept -> "CS")
....

SQLException: : 未为参数5指定值。 (SimpleParameterList.java:223)

（在我的实际查询中有更多的参数）

我可以通过将我的参数转换为 jsonb 类型，并使用 @> 运算符来检查包含关系来解决此问题。

SQL(s"SELECT id FROM program WHERE document -> 'dept' @> {dept}::jsonb")
.on('dept -> "CS")
....

我不太喜欢这种解决方法。虽然我不知道强制类型转换是否会影响性能，但它需要额外的输入，并且不是很明显。

还有其他可以做的吗？

- Jason

---

要么你没有正确粘贴代码，要么你有语法错误：SQL(s"SELECT id FROM program WHERE document -> 'dept' ? {dept} .on('dept -> "CS") → on 必须应用于 SQL(...)，而不是作为语句字符串的一部分，就像它似乎在那里 = SQL("...").on(...). 请注意，那里的字符串插值是无用的。 - cchantep

我没有粘贴整个 SQL 调用，因为它与我的问题无关。在 on 之后有一个对 apply 的调用。 - Jason

如果您想得到恰当的答案，最好粘贴一段具有有效语法的代码。 - cchantep

最新的JDBC驱动程序似乎有一个修复，允许通过加倍问号来转义它：??。请参见http://www.postgresql.org/message-id/[e-mail] - tiguchi

Answer 1

作为避免使用“?”运算符的一种解决方法，可以创建一个全新的操作符，该操作符执行完全相同的操作。 以下是原始操作符的代码：

CREATE OPERATOR ?(
  PROCEDURE = jsonb_exists,
  LEFTARG = jsonb,
  RIGHTARG = text,
  RESTRICT = contsel,
  JOIN = contjoinsel);

SELECT '{"a":1, "b":2}'::jsonb ? 'b'; -- true

使用一个不同的名称，没有任何冲突，例如#-#，并创建一个新的名称：

CREATE OPERATOR #-#(
  PROCEDURE = jsonb_exists,
  LEFTARG = jsonb,
  RIGHTARG = text,
  RESTRICT = contsel,
  JOIN = contjoinsel);

SELECT '{"a":1, "b":2}'::jsonb #-# 'b'; -- true

使用这个新操作符在你的代码中，它应该可以工作。

检查pgAdmin -> pg_catalog -> Operators，查看所有使用?在名称中的操作符。

- Frank Heikens

Answer 2

在JDBC（和标准SQL）中，问号被保留作为参数占位符。不允许其他用途。

请参见Does the JDBC spec prevent '?' from being used as an operator (outside of quotes)?和jdbc-spec-discuss上的讨论。

当前的PostgreSQL JDBC驱动程序将所有出现的问号（在文本或注释之外）转换为PostgreSQL特定的参数占位符。我不确定PostgreSQL JDBC项目是否已经采取了任何措施（如上面链接中讨论的引入转义），以解决这个问题。对代码和文档的快速查看表明他们没有这样做，但我没有深入挖掘。

补充说明：如bobmarksie的答案所示，当前版本的PostgreSQL JDBC驱动程序现在支持通过加倍来转义问号（即使用??而不是?）。

- Mark Rotteveel

---

请查看我的回答（https://dev59.com/dYbca4cB1Zd3GeqPTD3A#35359516）- 转义功能在PosgreSQL文档中已经有描述并可用（截至2016年2月）。 - bobmarksie

Answer 3

几天前，我遇到了同样的问题，经过一些调查，我找到了这个。

https://jdbc.postgresql.org/documentation/head/statement.html

在JDBC中，问号（?）是PreparedStatement位置参数的占位符。然而，有许多PostgreSQL操作符包含一个问号。为了防止这些SQL语句中的问号被解释为位置参数，请使用两个问号（??）作为转义序列。您也可以在Statement中使用此转义序列，但这不是必需的。特别地，在Statement中只能使用单个（?）作为运算符。

对我来说，使用2个问号似乎效果很好 - 我正在使用以下驱动程序（使用maven依赖项进行说明）...

    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>9.4-1201-jdbc41</version>
    </dependency>

我使用了MyBatis来创建SQL查询，这似乎比创建PostgreSQL操作符更加容易和清晰。

SQL从如下的形式变化：

select * from user_docs where userTags ?| array['sport','property']

... to ...

select * from user_docs where userTags ??| array['sport','property']

希望这对你的情况有所帮助！

- bobmarksie

Answer 4

正如Bob所说，只需使用??而不是?

SQL(s"SELECT id FROM program WHERE document -> 'dept' ?? {dept}")
.on('dept -> "CS")

- Robin