“Yeah It’s on. ”
数据批量插入到数据库解决方案
解决多条数据批量插入到数据库
数据表
编写ProMappingGroup，ProMappingGroup是一个假设的Java类，代表要插入数据库的数据结构
1
2
3
4
5
6
7
8
public class ProMappingGroup {
/** 项目id */
private Long proId;
/** 小组id */
private Long groupId;
//省略get、set方法
//省略tostring方法
}
编写GroupDetailsMapper.java
1
2
public int insertProMappingGroup(ProMappingGroup proMappingGroup);
public void insertProMappingGroupBatch(List<ProMappingGroup> proMappingGroups);
编写映射文件GroupDetailsMapper.xml
1
2
3
4
5
6
7
8
9
10
<insert id="insertProMappingGroup" parameterType="ProMappingGroup">
INSERT INTO pro_mapping_group (group_id, pro_id) VALUES (#{groupId}, #{proId})
</insert>
<insert id="insertProMappingGroupBatch" parameterType="List">
INSERT INTO pro_mapping_group (group_id, pro_id)
VALUES
<foreach collection="list" item="item" index="index" separator=",">
(#{item.groupId}, #{item.proId})
</foreach>
</insert>
编写GroupDetailsServiceImpl.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
public int insertProMappingGroup(ProMappingGroup proMappingGroup){
return groupDetailsMapper.insertProMappingGroup(proMappingGroup);
}
/*批量插入*/
@Resource
public SqlSessionFactory sqlSessionFactory;
public void insertProMappingGroupBatch(List<ProMappingGroup> proMappingGroups) {
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH,false);
GroupDetailsMapper mapper =sqlSession.getMapper(GroupDetailsMapper.class);
for (ProMappingGroup proMappingGroup : proMappingGroups){
mapper.insertProMappingGroup(proMappingGroup);
}
sqlSession.commit();
sqlSession.close();
}
编写service接口IGroupDetailsService.java
1
2
3
public int insertProMappingGroup(ProMappingGroup proMappingGroup);
public void insertProMappingGroupBatch(List<ProMappingGroup> proMappingGroups);
编写controller
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
public void allocation(){
Long parentDeptId = SecurityUtils.getDeptId();
List<Long> projectIds = proAllocationService.selectNotAllocationByParentDeptId(parentDeptId);
List<Long> GroupIds = groupDetailsService.selectGroupIdByDeptId(parentDeptId);
List<ProMappingGroup> proMappingGroups = new ArrayList<>();
Collections.shuffle(projectIds);
int groupSize =GroupIds.size();
for (int i = 0;i < projectIds.size();i++){
Long projectId = projectIds.get(i);
Long groupId = GroupIds.get(i % groupSize);
ProMappingGroup proMappingGroup = new ProMappingGroup();
proMappingGroup.setProId(projectId);
proMappingGroup.setGroupId(groupId);
proMappingGroups.add(proMappingGroup);
}
groupDetailsService.insertProMappingGroupBatch(proMappingGroups);
}
