java 如何在 JDBC 准备好的语句中转义文字问号 ('?')
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26516204/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I escape a literal question mark ('?') in a JDBC prepared statement
提问by Mike Godin
I'd like to create a JDBC PreparedStatement like:
我想创建一个 JDBC PreparedStatement 像：
SELECT URL,LOCATE ( '?', URL ) pos FROM Links WHERE pageId=? ORDER BY pos ASC
Where the 1st ?is a literal and the 2nd ?is a parameter. I could use CHAR(63)in place of the '?'but I think the extra function call would slow down the SQL execution. Is there some way to escape that 1st ??
其中第一个?是文字，第二个?是参数。我可以使用CHAR(63)代替，'?'但我认为额外的函数调用会减慢 SQL 执行速度。有什么办法可以逃脱第一个?？
Edit:
编辑：
The following code tests dkatzel's assertion that the ?character in a string is not considered a marker:
以下代码测试 dkatzel 的断言，即?字符串中的字符不被视为标记：
public class Test {
public static void main(String[] args) throws SQLException {
Connection conn = DriverManager.getConnection("jdbc:h2:mem:test");
Statement stmt = conn.createStatement();
stmt.executeUpdate("CREATE TABLE Links(URL VARCHAR(255) PRIMARY KEY,pageId BIGINT)");
stmt.executeUpdate("INSERT INTO Links(URL,pageId) VALUES('http://foo.bar?baz',1)");
stmt.executeUpdate("INSERT INTO Links(URL,pageId) VALUES('http://foo.bar/baz',1)");
stmt.close();
PreparedStatement ps = conn
.prepareStatement("SELECT URL,LOCATE ( '?', URL ) pos FROM Links WHERE pageId=? ORDER BY pos ASC");
ps.setLong(1, 1);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(1) + ":" + rs.getInt(2));
}
rs.close();
ps.close();
conn.close();
}
}
The output:
输出：
http://foo.bar/baz:0
http://foo.bar?baz:15
It appears that dkatzel is correct. I searched the the JDBC Specand could not find any mention that the ?parameter marker would be ignored if it's within quotes, but the few implementations of PreparedStatement parsers that I found (MySql,c-JDBC,H2) all appear to exclude text within single quotes from consideration as parameter markers.
看来 dkatzel 是正确的。我搜索了JDBC Spec并找不到任何提到如果?参数标记在引号内将被忽略，但我发现的 PreparedStatement 解析器的少数实现（MySql、c-JDBC、H2）似乎都排除了单个内的文本引用作为参数标记。
采纳答案by Mark Rotteveel
The meaning of the ?is specified in the SQL specification, and the JDBC specification defers to the SQL specification for this.
的含义?在SQL规范中有规定，JDBC规范对此以SQL规范为准。
A driver doesn't (and shouldn't) interpret a question mark in a literal as a parameter placeholder, as a question mark within a string literal is simply a character within the string literal. For more information look at chapter 5 of SQL:2011 Foundation (ISO-9075-2:2011).
驱动程序不会（也不应该）将文字中的问号解释为参数占位符，因为字符串文字中的问号只是字符串文字中的一个字符。有关更多信息，请查看 SQL:2011 Foundation (ISO-9075-2:2011) 的第 5 章。
So escaping is not necessary (nor possible).
所以逃避是没有必要的（也不可能）。
回答by bobmarksie
Depending on the JDBC driver you are using you may be able to escape by adding another question mark e.g. if you're using PostgreSQL
根据您使用的 JDBC 驱动程序，您可以通过添加另一个问号来进行转义，例如，如果您使用的是 PostgreSQL
https://jdbc.postgresql.org/documentation/head/statement.html
https://jdbc.postgresql.org/documentation/head/statement.html
In JDBC, the question mark (
?) is the placeholder for the positional parameters of a PreparedStatement. There are, however, a number of PostgreSQL operators that contain a question mark. To keep such question marks in a SQL statement from being interpreted as positional parameters, use two question marks (??) as escape sequence. You can also use this escape sequence in a Statement, but that is not required. Specifically only in a Statement a single (?) can be used as an operator.
在 JDBC 中，问号 (
?) 是 PreparedStatement 的位置参数的占位符。但是，有许多 PostgreSQL 运算符包含问号。要防止 SQL 语句中的此类问号被解释为位置参数，请使用两个问号 (??) 作为转义序列。您也可以在 Statement 中使用此转义序列，但这不是必需的。具体来说，只有在 Statement 中，单个 (?) 才能用作运算符。
回答by Elliott Frisch
If it doesn't work with your JDBC driver you could bind it as a String?,
如果它不适用于您的 JDBC 驱动程序，您可以将其绑定为String?,
ps.setString(1, "?");
回答by dkatzel
Did you try it? I think quoted question marks are OK. only "bare" question marks should get replaced in the prepared statement
你试了吗？我认为引用的问号是可以的。在准备好的语句中只应替换“裸”问号
回答by Yogesh A Sakurikar
I have used CHR(63) in my query and that helped resolving my issue.
我在查询中使用了 CHR(63)，这有助于解决我的问题。
Here is what I did for example:
select q'[<div id=['|"]TRD_%%GEN%%['|"].*]' || chr(63) || q'[</div>]' from dual;
这是我所做的，例如：
select q'[<div id=['|"]TRD_%%GEN%%['|"].*]' || chr(63) || q'[</div>]' from dual;
This helped getting the string as :
<div id=['|"]TRD_%%GEN%%['|"].*?</div>
这有助于获得字符串：
<div id=['|"]TRD_%%GEN%%['|"].*?</div>
I have then used this query inside the insert statement, and ran through PreparedStatement. Worked perfectly fine.
然后我在插入语句中使用了这个查询，并运行了 PreparedStatement。工作得很好。
The CHR function is an in built function and can be used similar to other oracle functions. You can use this if you know the query will not going to be repeated lots of times.
CHR 函数是一个内置函数，可以像其他 oracle 函数一样使用。如果您知道查询不会重复很多次，则可以使用它。
