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

出处：

http://twit88.com/blog/2008/03/14/ibatis-support-for-arrays-or-list-parameters-in-with-sql-in-keyword/

 

By admin on Mar 14, 2008 in Java, Programming

Related posts in this website for your search

• Java: Insert a BLOB Object Using iBATIS
• Load Data into Database without Creating Java Data Object
• SELECT COUNT in Database
• iBatis and Apache DBCP Connection Pooling
• Java: Alter Oracle Session Information using iBATIS

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>
 



…