• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Liutauras Vilda
  • Paul Clapham
Sheriffs:
  • paul wheaton
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Piet Souris
Bartenders:
  • Mike London

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

 
Bartender
Posts: 1938
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Bartender
Posts: 1938
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Marshal
Posts: 27531
88
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 608
Firefox Browser Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Bartender
Posts: 1938
17
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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

 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic