使用Select查询和IN CLAUSE的并集的PreparedStatement出现问题 [英] Trouble with PreparedStatement that uses union of selects query and IN CLAUSE

问题描述

我写了以下形式的查询:

I wrote a query of the form:

select .... where x.y in (?) union select .... where p.y in (?) and a.b not in (?) 

问号表示我在运行时使用prepareStatement.setString()方法放置多个值的位置(在IN子句中动态放置值).

The question marks indicate places where I put multiple values at run time (dynamically putting values in the IN clause) using the preparedStatement.setString() method.

执行此查询时，结果集似乎忽略了union子句之后的查询. 我在任何地方都没有例外.

The resultset, on executing this query seems to ignore the query after the union clause. I get no exception anywhere.

我发布此问题，只是想知道是否有人遇到过此类问题，例如此链接建议

I post this question, just to know if anyone else has faced such a problem, like this link suggests UNION of multiple tables and preparedstatement not working The database is Oracle 10g, in case that makes a difference.

推荐答案

您只能使用'?' 单独值的运算符.使用字符串设置IN值，您将获得...

You can only use the '?' operator for separate values. Using a String to set the IN value you will get...

SELECT * FROM TABLE WHERE ID IN (?)

...将被视为...

... will be considered ...

SELECT * FROM TABLE WHERE ID IN ("1,2,3,4")

...根据您的情况.

... in your case.

如果您使用JavaRanch链接中的选项2"，它将像..

If you use the "Option 2" from the JavaRanch link, it will be like..

SELECT * FROM TABLE WHERE ID IN (1, 2, 3, 4)

...我相信是您想要的，但是，您将需要始终准确提供4个值.如果数量减少，您当然可以再次使用其中一种而不会产生不良影响，但是如果数量更多，那么您就不走运了.

... which I believe is what you want, BUT you will need to always supply exactly 4 values. If you have fewer you canof course use one of them again with no ill effect, but if you have more of them you are out of luck.

我建议您做的是动态地构造PreparedStatement，并使用尽可能多的?"因为您有参数输入，然后遍历并设置它们.这样，您就可以将需要动态查询与清理输入结合起来，从而避免任何SQL注入攻击.

What I would recommend you to do, is to construct the PreparedStatement dynamically, with as many '?' as you have in-parameters and then loop through and set them. That way you combine that you need a dynamic query with cleaning the input, avoiding any SQL injection attack.

这篇关于使用Select查询和IN CLAUSE的并集的PreparedStatement出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！