Announcement Announcement Module
Collapse
No announcement yet.
Sql in clause issue with jdbc template Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Sql in clause issue with jdbc template

    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

  • #2
    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:

    Code:
    String sql = "SELECT ID, NAME, AGE FROM CUSTOMERS WHERE ID IN (:ids)";
    and then pass in a java.util.List containing the ids.

    Code:
    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);

    Comment


    • #3
      Originally posted by trisberg View Post
      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:

      Code:
      String sql = "SELECT ID, NAME, AGE FROM CUSTOMERS WHERE ID IN (:ids)";
      and then pass in a java.util.List containing the ids.

      Code:
      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

      Comment


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

        Code:
        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)}));

        Comment


        • #5
          Originally posted by trisberg View Post
          So you use two place holders, one for the country_id and one for the cities

          Code:
          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.

          Comment

          Working...
          X