posts - 431,  comments - 344,  trackbacks - 0
一年前一个项目里面使用了CSVJDBC,当时觉得这个工具特别好,今天又遇到客户给我一个CSV文件,里面有20万的数据,需要导入到数据库中进行分析处理。所以首选当然是CSVJDBC了。具体代码实现如下:

package com.founder.demo;

import Java.sql.Connection;
import Java.sql.DriverManager;
import Java.sql.PreparedStatement;
import Java.sql.ResultSet;
import Java.sql.Statement;

public class ImportCSV {

 public static void main(String[] args) {
  try {
   // load the driver into memory
   Class.forName("org.relique.jdbc.csv.CsvDriver");

   // create a connection. The first command line parameter is assumed to
   //  be the directory in which the .csv files are held
   Connection conn = DriverManager
     .getConnection("jdbc:relique:csv:H:\\PythonWorkSpace");

   // create a Statement object to execute the query with
   Statement stmt = conn.createStatement();

   // Select the columns from csv file
   ResultSet results = stmt
     .executeQuery("SELECT ORDER_NO,ARTICLE_NO,CATALOG_NO,DESCRIPTION,QUANTITY,ISO_UNIT,UNIT,MDL_NO,CAS, "
       + " MOLECULA,FORMULA,DENSITY,PRICE_EUR,UN_NO,DANGER_GR,DANGER_CLASS,ZUSATZGEFAHR1,ZUSATZGEFAHR2,R_PHRASES, "
       + " S_PHRASES,DANGER_SYMBOL,STORAGE_TEMPERATURE FROM ABCR");
   
   //MySQL
   Class.forName("com.mysql.jdbc.Driver").newInstance();
   Connection con = Java.sql.DriverManager
     .getConnection(
       "jdbc:mysql://localhost/chemicaldb?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull",
       "root", "root");

   String sql = "INSERT INTO meryer_abcr (ORDER_NO,ARTICLE_NO,CATALOG_NO,DESCRIPTION,QUANTITY,ISO_UNIT,UNIT,MDL_NO,CAS, "
     + " MOLECULA_FORMULA,MOLECULAR_WEIGHT,DENSITY,PRICE_EUR,UN_NO,DANGER_GROUP,DANGER_CLASS,ZUSATZGEFAHR1,ZUSATZGEFAHR2,R_PHRASES, "
     + " S_PHRASES,DANGER_SYMBOL,STORAGE_TEMPERATURE) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

   PreparedStatement mstmt = con.prepareStatement(sql);

   // dump out the results and set params
   while (results.next()) {
    mstmt.setString(1, results.getString("ORDER_NO"));
    mstmt.setString(2, results.getString("ARTICLE_NO"));
    mstmt.setString(3, results.getString("CATALOG_NO"));
    mstmt.setString(4, results.getString("DESCRIPTION"));
    mstmt.setString(5, results.getString("QUANTITY"));
    mstmt.setString(6, results.getString("ISO_UNIT"));
    mstmt.setString(7, results.getString("UNIT"));
    mstmt.setString(8, results.getString("MDL_NO"));
    mstmt.setString(9, results.getString("CAS"));
    mstmt.setString(10, results.getString("MOLECULA_FORMULA"));
    mstmt.setString(11, results.getString("MOLECULAR_WEIGHT"));
    mstmt.setString(12, results.getString("DENSITY"));
    mstmt.setString(13, results.getString("PRICE_EUR"));
    mstmt.setString(14, results.getString("UN_NO"));
    mstmt.setString(15, results.getString("DANGER_GROUP"));
    mstmt.setString(16, results.getString("DANGER_CLASS"));
    mstmt.setString(17, results.getString("ZUSATZGEFAHR1"));
    mstmt.setString(18, results.getString("ZUSATZGEFAHR2"));
    mstmt.setString(19, results.getString("R_PHRASES"));
    mstmt.setString(20, results.getString("S_PHRASES"));
    mstmt.setString(21, results.getString("DANGER_SYMBOL"));
    mstmt.setString(22, results.getString("STORAGE_TEMPERATURE"));
    mstmt.execute();
    
    System.out.println(results.getString("ORDER_NO"));
   }
   
   // clean up
   mstmt.close();
   con.close();
   results.close();
   stmt.close();
   conn.close();
  } catch (Exception e) {
   System.out.println("Oops-> " + e);
  }

 }

}

其实很简单,和操作数据库一样,提供了类似数据中的SQL语句来操作CSV文件中的数据。

posted on 2009-08-04 11:27 周锐 阅读(1897) 评论(0)  编辑  收藏 所属分类: JavaMySQL

只有注册用户登录后才能发表评论。


网站导航: