iBatis: Support for Array or List Parameter with SQL IN Keyword
By admin on Mar 14, 2008 in Java, Programming
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>
…
Popularity: 10% [?]
nhm tanveer hossain khan (hasan) | Nov 2, 2008 | Reply
thanks it was really helpful
E Hekkert | Apr 9, 2009 | Reply
Exactly what i needed. Thanks.
ranz | Jun 22, 2009 | Reply
how about like this
select * from my_table where col_1 in (’1′,’2′,’3′) and col_2 = ’4′
Colin | Oct 14, 2009 | Reply
Thank you! It helps a lot
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
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′)
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#
SMougenot | Jan 21, 2010 | Reply
<iterate property=”HUBS” open=”(” close=”)” conjunction=”,”>#HUBS[]:VARCHAR#</iterate>
Hey | Jan 31, 2011 | Reply
Thank you its really helpful
mangesh | Oct 13, 2011 | Reply
How can we achieve if i want to pass two parameters to the query
select * from my_table,my_table2 where col_1 in (’1′,’2′,’3′)
and my_table2.column in ( ‘A’, ‘B’, ‘C’ )
i.e. I want to pass 1,2,3 and A,B,C..
bfmv | Dec 29, 2011 | Reply
You can do multiple parameters by enclosing them in a Map, where the map key is the iterate property
Example:
Map parameters=new HashMap();
parameters.put(“param1″,some list);
parameters.put(“param2″,another list);
IBatis XML
…
where param1 in
#param1[]#
and
param2 in
#param2[]#
bfmv | Dec 29, 2011 | Reply
the iterate tags were stripped by the site.
< iterate property=”param1” open=”(” close=”)” conjunction=”,” > #param1[]# </iterate >