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.

4 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 ....



Blogger said...

There's SHOCKING news in the sports betting world.

It has been said that any bettor needs to look at this,

Watch this or quit betting on sports...

Sports Cash System - Sports Betting Robot