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,实际上如果你在传入参数的时候,在里面也是会把它封装成一个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> params=new HashMap >(); List orgList=new ArrayList (); orgList.add(10000003840076L); orgList.add(10000003840080L); List roleList=new ArrayList (); roleList.add(10000000050086L); roleList.add(10000012180016L); params.put("org", orgList); params.put("role", roleList); List list= bpmDefUserDao.getByMap(params); System.out.println(list.size()); } dao代码如下:
public ListgetByMap(Map > map){ Map params=new HashMap (); params.put("relationMap", map); return this.getBySqlKey("getByMap", params); } xml代码如下:
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_CODEwhere 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>