Trying to use multiple values in SQL "IN" Expression - Doesn't Work

[Mike London]

After extensive searching and experimenting trying to be able to construct, programatically, the list of values for the SQL "IN" clause, I came up with the code below.

(the database in question is, for testing, MySQL 5.5.)

The code below compiles, but when it runs, I get:

INFO: SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
Exception in thread "main" org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [SELECT * FROM CUSTOMER WHERE CUSTOMER_ID IN :ids]; Invalid argument value: java.io.NotSerializableException; nested exception is java.sql.SQLException: Invalid argument value: java.io.NotSerializableException

And, here's the actual Method code:

List Ids = new ArrayList();
Ids.add(1);
Ids.add(2);

MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue(":ids", Ids);

String sql = "SELECT * FROM CUSTOMER WHERE CUSTOMER_ID IN :ids";

List<Customer> customers = this.jdbcTemplate.query(sql, new CustomerRowMapper(), parameters);

return customers;

------

Look forward to hearing any ideas what the problem here might be.

Thanks,

mike

[Shashank Gollapudi]

Try modifying your sql string with braces around :ids

String sql = "SELECT * FROM CUSTOMER WHERE CUSTOMER_ID IN (:ids) "

[Mike London]

Shashank Gollapudi wrote:Try modifying your sql string with braces around :ids

String sql = "SELECT * FROM CUSTOMER WHERE CUSTOMER_ID IN (:ids) "

Appreciate your reply.

Yes, I had it way initially. Unfortunately, I still get the same error.

mike

[Paul Clapham]

I don't know anything about Spring, but the equivalent technique in an ordinary Java PreparedStatement doesn't do what you want either. So I wouldn't be at all surprised to find that it doesn't do what you want in Spring.

[Saifuddin Merchant]

Mike I answered the question on the other thread you started on this. It's recommended that you just create one thread, so same answers are not given out at multiple points.

JDBC prepared statements does not supported "in" clause for multiple values due to SQL injection attack security issue

Any code based on prepared statements (JDBC templates) does not support 'IN' clauses in queries.

Here is a good resource that provide alternate approaches
http://www.javaranch.com/journal/200510/Journal200510.jsp#a2

[Mike London]

Saifuddin Merchant wrote:Mike I answered the question on the other thread you started on this. It's recommended that you just create one thread, so same answers are not given out at multiple points.

JDBC prepared statements does not supported "in" clause for multiple values due to SQL injection attack security issue

Any code based on prepared statements (JDBC templates) does not support 'IN' clauses in queries.

Here is a good resource that provide alternate approaches
http://www.javaranch.com/journal/200510/Journal200510.jsp#a2

Appreciate very much your reply here.

I had read that Spring does "extend" the basic JDBC Prepared Statement capabilities (much like Spring's JDBCTemplate extends JDBC) to actually allow multiple values in a "in (?)" to be added programmatically via a List or HashMap.

The code sample I posted compiles fine as well which leads me to believe it should be possible. However, with much I find in Spring, actually getting seemingly simple things to work as expected can take much longer than expected.

In any case, the very easy and (eventually) obvious workaround I created (avoiding Spring's complexity entirely) is this:

1. Create a String variable, say stringVar.

2. Programmatically build this string with the values for the SQL "IN" clause while parsing the input Request received.

So, stringVar might have values like this: "(1,2,5,6)".

3. On the SQL statement's string variable with the "IN" clause, do a simple sql.replace("?", stringVar).

4. Limit the number of values in the "IN" clause to be less than 100 per the JDBC sepc.

5. Execute the SQL statement however.

Works great. No Spring needed. No Spring error stacks. No confusion. Done.

Thanks for your reply.

-mike