不能在 JDBC PreparedStatement 中使用 LIKE 查询? [英] Cannot use a LIKE query in a JDBC PreparedStatement?
问题描述
查询代码和查询:
ps = conn.prepareStatement("select instance_id, ? from eam_measurement where resource_id in (select RESOURCE_ID from eam_res_grp_res_map where resource_group_id = ?) and DSN like '?' order by 2");
ps.setString(1,"SUBSTR(DSN,27,16)");
ps.setInt(2,defaultWasGroup);
ps.setString(3,"%Module=jvmRuntimeModule:freeMemory%");
rs = ps.executeQuery();
while (rs.next()) { bla blah blah blah ...
返回一个空的ResultSet.
通过基本调试，我发现它的第三个绑定是问题，即
Through basic debugging I have found its the third bind that is the problem i.e.
DSN like '?'
我尝试了各种变体，其中最明智的似乎是使用:
I have tried all kinds of variations, the most sensible of which seemed to be using:
DSN like concat('%',?,'%')
但这不起作用，因为我在连接字符串的任一侧都缺少 ' 所以我尝试:
but that does not work as I am missing the ' on either side of the concatenated string so I try:
DSN like ' concat('%',Module=P_STAG_JDBC01:poolSize,'%') ' order by 2
但我似乎无法找到让他们参与其中的方法.
but I just cannot seem to find a way to get them in that works.
我错过了什么?
推荐答案
首先，PreparedStatement 占位符(那些 ? 的东西)用于 列值 仅适用于表名、列名、SQL 函数/子句等.最好使用 String#format() 代替.其次，您应该不要引用像'?'这样的占位符，它只会使最终查询格式不正确.PreparedStatement setter 已经为您完成了引用(和转义)工作.
First, the PreparedStatement placeholders (those ? things) are for column values only, not for table names, column names, SQL functions/clauses, etcetera. Better use String#format() instead. Second, you should not quote the placeholders like '?', it would only malform the final query. The PreparedStatement setters already do the quoting (and escaping) job for you.
这是固定的 SQL:
private static final String SQL = "select instance_id, %s from eam_measurement"
+ " where resource_id in (select RESOURCE_ID from eam_res_grp_res_map where"
+ " resource_group_id = ?) and DSN like ? order by 2");
使用方法如下:
String sql = String.format(SQL, "SUBSTR(DSN,27,16)"); // This replaces the %s.
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, defaultWasGroup);
preparedStatement.setString(2, "%Module=jvmRuntimeModule:freeMemory%");
<小时>
另见:
这篇关于不能在 JDBC PreparedStatement 中使用 LIKE 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
