多线程处理Excel导入数据入库

末蓝、 2023-07-22 03:43 55阅读 0赞

本次示例是使用springboot版本：2.2.6.RELEASE 数据库使用：Mysql

springboot整合mybatisPlus，模拟导入2.4万条数据。

**目录**

一、目录结构如下图

二、依赖

三、实体类

四、Dao

五、service

五、controller

六、线程类

七、测试

八、测试结果

--------------------

### **一、目录结构如下图** ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjIyODk1MA_size_16_color_FFFFFF_t_70][]

### **二、依赖** ###

<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-data-jpa</artifactId>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-web</artifactId>
    		</dependency>
    
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-devtools</artifactId>
    			<scope>runtime</scope>
    			<optional>true</optional>
    		</dependency>
    		
    		<dependency>
    			<groupId>mysql</groupId>
    			<artifactId>mysql-connector-java</artifactId>
    
    		</dependency>
    
    		<dependency>
    			<groupId>org.projectlombok</groupId>
    			<artifactId>lombok</artifactId>
    			<optional>false</optional>
    		</dependency>
    		<dependency>
    			<groupId>org.apache.poi</groupId>
    			<artifactId>poi</artifactId>
    			<version>3.14</version>
    		</dependency>
    		<dependency>
    			<groupId>org.apache.poi</groupId>
    			<artifactId>poi-ooxml</artifactId>
    			<version>3.14</version>
    		</dependency>
    		<dependency>
    			<groupId>com.baomidou</groupId>
    			<artifactId>mybatis-plus-boot-starter</artifactId>
    			<version>3.1.1</version>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-test</artifactId>
    			<scope>test</scope>
    			<exclusions>
    				<exclusion>
    					<groupId>org.junit.vintage</groupId>
    					<artifactId>junit-vintage-engine</artifactId>
    				</exclusion>
    			</exclusions>
    		</dependency>

### **三、实体类** ###

由于我idea没有lombok插件，虽然使用了lombok包，get和set方法依然不好使。

package com.springboot.po;
    
    import com.baomidou.mybatisplus.annotation.IdType;
    import com.baomidou.mybatisplus.annotation.TableId;
    import com.baomidou.mybatisplus.annotation.TableName;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    import org.springframework.context.annotation.Bean;
    
    import java.util.Date;
    
    @Data
    @TableName("TB_USER")
    @NoArgsConstructor
    public class User {
    
        @TableId(type = IdType.UUID)
        private String id;
        private String name;
        private String sex;
        private String address;
        private String telephone;
        private String type;
    
        private String idNumber;
        private Date createTime;
    
        public String getId() {
            return id;
        }
    
        public void setId(String id) {
            this.id = id;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public String getSex() {
            return sex;
        }
    
        public void setSex(String sex) {
            this.sex = sex;
        }
    
        public String getAddress() {
            return address;
        }
    
        public void setAddress(String address) {
            this.address = address;
        }
    
        public String getTelephone() {
            return telephone;
        }
    
        public void setTelephone(String telephone) {
            this.telephone = telephone;
        }
    
        public String getType() {
            return type;
        }
    
        public void setType(String type) {
            this.type = type;
        }
    
        public String getIdNumber() {
            return idNumber;
        }
    
        public void setIdNumber(String idNumber) {
            this.idNumber = idNumber;
        }
    
        public Date getCreateTime() {
            return createTime;
        }
    
        public void setCreateTime(Date createTime) {
            this.createTime = createTime;
        }
    }

### **四、Dao** ###

package com.springboot.dao;
    
    import com.baomidou.mybatisplus.core.mapper.BaseMapper;
    import com.springboot.po.User;
    import org.apache.ibatis.annotations.Mapper;
    
    
    @Mapper
    public interface UserDao extends BaseMapper<User> {
    
    }

### **五、service** ###

package com.springboot.service;
    
    
    import com.springboot.dao.UserDao;
    import com.springboot.po.User;
    import com.springboot.thread.ImportThread;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    
    import javax.persistence.criteria.CriteriaBuilder;
    import java.util.List;
    import java.util.concurrent.*;
    
    
    @Service
    public class UserService {
    
        @Autowired
        private UserDao userDao;
    
        public void insert(User user){
            userDao.insert(user);
        }
    
        public void saveUser(List<User> list) throws Exception {
            //一个线程处理100条数据
            int count = 100;
            //数据集合大小
            int listSize = list.size();
            //开启的线程数
            int runSize = (listSize / count) + 1;
            //存放每个线程的执行数据
            List<User> newlist = null;
            Integer mun = 0;
            ExecutorService executor = Executors.newFixedThreadPool(runSize);
            CountDownLatch begin = new CountDownLatch(1);
            CountDownLatch end = new CountDownLatch(runSize);
            //循环创建线程
            for (int i = 0; i < runSize; i++) {
                //计算每个线程执行的数据
                if ((i + 1) == runSize) {
                    int startIndex = (i * count);
                    int endIndex = list.size();
                    newlist = list.subList(startIndex, endIndex);
                } else {
                    int startIndex = (i * count);
                    int endIndex = (i + 1) * count;
                    newlist = list.subList(startIndex, endIndex);
                }
                //线程类
                ImportThread mythead = new ImportThread(newlist, begin, end, userDao);
                executor.execute(mythead);
                mun = mythead.getCount();
            }
            while (count == mun){
                break;
            }
            begin.countDown();
            end.await();
            //执行完关闭线程池
            executor.shutdown();
        }
    }

### **五、controller** ###

package com.springboot.controller;
    
    import com.springboot.po.User;
    import com.springboot.service.UserService;
    import com.springboot.utils.FileUtil;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestMethod;
    import org.springframework.web.bind.annotation.RestController;
    
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.List;
    
    @RequestMapping("/userController")
    @RestController
    public class UserController {
    
        @Autowired
        private UserService userService;
    
        @RequestMapping(value = "/import",method = RequestMethod.GET)
        public Integer importData() throws FileNotFoundException {
    
            long  start =  System.currentTimeMillis();
            InputStream in = new FileInputStream("F:\\temp\\测试数据.xls");
            List<Object> datas = FileUtil.formatDataByExcel(in, "测试数据.xls", true, User.class, setCloums());
            long  end  =  System.currentTimeMillis();
            System.out.println("读取Excel消耗时间："+(end-start)+"毫秒");
            List<User> list = new ArrayList<User>();
    
            for (Object object:datas) {
               User user =  (User)object;
               user.setCreateTime(new Date());
                list.add(user);
            }
            try {
                userService.saveUser(list);
            } catch (Exception e) {
                e.printStackTrace();
                System.out.println("多线程异常");
            }
            long  end2  =  System.currentTimeMillis();
            System.out.println("入库消耗时间："+(end2-end)+"毫秒");
            System.out.println("消耗总时间："+(end2-start)+"毫秒");
    
            return list.size();
        }
    
        private List<String> setCloums() {
            List<String> strings = new ArrayList<>();
            strings.add("name");
            strings.add("sex");
            strings.add("address");
            strings.add("telephone");
            strings.add("type");
            strings.add("idNumber");
            return strings;
        }
        @RequestMapping(value = "/test")
        public String welcome(){
            return "Crud Spring Boot Project ! ";
        }
    
    }

### 六、线程类 ###

package com.springboot.thread;
    
    import com.springboot.dao.UserDao;
    import com.springboot.po.User;
    
    import java.util.List;
    import java.util.concurrent.CountDownLatch;
    
    
    public class ImportThread implements Runnable {
    
    
        public ImportThread() {
        }
        UserDao userDao;
        private List<User> list;
        private CountDownLatch begin;
        private CountDownLatch end;
        public Integer count = 0;
    
    
        public Integer getCount() {
            return count;
        }
    
        public void setCount(Integer count) {
            this.count = count;
        }
    
        /**
         * @param list 入库数据
         * @param begin 计时器
         * @param end   计时器
         * @param userDao  数据库连接
         */
        public ImportThread(List<User> list, CountDownLatch begin, CountDownLatch end, UserDao userDao) {
            this.list = list;
            this.begin = begin;
            this.end = end;
            this.userDao=userDao;
        }
    
        @Override
        public void run() {
    
            try {
                for (User user :list) {
                    userDao.insert(user);
                }
                count = 1;
                begin.await();
            } catch (InterruptedException e) {
                e.printStackTrace();
            } finally {
                end.countDown();
            }
        }
    }

## ##

### **七、测试** ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjIyODk1MA_size_16_color_FFFFFF_t_70 1][]

### **八、测试结果** ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjIyODk1MA_size_16_color_FFFFFF_t_70 2][]

2.4万条数据，用了236秒，时间有点久，各路大神有没有更好的优化方案，欢迎来指导。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjIyODk1MA_size_16_color_FFFFFF_t_70]: /images/20230528/ca47d417276549adb2870116bf7a3427.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjIyODk1MA_size_16_color_FFFFFF_t_70 1]: /images/20230528/d18168d2fe9744ffb8b7be9f49305e5b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjIyODk1MA_size_16_color_FFFFFF_t_70 2]: /images/20230528/756b73bbdb79418284fd4b83b4344cfa.png