话不过多说,直接上代码。
核心思想
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();
}
}