How to use an IN clause in iBATIS?

Question

I'm using iBATIS to create select statements. Now I would like to implement the following SQL statement with iBATIS:

SELECT * FROM table WHERE col1 IN ('value1', 'value2');

With the following approach, the statement is not prepared correctly and no result returns:

SELECT * FROM table WHERE col1 IN #listOfValues#;

iBATIS seems to restructure this list and tries to interpret it as a string.

How can I use the IN clause correctly?

Answer 1

How about

<select id="foo" parameterClass="Quuxly" resultClass="Flobitz">
    select * from table
    <dynamic prepend="where col1 in ">
        <iterate property="list_of_values" open="('" close="')" conjunction=",  ">
            #list_of_values[]#
        </iterate>
    </dynamic>
</select>

Answer 2

Here's a blog post that answers your question:

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

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

Answer 3

or

select * from table where col1 = #list[]#

Answer 4

You can use it like this:

<select id="select-test" resultMap="MyTableResult" >
select * from my_table where col_1 in
 $listOfValues$
</select>

use the $ in the IN statement.

Answer 5

The last suggestion was really good and simple. I was breaking my head for hrs to figure this out. Thanks to All.

In Mapping.xml

<isNotEmpty prepend="and" property="statusStrArray">
      status in ($statusStrArray$)
 </isNotEmpty>

In DAO Java

map.put("statusStrArray", vo.getStatusString());

where the value of 'statusStrArray' would be 'All',Closed',Open'