prepared statement with "in" predicate

The actual list of ages will vary from one execution to the next, so the SQL string contains a parameter:

However there doesn't appear to be any method defined on PreparedStatement for setting a parameter to a list of values. Although there is a setArray() method, the array in question is of type java.sql.Array, and I've no idea how to create one of these from a "regular" Java array or Collection.

The actual list of ages will vary from one execution to the next, so the SQL string contains a parameter:

However there doesn't appear to be any method defined on PreparedStatement for setting a parameter to a list of values. Although there is a setArray() method, the array in question is of type java.sql.Array, and I've no idea how to create one of these from a "regular" Java array or Collection.

select * from person where age in (?,?,?); ps.setInt(1,iVariable1); //where 'ps' is ref varible of PreparedStatement ps.setInt(1,iVariable2); ps.setInt(1,iVariable3);

That won't work because each time I execute the PreparedStatement the list could have a different number of elements, so I can't simply define a '?' parameter for each item in the SQL string. [ June 27, 2008: Message edited by: Dan Murphy ]

Originally posted by Dan Murphy: That won't work because each time I execute the PreparedStatement the list could have a different number of elements, so I can't simply define a '?' parameter for each item in the SQL string.

While you can't define a variable for each one, you can define a '?' parameter for each one. The elements are stored in some data structure (array or list I would presume.) Which means you can build a String with the correct number of '?' values and then loop through the data structure calling preparedStatement.setString() the appropriate number of times.

Maybe you can tryResultSet rs1=stmt.executeQuery("select 10 union select 20 union select 30"); PreparedStatiment prestmt=con.prepareStatment("select * from person where age in ?"); prestmt.setParameter(1,rs1); If your jdbc driver doesn't support that feature, you can try to create a temporary table in memory too.

Originally posted by Wei Dai: Maybe you can try ResultSet rs1=stmt.executeQuery("select 10 union select 20 union select 30"); PreparedStatiment prestmt=con.prepareStatment("select * from person where age in ?"); prestmt.setParameter(1,rs1); If your jdbc driver doesn't support that feature, you can try to create a temporary table in memory too.

This moves the problem to how to create the "select 10 union select ..." statement. If you don't use the prepared statement, you leave yourself vulnerable to SQL injection.

Amit Kumar S
Greenhorn

Joined: Jun 27, 2008
Posts: 3

posted Jul 01, 2008 00:44:00

0

Originally posted by Dan Murphy:

That won't work because each time I execute the PreparedStatement the list could have a different number of elements, so I can't simply define a '?' parameter for each item in the SQL string.

[ June 27, 2008: Message edited by: Dan Murphy ]

This code is working for variable number of argument..... This is done by Dynamic query generation..... However I am searching smaller function that will do the same .. For the time being use it ,if it is useful for you...

"IN" clauses are the one area JDBC is underdeveloped. There's no good solution other than to construct the SQL query by hand at runtime, possibly using a loop for unknown number of values (watch the commas tho if there's only one element!)

I would suggest you use features of the DBMS to accomplish your goal. The key is to stay committed to the use of bind variables, but you need to parse the value of that bind variable at the DBMS level for it to behave as you want it.