Sunday, March 22, 2009

How to use IN clause in iBatis

How to use IN clause in iBatis

1. Table Structure (Table Name: users)

CREATE TABLE 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();
names.add("Bond");
names.add("Josef");
names.add("Robert");
names.add("abcd");

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" />
</resultMap>

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

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

:) :)

5 comments:

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

    ReplyDelete
  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?

    ReplyDelete
  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

    Regards,
    Pramod Tiwari

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

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

    ReplyDelete

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