Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Mike London
Ranch Hand
Posts: 1185
4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Try modifying your sql string with braces around :ids

String sql = "SELECT * FROM CUSTOMER WHERE CUSTOMER_ID IN (:ids) "
 
Mike London
Ranch Hand
Posts: 1185
4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Pie
Posts: 20768
30
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 607
Firefox Browser Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1185
4
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic