mybatis錯誤之in查詢 <foreach>循環問題
in查詢 <foreach>循環問題
當我在做in查詢的時候,發現在網上有很多種寫法:
接口
public List<CaseReview > findList(CaseReview caseReview);
類
public class CaseReview{ private String caseNo; private List<String> caseNos;//caseNo //===gettter,setter省略=== }
1.我就隨便用瞭一種傳list,再foreach循環
具體如下:
t.case_no in <foreach item="item" index="index" collection="caseNos" open="(" separator="," close=")"> #{item} </foreach>
然而報:
org.apache.ibatis.reflection.ReflectionException: There is no getter for property named '__frch_item_0' in 'class
於是解決辦法就出來瞭:
就是將 #{item} 改為 ‘${item}’就搞定瞭
<foreach item="item" index="index" collection="caseNos" open="(" separator="," close=")"> '${item}' </foreach>
如果你非要用#也不是不可以
<foreach item="item" index="index" collection="caseNos" open="(" separator="," close=")"> #{caseNos[${index}]} </foreach>
還有兩種in查詢方法,其實都在做foreach循環遍歷
2.findByCaseNos(Long[] caseNos)
如果參數的類型是Array,則在使用時,collection屬性要必須指定為 array
caseNos in <foreach item="item" index="index" collection="array" open="(" separator="," close=")"> #{caseNos} </foreach>
3.findByCaseNos(String name, Long[] caseNos)
當查詢的參數有多個時:
這種情況需要特別註意,在傳參數時,一定要改用Map方式, 這樣在collection屬性可以指定名稱
Map<String, Object> params = new HashMap<String, Object>(); params.put("name", name); params.put("ids", caseNos); mapper.findByIdsMap(params);
caseNos in <foreach item="item" index="index" collection="caseNos" open="(" separator="," close=")"> #{item} </foreach>
in查詢和foreach標簽使用
Mybatis中的foreach的主要用在構建in條件中,它可以在SQL語句中進行迭代一個集合。
foreach元素的屬性主要有 item,index,collection,open,separator,close:
item
:表示集合中每一個元素進行迭代時的別;index
:指定一個名字,用於表示在迭代過程中,每次迭代到的位置;open
:表示該語句以什麼開始;separator
:表示在每次進行迭代之間以什麼符號作為分隔 符;close
:表示以什麼結束;collection
:在使用foreach的時候最關鍵的也是最容易出錯的就是collection屬性,該屬性是必須指定的,但是在不同情況 下,該屬性的值是不一樣的,主要有一下3種情況:
1. 如果傳入的是單參數且參數類型是一個List的時候,collection屬性值為list
2. 如果傳入的是單參數且參數類型是一個array數組的時候,collection的屬性值為array
3. 如果傳入的參數是多個的時候,我們就需要把它們封裝成一個Map瞭,當然單參數也可以封裝成map,實際上如果你在傳入參數的時候,在breast裡面也是會把它封裝成一個Map的,map的key就是參數名,所以這個時候collection屬性值就是傳入的List或array對象在自己封裝的map裡面的key
下面分別來看看上述三種情況的示例代碼:
1.單參數List的類型
<select id="dynamicForeachTest" resultType="Blog"> select * from t_blog where id in <foreach collection="list" index="index" item="item" open="(" separator="," close=")"> #{item} </foreach> </select>
上述collection的值為list,對應的Mapper是這樣的:
public List<Blog> dynamicForeachTest(List<Integer> ids);
測試代碼:
@Test public void dynamicForeachTest() { SqlSession session = Util.getSqlSessionFactory().openSession(); BlogMapper blogMapper = session.getMapper(BlogMapper.class); List<Integer> ids = new ArrayList<Integer>(); ids.add(1); ids.add(3); ids.add(6); List<Blog> blogs = blogMapper.dynamicForeachTest(ids); for (Blog blog : blogs){ System.out.println(blog); } session.close(); }
2.單參數Array的類型
<select id="dynamicForeach2Test" resultType="Blog"> select * from t_blog where id in <foreach collection="array" index="index" item="item" open="(" separator="," close=")"> #{item} </foreach> </select>
上述collection為array,對應的Mapper代碼:
public List<Blog> dynamicForeach2Test(int[] ids);
對應的測試代碼:
@Test public void dynamicForeach2Test() { SqlSession session = Util.getSqlSessionFactory().openSession(); BlogMapper blogMapper = session.getMapper(BlogMapper.class); int[] ids = new int[] {1,3,6,9}; List<Blog> blogs = blogMapper.dynamicForeach2Test(ids); for (Blog blog : blogs){ System.out.println(blog); } session.close(); }
3.多參數封裝成Map的類型
<select id="dynamicForeach3Test" resultType="Blog"> select * from t_blog where title like "%"#{title}"%" and id in <foreach collection="ids" index="index" item="item" open="(" separator="," close=")"> #{item} </foreach> </select>
上述collection的值為ids,是傳入的參數Map的key,對應的Mapper代碼:
public List<Blog> dynamicForeach3Test(Map<String, Object> params);
對應測試代碼:
@Test public void dynamicForeach3Test() { SqlSession session = Util.getSqlSessionFactory().openSession(); BlogMapper blogMapper = session.getMapper(BlogMapper.class); final List<Integer> ids = new ArrayList<Integer>(); ids.add(1); ids.add(2); ids.add(3); ids.add(6); Map<String, Object> params = new HashMap<String, Object>(); params.put("ids", ids); params.put("title", "中國"); List<Blog> blogs = blogMapper.dynamicForeach3Test(params); for (Blog blog : blogs) System.out.println(blog); session.close(); }
4.嵌套foreach的使用
map 數據如下 Map<String,List<Long>>
測試代碼如下:
public void getByMap(){ Map<String,List<Long>> params=new HashMap<String, List<Long>>(); List<Long> orgList=new ArrayList<Long>(); orgList.add(10000003840076L); orgList.add(10000003840080L); List<Long> roleList=new ArrayList<Long>(); roleList.add(10000000050086L); roleList.add(10000012180016L); params.put("org", orgList); params.put("role", roleList); List<BpmDefUser> list= bpmDefUserDao.getByMap(params); System.out.println(list.size()); }
dao代碼如下:
public List<BpmDefUser> getByMap(Map<String,List<Long>> map){ Map<String,Object> params=new HashMap<String, Object>(); params.put("relationMap", map); return this.getBySqlKey("getByMap", params); }
xml代碼如下:
<select id="getByMap" resultMap="BpmDefUser"> <foreach collection="relationMap" index="key" item="ent" separator="union"> SELECT * FROM BPM_DEF_USER where RIGHT_TYPE=#{key} and OWNER_ID in <foreach collection="ent" item="id" separator="," open="(" close=")"> #{id} </foreach> </foreach> </select>
index 作為map 的key。item為map的值,這裡使用瞭嵌套循環,嵌套循環使用ent。
《項目實踐》
@Override public Container<Map<String,Object>> findAuditListInPage( Map<String, Object> params) { //1、參數組裝 PageModel pageMode = new PageModel(); try { if(params.get("page")!=null){ pageMode.setPage(Integer.parseInt(params.get("page").toString())); } if(params.get("rows")!=null){ pageMode.setRows(Integer.parseInt(params.get("rows").toString())); } } catch (Exception e) { Assert.customException(RestApiError.COMMON_ARGUMENT_NOTVALID); } //分頁條件組裝 pageMode.putParam(params); if(params.get("startCreateTime") !=null){ Date parse = DateUtil.parse(params.get("startCreateTime").toString(), DateUtil.yyyyMMddHHmmss); params.put("startCreateTime",parse); } if(params.get("endCreateTime") !=null){ Date parse = DateUtil.parse(params.get("endCreateTime").toString(), DateUtil.yyyyMMddHHmmss); params.put("endCreateTime",parse); } if(params.get("type") !=null){ //type可以多選 String typeString = params.get("type").toString(); String typeArray [] = typeString.split(","); params.put("type", typeArray); } if(params.get("state") !=null){ //state可以多選 String stateString = params.get("state").toString(); if(stateString.equals(DictConstants.APPLICATION_STATE.AUDITING) ||stateString.equals(DictConstants.APPLICATION_STATE.WAITING_AUDIT)){ stateString = "waitingAudit,auditing"; } String stateArray [] = stateString.split(","); params.put("state", stateArray); } //分頁數據組裝 Container<Map<String,Object>> container = new Container<Map<String,Object>>(); List<Map<String,Object>> auditModelList = cmApplicationRepo.findAuditList(params); for(Map<String,Object> audit:auditModelList){ //設置是否關註過 Long auditId = Long.parseLong(audit.get("auditId").toString()); Long auditPersonId = Long.parseLong(params.get("auditPersonId").toString()); Map<String, Object> followMap = new HashMap<String,Object>(); followMap.put("sourceType", DictConstants.FOLLOW_SOURCE_TYPE.FOLLOW_APPLICATION); followMap.put("sourceId", auditId); followMap.put("userId", auditPersonId); List<BizFollowModel> followList = bizFollowService.find(followMap); if(followList!= null && followList.size()>0){ audit.put("isFollow", "true"); }else{ audit.put("isFollow", "false"); } } container.setList(auditModelList); container.setTotalNum(cmApplicationRepo.countAuditListNumber(params)); return container; }
DAO
@Override public List<Map<String,Object>> findAuditList(Map<String, Object> map) { return findList("getAuditList", map); }
xml
<!-- 查詢申請列表--> <select id="getApplicationList" resultType="java.util.Map" parameterType="map"> select a.ID AS id, a.STATE AS stateCode, b.DICT_VALUE AS stateValue, a.ITEM AS itemCode, c.DICT_VALUE AS itemValue, a.TYPE AS typeCode, d.DICT_VALUE AS typeValue, a.APP_PERSON_ID AS appPersonId, a.CREATE_TIME AS createTime from cm_application a LEFT JOIN cm_dict_type b on a.STATE = b.DICT_CODE AND b.TYPE = 'Application_State' LEFT JOIN cm_dict_type c on a.ITEM = c.DICT_CODE LEFT JOIN cm_dict_type d on a.TYPE = d.DICT_CODE where 1=1 <if test="item != null" > and a.ITEM = #{item,jdbcType=VARCHAR} </if> <if test="type != null" > and a.TYPE IN <foreach item="typeArray" index="index" collection="type" open="(" separator="," close=")"> #{typeArray} </foreach> </if> <if test="appPersonId != null" > and a.APP_PERSON_ID = #{appPersonId,jdbcType=BIGINT} </if> <if test="state != null" > and a.STATE IN <foreach item="stateArray" index="index" collection="state" open="(" separator="," close=")"> #{stateArray} </foreach> </if> <!-- 分頁查詢時,要選擇createTime在starCreateTime和endCreatetTime之間的記錄 --> <if test="startCreateTime != null" > and a.CREATE_TIME >= #{startCreateTime,jdbcType=TIMESTAMP} </if> <if test="endCreateTime != null" > and a.CREATE_TIME <= #{endCreateTime,jdbcType=TIMESTAMP} </if> order by a.ID <include refid="Paging" /> </select>
以上為個人經驗,希望能給大傢一個參考,也希望大傢多多支持WalkonNet。
推薦閱讀:
- 基於mybatis 動態SQL查詢總結
- mybatis in查詢條件過長的解決方案
- MyBatis傳入參數為List對象的實現
- mybatis in查詢傳入String方式
- mybatis foreach 屬性及其三種使用情況詳解