excel表数据多线程导入数据库

Student table

一个学生表

excel表

Student实体

@Data
public class Student {
    private int id;
    private int studentId;
    private int teacherId;
    private String studentName;
    private int studentAge;
    private int studentSex;
}

StudentMapper

void studentAdd(int id,int studentId,int teacherId,String studentName,int studentAge,int studentSex);

 <insert id="studentAdd" >
        insert into student values(#{arg0},#{arg2},#{arg1},#{arg3},#{arg4},#{arg5})
</insert>

Service

public void saveExcelStudent(List<Student> studentList) throws InterruptedException {
        //一个线程处理100条数据
        int count = 100;
        //数据集合大小
        int listSize = studentList.size();
        //开启的线程数
        int threadSize = (listSize / count) +1;
        //存放每个线程的执行数据
        List<Student> newList = null;
        Integer mun = 0;
        ExecutorService executor = Executors.newFixedThreadPool(threadSize);
        CountDownLatch begin = new CountDownLatch(1);
        CountDownLatch end = new CountDownLatch(threadSize);
        //循环创建线程
        for(int i = 0;i < threadSize;i++){
            //startIndex是一个线程的第一个元素在studentList的索引，endIndex是一个线程最后一个元素在studentList的索引
            //newList 将一个线程在studentList对应的student放入newList
            if((i + 1) == threadSize){
                int startIndex = (i * count);
                int endIndex = studentList.size();
                newList = studentList.subList(startIndex,endIndex);
            }else{
                int startIndex = (i * count);
                int endIndex = (i + 1) * count;
                newList = studentList.subList(startIndex,endIndex);
            }
            //线程类
            ImportThread mythread = new ImportThread(newList,begin,end,studentMapper);
            executor.execute(mythread);
            mun = mythread.getCount();
        }
        while (count == mun){
            break;
        }
        begin.countDown();
        end.await();
        //执行完关闭线程池
        executor.shutdown();
    }

Controller

 @RequestMapping(value = "/import",method = RequestMethod.GET)
    public Integer importData() throws FileNotFoundException {

        //从excel读取数据
        long  start =  System.currentTimeMillis();
       // InputStream in = new FileInputStream("F:\\temp\\测试数据.xls");
        List<Student> studentList = ExcelToModelListUtil.getList("d:/student.xls");
        long  end  =  System.currentTimeMillis();


        try {
            studentService.saveExcelStudent(studentList);
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("多线程异常");
        }
        long  end2  =  System.currentTimeMillis();
        System.out.println("读取Excel消耗时间："+(end-start)+"毫秒");
        System.out.println("入库消耗时间："+(end2-end)+"毫秒");
        System.out.println("消耗总时间："+(end2-start)+"毫秒");

        return studentList.size();
    }

   

ExcelToModelListUtil

导入excel工具类，excel数据转studentList，传入excel文件路径，返回studentList

public class ExcelToModelListUtil {

    private ExcelToModelListUtil(){
    }
    public static List<Student> getList(String fileName) {
        //需要解析的Excel文件
        File file = new  File(fileName);
        List<Student> studentList = new ArrayList<>();
        try{
            //获取工作簿
            FileInputStream fs= FileUtils.openInputStream(file);
            HSSFWorkbook workbook=new HSSFWorkbook(fs);
            //获取第一个工作表
            HSSFSheet hs=workbook.getSheetAt(0);
            //获取Sheet的第一个行号和最后一个行号
            int last=hs.getLastRowNum();
            int first=hs.getFirstRowNum();
            //遍历获取单元格里的信息
            for (int i = first+1; i <= last; i++) {
                HSSFRow row=hs.getRow(i);
                int firstCellNum=row.getFirstCellNum();//获取所在行的第一个行号
                int lastCellNum=row.getLastCellNum();//获取所在行的最后一个行号
                Student student = new Student();

                student.setId(Integer.parseInt(String.valueOf(getValue(row,firstCellNum++))));
                student.setStudentId(Integer.parseInt(String.valueOf(getValue(row,firstCellNum++))));
                student.setTeacherId(Integer.parseInt(String.valueOf(getValue(row,firstCellNum++))));
                student.setStudentName((String) getValue(row,firstCellNum++));
                student.setStudentAge(Integer.parseInt(String.valueOf(getValue(row,firstCellNum++))));
                student.setStudentSex(Integer.parseInt(String.valueOf(getValue(row,firstCellNum++))));
                studentList.add(student);
            }
            System.out.println(studentList.toString());
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return studentList;
    }

    private static Object  getValue(HSSFRow row,int j){
        HSSFCell cell=row.getCell(j);
        //设置单元格类型
        cell.setCellType(CellType.STRING);
        return cell.getStringCellValue();
    }
}

ImportThread

线程类

public class ImportThread implements Runnable {


    public ImportThread() {
    }

    StudentMapper studentMapper;
    private List<Student> 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 studentMapper 数据库连接
     */
    public ImportThread(List<Student> list, CountDownLatch begin, CountDownLatch end, StudentMapper studentMapper) {
        this.list = list;
        this.begin = begin;
        this.end = end;
        this.studentMapper = studentMapper;
    }

    @Override
    public void run() {
        try {
            for (Student student : list) {
                studentMapper.studentAdd(student.getId(),student.getStudentId(),student.getTeacherId(),student.getStudentName(),student.getStudentAge(),student.getStudentSex());
            }
            count = 1;
            begin.await();
        } catch (InterruptedException e) {
            e.printStackTrace();
        } finally {
            end.countDown();
        }
    }

}

测试