实体类记录批量导入mysql数据库操作

实体类记录批量导入mysql数据库操作

1. 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));
       }
   

2. service 定义

       SelectBean batchInsert(List<Entity> list);
   

3. SelectBean

   
   import lombok.Data;
   /**
    * @Description 下拉列表类，用于后端管理
    */
   @Data
   public class SelectBean {
       /**
        * 标识
        */
       private String key;
       /**
        * 值
        */
       private String value;
   }
   

4. 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;
       }
   

5. Dao

       /**
        * 通过Excel批量插入实体类
        * @param list Excel文件读取的记录
        * @return void
        */
       void batchInsert(@Param("list") List<Entity> list);
   

6. 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>