导入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();
?
?}
}
?
?
?
?
?
?
?
?
?
?
?
?
