java使用javacsv读取csv文件 导入Mysql数据库

科技优家 2016-11-23 09:48

包和测试文件已经上传：

package csv;

import java.io.IOException;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import com.csvreader.CsvReader;
import com.csvreader.CsvWriter;

public class DB2ExportCsv   
{   
    /**  
    * 读取CSV文件  
     * @param con  数据库访问
     * @param csvFilePath   文件路径
    */  
    public static void  readCsv(String csvFilePath, Connection con){   
        try {       
        String sql="INSERT INTO app_stock_news(title,external_links,create_time,news_time,stock_code,stock_name,content) VALUES(?,?,?,?,?,?,?)";
        PreparedStatement pStatement=con.prepareStatement(sql);
        
        ArrayList<String> csvList = new ArrayList<String>; //用来保存数据 
        //生成CsvReader对象，以，为分隔符，GBK编码方式
        CsvReader reader = new CsvReader(csvFilePath,',',Charset.forName("gbk"));    //一般用这编码读就可以了     
 reader.readHeaders; // 跳过表头   如果需要表头的话，不要写这句。  
 //逐条读取记录，直至读完
 while(reader.readRecord){ 
 csvList.add(reader.getValues);   
 } 
 reader.close;   
 for(int row=0;row<csvList.size;row++){   
 String  cell = csvList.get(row)[0]; //取得第row行第0列的数据  
 System.out.println(cell+"    "+ csvList.get(row)[1]+"    "+ csvList.get(row)[2]+"   "+ csvList.get(row)[3]+"    "+ csvList.get(row)[4]);   
 if( csvList.get(row)[0]!=null||csvList.get(row)[1]!=null||csvList.get(row)[2]!=null||csvList.get(row)[3]!=null||csvList.get(row)[4]!=null||csvList.get(row)[5]!=null&&csvList.get(row)[6]!=null){
        pStatement.setString(1, csvList.get(row)[1]);//title
        pStatement.setString(2, csvList.get(row)[2]);//external_links
        pStatement.setString(3, csvList.get(row)[0]);
        pStatement.setString(4, csvList.get(row)[5]);
        pStatement.setString(5, csvList.get(row)[4]);
        pStatement.setString(6, csvList.get(row)[3]);
        pStatement.setString(7, csvList.get(row)[6]);
        pStatement.execute;
        }else{
        System.out.println("这条数据不全，丢弃..");
        }
 }        
 } catch (Exception ex) {   
 System.out.println(ex);   
 }   
    }   
       
//    写入CSV文件，无追加功能，所以每次都得重新写（包括表头）：
//
//    1 CsvWriter wr =new CsvWriter("F://Eclipse//Test//src//info.csv",',',Charset.forName("GBK"));
//    2         String contents = {"Lily","五一","90","女"}; 
//    3         wr.writeRecord(contents);
//    4         wr.close;
    
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
    String csvFilePath = "D://jiangwan//jrj_ssgs_11_content11_1.csv";  
    Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://LocalHost:3306/tradergem_news", "root","123456");
System.out.println("数据库连接成功！");
    readCsv(csvFilePath,con);
    System.out.println("数据导入完成!");
}
    
}