MyBatis实现批量插入数据,多重forEach循环
时间：2023-02-18 10:16:09 | 栏目：JAVA代码 | 点击：次
批量插入数据,多重forEach循环
在业务开发过程中，遇到批量插入时，需要进行多重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，在批量插入时从而形成多重循环。
上图为列表页面，
上图为添加页面。部门名称和品牌，品类名称支持多选，而在保存时，需要将其拆分保存。在查询时通过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循环插入数据。
mybatis insert foreach
项目场景
报错 ，找不到参数
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.binding.BindingException: Parameter ‘statusInfoId’ not found. Available parameters are [collection, list]
@Mapper
public interface PatrolRecordMapper extends BaseMapper<PatrolRecord> {
int insertList(@Param(value = "list") List<PatrolRecord> list);
}
mapper 换了很多种写法
<insert id="insertList" parameterType="com.iricto.soft.patrol.entity.PatrolRecord">
insert into patrol_record(status_info_id,route_id,place_name,patrol_time,
patrol_user,patrol_class,`status`)
VALUES
<foreach collection="list" item="patrolRecord" separator=",">
(patrolRecord.#{statusInfoId},
patrolRecord.#{routeId},
patrolRecord.#{placeName},
patrolRecord.#{patrolTime},
patrolRecord.#{patrolUser},
patrolRecord.#{patrolClass},
patrolRecord.#{status})
</foreach>
</insert>
mapper
<insert id="insertList" parameterType="com.iricto.soft.patrol.entity.PatrolRecord">
insert into patrol_record(status_info_id,route_id,place_name,patrol_time,
patrol_user,patrol_class,`status`)
VALUES
<foreach collection="list" item="list"
open="(" separator="," close=")">
list.#{statusInfoId},
list.#{routeId},
list.#{placeName},
list.#{patrolTime},
list.#{patrolUser},
list.#{patrolClass},
list.#{status}
</foreach>
</insert>
最后应该这么写才对 ： mapper
<insert id="insertList" parameterType="com.iricto.soft.patrol.entity.PatrolRecord">
insert into patrol_record(status_info_id,route_id,place_name,patrol_time,
patrol_user,patrol_class,`status`)
VALUES
<foreach collection="list" item="patrolRecord" separator=",">
(
#{patrolRecord.statusInfoId},
#{patrolRecord.routeId},
#{patrolRecord.placeName},
#{patrolRecord.patrolTime},
#{patrolRecord.patrolUser},
#{patrolRecord.patrolClass},
#{patrolRecord.status})
</foreach>
</insert>