csv100万数据2分30秒导入数据库

/**
 * 
 */
import java.sql.Connection;
import java.sql.DriverManager; 
import java.sql.PreparedStatement; 
import java.sql.ResultSet; 
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List; 
public class BaseDao { 
	private final String DRIVER="oracle.jdbc.driver.OracleDriver"; 
	private final String URL= ""; 
	private final String USERNAME = "scott"; 
	private final String PWD = ""; 
	private Connection connection; 
	private PreparedStatement ps; 
	protected ResultSet rs; 
	protected Savepoint sp = null;
	@SuppressWarnings("rawtypes")
	protected List params;  //放单条数据
	@SuppressWarnings({ "rawtypes", "unchecked" })
	List<List> newDatas = new ArrayList(); //放每次批量的数据
	@SuppressWarnings({ "rawtypes", "unchecked" })
	List<List<List>> oneDatas = new ArrayList(); //放第一次批量的数据  出现错误的数据的批量数据
	@SuppressWarnings({ "rawtypes", "unchecked" })
	List<List<List>> twoDatas = new ArrayList(); //放第二次批量的数据  出现错误的数据的批量数据
	StringBuffer sb = null;  //sql语句
	int count = 0;
	int counts = 0;
	boolean a = true;  //设置第一次错误数据 重新执行
	boolean b = false; //设置第二次错误数据 重新执行
	public Connection getConnection(){ 
		try { 
		Class.forName(DRIVER); // 2在网络中查找数据库，创建连接对象 
		connection = DriverManager.getConnection( URL, USERNAME,PWD); 
		} catch (ClassNotFoundException e) { // TODO Auto-generated catch block 
			e.printStackTrace(); 
			} catch (SQLException e) { // TODO Auto-generated catch block 
				e.printStackTrace(); 
				} 
		return connection; 
		} 
	public void closeAll(){ 
		try { // 释放资源 
			if (rs != null) rs.close(); 
			if (ps != null) ps.close(); 
			if (connection != null) connection.close(); 
			} catch (SQLException e) { // TODO Auto-generated catch block 
				e.printStackTrace(); 
				} 
		} 
	public void delete(String table) {
		getConnection();
		try {
			ps = connection.prepareStatement("delete from " + table + " where 1=1");
			ps.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	//execute 批量插入
	//datas 数据
	//types 每个字段的数据类型
	//table 表名称
	//batchCount 一次插入数据的大小
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public void execute(List<List> datas, List types, String table, int batchCount) {
		try { 
			getConnection();
			sb = new StringBuffer();
			if(datas!= null && datas.size() != 0){ 
				//System.out.println(datas.get(0));
			for(int i = 0; i < datas.get(0).size(); i++) {
					sb.append("?,");
				}
			ps = connection.prepareStatement("insert into " + table + " values(" + sb.substring(0, sb.length() - 1) + ")");
			}
			//设置不自动提交
			connection.setAutoCommit(false);
			if(datas!= null){ 
				for (int i = 0; i < datas.size(); i++) {
					if(count == 0){
						//设置保存点
						sp = connection.setSavepoint();
					}
					params = datas.get(i);
					if(params != null) {
						//遍历单条数据
						for (int j = 0; j < types.size(); j++) {
								if(types.get(j) == "String") {
									ps.setString(j + 1, (String) params.get(j));
								}
								if(types.get(j) == "Float") {
									ps.setFloat(j + 1, (Float) params.get(j));
								}
								if(types.get(j) == "Integer") {
									ps.setInt(j + 1, (Integer) params.get(j));
								}
								if(types.get(j) == "Date") {
									ps.setTimestamp(j + 1, (Timestamp) params.get(j));
								}
							}
						ps.addBatch();
						newDatas.add(params);
						}
					count++;
					if(count == batchCount) {  //oracle14驱动执行的批量提交只支持19455个参数   数据条数乘于字段数小于32573
						try{
							ps.executeBatch();
						}catch (Exception e) {
							if(batchCount == 1){
								counts++; //错误数据个数
								System.out.println("错误数据：" + params.get(0));
								e.printStackTrace();
							}
							if(batchCount == 19455/types.size()){
								oneDatas.add(newDatas);
							}
							if(batchCount == 1945/types.size()){
								twoDatas.add(newDatas);
							}
							connection.rollback(sp);
						}finally{
							connection.commit();
							ps.clearBatch();
							newDatas = new ArrayList();
							count = 0;
						}
						
					}
					} 
				} 
			ps.executeBatch();
			} catch (SQLException e) { // TODO Auto-generated catch block 
				try {
					if(batchCount == 1){
						System.out.println("错误数据：" + params.get(0));
						e.printStackTrace();
					}
					if(batchCount == 19455/types.size()){
						oneDatas.add(newDatas);
					}
					if(batchCount == 1945/types.size()){
						twoDatas.add(newDatas);
					}
					connection.rollback(sp);
				} catch (SQLException e1) {
				}
				}finally{ 
					newDatas = new ArrayList();
					try {
						connection.commit();
					} catch (SQLException e) {

					} 
					try {
						ps.clearBatch();
					} catch (SQLException e) {

					}
					count = 0;
					this.closeAll();
					if(a){
						a = false;
						for(int i = 0; i < oneDatas.size(); i++){
							if(i == oneDatas.size() - 1){
								b = true;
							}
							List<List> list = oneDatas.get(i);
							execute(list, types, table, 1945/types.size());
						}
						
					}
					if(b){
						b = false;
						for(int i = 0; i < twoDatas.size(); i++){
							List<List> list = twoDatas.get(i);
							execute(list, types, table, 1);
						}
						System.out.println("错误数据总个数：" + counts);
					}
					}
		}
}



import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class csvdao extends BaseDao{
	//table 表名
	//path csv文件路径
	//rowCount 字段数目前只写string类型的
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public void csv(String table, String path, int rowCount) {
		int count = 0; //一共有多少行数据
		int counts = 0; //一共有多少条不符合规则的数据
		int sumcount = 0; //每条数据有多少字段
		String prve = ""; //换行的数据
	    File csv = new File(path);  // CSV文件路径
	    BufferedReader br = null;
	    int sum = 1;
	    try
	    {
	        br = new BufferedReader(new FileReader(csv));
	    } catch (FileNotFoundException e)
	    {
	        e.printStackTrace();
	    }
	    String line = ""; //每行数据
	    String everyLine = ""; //每行数据
	    try {
				List<List> paramss = new ArrayList(); //所有数据
	            List<String> allStr = new ArrayList(); //重新保存每行数据
	            List<String> types = new ArrayList();
	            for(int i = 0; i < rowCount; i++){
	            	types.add("String");
	            }
	            while ((line = br.readLine()) != null)  //读取到的内容给line变量
	            {
	            	count++;
	                everyLine = line;
	                String[] split = everyLine.split("\",\"");
	                if( count == 1) {
	                	sumcount = split.length;
	                	continue;
	                }
	                if(split.length != sumcount){
	                	count--;
	                	prve += everyLine;
	                	if(prve.split("\",\"").length == sumcount){
	                		count++;
	                		if(!"\"".equals(prve.split("\",\"")[sumcount - 1].substring(prve.split("\",\"")[sumcount - 1].length() - 1))){
	                			split = prve.split("\",\"");
	                		}else {
	                			split = prve.split("\",\"");
		                		prve = "";
							}
	                	}else {	
	                		if(prve.split("\",\"").length > sumcount){
	                			if(!"\"".equals(prve.split("\",\"")[sumcount - 1].substring(prve.split("\",\"")[sumcount - 1].length() - 1))){
		                			split = prve.split("\",\"");
		                		}else {
		                			System.out.println("错误信息：" + prve);
		                			counts++;
		                			prve = "";
		                			continue;
								}
		                	}else{
		                		continue;
		                	}
						}	
	                }
	                if(!"\"".equals(split[sumcount - 1].substring(split[sumcount - 1].length() - 1))){
	                	if(prve == null || "".equals(prve)){
	                		prve = everyLine;
	                	}else {
	                		prve += everyLine;
						}
	                	count--;
	                	continue;
	                }
	                for(int i = 0; i < sumcount; i++){
	                	if(i == 0) {
	                		try{
	                			allStr.add(split[i].substring(1, split[i].length())); 
	                		}catch (Exception e) {
	                			e.printStackTrace();
	                			System.out.println("错误信息：" + split[i]+count);
							}
	                		continue;
	                	}
	                	if(i == split.length - 1) {
	                		try{
	                			allStr.add(split[i].substring(0, split[i].length() - 1));
	                		}catch (Exception e) {
	                			e.printStackTrace();
	                			System.out.println("错误信息：" + split[i]+count);
							}
	                		continue;
	                	}
	                	allStr.add(split[i]);
	                }
	                paramss.add(allStr);
	                if(count/800000 == sum){
	                	this.execute(paramss, types, table,19455/types.size());
	                	paramss.clear();
	                	allStr.clear();
	                	sum++;
	                }
	                allStr = new ArrayList();
	            }
	            br.close();
	            System.out.println("错误数据个数counts: " + counts);
	            this.execute(paramss, types, table,19455/types.size());
	    } catch (IOException e)
	    {
	        e.printStackTrace();
	    }
	}
	
	public static void main(String[] args)
	{
		Date date = new Date();
		new csvdao().csv("","",);
		Date date1 = new Date();
		System.out.println(new Date(date1.getTime() - date.getTime()));

	}
}