Sunday, March 22, 2009

How to use IN clause in iBatis

How to use IN clause in iBatis

1. Table Structure (Table Name: users)

username varchar(15) NOT NULL,
name varchar(255) NOT NULL,
password varchar(15) NOT NULL,
CONSTRAINT users_pkey PRIMARY KEY (username))

2. Insert data in this table

3. DAO Methos to fetch username with IN clause
public void getAllUserByName() {
List users;
try {
List names = new ArrayList();

users = sqlMap.queryForList("getAllUserByName",names);
for (int i = 0; i < user =" (User)">NOTE: If you use IN clause then iBatis expects that either you will send List or array. For example you can use names as array like String names[] = {"Robert","Bush","abcd"};

4. Code in SQL config file

<resultMap id="UserMap" class="com.domain.User">
<result property="name" column="username" />
<result property="pass" column="password" />

<select id="getAllUserByName" resultMap="UserMap">
SELECT username, password from users
WHERE username IN
<iterate open="(" close=")" conjunction=",">

NOTE : In select tag, no need to use parameterClass, it will take either List or array as you send during call the sqlMap.queryForList("getAllUserByName",names);

:) :)


  1. Thanks for the wonderful straight forward solution it worked...

  2. How would you do it if you had more than one IN? For example, a Where clause like this:

    WHERE col1 IN (?,?)
    AND col2 IN (?,?)

    Is there a way to pass in a map of string values for each IN?

  3. How to use Function in Ibatis

    Hi Binod,
    i like ur this publish using in clause
    can u tell me how to use Table value function in ibatis how to call using sqlconfig file and how to get that Query Result in Java Code

    please tell me i am not able to use Function and Stored Procedure in Ibatis
    Please Help Me............
    Example using Function and Store Procedure in Ibatis

    Pramod Tiwari

  4. hi can u give the example for batch insert by using iterator

  5. hi can u give the sql batch insert by using iterator


You can put your comments here (Either feedback or your Question related to blog)