java 多线程写excel_java通过poi上传excel表格并多线程批量插入数据到数据库

废话少说，直接上代码。

BaseMapper.xml

insert into base (name,password) values

(#{item.name},#{item.password})

select @@identity

BaseDao.java

public interface BaseDao {

/**

* 批量新增

* @param list

*/

public void batchInsert(List list);

}

BaseService.java

public interface BaseService{

/**

* 批量新增

* @param list

*/

public void batchInsert(List list);

}

BaseServiceImpl.java

@Service("baseService")

public class BaseServiceImpl implements BaseService{

@Autowired

private BaseDao baseDao;

@Override

public void batchInsert(List list) {

baseDao.batchInsert(list);

}

}

BaseController.java

/**

* 专门作测试用的Action类

* @author reyco

*

*/

@RequestMapping("base")

@Controller("baseController")

public class BaseController {

protected Logger logger = LoggerFactory.getLogger(getClass());

@Autowired

public BaseService baseService;

/**

* excel批量新增数据

* @param file

* @param request

* @return

* @throws IOException

*/

@ResponseBody

@RequestMapping("batchInsert")

public String batchInsert(@RequestParam("file")CommonsMultipartFile file,HttpServletRequest request) throws IOException {

//读取流

InputStream is = file.getInputStream();

//根据is流创建wb

HSSFWorkbook wb = new HSSFWorkbook(is);

//获取页

HSSFSheet sheet = wb.getSheetAt(0);

if(sheet != null) {

//过滤表格数据到List集合

List baseList = filterSheet(sheet);

//保存/提交数据

saveBaseInfoList(baseList);

return JSONResult.create("上传成功").toJSON();

}

return JSONResult.failCreate("上传失败", "").toJSON();

}

/**

* 读取表格数据加入到List容器

* @return

*/

private List filterSheet(HSSFSheet sheet){

// 作返回值

List list = null;

// 为null直接返回

if(null != sheet) {

list = new ArrayList();;

//循环读取表格数据

for(int i=1;ilist) {

int listSize = list.size();// 数据集合大小

int count =999; // 一个线程处理多少条数据,默认1000

//根据数据量确定一个线程处理多少条数据

if( listSize >= 1 && listSize < 1000) {

count = 333;

}else if(listSize >= 1000 && listSize < 10000) {

count = 1333;

}else if(listSize >= 10000) {

count = 5333;

}

int runThreadSize = (listSize / count) + 1; // 开启的线程数

logger.error("--------runThreadSize-------====="+runThreadSize);

List newList = null;// 存放每个线程的执行数据

ExecutorService executor = Executors.newFixedThreadPool(runThreadSize);// 创建一个线程池,数量和开启线程的数量一样

// 创建两个个计数器

CountDownLatch begin = new CountDownLatch(1);

CountDownLatch end = new CountDownLatch(runThreadSize);

// 循环创建线程

for (int i=0;inewList, CountDownLatch begin,CountDownLatch end) {

BatchInsertThread batchinsertThread = new BatchInsertThread(newList,begin,end);

new Thread(batchinsertThread).start();

}

}

BatchInsertThread.java

/**

* batchInsert线程类

* @author reyco

*

*/

public class BatchInsertThread implements Runnable{

protected Logger logger = LoggerFactory.getLogger(getClass());

private BaseService baseService = SpringContextHolder.getBean("baseService");

List list;

CountDownLatch begin;

CountDownLatch end;

public BatchInsertThread() {

}

public BatchInsertThread(List list, CountDownLatch begin, CountDownLatch end) {

this.list = list;

this.begin = begin;

this.end = end;

}

@Override

public void run() {

try {

baseService.batchInsert(list);

//执行完让线程直接进入等待

begin.await();

}catch (Exception e) {

logger.error(e.getMessage(),e);

}finally {

//这里要主要了，当一个线程执行完了计数要减一不要这个线程会被一直挂起

//，end.countDown()，这个方法就是直接把计数器减一的

end.countDown();

}

}

}

关于一点题外话，SpringContextHolder是小编写的一个获取spring管理bean的util类,需要了解的兄弟可以浏览我的另一篇博客:

简单获取Spring管理的bean

小编经过测试：  1万条数据内大约需要2s以内,10万条数据大约需要5s以内。更多数据小编没测试试过。