Sql in clause issue with jdbc template

[sirfak]

Hi,

I have query like this

select * form map_info where country_id = 4 and cities in (1,3,4)

Can anyone help me with writing this using Spring JDBC template?

Thanks in advance
sirfak

[trisberg]

If you use the NamedParameterJdbcTemplate then you can use a named placeholder for the list of values and pass in a java.util.List with the values. The template will internally replace the placeholder with the correct number of '?' for the bind variables. You have to limit the number of items in the list to what your JDBC driver supports.

You create a query like this:

String sql = "SELECT ID, NAME, AGE FROM CUSTOMERS WHERE ID IN (:ids)";

and then pass in a java.util.List containing the ids.

MapSqlParameterSource args = new MapSqlParameterSource();
args.addValue("ids", Arrays.asList(new Object[] {new Integer(3), new Integer(4)}));
List<Customer> customers = template.query(sql, args, customerMapper);

[sirfak]

If you use the NamedParameterJdbcTemplate then you can use a named placeholder for the list of values and pass in a java.util.List with the values. The template will internally replace the placeholder with the correct number of '?' for the bind variables. You have to limit the number of items in the list to what your JDBC driver supports.

You create a query like this:

String sql = "SELECT ID, NAME, AGE FROM CUSTOMERS WHERE ID IN (:ids)";

and then pass in a java.util.List containing the ids.

MapSqlParameterSource args = new MapSqlParameterSource();
args.addValue("ids", Arrays.asList(new Object[] {new Integer(3), new Integer(4)}));
List<Customer> customers = template.query(sql, args, customerMapper);

Hello Trisberg,

I understand your point.But if you observe in my query i have two parameter one single value and other is List.
adding the list, I understood from your reply. How do i add one more parameter in the query
select * form map_info where COUNTRY_ID= 4 and CITIES in (1,3,4)


Thanks
sirfak

[trisberg]

So you use two place holders, one for the country_id and one for the cities

String sql = "select * from map_info where COUNTRY_ID = :country and CITIES in (:cities)";
MapSqlParameterSource args = new MapSqlParameterSource();
args.addValue("country", new Integer(4));
args.addValue("cities", Arrays.asList(new Object[] {new Integer(1), new Integer(3), new Integer(4)}));

[sirfak]

So you use two place holders, one for the country_id and one for the cities

String sql = "select * from map_info where COUNTRY_ID = :country and CITIES in (:cities)";
MapSqlParameterSource args = new MapSqlParameterSource();
args.addValue("country", new Integer(4));
args.addValue("cities", Arrays.asList(new Object[] {new Integer(1), new Integer(3), new Integer(4)}));

Thanks a lot! Trsberg.