Java 不能在 JDBC PreparedStatement 中使用 LIKE 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2857164/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Cannot use a LIKE query in a JDBC PreparedStatement?
提问by SeerUK
The query code and query:
查询代码及查询：
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 ...
Returns an empty ResultSet.
返回一个空的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.
但我似乎无法找到让他们参与其中的方法。
What am I missing?
我错过了什么？
采纳答案by BalusC
First, the PreparedStatementplaceholders (those ?things) are for column valuesonly, not for table names, column names, SQL functions/clauses, etcetera. Better use String#format()instead. Second, you should notquote the placeholders like '?', it would only malform the final query. The PreparedStatementsetters already do the quoting (and escaping) job for you.
首先，PreparedStatement占位符（那些?东西）仅用于列值，而不用于表名、列名、SQL 函数/子句等。更好地使用String#format()。其次，你应该不引用占位符一样'?'，它只会malform最终的查询。的PreparedStatement制定者已经做了引用（和转义）的工作适合你。
Here's the fixed SQL:
这是固定的 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");
Here is how to use it:
以下是如何使用它：
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%");
See also:
另见：
回答by Aaron Digulla
Omit the 'around the ?. Without the ', ?is a placeholder for a parameter. With it, it's an SQL string (i.e. the same as "?"in Java).
略去'各地?。没有',?是参数的占位符。有了它，它就是一个 SQL 字符串（即与"?"Java 中的相同）。
Then you must concatenate the string on the Java side; you can't pass SQL functions as parameters to queries; only basic values (like string, integer, etc) because the JDBC driver will convert the parameter to the SQL type the database expects and it cannot execute SQL functions in this step.
然后你必须在Java端连接字符串；您不能将 SQL 函数作为参数传递给查询；只有基本值（如字符串、整数等），因为 JDBC 驱动程序会将参数转换为数据库期望的 SQL 类型，并且在此步骤中无法执行 SQL 函数。
回答by Vincent Malgrat
There are two problems with your statement. You have to understand how bind variables work. The query is not processed by substituingthe characters ?with your parameters. Instead, the statement is compiled with placeholders and then, during execution, the actual values of the parameters are given to the DB.
你的说法有两个问题。您必须了解绑定变量的工作原理。查询不是通过?用您的参数替换字符来处理的。而是使用占位符编译语句，然后在执行期间将参数的实际值提供给 DB。
In other words, you parse the following query:
换句话说，您解析以下查询：
SELECT instance_id, :p1
FROM eam_measurement
WHERE resource_id IN (SELECT RESOURCE_ID
FROM eam_res_grp_res_map
WHERE resource_group_id = :p2)
AND DSN LIKE '?'
ORDER BY 2
I'm pretty sure the last parameter will be ignored because it is in a delimited character string. Even if it is not ignored, it does not make sense to have 'characters around because Oracle won't bind a parameter in a string (I'm surprised it hasn't raised any error, do you catch exceptions ?).
我很确定最后一个参数将被忽略，因为它在一个分隔的字符串中。即使它没有被忽略，'周围有字符也没有意义，因为 Oracle 不会在字符串中绑定参数（我很惊讶它没有引发任何错误，您是否捕获异常？）。
Now if you replace your DNS LIKE '?'with DSN LIKE ?and bind "%Module=jvmRuntimeModule:freeMemory%"this will make sense and should return the correct rows.
现在，如果您将DNS LIKE '?'with替换为DSN LIKE ?并绑定，"%Module=jvmRuntimeModule:freeMemory%"这将是有意义的，并且应该返回正确的行。
You still have the problem with your first parameter, it won't do what you expect, i-e the query that will be executed will be equivalent to the following query:
您的第一个参数仍然存在问题，它不会按照您的预期执行，即将执行的查询将等效于以下查询：
SELECT instance_id, 'SUBSTR(DSN,27,16)'
FROM ...
which is not at all the same as
这与完全不同
SELECT instance_id, SUBSTR(DSN,27,16)
FROM ...
I would suggest parsing (=prepareStatement) the following query if you expect the SUBSTR to be dynamic:
如果您希望 SUBSTR 是动态的，我建议解析 (=prepareStatement) 以下查询：
SELECT instance_id, SUBSTR(DSN,?,?)
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
回答by Hadi
You can try:
你可以试试：
String beforeAndAfter = "%" + yourVariable + "%";
回答by csonuryilmaz
If you want to use LIKE in prepared statement and also want to use % characters in LIKE;
如果您想在准备好的语句中使用 LIKE 并且还想在 LIKE 中使用 % 字符；
write prepared statement as normally " .... LIKE ? ...." and while assigning parameter value to question mark use
像往常一样编写准备好的语句“.... LIKE ? ....”，同时将参数值分配给问号使用
ps.setString(1, "%" + "your string value" + "%");
ps.setString(1, "%" + "your string value" + "%");
This will work :)
这将工作:)
回答by ADITYA VALLURU
This should work:
这应该有效：
"\'" + "?" + "\'"
回答by Saheba
PreparedStatement ps = con.prepareStatement(
"select columname from tablename where LOWER(columnname) LIKE LOWER('"+var+"%')");
Here varis the variable in which value that is to be searched is stored...
这var是存储要搜索的值的变量...
