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

编程技术  /  houtizong 发布于 3年前   69

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;}}