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

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