本文介绍了不能在JDBC PreparedStatement中使用LIKE查询?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

查询代码和查询:

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 占位符(那些的东西)是列val仅适用于,不适用于表名,列名,SQL函数/子句等。更好地使用。其次,您应引用占位符,例如'?,它只会使最终查询失真。 PreparedStatement setters已经为你做了引用(和转义)工作。

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%");






参见




  • Sun JDBC tutorial: Using Prepared Statements
  • Format string syntax

这篇关于不能在JDBC PreparedStatement中使用LIKE查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-27 13:00
查看更多