I have to find out if there are entries in a table that also exist in a list I have in Java. The logical solution would be "SELECT column FROM table WHERE column IN (?)" and then set ? to the array I have in Java, but setArray asks for a java.sql.Array and I'm not really sure how to obtain one and if it really is what I am looking for.
+3
A:
No, that's not possible.
Unfortunately, IN requires two steps:
- Add one binding
?for each value. - Loop over the values and bind them to the
?
No easy way to do it.
duffymo
2010-08-24 09:59:34
I'd ask everyone who downvotes my answer to try the jGuru solution and see if it really works. If it fails, come back and up vote mine.
duffymo
2010-08-24 11:56:07
Yes please, if the jGuru method fails or is notapplicable here, I'll delete my answer. Can't test it myself, unfortunately.
Andreas_D
2010-08-24 12:21:17
You're right, this is the only way.
Henning
2010-08-24 12:35:09
This is the only way. I'll test it tonight to prove it.
duffymo
2010-08-24 13:01:04
+1
A:
You can't do this with JDBC but if are using spring or hibernate, both have support for rewriting a query to unroll a list of parameters into the right number of ?.
Query query = session.createQuery("select x from y where z in (:params)");
query.setParameterList("params", paramList);
List list = query.list();
In spring you need to use the NamedParameterJdbcTemplate.
Mike Q
2010-08-24 12:42:11
@duffymo: Spring does: http://static.springsource.org/spring/docs/3.0.x/spring-framework-reference/html/jdbc.html#jdbc-in-clause
axtavt
2010-08-24 14:14:32
Thank you for the correction axtavt - I haven't digested Spring 3.x yet.
duffymo
2010-08-24 14:35:01
I'd wonder if you looked at the Spring 3.x source code that it'd be doing exactly what I suggested. Spring is just "syntactic sugar" in this case; it can only be done easily if the JDBC driver supports it. Has there been a change to JDBC to make this possible?
duffymo
2010-08-24 15:25:48
@duffymo: Spring is rewriting the query. The NamedParamJdbcTemplate supports hibernate style named parameters (:params) which it rewrites to ? internally. Much like hibernate it can detect that a parameter is actually a Collection/array and unroll :params -> (?, ?, ? ... ?) JDBC has no support for this, tho it would be nice.
Mike Q
2010-08-24 22:03:27
Correct - "rewrites internally". So it's not possible to do it without unrolling.
duffymo
2010-08-24 22:04:21