转义hstore在JDBC Prepared语句中包含运算符 [英] Escaping hstore contains operators in a JDBC Prepared statement
问题描述
我正在将PostgreSQL 9.1.4与hstore和 PostgreSQL JDBC 驱动程序(9.1-901.jdbc4)一起使用.
I am using PostgreSQL 9.1.4 with hstore and the PostgreSQL JDBC driver (9.1-901.jdbc4).
我试图在PreparedStatement中使用包含运算符(?，?&，?|)，但是?字符被解析为变量占位符.可以转义此字符以在查询中发送正确的运算符吗?
I am trying to use the contains operators (?, ?&, ?|) in a PreparedStatement, however the ? character is parsed as a variable placeholder. Can this character be escaped to send the correct operator in the query?
一个例子:
PreparedStatement stmt = conn.prepareStatement("SELECT a, b FROM table1 WHERE c ? 'foo' AND d = ?");
stmt.setInt(1, dValue);
stmt.executeQuery();
在这种形式下，以下示例将引发异常:
In this form the following example would raise an exception:
org.postgresql.util.PSQLException: No value specified for parameter 2.
更新:
在pgjdbc驱动程序中调查查询解析器后此代码段似乎表明无法转义?字符.剩下的问题是:
After investigating the query parser in the pgjdbc driver this snippet seems to indicate that it is not possible to escape the ? character. The questions that remain are:
- JDBC规范中是否有任何允许转义
?的内容，并且可以是参数占位符以外的其他内容? - 与仅使用将声明变量手动插入查询字符串中的普通语句相比，是否有更好的解决此问题的方法?
- Is there anything in the JDBC spec which allows a
?to be escaped and be anything other than a parameter placeholder? - Is there any better work around for this issue than just using plain Statements with variables manually inserted into the query string?
推荐答案
有效地，看来Java SQL解析器与hstore不兼容.
Effectively, it looks like the java SQL parser is not hstore compliant.
但是，由于语法c ? 'foo'与exist(c, 'foo')等价，因此您可以轻松解决此问题.请看以下页面，了解hstore的 verbose 运算符是什么.
But since the syntax c ? 'foo' is equivalent to exist(c, 'foo'), you can easily workaround this problem. Have a look at the following page to see what the verbose operators for hstore are.
这篇关于转义hstore在JDBC Prepared语句中包含运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
