我想创建一个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)似乎都将单引号中的文本排除在考虑范围内作为参数标记.