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

废话少说，直接上代码。

BaseMapper.xml

 	<!-- batchInsert -->
 	<insert id="batchInsert" parameterType="java.util.List">
 		insert into base (name,password) values
	    <foreach collection="list" item="item" index="index" separator=",">
	   		(#{item.name},#{item.password})
	   	</foreach>
		<selectKey resultType="int" keyProperty="id" order="AFTER">
			select @@identity
		</selectKey>
 	</insert>

BaseDao.java

public interface BaseDao {
	
	/**
	 * 批量新增
	 * @param list
	 */
	public void batchInsert(List<Base> list);
	
}

BaseService.java

public interface BaseService{

	/**
	 * 批量新增
	 * @param list
	 */
	public void batchInsert(List<Base> list);
	
}

BaseServiceImpl.java

@Service("baseService")
public class BaseServiceImpl implements BaseService{
	
	@Autowired
	private BaseDao baseDao;
	
	@Override
	public void batchInsert(List<Base> 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<Base> baseList = filterSheet(sheet);
			//保存/提交数据
			saveBaseInfoList(baseList);
			return JSONResult.create("上传成功").toJSON();
		}
		return JSONResult.failCreate("上传失败", "").toJSON();
	}
	/**
	 * 读取表格数据加入到List容器
	 * @return
	 */
	private List<Base> filterSheet(HSSFSheet sheet){
		// 作返回值
		List<Base> list = null;
		// 为null直接返回
		if(null != sheet) {
			list = new ArrayList<Base>();;
			//循环读取表格数据
			for(int i=1;i<sheet.getLastRowNum()+1;i++) {
				HSSFRow row = sheet.getRow(i);
				//当前行为null
				if(null == row) {
					continue;
				}
				// 第二列开始读,当前列如果没有数据，
				String name = PoiUtil.formatCell(row.getCell(1));
				String password = PoiUtil.formatCell(row.getCell(2));
				//name为null或空串直接返回,不要添加到List集合
				if(StringUtils.isBlank(name)) {
					continue;
				}
				//如果没有指定密码，给默认密码
				if(StringUtils.isBlank(password)) {
					password="123456";
				}
				//封装数据到实体类中
				Base base = new Base(name,password);
				//封装对象到List
				list.add(base);
			}
			return list;
		}
		return null;
	}
	/**
	 * 保存操作
	 */
	public void saveBaseInfoList(List<Base> list) {
		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<Base> newList = null;// 存放每个线程的执行数据
		ExecutorService executor = Executors.newFixedThreadPool(runThreadSize);// 创建一个线程池,数量和开启线程的数量一样
		// 创建两个个计数器
                CountDownLatch begin = new CountDownLatch(1);
                CountDownLatch end = new CountDownLatch(runThreadSize);
                // 循环创建线程
                for (int i=0;i<runThreadSize;i++) {
                    if ((i + 1) == runThreadSize) {
                        int startIndex;
                        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);
                    }
        	    createBatchInsertThread(newList,begin,end);
		 }
	}
	/**
	 * 创建batchInsert线程
	 * @param newList
	 * @param begin
	 * @param end
	 */
	private void createBatchInsertThread(List<Base> newList, 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<Base> list;
	CountDownLatch begin;
	CountDownLatch end;
	public BatchInsertThread() {
	}
	public BatchInsertThread(List<Base> 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以内。更多数据小编没测试试过。