依据"|"大容量插入数据

根据"|"，大容量插入数据

50万数据9秒插完，前提是把mysql的执行SQL大小设大点。jvm的虚拟也大点

 

package com.ikea.utils;

import java.io.BufferedReader;

public class InsertMember {
	public static String encodin = "UTF8";
	
	
	public static void main(String[] args) {
		System.out.println(new Date());
		Connection conn = null;
		List<String[]> dataList = linePaser("D://document//wunderman//ikea//database//ikea_sftp//ikea_member_email_final//11.txt");
		String sql = getSQL(dataList);
//		insert into member (cst_id, cst_membershipnum, cst_name, cst_firstname, cst_email, cnt_code) values (1,1,1,1,1,1),(2,2,2,3)
		String url = "jdbc:mysql://localhost:3306/test";
		String username = "root";
		String password = "root";
		//加载驱动程序以连接数据库
		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			conn = DriverManager.getConnection(url, username, password );
			PreparedStatement pstmt = conn.prepareStatement(sql);
			String[] lines;
			for (int i = 0; i < dataList.size(); i++) {
				lines = dataList.get(i);
				if(lines.length == 6) {
					pstmt.setString(i * 6 + 1, lines[0].equals("NULL") ? null : lines[0] );
					pstmt.setString(i * 6 + 2, lines[1].equals("NULL") ? null : lines[1] );
					pstmt.setString(i * 6 + 3, lines[2].equals("NULL") ? null : lines[2] );
					pstmt.setString(i * 6 + 4, lines[3].equals("NULL") ? null : lines[3] );
					pstmt.setString(i * 6 + 5, lines[4].equals("NULL") ? null : lines[4] );
					pstmt.setString(i * 6 + 6, lines[5].equals("NULL") ? null : lines[5] );
				}
			}
			int result = pstmt.executeUpdate();
			System.out.println(result);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println(new Date());
	}
	
	
	public static String getSQL(List<String[]> dataList) {
		StringBuilder sql = new StringBuilder()
		.append(" INSERT INTO _member (cst_id, cst_membershipnum, cst_name, cst_firstname, cst_email, cnt_code) VALUES ");
		String[] lines = null;
		for (int i = 0; i < dataList.size(); i++) {
			lines = dataList.get(i);
			if(lines.length == 6) {
				sql.append(" ( ");
				for (int j = 0; j < lines.length; j++) {
					sql.append(" ? ");
					if(j < lines.length - 1) {
						sql.append(" , ");
					}
					if(j == lines.length - 1) {
						sql.append(" ) ");
					}
				}
				if(i < dataList.size() - 1) {
					sql.append(" , ");
				}
			} else {
				System.out.println("行数："+ i + "data: " + lines[0]);
			}
		};
		return sql.toString();
	}
	
	
	
	private static String[] splitLine(String src) throws Exception {
		if (src == null || src.equals(""))
			return new String[0];

		StringBuffer st = new StringBuffer();
		Vector<String> result = new Vector<String>();
		boolean beginWithQuote = false;
		for (int i = 0; i < src.length(); i++) {
			char ch = src.charAt(i);
			if (ch == '\"') {
				if (beginWithQuote) {
					i++;
					if (i >= src.length()) {
						result.addElement(st.toString());
						st = new StringBuffer();
						beginWithQuote = false;
					} else {
						ch = src.charAt(i);
						if (ch == '\"') {
							st.append(ch);
						} else if (ch == '|') {
							result.addElement(st.toString());
							st = new StringBuffer();
							beginWithQuote = false;
						} else {
							throw new Exception(
									"Single double-quote char mustn't exist in filed "
											+ (result.size() + 1)
											+ " while it is begined with quote\nchar at:"
											+ i);
						}
					}
				} else if (st.length() == 0) {
					beginWithQuote = true;
				} else {
					throw new Exception(
							"Quote cannot exist in a filed which doesn't begin with quote!\nfield:"
									+ (result.size() + 1));
				}
			} else if (ch == '|') {
				if (beginWithQuote) {
					st.append(ch);
				} else {
					result.addElement(st.toString());
					st = new StringBuffer();
					beginWithQuote = false;
				}
			} else {
				st.append(ch);
			}
		}
		if (st.length() != 0) {
			if (beginWithQuote) {
				throw new Exception(
						"last field is begin with but not end with double quote");
			} else {
				result.addElement(st.toString());
			}
		}
		String rs[] = new String[result.size()];
		for (int i = 0; i < rs.length; i++) {
			rs[i] = (String) result.elementAt(i);
		}
		return rs;
	}

	public static ArrayList<String[]> linePaser(String fileName) {
		// BufferedReader br;
		ArrayList<String[]> dataList = new ArrayList<String[]>();
		try {
			//FileInputStream fis = new FileInputStream(fileName);
			//InputStreamReader isr = new InputStreamReader(fis, encodin);
			//BufferedReader br = new BufferedReader(isr);
			BufferedReader br = null;
			br = new BufferedReader(new FileReader(fileName));
			String rec;
			String[] ret;
			int count = 0;
			while ((rec = br.readLine()) != null) {
				count ++ ;
				if(rec.endsWith("cnt_code") || rec.startsWith("(")) {
					System.out.println("过滤提示信息" + rec);
				} else {
					ret = splitLine(rec);
					if(ret.length != 6) {
						if(ret.length == 0) {
							System.out.println("数据过滤不是6个字段的数据。空行");
						} else {
							System.out.println("数据过滤不是6个字段的数据。第" + count + "行  " + rec);
						}
						
					} else {
						dataList.add(ret);
					}
				}
			}
			br.close();
			//isr.close();
			//fis.close();
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dataList;
	}
	
}