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文件中的數據。