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" 

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


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

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

This is another example

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

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


  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.