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 Post1 Comment(s)

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

    thanks it was really helpful :)

RSS Feed for This PostPost a Comment