Trouble with PreparedStatement that uses union of selects query and IN CLAUSE

I wrote a query of the form:

select .... where x.y 开发者_如何学Pythonin (?) union select .... where p.y in (?) and a.b not 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.

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.

---

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.

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

SELECT * FROM TABLE WHERE ID IN (1, 2, 3, 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.

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.

---

As far as I remember - you cant use a ? for the in operator - but union is supported fine.

Have you tried replacing the in with =? Or removing the union and checking if it solves the problem?