3

Possible Duplicate:
PreparedStatement IN clause alternatives? 

SELECT * FROM tableName WHERE id IN ?

I wish to set the PreparedStatement's IN parameter as an int[]. For example, an integer array of {1, 2, 3, 4} would become IN (1,2,3,4) in the query.

Is there a simple way to achieve this functionality using PreparedStatement's methods, or must I dynamically create the IN parameter as a string by looping over the array?

I imagine something similar to #setString(int parameterIndex, String x) but of course for an int array.

Community
  • 1
  • 1
FThompson
  • 28,352
  • 13
  • 60
  • 93
  • 1
    I don't think it works like that. I think you need to find a different syntax in which there are multiple ?'s and you set each one's value. Pretty cool thought, though. – Chris Gerken Dec 04 '12 at 03:04
  • You are going to have to write your own method. e.g. http://stackoverflow.com/a/1916391/738746, which you can reuse. Notice that arguments is not an array of `int`s but an array of `Object`s. – Bhesh Gurung Dec 04 '12 at 03:07
  • I was about to suggest doing what you just mentioned: dynamically build a query by looping over the array. If you want something more convenient, use JPA 2 (where you can assign Collections to query parameters). – jahroy Dec 04 '12 at 03:07
  • Nice find @BheshGurung. As my question is a duplicate of that, I'm voting to close it. (I am not deleting it, though, because I feel like my question's title is more search-friendly for future programmers with this same question). – FThompson Dec 04 '12 at 03:11
  • How about changing the title of the the other question and deleting this one then? – reprogrammer Dec 04 '12 at 03:15
  • @reprogrammer I considered that, but the other question applies to a broader situation. Several answers skip the usage of `IN` and take entirely different approaches to the issue. While that answer applies to *all* `IN` alternatives, mine only applies to setting an `int[]` parameter. – FThompson Dec 04 '12 at 03:17

2 Answers2

1

I'd recommend Spring JDBC. See how easy to start working with it:

    NamedParameterJdbcTemplate t = new NamedParameterJdbcTemplate(dataSource);
    Map<String, Object> params = new HashMap<String, Object>();
    params.put("ids", Arrays.asList(1, 2));
    List<Map<String, Object>> list = t.queryForList("select * from t1 where id in (:ids)", params);

You will see that it's not only IN what's good about Spring JDBC. Named parameters, no need to create / close Connection, PreparedStatement, ResultSet, no checked exceptions, declarative transaction support and many other things.

Evgeniy Dorofeev
  • 133,369
  • 30
  • 199
  • 275
  • +1, Very interesting approach. However this would require that I rewrite the majority of my application to accommodate (and I don't know whether this approach pools connections for async use), so I won't be taking this route. – FThompson Dec 04 '12 at 03:35
0

The only way I know of to accomplish this is to alter the query string before you create the prepared statement to add new ?s. Then just perform the binds as normal.

Michael
  • 6,141
  • 2
  • 20
  • 21
  • I am marking this as accepted, because it is indeed the approach I have chosen to take, but I'm not upvoting it because it came after the question was answered via the comments. Thanks anyway though. – FThompson Dec 04 '12 at 03:36