如何在JDBC预准备语句中转义文字问号（'？'） [英] How do I escape a literal question mark ('?') in a JDBC prepared statement

问题描述

我想创建一个JDBC PreparedStatement，如：

I'd like to create a JDBC PreparedStatement like:

SELECT URL,LOCATE ( '?', URL ) pos FROM Links WHERE pageId=? ORDER BY pos ASC

第一个？是文字，第二个？是一个参数。我可以使用 CHAR（63）代替'？'，但我认为额外的函数调用会减慢SQL执行。有没有办法逃脱第一个？？

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 ??

编辑：

以下代码测试dkatzel断言字符串中的？字符不被视为标记：

The following code tests dkatzel's assertion that the ? character in a string is not considered a marker:

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();
    }
}

输出：

http://foo.bar/baz:0
http://foo.bar?baz:15

看来dkatzel是正确的。我搜索了 JDBC Spec ，但是没有发现任何提及？参数标记如果在引号内会被忽略，但是我找到的PreparedStatement解析器的几个实现（ MySql ， c-JDBC ， H2 ）所有似乎都将单引号中的文本排除在考虑范围之外作为参数标记。

It appears that dkatzel is correct. I searched the the JDBC Spec and 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.

推荐答案

SQL规范中指定了？的含义，JDBC规范遵循SQL规范为此。

The meaning of the ? is specified in the SQL specification, and the JDBC specification defers to the SQL specification for this.

司机没有（和笑uld））将文字中的问号解释为参数占位符，因为字符串文字中的问号只是字符串文字中的字符。有关更多信息，请参阅SQL：2011 Foundation（ISO-9075-2：2011）的第5章。

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).

因此无需转义（也不可能）。

So escaping is not necessary (nor possible).

这篇关于如何在JDBC预准备语句中转义文字问号（'？'）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！