Csv导入数据至数据库

import java.sql.*;
import java.util.*;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.ResultSetMetaData;

public class test {
	private static final String DRIVER = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost:3306/world?useUnicode=true&characterEncoding=utf-8";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "root";
    private static final String SQL = "SELECT * FROM ";// 数据库操作

     static {
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
        }
    }
    /**
     * 获取数据库连接
     *
     * @return
     */
    public static Connection getConnection() {
        Connection conn = null;
		try {
			conn = (Connection) DriverManager.getConnection(URL, USERNAME, PASSWORD);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        return conn;
    }
    /**
     * 关闭数据库连接
     * @param conn
     */
    public static void closeConnection(Connection conn) {
        if(conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
            	e.printStackTrace();
            }
        }
    }
    /**
     * 用于注入参数
     * @param data  参数
     * @param n 不插入字段的个数
     * @throws SQLException
     */
	private static PreparedStatement excutePs(PreparedStatement pStemt, Connection conn, String sql, int length, String[] data, int n) throws SQLException {
//		Connection conn = getConnection(); // 首先要获取连接，即连接到数据库
//		PreparedStatement ps = null;
		// 预处理SQL 防止注入
		pStemt = (PreparedStatement) conn.prepareStatement(sql);
		// 注入参数
		for (int i = 0; i < length-n; i++) {
			if("".equals(data[i+n])) {//mysql 5以上的版本插入时，如果是空值应该要写NULL
				data[i+1] = null;
			}
			pStemt.setString(i + 1, data[i+n]);
		}
		return pStemt;
	}
	
    /**
     * 添加数据
     * @param tabName 表名
     * @param fields 参数字段
     * @param data 参数字段数据
     */
	public static void insert(String tabName, String[] fields, String[] data) {
		Connection conn = getConnection(); // 首先要获取连接，即连接到数据库
		PreparedStatement pStemt = null;
		try {
			String sql = "insert into " + tabName + "(";
			int length = fields.length;
			int n = 0;//不插入字段的个数
			for (int i = 0; i < length; i++) {
				if("id".equals(fields[i])) {//id是主键，不插入
					n++;
					continue;
				}
				sql += fields[i];
				// 防止最后一个,
				if (i < length - 1) {
					sql += ",";
				}
			}
			sql += ") values(";
			for (int i = 0; i < length - n; i++) {
				sql += "?";
				// 防止最后一个,
				if (i < length - (n+1)) {
					sql += ",";
				}
			}
			sql += ");";
//			System.out.println("添加数据的sql:" + sql);
			// 预处理SQL 防止注入
			pStemt = excutePs(pStemt,conn,sql, length, data,n);
			// 执行
			pStemt.executeUpdate();
		} catch (SQLException e) {
			System.out.println("添加数据失败" + e.getMessage());
		} finally {
			if (pStemt != null) {
				try {
					pStemt.close();
					closeConnection(conn);
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	/**
     * 批量添加数据
     * @param tabName 表名
     * @param fields 参数字段
     * @param contentList 参数字段数据
     */
	public static void plinsert(String tabName, String[] fields, List<String[]> contentList) {
		Connection conn = getConnection(); // 首先要获取连接，即连接到数据库
		PreparedStatement pStemt = null;
		try {
			//设置conn.setAutoCommit(false); 关闭事务自动提交 ,这一行必须加上
			//最后在所有命令执行完之后再提交事务conn.commit();
	        conn.setAutoCommit(false);
			String sql = "insert into " + tabName + "(";
			int length = fields.length;
			int n = 0;//不插入字段的个数
			for (int i = 0; i < length; i++) {
				if("id".equals(fields[i])) {//id是主键，不插入
					n++;
					continue;
				}
				sql += fields[i];
				// 防止最后一个,
				if (i < length - 1) {
					sql += ",";
				}
			}
			sql += ") values(";
			for (int i = 0; i < length - n; i++) {
				sql += "?";
				// 防止最后一个,
				if (i < length - (n+1)) {
					sql += ",";
				}
			}
			sql += ");";
			System.out.println("添加数据的sql:" + sql);
			pStemt = plexcutePs(pStemt,conn,sql, length, contentList,n);//批量插入方法
		} catch (SQLException e) {
			System.out.println("添加数据失败" + e.getMessage());
		} finally {
			if (pStemt != null) {
				try {
					pStemt.close();
					closeConnection(conn);
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	/**
	*注入参数
	*/
	private static PreparedStatement plexcutePs(PreparedStatement pStemt, Connection conn, String sql, int length, List<String[]> contentList, int n) throws SQLException {
		int batchSize = 5000; //设置批量处理的数量  
		int j = 0;
		// 预处理SQL 防止注入
		pStemt = (PreparedStatement) conn.prepareStatement(sql);
		// 注入参数
		for(String[] data: contentList) {
			for (int i = 0; i < length-n; i++) {
				if("".equals(data[i+n])) {//mysql 5以上的版本插入时，如果是空值应该要写NULL
					data[i+n] = null;
				}
				pStemt.setString(i + 1, data[i+n]);
			}
			pStemt.addBatch();
			j++;
			if(j % batchSize == 0) {
				pStemt.executeBatch();//批量操作
		        conn.commit();
		        pStemt.clearBatch();//清除缓存
			}
		}
		if(j % batchSize != 0) {
			pStemt.executeBatch();
	        conn.commit(); 
	        pStemt.clearBatch();
		}
        return pStemt;
	}
	public static void main(String[] args) throws IOException {
		String tabName = "tableName";//要插入数据的表名
		CsvReader csvReader = new CsvReader("D:\\csv\\test.csv", ',', Charset.forName("GBK"));//Charset.forName("GBK")---与导出时一致
		csvReader.setSafetySwitch(false); 
		//设置为false,可使每列的最大值超过100,000,解析器会使用大量内存
		//不设置默认每列的最大值不可超过100,000
		char delimiter = csvReader.getDelimiter(); // 获取用作列分隔符的字符
		csvReader.readHeaders();
		int headerNum = csvReader.getHeaderCount();// 获取上一次调用读入的表头数 readHeaders()
		String[] headers = csvReader.getHeaders();// 以字符串数组的形式返回表头值
		List<String[]> contentList = new ArrayList<>();
		while (csvReader.readRecord()) {// 读取另一条记录
			String[] contents = new String[headers.length];
			int i = 0;
			for (String header : headers) {//
				String content = csvReader.get(header);// 获取 给定列 标题名称 的当前列值
				contents[i] = content;
				i++;
			}
			contentList.add(contents);
		}
		Long startTime = System.currentTimeMillis();
		for (String[] data : contentList) {// 执行插入数据方法---传统方法，一条一条插入
			DataBaseUtils.plinsert(tabName, headers, data);
		}
		plinsert(tabName, headers, contentList);// 批量执行插入数据方法
		Long endTime = System.currentTimeMillis();
		System.out.println("用时：" + (endTime - startTime)+"ms");
	}
}

参考链接：
https://www.jb51.net/article/121530.htm
https://my.oschina.net/u/2930289/blog/1589704
https://blog.csdn.net/qy20115549/article/details/52699724