aspose file tools*
The moose likes Spring and the fly likes Trying to use multiple values in SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Frameworks » Spring
Bookmark "Trying to use multiple values in SQL "IN" Expression - Doesn Watch "Trying to use multiple values in SQL "IN" Expression - Doesn New topic
Author

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

Mike London
Ranch Hand

Joined: Jul 12, 2002
Posts: 1054
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

Joined: Jan 19, 2012
Posts: 33
Try modifying your sql string with braces around :ids

String sql = "SELECT * FROM CUSTOMER WHERE CUSTOMER_ID IN (:ids) "
Mike London
Ranch Hand

Joined: Jul 12, 2002
Posts: 1054
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
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

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

Joined: Feb 08, 2009
Posts: 605

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


Cheers - Sam.
Twisters - The new age Java Quiz || My Blog
Mike London
Ranch Hand

Joined: Jul 12, 2002
Posts: 1054
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

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Trying to use multiple values in SQL "IN" Expression - Doesn't Work