Implementing multiple SQL IN clauses in a complex query using IBatis/MyBatis

There are couple of good posts which explain implementing SQL IN clause in a query using iBatis/MyBatis. (Incase, you are not aware, iBatis now has a new home and new name.)

http://binodsuman.blogspot.com/2009/03/how-to-use-in-clause-in-ibatis.html
http://stackoverflow.com/questions/1637825/how-to-use-an-in-clause-in-ibatis

But, they don't discuss about how to do this for a complex query which has multiple IN clauses or a query which has IN clause and some other parameters also.

For example, let's take a query like this:

select firstname, lastname, nickname, hobby from users WHERE nickname=? and lastname IN (?,?,?) AND hobby IN (?,?,?)

In this case, we could have both the list of lastnames and hobbies in two lists and put the two lists in a map and pass that as param to the query.

List lastNameList= new ArrayList();
lastNameList.add("Smith");
lastNameList.add("Sharma");
lastNameList.add("Sherman");

hobbiesList= new ArrayList();
hobbiesList.add("Cricket");
hobbiesList.add("Football");
hobbiesList.add("Tennis");

Map params = new HashMap();
params.put("nickName", "Nick");
params.put("lastNames", lastNameList);
params.put("hobbies", hobbiesList);
return this.clientProvider.get().queryForList("getUsersByLastNamesListAndHobbiesList", params);

In iBatis/MyBatis side, you can use the two lists in different IN clauses like this:

<select id="getUsersByLastNamesListAndHobbiesList" parameterClass="map" resultClass="UserBean">
SELECT firstname, lastname, nickname, hobby from users
where nickname=#nickName#
<dynamic>
<iterate prepend="and lastname IN" property="lastNameList" open="(" close=")" conjunction=",">
#lastnameList[]#
</iterate>
<iterate prepend="and hobby IN" property="hobbiesList" open="(" close=")" conjunction=",">
#hobbiesList[]#
</iterate>
</dynamic>
</select>

Hope this helps you in implementing SQL IN clauses in a complex query using iBatis/MyBatis.