Monday, May 31, 2010

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.

3 comments:

indrajith said...

hi
i need to pass columnname and value
so by using iterate how to build the query

indrajith said...

hi
i need to pass columnname and value as a list format for inserting query
so by using iterate how to build the query

Anonymous said...


Thanks In Advance : Can Anyone help me to convert below MySQL Query into Dynamic Select into ibatis

MYSQl Select Querry:
SELECT * FROM vwbmp.VW_MODEL v WHERE DELETE_FLAG=0 AND MODEL_TYPE='SEDAN' AND GEAR='Automatic' AND ENGINCC_DISPALCEMENT in(1.78, 1.6) AND LANG='CN';

Need to convert it into :

@Select(value = {"SELECT * FROM VW_MODEL WHERE DELETE_FLAG=0 AND MODEL_TYPE=#{model_type} AND GEAR=#{gear} AND ENGINCC_DISPALCEMENT IN (1.78, 1.6) AND RECOMMENDED_RETAIL_PRICE <= #{recommended_retail_price}"})

Seeking for help ....