JDBC 批量插入

package test.utils; 

import org.apache.commons.logging.Log; 
import org.apache.commons.logging.LogFactory; 

import java.io.IOException; 
import java.sql.*; 
import java.util.List; 
import java.util.Properties; 

/** 
* JDBC工具类 
* 
* @author leizhimin 2009-11-24 9:28:03 
*/ 
public class DBToolkit { 
		
        private static final Log log = LogFactory.getLog(DBToolkit.class); 
        private static String url = "jdbc:mysql://localhost:3306/vsvdata?useUnicode=true&characterEncoding=UTF-8"; 
        private static String username = "root"; 
        private static String password = "root"; 


        /** 
         * 创建一个数据库连接 
         * 
         * @return 一个数据库连接 
         */ 
        public static Connection getConnection() { 
                Connection conn = null; 
               
                //创建数据库连接 
                try {  
                	Class.forName("oracle.jdbc.driver.OracleDriver");  
                        conn = DriverManager.getConnection(url, username, password); 
                } catch (Exception e) { 
                        log.error("#ERROR# :创建数据库连接发生异常，请检查！", e); 
                } 
                return conn; 
        } 

        /** 
         * 在一个数据库连接上执行一个静态SQL语句查询 
         * 
         * @param conn            数据库连接 
         * @param staticSql 静态SQL语句字符串 
         * @return 返回查询结果集ResultSet对象 
         */ 
        public static ResultSet executeQuery(Connection conn, String staticSql) { 
                ResultSet rs = null; 
                try { 
                        //创建执行SQL的对象 
                        Statement stmt = conn.createStatement(); 
                        //执行SQL，并获取返回结果 
                        rs = stmt.executeQuery(staticSql); 
                } catch (SQLException e) { 
                        log.error("#ERROR# :执行SQL语句出错，请检查！\n" + staticSql, e); 
                } 
                return rs; 
        } 

        /** 
         * 在一个数据库连接上执行一个静态SQL语句 
         * 
         * @param conn            数据库连接 
         * @param staticSql 静态SQL语句字符串 
         */ 
        public static void executeSQL(Connection conn, String staticSql) { 
                try { 
                        //创建执行SQL的对象 
                        Statement stmt = conn.createStatement(); 
                        //执行SQL，并获取返回结果 
                        stmt.execute(staticSql); 
                } catch (SQLException e) { 
                        log.error("#ERROR# :执行SQL语句出错，请检查！\n" + staticSql, e); 
                } 
        } 

        /** 
         * 在一个数据库连接上执行一批静态SQL语句 
         * 
         * @param conn        数据库连接 
         * @param sqlList 静态SQL语句字符串集合 
         */ 
        public static void executeBatchSQL(Connection conn, List<String> sqlList) { 
                try { 
                        //创建执行SQL的对象 
                        Statement stmt = conn.createStatement(); 
                        for (String sql : sqlList) { 
                                stmt.addBatch(sql); 
                        } 
                        //执行SQL，并获取返回结果 
                        stmt.executeBatch(); 
                } catch (SQLException e) { 
                        log.error("#ERROR# :执行批量SQL语句出错，请检查！", e); 
                } 
        } 

        public static void closeConnection(Connection conn) { 
                if (conn == null) return; 
                try { 
                        if (!conn.isClosed()) { 
                                //关闭数据库连接 
                                conn.close(); 
                        } 
                } catch (SQLException e) { 
                        log.error("#ERROR# :关闭数据库连接发生异常，请检查！", e); 
                } 
        } 
}

读取CSV批量插入

package test.utils;

import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.opencsv.CSVReader;

public class ResolvFileU {


	public static String readFileContent(String filepath) throws IOException {
		// 1.读取每一行记录，保存到List中
		File file = new File(filepath);
		FileReader fReader = new FileReader(file);
		CSVReader csvReader = new CSVReader(fReader);
		String[] strs = csvReader.readNext();
		System.out.println(strs.length);
		List<String[]> list = csvReader.readAll();

		csvReader.close();

		// 2.处理每一条记录成SQL语句或保存为对象（a.去掉字段前后的分号b.拼接成SQL或者保存为对象）
		ArrayList<String> recordList = new ArrayList<String>();// 用于保存生成的SQL或对象
		for (int i = 0; i < list.size(); i++) {
			String[] recArray = list.get(i);

			// 拼接SQL语句或保存为对象
			String recordSql = getRecordSql(recArray);
			if (null != recordSql) {
				recordList.add(recordSql);
			}
		}
		// 3.批量执行SQL或保存对象
		batchExecuteSql(recordList);
		return null;
	}

	// 生成每条记录的SQL
	public static String getRecordSql(String[] recArray) {
		if (null == recArray) {
			return null;
		}
		String recordSql = "insert into t_b_ho () values(";

		StringBuffer sb = new StringBuffer(recordSql);
		for (int j = 0; j < recArray.length; j++) {
			sb.append("'");
			sb.append(recArray[j]);
			sb.append("'");
			if (j != recArray.length - 1) {
				sb.append(",");
			}
		}
		sb.append(")");
		return sb.toString();
	}

	
	
	//批量执行
	public static int batchExecuteSql(ArrayList<String> sqlList) {

		try {
			Long start = System.currentTimeMillis();

			System.out.println("接下来可以执行SQL语句或保存对象");
			System.out.println("========批量执行SQL语句==========");
			System.out.println("将所有语句加入到Statment stat中");
			Connection conn = DBToolkit.getConnection();
			conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			System.out.println("数据总条数:" + sqlList.size());
			for (int i = 0; i < sqlList.size(); i++) {
				String sql = sqlList.get(i);
				if (i % 100000 == 0) {
					System.out.println("通过stat.addBatch(sql)来加入语句" + i + ": '"
							+ sql + "'");
				}
				stmt.addBatch(sql);
			}
			System.out.println("通过stat.executeBatch()来执行所有的SQL语句");
			System.out.println("========批量执行SQL语句结束==========");
			// int count = stat.executeBatch();
			// return count;//返回执行的语句数量
			Long end = System.currentTimeMillis();
			stmt.executeBatch(); // 执行批处理
			conn.commit();
			stmt.clearBatch(); //清理批处理
			stmt.close();
			DBToolkit.closeConnection(conn); // 连接归池
			System.out.println("单条执行" + sqlList + "条Insert操作，共耗时："
					+ (end - start) / 1000f + "秒！");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sqlList.size();
	}

	/**
	 * 去掉数组中每一个元素的开头和结尾的引号
	 * 
	 * @param recArray
	 *            要处理的数组
	 * @return 处理后的数组
	 */
	public static String[] minusQuotation(String[] recArray) {
		for (int i = 0; i < recArray.length; i++) {
			String str = recArray[i];
			if (null != str) {
				if (str.indexOf("\"") == 0)
					str = str.substring(1, str.length());// 去掉开头的分号
				if (str.lastIndexOf("\"") == (str.length() - 1))
					str = str.substring(0, str.length() - 1); // 去掉最后的分号
			}
			recArray[i] = str;
		}
		return recArray;
	}

	public static void main(String[] args) throws IOException {
		String filepath = "E:\\2014_traffica\\vlr_file_20140117\\vlr_file_0.csv";
		readFileContent(filepath);
	}
}