RSS Feed for This PostCurrent Article

iBatis: Support for Array or List Parameter with SQL IN Keyword

This is a feature available in iBatis but it is not mentioned in the documentation. You can find the example in the iBatis source code under the unit tests.

Let’s said I need to run the following SQL statement

select * from my_table where col_1 in ('1','2','3')

So how do I pass in the values of 1, 2 and 3 ?

In this case you need to pass in a list parameter. The correct iBatis syntax should be

<select id="select-test" resultMap="MyTableResult" 
  parameterClass="list">

select * from my_table where col_1 in
  <iterate open="(" close=")" conjunction=",">
   #[]#
  </iterate>

</select>

And in Java you should pass in a java.util.List. E.g.

List<String> list = new ArrayList<String>(3);
list.add("1");
list.add("2");
list.add("3");
List objs = sqlMapClient.queryForList("select-test",list);

This is another example


<select id="getProducts" parameterClass="Product" 
        resultClass="Product">
  SELECT * FROM Products
  <dynamic prepend="WHERE productType IN ">
    <iterate property="productTypes"
             open="(" close=")"
             conjunction=",">
      productType=#productType#
    </iterate>
  </dynamic>
</select>
…


Trackback URL


RSS Feed for This Post8 Comment(s)

  1. nhm tanveer hossain khan (hasan) | Nov 2, 2008 | Reply

    thanks it was really helpful :)

  2. E Hekkert | Apr 9, 2009 | Reply

    Exactly what i needed. Thanks.

  3. ranz | Jun 22, 2009 | Reply

    how about like this
    select * from my_table where col_1 in (’1′,’2′,’3′) and col_2 = ’4′

  4. Colin | Oct 14, 2009 | Reply

    Thank you! It helps a lot :)

  5. Kalyan | Nov 11, 2009 | Reply

    Hi is there any way I send a generic class as sql parameter and iBatis? Something like Parameters.

    I want to access the properties of iBatis in runtime as the parameters of the query

  6. kou | Nov 23, 2009 | Reply

    how about like this
    select * from my_table where col_1 in (’1′,’2′,’3′) and col_2 = (’11′,’12′,’13′)

  7. SMougenot | Jan 21, 2010 | Reply

    Using ibatis 2.3.2.715 I couldn’t have those exemple to work.
    Debugging the code, I managed to have it working.
    Becarefull, on this version, you can only iterate on List objects (not on Collection).

    Here is the XML portion of the query
    and COL in

    #VALUES[]:VARCHAR#

  8. SMougenot | Jan 21, 2010 | Reply

    <iterate property=”HUBS” open=”(” close=”)” conjunction=”,”>#HUBS[]:VARCHAR#</iterate>

Sorry, comments for this entry are closed at this time.