3

I have a array of Strings "String[]" with certain values. I want to pass that array dynamically to SQL "IN" clause something like:

Select * from Employee where emp_name in (?);

Here "?" has to replaced with String Array. I want to do this in Java Class. I tried with 

Array array = conn.createArrayOf("VARCHAR", new Object[]{"1", "2","3"});
pstmt.setArray(1, array);

But it is not working and getting exception "SQLFeatureException".

Thanks!!!

user2902067
  • 75
  • 1
  • 2
  • 8
  • And also see [PreparedStatement with list of parameters in a IN clause](http://stackoverflow.com/q/3107044/2024761) which has a lot of dupes linked in it. – Rahul Nov 29 '13 at 05:30

3 Answers3

2

Basically the field emp_name is not an array but a VARCHAR so when you set a value for it, it must be a String. So having only 1 ? in the query represents a single possible value for emp_name. You would have to list as many ?'s as the length of the array and substitute these individually with their values.

Assuming that empNames is a String[], you could do this to prepare the query:

StringBuilder query = new StringBuilder("Select * from Employee where emp_name in (");
for (int i = 0; i < empNames.length; i++) {
  if (i > 0) {
    query.append(",");
  }
  query.append("?");
}
query.append(")");

and this to set the individual values behind the ?'s:

for (int i = 0; i < empNames.length; i++) {
  pstmt.setString(i+1, empNames[i]);
}
cosjav
  • 2,095
  • 1
  • 17
  • 17
1

We can appy Select batching to resolve your problem. Reference:

link

Tien Nguyen
  • 4,298
  • 9
  • 30
  • 44
0

You can create the query with IN clause by iterating the String array using StringBuilder.

I would recommend that don't get into complexity of using Arrays.toString() unless you really need that. There will not be much difference in the performance since both are needed to iterate the array to create the String literal.

In my approach we can have a good control over iteration of the array adding the single quote and comma etc.

Suresh Atta
  • 120,458
  • 37
  • 198
  • 307
Keerthivasan
  • 12,760
  • 2
  • 32
  • 53
  • Above solution is working. I want to just avoid that loop and want to pass String like 'ABC','DFD','ss' for "?" .. Is that possible – user2902067 Nov 29 '13 at 05:19