java - 如何在 JDBC 准备好的语句中转义文字问号 ('?' )

Question

我想创建一个 JDBC PreparedStatement，如下所示:

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

第一个 ? 是文字，第二个 ? 是参数。我可以使用 CHAR(63) 代替 '?' 但我认为额外的函数调用会减慢 SQL 的执行速度。有什么方法可以逃避第一个 ??

编辑:

以下代码测试了 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();
    }
}

输出:

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

看来 dkatzel 是正确的。我搜索了 JDBC Spec并且找不到任何提及如果 ? 参数标记在引号内将被忽略，但我发现的 PreparedStatement 解析器的少数实现( MySql ， c-JDBC ， H2 )全部似乎将单引号内的文本排除在参数标记之外。

Answer 1

根据您使用的 JDBC 驱动程序，您可以通过添加另一个问号来转义，例如如果你使用的是 PostgreSQL

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.