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?

score 32 · Accepted Answer · edited Mar 13 '13 at 17:34

32

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

edited Mar 13 '13 at 17:34

vsingh

6,365
3
53
57

answered Oct 28 '09 at 15:08

jitter

53,475
11
111
124

all this xml makes me want to vomit, but thx for the info very helpful for a project where I have to use ibatis – Rick Dec 09 '11 at 20:04
3

I can't believe I just upvoted an iBatis question/answer. When will this project be over, and I can stop using iBatis? – Steve Duitsman Dec 05 '12 at 15:49
1

Nah, iBatis is fantastic. At least you get to write SQL instead of some other abomination. – WW. Dec 30 '14 at 00:59
Could you please read my similar post, thanks !: http://stackoverflow.com/questions/28343006/how-to-create-an-sql-in-clause-in-ibatis-2-for-an-sql-update-or-sql-select-state – Feb 05 '15 at 14:09

score 12 · Answer 2 · answered Oct 28 '09 at 15:06

12

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>

answered Oct 28 '09 at 15:06

Jonathan Feinberg

44,698
7
80
103

Thanks, you save my life :D – glider Jul 28 '15 at 03:29

score 4 · Answer 3 · edited Oct 06 '11 at 13:28

4

Or:

<select id="select-test" resultMap="MyTableResult" parameterClass="list"> 
 select * from table where
 <iterate property="list" conjunction="OR">
  col1 = #list[]#
 </iterate>
</select>

edited Oct 06 '11 at 13:28

Jason Plank

2,336
5
31
40

answered Apr 29 '10 at 20:10

bastola

41
1

score 0 · Answer 4 · edited Sep 12 '12 at 20:35

0

<select id="select-test" parameterClass="list"resultMap="YourResultMap">
     select * from table where col_1 IN 
     <iterate open="(" close=")" conjunction=",">
      #[]#
    </iterate>
</select>

edited Sep 12 '12 at 20:35

Jack

10,943
13
50
65

answered Sep 12 '12 at 20:10

Venkat R Paruchuri

9
1

1

Care to comment your code? If there is another accepted answer from 2 years ago with 9 upvotes, what does your answer adds that important that can be a usefull answer? – Yaroslav Oct 05 '12 at 10:00

score 0 · Answer 5 · answered Dec 22 '17 at 16:57

An old question, but for the users of MyBatis, the syntax is a bit different:

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  WHERE ID in
  <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
  </foreach>
</select>

Refer to the guide in here.

iamxhu · Answer 6 · 2010-06-23T04:03:10.023

-1

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.

edited Jun 23 '10 at 04:03

answered Jun 23 '10 at 02:32

iamxhu

134
5

this generate something like [aaa,abb,acc] can you suggest how can i change this default `[]` into `()` because oracle doesn't support `[]` – Saif Aug 27 '14 at 05:35

How to use an IN clause in iBATIS?

6 Answers6

Linked