java - JDBCPreparedStatement 忽略 $$ ... $$ 查询 block 中的 ' ? '

Question

我有一个java中的PreparedStatement。我向它提供包含“？”的查询，例如

Select .... where date >= ?

这没问题，当我尝试使用以下方法设置参数时:

preparedStatement.setObject(1, dateToSet);

它按预期工作。但是，如果查询包含 $$ ... $$ block ，则似乎无法找到问号(在 block 内)，并且当我尝试设置参数时，我收到以下错误“列索引超出范围:1 ，列数:0"

SELECT * FROM crosstab( $$ Select .... where date >= ? $$ ) AS ct (...);

我也尝试把 ?就在 $$ block 之后(只是为了确保)，并且它能够找到该 block (在 $$ block 之外)。有什么想法可以让参数占位符在 $$ block 中被发现吗？

Answer 1

对于 $$，您已使用 Dollar-quoted String Constants用于声明 JDBC 绑定(bind)参数的 ? 符号不会在字符串常量内解析。根据 PostgreSQL 文档:

While the standard syntax for specifying string constants is usually convenient, it can be difficult to understand when the desired string contains many single quotes or backslashes, since each of those must be doubled. To allow more readable queries in such situations, PostgreSQL provides another way, called “dollar quoting”, to write string constants. A dollar-quoted string constant consists of a dollar sign ($), an optional “tag” of zero or more characters, another dollar sign, an arbitrary sequence of characters that makes up the string content, a dollar sign, the same tag that began this dollar quote, and a dollar sign. For example, here are two different ways to specify the string “Dianne's horse” using dollar quoting:

$$Dianne's horse$$
$SomeTag$Dianne's horse$SomeTag$

由于 crosstab() 使用字符串常量参数，因此您必须自己在 Java 中准备嵌套查询。这已被讨论on the mailing list .