Passing an array to SELECT * FROM table WHERE country IN (?...)

Question

Is there a way to pass an array of strings for a "WHERE country IN (...)" query?

something like this:

String[] countries = {"France", "Switzerland"};
PreparedStatement pstmt = con.prepareStatement("SELECT * FROM table WHERE country IN (?...)");
pstmt.setStringArray(1, countries);
pstmt.executeQuery();

an ugly workaround would be to create the query based on the size of the array

String[] countries = {"France", "Switzerland"};
if (countries.size() == 0) { return null; }
String query = "SELECT * FROM table WHERE country IN (?";
for (int i = 1; i < countries.size; i++) { query += ", ?"; }
PreparedStatement pstmt = con.prepareStatement(query);
for (int i = 0; i < countries.size; i++) { pstmt.setString(1+i, countries[i]); }
pstmt.executeQuery();

but this looks really ugly.

any idea?

Related: http://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives — jmj, Jun 04 '12 at 09:17
i wanted to add post but BalusC wrote everything and he wrote it awesome, so check BalusC post in thead as posted @JigarJoshi — Simon Dorociak, Jun 04 '12 at 09:34
there are vendor specific ways to do this. see this post postgresql and h2 http://stackoverflow.com/a/3724272/280393 — David Portabella, Jul 17 '16 at 15:51

score 4 · Answer 1 · edited Jun 04 '12 at 09:22

4

No, it's not possible. ORMs like Hibernate or wrapper APIs like Spring JDBC allows doing that. But with plain JDBC, you must do it yourself.

edited Jun 04 '12 at 09:22

Buhake Sindi

87,898
29
167
228

answered Jun 04 '12 at 09:17

JB Nizet

678,734
91
1,224
1,255

That's not really true. Many databases support parametrized IN(...) conditions using PreparedStatements. – jarnbjo Jun 04 '12 at 09:25
@jambo: It has nothing to do with databases supporting them or not. All databases do, AFAIK. But you need one placeholder per element of the IN clause. – JB Nizet Jun 04 '12 at 10:01
1

No, that is exactly what you don't need. The exact syntax is DB specific, here for example a solution for Oracle: http://blogs.itemis.de/kloss/2009/03/05/arrays-preparedstatements-jdbc-and-oracle/ – jarnbjo Jun 04 '12 at 10:12
OK, so you don't need them if you create an additional type in your database, if you change your query, and if you use proprietary types. Point taken. But I would prefer generating the query by myself, keep my code database-agnostic, and my database setup simpler. – JB Nizet Jun 04 '12 at 10:20

score 0 · Answer 2 · answered Jun 04 '12 at 09:37

0

I think the work around would be formulating the entire query string at runtime and using a Statement object instead of PreparedStatement.

answered Jun 04 '12 at 09:37

Michael

11

score 0 · Answer 3 · answered Jun 04 '12 at 11:04

0

No way to try this.See here to other ways.But there is one exception, if you use oracle database then you can try this

answered Jun 04 '12 at 11:04

Sai Ye Yan Naing Aye

6,622
12
47
65

score 0 · Answer 4 · answered Jun 09 '12 at 13:17

0

If your database engine support IN (subquery), you can create a view or memory table to do it.

answered Jun 09 '12 at 13:17

Yongxin Zhao

124
1

Passing an array to SELECT * FROM table WHERE country IN (?...)

4 Answers4