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();
}
}
}