POI结合线程池批量处理导入减少导入时间

话不过多说，直接上代码。

核心思想

1，利用

ExecutorService service = Executors.newFixedThreadPool(10);

创建一个长度为10的线城池，用以导入开线程导入
2，创建两个计数器

CountDownLatch rowLatch = new CountDownLatch(1);
CountDownLatch exceLatch = new CountDownLatch(10);

rowLatch用于控制每个线程的执行和挂起；
exceLatch用于控制主线程的执行和挂起。用以导入统计时间。
3，jdbc批量提交

if(list.size()>=200) {
                                prepareStatement.executeBatch();
                                list.clear();
                                connection.commit();
                                prepareStatement.clearBatch();
                            }

利用批量提交技术，单线程上，每两百条提交一次。

测试结果

用笔记本测试;
最高频率2.6Ghz，内存8G，硬盘5400转，数据库oracle数据库，装载第二分区，程序在第三个分区。
6万导入，在不加线程时，用了40多分钟；
如果不加jdbc的批量提交，则时间为9秒左右；
如果加了jdbc的批量提交，则时间缩短为2秒左右。

源码实例

package com.wayne;

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class POI2Excel2 {
    public static void main(String[] args) throws Exception {

        // SimpleDateFormat sdf = new SimpleDateFormat("YYYY-MM-dd hh:mm:ss");

        Class.forName("oracle.jdbc.driver.OracleDriver");

        ExecutorService service = Executors.newFixedThreadPool(10);

        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(
                new FileInputStream(new File("E:\\workspace\\Java\\Staff\\MyThreadPool\\Output\\测试1.xlsx")));
        Sheet sheet = xssfWorkbook.getSheetAt(0);
        long start = System.currentTimeMillis();
        CountDownLatch rowLatch = new CountDownLatch(1);
        CountDownLatch exceLatch = new CountDownLatch(10);

        for (int i = 0; i < 10; i++) {
            final int page = i;

            service.submit(new Runnable() {
                @Override
                public void run() {
                    Connection connection =null;
                    try {
                        connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "resdb", "tnms320_resdb");
                        rowLatch.await();
                        connection.setAutoCommit(false);
                        //synchronized(connection) {}

                        String sql = "insert into person(name,age,birthday) values(?,?,?)";
                        PreparedStatement prepareStatement = connection.prepareStatement(sql);
                        List<Integer> list =  new ArrayList<Integer>(200);
                        for(int j = 0;j<sheet.getLastRowNum()/10;j++) {
                            list.add(j);
                            Row r = sheet.getRow(j+page*sheet.getLastRowNum()/10);
                            String name = r.getCell(0).getStringCellValue();
                            int age = (int)(r.getCell(1).getNumericCellValue());
                            String birthday = r.getCell(2).getStringCellValue();
                            prepareStatement.setString(1, name);
                            prepareStatement.setInt(2, age);
                            prepareStatement.setString(3,birthday);
                            prepareStatement.addBatch();
                            if(list.size()>=200) {
                                prepareStatement.executeBatch();
                                list.clear();
                                connection.commit();
                                prepareStatement.clearBatch();
                            }
                        }
                        if(!list.isEmpty()) {
                            prepareStatement.executeBatch();
                            list.clear();
                            connection.commit();
                            prepareStatement.clearBatch();

                        }
                        prepareStatement.close();

                    } catch (Exception e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    } finally {
                        exceLatch.countDown();
                        try {
                            connection.close();
                        } catch (Exception e) {
                            // TODO Auto-generated catch block
                            e.printStackTrace();
                        }
                    }

                }
            });

        }
        rowLatch.countDown();
        exceLatch.await();

        long end = System.currentTimeMillis();

        System.out.println("导入时间为" + (end - start));
        service.shutdown();
    }
}