excel表数据多线程导入数据库
2021/11/2 19:11:30
本文主要是介绍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(); } } }
测试
这篇关于excel表数据多线程导入数据库的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-10-04el-table 开启定时器下,表格的选中状态会消失是什么原因-icode9专业技术文章分享
- 2024-10-03如何安装和初始化飞牛私有云 fnOS?-icode9专业技术文章分享
- 2024-10-03如何安装 App 并连接到飞牛 NAS?-icode9专业技术文章分享
- 2024-10-03如何安装飞牛 TV 并连接到影视服务器?-icode9专业技术文章分享
- 2024-10-03如何在PVE和ESXI上安装飞牛私有云 fnOS?-icode9专业技术文章分享
- 2024-10-03fnOS国产最强NAS安装系统异常情况处理-icode9专业技术文章分享
- 2024-10-03飞牛NAS如何创建存储空间?-icode9专业技术文章分享
- 2024-10-03fnOS国产最强NAS硬盘会自动休眠吗?-icode9专业技术文章分享
- 2024-10-03fnOS国产最强NAS如何安装飞牛影视和创建媒体库?-icode9专业技术文章分享
- 2024-10-03fnOS国产最强NAS如何为家人朋友开通影视账号?-icode9专业技术文章分享