Announcement Announcement Module
Collapse
No announcement yet.
IN clause with JdbcTemplate Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • IN clause with JdbcTemplate

    Hi

    Need bit of help with JdbcTemplate.
    I have have a sql query something like Selct * from bla where id in ( ? )

    ? needs to be replaced with list of Integers with i store in an ArrayList.

    How do i tell jdbcTemplate to do this for me/.


    List objects = jdbcTempalate.query(SQL,new Object[]{listOfIntegers},myRowMapper);


    Even if i pass the args typ as an Types.Array, the prepairedstatement setter only using the single value from the list even if the list contains more than 1.

    So is it possible to use jdbcTemplate methods calls to work with IN clause ??

    thanks in advance

  • #2
    I am not sure if I understand your use case correctly, but I tried the following as a comparable scenario:

    Code:
    String numbers = "1,2,3";
    				
    String sql = "SELECT * FROM TEST where id IN ("+numbers+")";
    		
    List<TEST> tests = simpleJdbcTemplate.query(sql, ParameterizedBeanPropertyRowMapper.newInstance(TEST.class));
    		
    System.out.println("tests.size(): "+tests.size());
    This prints: "tests.size(): 3"

    However, if I try the following:

    Code:
    String numbers = "1,2,3";
    				
    String sql = "SELECT * FROM TEST where id IN (?)";
    		
    List<TEST> tests = simpleJdbcTemplate.query(sql, ParameterizedBeanPropertyRowMapper.newInstance(TEST.class),numbers);
    		
    System.out.println("tests.size(): "+tests.size());
    this prints: "tests.size(): 1" and returns only the first row (with id=1).

    Is this the problem you are referring to? I think this may be a bug. Can you open a Jira entry on this?

    Comment


    • #3
      It isn't a bug. There should be the same number of question marks in your query as there are elements in your list.

      So if you have 3 elements your query should read

      Code:
      String sql = "select * from test where id in (?, ?, ?)";
      The JdbcTemplate will iterate of your collection/array for the amount of ? you have. That is the way dynamic/prepared statement work, a question mark can only be replaced by 1 single string. So replacing it with "1, 2, 3" isn't the same as replacing it with "1", "2", "3".

      Also if the ? would be dynamically expanded it could screw up other placeholders in your query. Next to that the number of ? is limited also, the limit depends on your db provider (and is in most cases only detectable by some illusive exception thrown from your database) so it is nearly impossible to handle that in a nice way.

      Comment


      • #4
        Ok, that makes sense. Sorry for assuming this was incorrect behavior.

        So how do you cater for cases where you may need to keep the number of parameters dynamic as in this case?

        Comment


        • #5
          To be able to pass in a variable list of ids you need to use the named parameter support in NamedParameterJdbcTemplate. Just keep the number of ids to a reasonable number since most jdbc drivers/databases have a hard limit for how many items are allowed in the in clause list.

          Your query code would look something like:

          Code:
          List listOfIntegers = Arrays.asList(new Integer[] {1, 2, 3});
          List objects = 
              namedParameterJdbcTemplate.query("select * from bla where id in ( :ids )",
                      Collections.singletonMap("ids", listOfIntegers),
                      myRowMapper);

          Comment


          • #6
            Originally posted by trisberg View Post
            To be able to pass in a variable list of ids you need to use the named parameter support in NamedParameterJdbcTemplate. Just keep the number of ids to a reasonable number since most jdbc drivers/databases have a hard limit for how many items are allowed in the in clause list.

            Your query code would look something like:

            Code:
            List listOfIntegers = Arrays.asList(new Integer[] {1, 2, 3});
            List objects = 
                namedParameterJdbcTemplate.query("select * from bla where id in ( :ids )",
                        Collections.singletonMap("ids", listOfIntegers),
                        myRowMapper);
            It works,thanks
            Last edited by janwen; Feb 8th, 2012, 01:54 AM.

            Comment

            Working...
            X