导入csv文件

 

package com.kylin.test.ImportCSV;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class MetaDataInfoProvider {

 public int getMetaDataName(String m_TableName, Connection m_Connection) {
  int colCount = 0;
  try {
   if (m_Connection == null) {
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    m_Connection = DriverManager
      .getConnection("jdbc:mysql://localhost:3306/db_extjs?;characterEncoding=gbk&useUnicode=true","admin","admin");
   }

   DatabaseMetaData m_DBMetaData = m_Connection.getMetaData();
   ResultSet tableRet = m_DBMetaData.getTables(null, "%", m_TableName,
     new String[] { "TABLE" });
   while (tableRet.next())
    System.out.println("Table name is:"
      + tableRet.getString("TABLE_NAME"));

   String columnName;
   String columnType;

   ResultSet colRet = m_DBMetaData.getColumns(null, "%", m_TableName,
     "%");

   while (colRet.next()) {

    columnName = colRet.getString("COLUMN_NAME");
    columnType = colRet.getString("TYPE_NAME");
    int datasize = colRet.getInt("COLUMN_SIZE");
    int digits = colRet.getInt("DECIMAL_DIGITS");
    int nullable = colRet.getInt("NULLABLE");
    String nullFlag;
    if (nullable == 1) {
     nullFlag = "Null";
    } else {
     nullFlag = "Not Null";
    }

    System.out.println(columnName + " " + columnType + "("
      + datasize + "," + digits + ") " + nullFlag);

    colCount++;
   }
  } catch (SQLException e) {
   e.printStackTrace();
  } catch (InstantiationException e) {
   e.printStackTrace();
  } catch (IllegalAccessException e) {
   e.printStackTrace();
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  }
  System.out.println("The number of column is: " + colCount);
  return colCount;
 }

 public static void main(String args[]) {
  MetaDataInfoProvider mdip = new MetaDataInfoProvider();
  mdip.getMetaDataName("dim_customer", null);
 }

}

 

 

 

 

 

package com.kylin.test.ImportCSV;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

 
public class InsertDB extends Thread {

 private static final String user = "admin";
 private static final String pwd = "admin";
 private static final String url = "jdbc:mysql://localhost:3306/db_extjs?;characterEncoding=gbk&useUnicode=true";
 private static final String driver = "com.mysql.jdbc.Driver";
 private static String DELIMITERS = ",";

 public static String getDELIMITERS() {
  return DELIMITERS;
 }

 public static void setDELIMITERS(String delimiters) {
  DELIMITERS = delimiters;
 }

 public static Connection getCon() {
  Connection con = null;
 
  try {

   Class.forName(driver).newInstance();
   con = DriverManager.getConnection(url, user, pwd);
   if (con != null) {
    System.out.println("你已连接到数据库：" + con.getCatalog());
   }

  } catch (Exception e) {
   System.out.println("连接数据库失败！");
   e.printStackTrace();
  }

  return con;

 }

 public boolean insertDB(String tablename, long rc) {
  if (rc < 1) {
   rc = 100;
  }
  Connection con = null;
  Statement stm = null;
  boolean flag = false;
  Statement pre;
 
  String sql = "";
  MetaDataInfoProvider mdip = new MetaDataInfoProvider();
  try {
   con = getCon();
   stm = con.createStatement();
   pre=con.createStatement();
   int colCount = mdip.getMetaDataName(tablename, con);
   int rowCount = 0;

   File raf = new File("c:/Tempaltes.csv");
   BufferedReader buf = null;
   buf = new BufferedReader(new InputStreamReader(new FileInputStream(
     raf)));

  // FileWriter fw = new FileWriter("f:/dim_customer_new.sql", true); //以文件方式输出
  // BufferedWriter bw = new BufferedWriter(fw);

   String line_record = buf.readLine();
  
   long sqlstart = System.currentTimeMillis(); //开始计时
  
   while (line_record != null) {

    // 解析每一条记录
    sql = "insert into " + tablename + " values('";
   
    String[] fields = line_record.split(DELIMITERS);
   
   
      //对Insert语句的合法性进行判断
     
       if(fields.length!=colCount){
       System.out.println("要插入的数据列数和表的数据列不相匹配，停止执行"); break; }
   
   
    for (int i = 0; i < fields.length; i++) {
     sql += fields[i];
     if (i < fields.length - 1) {
      sql += "','";
     }
    }
   
    sql += "');";

    // 在控制台输出SQL语句
   // System.out.println(sql);

   
     //执行SQL语句
     // stm.executeUpdate(sql); //直接执行效率比较低
    pre.addBatch(sql);
   
   
    rowCount++;
    line_record = buf.readLine();
    if (rowCount >= rc)
     break;
   }
   pre.executeBatch();
   pre.close();
 //  bw.flush(); // 将数据更新至文件
 //  bw.close();
 //  fw.close();
  
 //  bw.close();
 //  fw.close();
  
   System.out.println("共写入行数：" + rowCount);
  
   long sqlend = System.currentTimeMillis(); //停止计时
  
   System.out.println("执行时间为:" + (sqlend - sqlstart) + " ms");

  } catch (Exception e) {
   flag = false;
   e.printStackTrace();
  } finally {
   close(null, stm, con);
  }
  return flag;
 }

 // 关闭相关连接

 public void close(ResultSet rs, Statement stm, Connection con) {
  if (rs != null)
   try {
    rs.close();
   } catch (Exception e) {
    e.printStackTrace();
   }

  if (stm != null)
   try {
    stm.close();
   } catch (Exception e) {
    e.printStackTrace();
   }

  if (con != null)
   try {
    con.close();
   } catch (Exception e) {
    e.printStackTrace();
   }
 }
    public void run() {

      
         this.insertDB("person", 500000);

        }

 public static void main(String[] args) {
  InsertDB insertDB1 = new InsertDB();
  insertDB1.start();
 
//  InsertDB insertDB2 = new InsertDB();
//  insertDB2.start();
 
 }
}