MyBatis中批量插入数据，多重forEach循环

àì夳堔傛蜴生んèń 2022-05-16 01:51 363阅读 0赞

在业务开发过程中，遇到批量插入时，需要进行多重forEach循环的情况。下面是一个实际应用：

public class SysRoleData extends DataEntity<SysRoleData> {
       
       private static final long serialVersionUID = 1L;
       private String kind;      //类别（1：按部门2：按角色）
       private String roleId;    // role_id
       private String roleName;  //角色名称
       private String officeId;   //office_id
       private String officeName;  //部门名称
       private String type;      // 1.品牌 2.品类 3.品牌&品类
       private String dataId;    // 数据ID品牌
       private String dataName;    //数据名称品牌
       private String dataIds;       // 数据ID品类
       private String dataNames;    //数据名称品类
       private String groupNo;  //分组标识
       private String useable; //是否可用(1:可用,0:不可用）
       private String remarks; //备注
       private List<String> officeIdList = Lists.newArrayList();
       private List<String> roleIdList = Lists.newArrayList();
       private List<BrandCategoryVO> dataList = Lists.newArrayList();
       public SysRoleData() {
          super();
       }
    
       public SysRoleData(String id){
          super(id);
       }
    
       public String getKind() {
          return kind;
       }
    
       public void setKind(String kind) {
          this.kind = kind;
       }
    
       @Length(min=0, max=45, message="role_id长度必须介于 0 和 45 之间")
       public String getRoleId() {
          return roleId;
       }
    
       public void setRoleId(String roleId) {
          this.roleId = roleId;
       }
    
       public String getRoleName() {
          return roleName;
       }
    
       public void setRoleName(String roleName) {
          this.roleName = roleName;
       }
    
       public String getOfficeName() {
          return officeName;
       }
    
       public void setOfficeName(String officeName) {
          this.officeName = officeName;
       }
    
       @Length(min=0, max=45, message="office_id长度必须介于 0 和 45 之间")
       public String getOfficeId() {
          return officeId;
       }
    
       public void setOfficeId(String officeId) {
          this.officeId = officeId;
       }
    
       @Length(min=0, max=4, message="品类长度必须介于 0 和 45 之间")
       public String getType() {
          return type;
       }
    
       public void setType(String type) {
          this.type = type;
       }
    
       @NotNull
       public String getDataId() {
          return dataId;
       }
    
       public void setDataId(String dataId) {
          this.dataId = dataId;
       }
    
       public String getDataName() {
          return dataName;
       }
    
       public void setDataName(String dataName) {
          this.dataName = dataName;
       }
    
       public String getDataIds() {
          return dataIds;
       }
    
       public void setDataIds(String dataIds) {
          this.dataIds = dataIds;
       }
    
       public String getDataNames() {
          return dataNames;
       }
    
       public void setDataNames(String dataNames) {
          this.dataNames = dataNames;
       }
    
       public String getUseable() {
          return useable;
       }
    
       public void setUseable(String useable) {
          this.useable = useable;
       }
    
       public String getRemarks() {
          return remarks;
       }
    
       public void setRemarks(String remarks) {
          this.remarks = remarks;
       }
    
       public List<BrandCategoryVO> getDataList() {
          return dataList;
       }
    
       public void setDataList(List<BrandCategoryVO> dataList) {
          this.dataList = dataList;
       }
    
       public List<String> getOfficeIdList() {
          return officeIdList;
       }
    
       public void setOfficeIdList(List<String> officeIdList) {
          this.officeIdList = officeIdList;
       }
    
       public List<String> getRoleIdList() {
          return roleIdList;
       }
    
       public void setRoleIdList(List<String> roleIdList) {
          this.roleIdList = roleIdList;
       }
    
       public String getGroupNo() {
          return groupNo;
       }
    
       public void setGroupNo(String groupNo) {
          this.groupNo = groupNo;
       }
    }

如上所示为一个实体类，会有dataList和roleIdList或officeIdList，在批量插入时从而形成多重循环。

![70][]

上图为列表页面，

![70 1][]

上图为添加页面。部门名称和品牌，品类名称支持多选，而在保存时，需要将其拆分保存。在查询时通过group\_concat函数进行聚合展示在列表页面。

故在批量插入数据时：

<insert id="insert">
       INSERT INTO sys_role_data(
       kind,
       role_id,
       office_id,
       type,
       data_id,
       data_name,
       group_no,
       useable,
       remarks,
       create_date,
       create_by,
       update_date,
       update_by
       )VALUES
       <if test="kind != null and kind == 0">
          <foreach collection="officeIdList" item="officeId" separator=",">
             <foreach collection="dataList" item="data" separator=",">
             (
                #{kind},
                null,
                #{officeId},
                #{type},
                #{data.id},
                #{data.name},
                #{groupNo},
                #{useable},
                #{remarks},
                #{createDate},
                #{createBy.id},
                #{updateDate},
                #{updateBy.id}
             )
             </foreach>
          </foreach>
       </if>
       <if test="kind != null and kind == 1">
          <foreach collection="roleIdList" item="roleId" separator=",">
             <foreach collection="dataList" item="data" separator=",">
             (
                #{kind},
                #{roleId},
                null,
                #{type},
                #{data.id},
                #{data.name},
                #{groupNo},
                #{useable},
                #{remarks},
                #{createDate},
                #{createBy.id},
                #{updateDate},
                #{updateBy.id}
             )
             </foreach>
          </foreach>
       </if>
    </insert>

由上面sql可以看出，根据kind不同，进行相应的双重forEach循环插入数据。

[70]: /images/20220516/2ee7ba0b29e5434fb475365052b03e7b.png
[70 1]: /images/20220516/8093cffddb8b4e54a0ce006b4b15c8db.png