Creating java.sql.Array as an array of integers for use in prepared statement

Question

I have a need to use a prepared statement to use an array of integer in my SQL statement.

When I try to create the array, and when I read the Javadoc it says It only shows the creation of an array of strings. I have to search for a lot of table key ids in one call (previously iterating through them one at time resulted in 22k queries) So I need all of the results at one time.

A piece of my query string looks like this

AND A.KEY in (?) order by KEYID DESC

Here is where I build my query (via using java.sql.Array with my list of IDs)

StringBuilder query = new StringBuilder(Query);             

PreparedStatement stmt = connection.prepareStatement(query.toString());

java.sql.Array array = connection.createArrayOf("varchar", Ids.toArray());
stmt.setArray( 1, array);

ResultSet results = stmt.executeQuery();

I tried to run them through as strings, as the sql.Array uses strings, but I NEED to send them as ints.

My ids come through as an ArrayList<Integer>, this is the key to why this is not a duplicate question I need to send the sql statement a list of integers - my way works for strings.

edit when i run through my program sending one int at a time, it works fine.

How can I solve this to send the SQL statement with integers in place of my? In the sql query?

Answer 1

So I figured out my own workaround to this issue. Instead of sending an entire list as a parameter to my set statement, I dynamically added ', ?' to meet how many inputs I have.

I then dynamically added each item in my arraylist to a set statement for the next position (?).

This is not as efficient as throwing a list at it, but at least I only make 1 call to my database.

if (Ids.size()==1){
    stmt.setInt( 1, Ids.get(0).intValue());
}



if (Ids.size() > 1){
    int temp = Ids.size();
    while (temp >1){
        query.insert(Query.indexOf("?") + 1, ", ?");
        temp-=1;

    }

     stmt = connection.prepareStatement(query.toString());

    for (int i=1 ; i<=Ids.size(); i++){

        stmt.setInt( i, Ids.get(i-1).intValue());
    }
}