实体类记录批量导入mysql数据库操作
-
controller
/** * 通过Excel批量插入实体类 * @param list Excel文件记录 * @return ResultBody */ @Override @PostMapping("/batchInsert") public ResultBody batchInsert(@RequestBody List<Entity> list){ logger.info("批量插入实体类记录:{}", JSONUtil.toJsonStr(list)); return ResultBody.success(this.entityService.batchInsert(list)); }
-
service 定义
SelectBean batchInsert(List<Entity> list);
-
SelectBean
import lombok.Data; /** * @Description 下拉列表类,用于后端管理 */ @Data public class SelectBean { /** * 标识 */ private String key; /** * 值 */ private String value; }
-
serviceImpl实现
/** * 通过Excel批量插入实体类 * @param list Excel文件读取的记录 * @return void */ @Override public SelectBean batchInsert(List<Entity> list){ if (list != null && list.size() > 0) { Integer oldSize = list.size(); //自身去重 list = list.stream().collect(Collectors.collectingAndThen( Collectors.toCollection(() -> new TreeSet<>( Comparator.comparing(p -> p.getName() + ";" + ";" + p.getPhone()))), ArrayList::new)); //查询数据库中是否存在相同数据 for (int s = list.size() - 1; s >= 0; s--) { Entity entity1 = new Entity(); entity1.setName(list.get(s).getName()); entity1.setPhone(list.get(s).getPhone()); List<Entitylist> entitylistList = this.entitylistDao.queryAll(entity1); if (entitylistList != null && entitylistList.size() > 0) { list.remove(s); } } //设置id与时间 list.forEach(entitylist -> { entitylistentitylist.setId(IdWorker.getId()); entitylist.setCreateTime(LocalDateTime.now()); }); if (list.size() > 0) { this.entitylistDao.batchInsert(list); } Integer newSize = list.size(); SelectBean selectBean = new SelectBean(); selectBean.setKey(oldSize.toString()); selectBean.setValue(newSize.toString()); return selectBean; } return null; }
-
Dao
/** * 通过Excel批量插入实体类 * @param list Excel文件读取的记录 * @return void */ void batchInsert(@Param("list") List<Entity> list);
-
sql
<insert id="batchInsert"> insert into t_entitylist(id, name, phone, create_time) values <foreach collection="list" item="blacklist" separator=","> (#{entitylist.id},#{entitylist.phoneNum},#{entitylist.createTime}) </foreach> </insert>