POI读取excle多线程批量插入到数据库

开发环境

• JDK 1.6
• POI 3.9
• 框架 struts2 spring mybatis

model

public class ConsultGovDataModel extends ModelBase {
    private static final long serialVersionUID = 1L;
    /**
     * 政务咨询数据主键
     */
    private String govdataId;
    /**
     * 项目名称
     */
    private String projectName;
    /**
     * 项目地点
     */
    private String projectAddress;
    /**
     *行业分类
     */
    private String projectIndustry;
    /**
     * 发布日期
     */
    private String publishDate;
    /**
     * 行业
     */
    private String industry;
    /**
     * 产品
     */
    private String product;
    /**
     * 导入日期
     */
    private String importDate;
    /**
     * 创建时间开始 检索条件
     */
    private String start_time;
    /**
     * 创建时间结束 检索条件
     */
    private String end_time;
	
     Get .... set ...

}

Action

public class ConsultGovDataAction extends ActionBase implements ModelDriven {

    private static final long serialVersionUID = 1L;

    /**
     * 上传文件
     */
    private File upload;
    /**
     * 上传文件名称
     */
    private String uploadFileName;
    /**
     * 上传文件的mimeType类型
     */
    private String uploadContentType;

    ConsultGovDataService consultGovDataService;

    ConsultGovDataModel model = new ConsultGovDataModel();
     
Get set ... 

@Override
    public ConsultGovDataModel getModel() {
      return model;
    }


/**
 * 方法名: importExcle
 * 方法描述: 导入政务咨询的excle数据
 * 参数 []
 * 返回类型 java.lang.String
 * @throws
 */
public String importExcle() throws Exception {
    String type = getRequest().getParameter("type");
    String fileEnd="xls";
    //获取文件名,判断是xls还是xlsx
    Workbook workbook=null;
    ArrayList list = new ArrayList();
    if (StringUtils.isNotBlank(uploadFileName)) {
        if (uploadFileName.endsWith(fileEnd)) {
            //03版本的xls
            workbook= new HSSFWorkbook(new FileInputStream(upload));
        }else {
            //07版本的xlsx
            workbook= new XSSFWorkbook(new FileInputStream(upload));
        }
        //获取一共有几个sheet
        int numberOfSheets = workbook.getNumberOfSheets();
        for (int i=0;i
    insert all
    
        INTO CONSULT_GOVDATA
        (
        PROJECT_NAME, PROJECT_ADDRESS, PROJECT_INDUSTRY, PUBLISH_DATE, INDUSTRY,
        PRODUCT,IMPORT_DATE
        )
        values
        (
        #{item.projectName,jdbcType=VARCHAR},
        #{item.projectAddress,jdbcType=VARCHAR},
        #{item.projectIndustry,jdbcType=VARCHAR},
        to_date(#{item.publishDate,jdbcType=DATE},'yyyy-mm-dd'),
        #{item.industry,jdbcType=VARCHAR},
        #{item.product,jdbcType=VARCHAR},
        to_date(#{item.importDate,jdbcType=DATE},'yyyy-mm-dd hh24:mi:ss')
        )
    
    select * from dual

Thread类

public class ImportThread implements Runnable {
    public ImportThread() {
    }

    ConsultGovdataDao consultGovdataDao;

    public ConsultGovdataDao getConsultGovdataDao() {
        return consultGovdataDao;
    }

    public void setConsultGovdataDao(ConsultGovdataDao consultGovdataDao) {
        this.consultGovdataDao = consultGovdataDao;
    }

    private List list;
    private CountDownLatch begin;
    private CountDownLatch end;

    /**
     * 方法名: ImportThread
     * 方法描述: 创建个构造函数初始化 list,和其他用到的参数
     * @throws
     */
    public ImportThread(List list, CountDownLatch begin, CountDownLatch end) {
        this.list = list;
        this.begin = begin;
        this.end = end;
    }

    @Override
    public void run() {
        try {
            //执行完让线程直接进入等待
            ConsultGovdataDao consultGovdataDao = (ConsultGovdataDao) ServiceFactory.getService("consultGovdataDao");
            consultGovdataDao.saveBatch(list);
            begin.await();
        } catch (InterruptedException e) {
            e.printStackTrace();
        } finally {
            //这里要主要了，当一个线程执行完 了计数要减一不然这个线程会被一直挂起
            //这个方法就是直接把计数器减一的
            end.countDown();
        }
    }
}

测试结果

经测试, 2万条数据插入, 批量插入200到300一次最佳. 原来要四分钟, 现在为41秒.

需要优化的地方

创建线程池的方式为Executors.newFixedThreadPool 需要改进为 ThreadPoolExecutor 的方式创建线程 20181120已经优化