44

I have looked and have been unable to find an answer to the following challenge I am having. It seems pretty straightforward but I have been unable to resolve it.

I have an ArrayList of record ids that are type Long -> ArrayList<Long>. I would like to use this list of record ids to select rows from another table. So far so good. Now onto the challenge...

a) I am using a prepared statement to select the data from a table using the ArrayList as input for this. 

selectPS = dbConnection.prepareStatement("select columnA from tableA where id in ?");

Question on the above - how should the parameter be defined? The above does not seem correct for an ArrayList type parameter.

b) I am also running into problems when setting the value of the parameter for the prepared statement. There is no method for setting an ArrayList type value and I see no other viable options.

---> selectPS.set?????(1, arraylistParameter);
     ResultSet rs = selectPS.executeQuery();

Any help or direction you can set me in is greatly appreciated.

Thank you.

Bernhard Barker
  • 54,589
  • 14
  • 104
  • 138
Thomas Grady
  • 752
  • 2
  • 11
  • 22
  • **Can you try passing an array ?** , [PreparedStatement#setArray()](http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#setArray(int,java.sql.Array)) ? You need to create an array first using [Connection#createArrayOf()](http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#createArrayOf(java.lang.String,java.lang.Object[])). There are certain ways listed [here](http://www.javaranch.com/journal/200510/Journal200510.jsp#a2). – AllTooSir Jul 24 '13 at 18:41
  • I would dynamically create a String with a `?` for each element in the array, the call the `setLong` method with each value, incrementing the index each time. – Sotirios Delimanolis Jul 24 '13 at 18:42
  • @Thomas Grady CBIP What is your database ? If it is Oracle,then won't support more than 1000 records in `IN` clause.You have to write INNER JOIN clause more info - http://stackoverflow.com/questions/4722220/sql-in-clause-1000-item-limit – Prabhaker A Jul 24 '13 at 19:12
  • @Prabhaker... We are using MySQL. That said, it is not the sql that is the challenge but getting my prepared statement set-up correctly to make use of an array list. Thanks for forwarding the information just the same. – Thomas Grady Jul 24 '13 at 19:35
  • Hi all. I went with the suggestion to loop thru the arraylist and to set the Long parameter for each call and I have what I need. Though there seems to be more overhead with this option, the number of rows read is a couple of thousand and it is to fix a production issue. – Thomas Grady Jul 24 '13 at 19:35

4 Answers4

69

You may want to use setArray method as mentioned in the javadoc below:

http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html#setArray(int, java.sql.Array)

Sample Code:

PreparedStatement pstmt = 
                conn.prepareStatement("select * from employee where id in (?)");
Array array = conn.createArrayOf("VARCHAR", new Object[]{"1", "2","3"});
pstmt.setArray(1, array);
ResultSet rs = pstmt.executeQuery();
Ravi K Thapliyal
  • 51,095
  • 9
  • 76
  • 89
Yogendra Singh
  • 33,927
  • 6
  • 63
  • 73
  • 11
    Getting back to you (and TNI) on the suggestion of using an array. Unfortunately I cannot use that due to us having MySQL as our database. MySQL does not support an sql array. It is too bad because this would have addressed my need as well. Thanks your your help. – Thomas Grady Jul 25 '13 at 12:32
  • 1
    I'm basically using this code, however I'm getting a `text = text[]` error. Would it be possible to show what the result query would be? I expect something like this: `SELECT * FROM employee where id in ('{"1", "2","3"}')` as it does in my code. – Mathieu Brouwers Dec 27 '17 at 13:36
  • 2
    For the comment above; haven't solved why I'm getting the error, but using `SELECT * FROM employee WHERE id = ANY (?)` does work (for me). – Mathieu Brouwers Dec 29 '17 at 15:43
  • 7
    This will not work on Oracle Databases, because createArrayOf is not implemented – Jorge Machado Mar 22 '18 at 08:54
  • 1
    Not working in Informix either, also because createArrayOf is not implemented – César Alves Aug 17 '20 at 17:41
26

If you have ArrayList then convert into Array[Object]

ArrayList<String> list = new ArrayList<String>();
PreparedStatement pstmt = 
            conn.prepareStatement("select * from employee where id in (?)");
Array array = conn.createArrayOf("VARCHAR", list.toArray());
pstmt.setArray(1, array);
ResultSet rs = pstmt.executeQuery();
Gabber
  • 7,169
  • 3
  • 32
  • 46
-10

@JulienD Best way is to break above process into two steps.

Step 1 : Lets say 'rawList' as your list that you want to add as parameters in prepared statement.

Create another list : 

ArrayList<String> listWithQuotes = new ArrayList<String>();

for(String element : rawList){
    listWithQuotes.add("'"+element+"'");
}

Step 2 : Make 'listWithQuotes' comma separated.

String finalString = StringUtils.join(listWithQuotes.iterator(),",");

'finalString' will be string parameters with each element as single quoted and comma separated.

Nikhil Pate
  • 623
  • 5
  • 8
-28

why making life hard-

PreparedStatement pstmt = conn.prepareStatement("select * from employee where id in ("+ StringUtils.join(arraylistParameter.iterator(),",") +)");
user149925
  • 3
  • 2
John Lim
  • 1