MyBatis实现批量插入数据,多重forEach循环
批量插入数据,多重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>
上一篇:基于SpringMVC @RequestMapping的参数和用法
栏 目:JAVA代码
下一篇:netty服务端辅助类ServerBootstrap创建逻辑分析
本文标题:MyBatis实现批量插入数据,多重forEach循环
本文地址:http://www.codeinn.net/misctech/225968.html