Java处理30W数据入库的问题！

Question

30W的数据保存在txt文件中，格式如下：

部门名称，部门id，上级部门id，等级

部门名称1,001,01,2 //01表示企业id
部门名称2,002,001,2
部门名称3,003,002,2

其中每条记录要插入的表有部门表，部门关系表，名片表。

问题：该如何处理这些数据保存到mysql数据库，获得较高的性能？？

Answer 1

楼主不妨看下我的代码,处理1000万10几分钟就OK了：

[code="java"]
package net.ltan.test;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.DriverManager;

public class SQLTools {

    private static int failed = 0;
    private static int success = 0;
    private static final String MYSQLDRIVER = "com.mysql.jdbc.Driver";
    private static final String MYSQLURL = "jdbc:mysql://localhost:3306/db?user=root&password=&useUnicode=true&characterEncoding=utf8&autoReconnect=true";//使用时修改下数据库连接信息

    /**
     * 数据库更新
     * @param sql
     */
    public static void executeSQL(StringBuilder sql) {
            try {
                    Class.forName(MYSQLDRIVER);
                    Connection conns = DriverManager.getConnection(MYSQLURL);
                    conns.prepareStatement(sql.toString()).execute();
            } catch (Exception e) {
                    e.printStackTrace();
            }
    }

    /**
     * 把SQL批量插入数据库
     */
    public static void getSql(){
            try {
                    File file=new File("d:/csdn/");
                      String test[];
                      test=file.list();
                      for(int i=0;i<test.length;i++){
                              BufferedReader br = new BufferedReader(new FileReader(new File("d:/csdn/"+test[i])));
                              StringBuilder sb = new StringBuilder();
                              String temp = null;
                              while((temp=br.readLine())!=null){
                                      sb.append(temp);
                              }
                              System.out.println("开始导入:"+test[i]);
                              executeSQL(sb.delete(sb.length()-1, sb.length()));
                      }
            } catch (Exception e) {
                    e.printStackTrace();
            }
    }

    /**
     * 把txt转换成批量SQL
     */
    public static void toSql(){
            try {
                    StringBuilder sb = new StringBuilder();
                    BufferedReader br = new BufferedReader(new FileReader(new File("d:/www.csdn.net.sql")));//数据库地址
                    BufferedWriter bw = null;
                    String temp = null;
                    while((temp = br.readLine())!=null){
                            if (success%10000==0) {
                                    sb.append("INSERT INTO CSDN (username,password,email) VALUES ");
                                    bw = new BufferedWriter(new FileWriter(new File("d:/csdn/csdn_sql_"+(success/10000)+".txt")));//转换后存放数据库的目录
                            }
                            String str[] = temp.split("\\s#\\s");
                            if (str.length == 3) {
                                    sb.append("(");
                                    for (int i = 0; i < str.length; i++) {
                                            sb.append((i!=3-1?"'"+sqlSpecialChars(str[i])+"',":"'"+sqlSpecialChars(str[i])+"'"));//Mysql特殊字符转意
                                    }
                                    sb.append("),");
                                    bw.write(sb.toString());
                                    bw.newLine();
                                    bw.flush();
                                    sb.setLength(0);
                                    ++success;
                                    System.out.println("转换成功:"+success+"次。");
                            }else {
                                    ++failed;
                                    System.out.println("转换失败:"+failed+"次。");
                            }
                    }
                    System.out.println("转换完成，共转换:"+success+"次。失败："+failed+"次。");
                    System.out.println("-----------------开始导入SQL到数据库--------------");
                    getSql();
            } catch (Exception e) {
                    e.printStackTrace();
            }
    }

    /**
     * 转意MYSQL中的特殊字符
     * @param str
     * @return
     */
    public static String sqlSpecialChars(String str) {
    str = str.replaceAll("\"", "\\\\\"");
    str = str.replaceAll("\\\\", "\\\\\\\\");
    str = str.replaceAll("'", "\\\\'");
    return str;
    }

    public static void main(String[] args) {
            toSql();
    }

}
[/code]