Mybatis foreach嵌套foreach

• mybatis框架简单搭建_Oracle，如果已搭建mybatis框架，则忽略

• sql语句,加入declare 和 select count(*) into v_rowcount from dual;目的是为了防止出现，begin end; 这种情况，导致后台报错。

<insert id="batchDoubleForeachInsertTest">
	 declare 
  		 v_rowcount number(10);
	begin
	  	 select count(*) into v_rowcount from dual;
	     <foreach collection="items" item="aa" index="index">
	          <foreach  collection="aa.items" item="it" index="index">
	            	<if test="aa.biz_flow_uuid != null">
	             		insert into aa(a,b) values(#{it,jdbcType=VARCHAR},#{aa.biz_flow_uuid,jdbcType=VARCHAR});
	            	</if>
	          </foreach>
	     </foreach>
	end;
</insert>

• 后台传参及其调用

//拼装数据进行插入
Map<String,Object> attribute = new HashMap<String,Object>();
List<Map<String,Object>> data = new ArrayList<Map<String,Object>>();
Map<String,Object> map = new HashMap<String,Object>();
map.put("biz_flow_uuid", "a");
map.put("items", "1,2".split(","));
data.add(map);
//注意：少了biz_flow_uuid
Map<String,Object> map1 = new HashMap<String,Object>();
map1.put("items", "3,4".split(","));
data.add(map1);
Map<String,Object> map2 = new HashMap<String,Object>();
map2.put("biz_flow_uuid", "b");
map2.put("items", "5,6".split(","));
data.add(map2);
attribute.put("items", data);
int count = sqlSession.insert("com.kyl.oracle.batchDoubleForeachInsertTest", attribute);
System.out.println("countAdmin, count:" + count);  

• 最终拼接成的sql

begin 
	insert into aa(a,b) values( '1' ,'a');
	insert into aa(a,b) values( '2' ,'a');
	insert into aa(a,b) values( '5' ,'b');
	insert into aa(a,b) values( '6' ,'b');
end;

特别注意

1. 如果使用如下方法则可能会报错

<insert id="batchDoubleForeachInsertTest">
	 insert into aa(a,b)
     <foreach close=")" collection="items" item="aa" index="index"  open="(" separator="union">
    		<foreach  collection="aa.items" item="it" index="index"  separator="union">
            		<if test="aa.biz_flow_uuid != null">
            			select #{it,jdbcType=VARCHAR},#{aa.biz_flow_uuid,jdbcType=VARCHAR} from dual
            		</if>
          	</foreach>
     </foreach>
</insert>

2. 最终拼接成的sql，会在中间少了个union

 insert into aa(a,b)(
	  select '1','a' from dual union
	  select '2','a' from dual 
	  select '5','b' from dual union
	  select '6','b' from dual 
 )

3. 原因分析
   这种方法最好不要使用，我在实际项目开发中就出现上面的问题，但是在自己搭的项目中有没复现，具体原因没有去细查。所以个人建议使用最上面的sql语句是最保险的。