Announcement Announcement Module
Collapse
No announcement yet.
NamedParameterJdbcTemplate usage for multi-column IN-clause Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • NamedParameterJdbcTemplate usage for multi-column IN-clause

    All,
    I'm looking for a way to use the NamedParameterJdbcTemplate to bind data dynamically to a multi-column IN-clause. Here's an example:


    SELECT * FROM employee WHERE (firstname, lastname) IN ( ('Joe', 'Smith'), ('Sally', 'Johnson') )

    It is important to note that this query is not the same as...

    SELECT * FROM employee WHERE firstname IN ('Joe', 'Sally') AND lastname IN ('Smith', 'Johnson')

    That query would return "Sally Smith" and "Joe Johnson" rows erroneously, in addition to the correct "Joe Smith" and "Sally Johnson" results.

    Any thoughts? Thanks,
    Brian

  • #2
    It's not supported now, but I would consider adding this since it could be useful. You would have to pass in a Collection containing a number of Collections. That way we could loop over the collections and add placeholders for the expression lists. Can you raise a JIRA issue for this?

    Comment


    • #3
      Opened JIRA new feature request

      Thanks, Thomas! I have opened JIRA 3610 here:

      http://opensource.atlassian.com/proj...rowse/SPR-3610

      Thanks again,
      Brian

      Comment


      • #4
        Hi,

        Now that i see this issue is fixed, how to use it ? I have come across this situation. I have a query like this...

        select * from employee where (dept_no, name) in ((d1, n1),(d2,n2)..........)

        The combination is not finite, i wouldn't know how many combinations. How to implement this situation with fix provided ?

        Comment


        • #5
          Originally posted by prashant_spring View Post
          Hi,

          Now that i see this issue is fixed, how to use it ? I have come across this situation. I have a query like this...

          select * from employee where (dept_no, name) in ((d1, n1),(d2,n2)..........)

          The combination is not finite, i wouldn't know how many combinations. How to implement this situation with fix provided ?
          Try the following:

          Example:

          String query = SELECT * FROM TABLE WHERE (A,B) IN ( :listofListsContainingAandBvalues )

          Map<String, Object> mappedParams = new HashMap<String, Object>();

          ....
          StringBuilder stringBuilder = new StringBuilder(); // Use StringBuffer if thread safety is a concern.


          for (ABObject abObject : ABObjectList) {
          stringBuilder.append("(:valSet" + i + "),");
          mappedParams.put("valSet" + i++, new ArrayList<Object>(Arrays.asList(new Long[] {ABObjectList.getA(),ABObjectList.getB()} )));

          }

          String multiColumnParams = stringBuilder.substring(0,stringBuilder.toString() .length() - 1);//to remove last comma

          String queryWithParams = query.replace(":listofListsContainingAandBvalues", multiColumnParams);


          NamedParameterJdbcTemplate() usage that works ->

          queryForList(queryWithParams , mapppedParams);

          OR

          query(queryWithParams , mappedParams, relevantRowMapper())

          Hope this helps!

          -Utsav

          Comment


          • #6
            Usage example

            Originally posted by prashant_spring View Post
            Hi,

            Now that i see this issue is fixed, how to use it ? I have come across this situation. I have a query like this...

            select * from employee where (dept_no, name) in ((d1, n1),(d2,n2)..........)

            The combination is not finite, i wouldn't know how many combinations. How to implement this situation with fix provided ?
            Try the following:

            Example:

            String query = SELECT * FROM TABLE WHERE (A,B) IN ( :listofListsContainingAandBvalues )

            Map<String, Object> mappedParams = new HashMap<String, Object>();

            ....
            StringBuilder stringBuilder = new StringBuilder(); // Use StringBuffer if thread safety is a concern.


            for (ABObject abObject : ABObjectList) {
            stringBuilder.append("(:valSet" + i + "),");
            mappedParams.put("valSet" + i++, new ArrayList<Object>(Arrays.asList(new Long[] {ABObjectList.getA(),ABObjectList.getB()} )));

            }

            String multiColumnParams = stringBuilder.substring(0,stringBuilder.toString() .length() - 1);//to remove last comma

            String queryWithParams = query.replace(":listofListsContainingAandBvalues", multiColumnParams);


            NamedParameterJdbcTemplate() usage that works ->

            queryForList(queryWithParams , mapppedParams);

            OR

            query(queryWithParams , mappedParams, relevantRowMapper())

            Hope this helps!

            -Utsav

            Comment

            Working...
            X