java导入csv文件到mysql数据库里

java导入csv文件到mysql数据库里

不用导poi的包 简单易懂`
package com;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.LineNumberReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

public class ExceltoDB {

public static void main(String[] args) throws ClassNotFoundException, SQLException, Exception {
	String csvFilePath = "C:\\Users\\42126\\Desktop\\XJDTriad.csv";//C:\\Users\\42126\\Desktop\\XJDTriad.csv
	Class.forName("com.mysql.jdbc.Driver");
	Connection con=DriverManager.getConnection("jdbc:mysql://LocalHost:3306/iotdata", "root","123123");
	System.out.println("数据库连接成功！");
	readCsv(csvFilePath,con);
	System.out.println("数据导入完成!");
	}

	/**
	* map.put("IMEI", split[0].trim());//IMEI
	map.put("SN", split[1].trim());//SN
	map.put("DevicesName", split[2].trim());//DevicesName
	map.put("DeviceSecret",split[3].trim());//DeviceSecret
	map.put("ProductKey",split[4].trim() );//ProductKey
	map.put("State",split[5].trim());//State
	* @param csvFilePath
	* @param con
	* @throws Exception
	*/
	private static void readCsv(String csvFilePath, Connection con) throws Exception {
	int lineNumber=countLines();
	System.out.println("lineNumber="+lineNumber);
	BufferedReader br = null;
	try {
	String sql="Insert into iottable(IMEI,SN,DevicesName,DeviceSecret,ProductKey,State) values(?,?,?,?,?,1)";
	PreparedStatement pStatement=con.prepareStatement(sql);

	br = new BufferedReader(new FileReader(csvFilePath));
	String line =null;
	int i=1;
	while((line = br.readLine())!=null){
//	String ResultDynPara=DynamicParameter.getGuid();
	//读取到的内容给line变量
	//line = br.readLine();//加上这一行会导致readLine()方法被执行了两次,导致读取数据不全
	//System.out.println("line="+line);
	//System.out.println("line="+line);
	//ArrayList<String> csvList = new ArrayList<String>();
	//CsvReader reader = new CsvReader(csvFilePath,',',Charset.forName("gbk")); //一般用这编码读就可以了
	//reader.readHeaders(); // 跳过表头 如果需要表头的话，不要写这句。
	//逐条读取记录，直至读完
	//System.out.println(reader.readRecord());
	//while(reader.readRecord()){
	//csvList.add("123");
	//}
	//System.out.println("csvList="+csvList);
	Map map=new HashMap<>();
	//System.out.println(br.readLine());//读取每一行数据
	//replaceAll的参数是regex,即基于规则表达式的替换,比如,可以通过replaceAll("\\d", "*")把一个字符串所有的数字字符都换成星号;
	String[] split = line.replaceAll("\"", "").split(",");//去掉存入数据库中的""
	System.out.println("split.length="+split.length);

// Eexcel中该标题在哪个位置。下标从0开始
String IMEI="";
if (split[0].trim().length()!=0) {
IMEI=split[0].trim();
}
String SN="";
if (split[1].trim().length()!=0) {
SN= split[1].trim();
}
String DevicesName="";
if (split[2].trim().length()!=0) {
DevicesName=split[2].trim();
}
String DeviceSecret="";
if (split[3].trim().length()!=0) {
DeviceSecret=split[3].trim();
}
String ProductKey="";
if (split[4].trim().length()!=0) {
ProductKey=split[4].trim();
}
String State="";
// if (split[3].trim().length()!=0) {
// State=split[3].trim();
// }

//	pStatement.setString(1, ResultDynPara);
	
	pStatement.setString(1, IMEI);
	pStatement.setString(2, SN);
	pStatement.setString(3, DevicesName);
	pStatement.setString(4, DeviceSecret);
	pStatement.setString(5, ProductKey);
//	pStatement.setString(6,State );
	pStatement.execute();
	System.out.println("pStatement="+pStatement);
	System.out.println("第"+(i++)+"次循环");
	}
	} catch (SQLException e) {
	e.printStackTrace();
	}

	}

	private static int countLines() throws Exception {
	int lineNumber=0;
	File file4=new File("C:\\Users\\42126\\Desktop\\XJDTriad.csv");
	if(file4.exists()) {
	FileReader fr=new FileReader(file4);
	LineNumberReader lnr=new LineNumberReader(fr);

	long timeS=System.currentTimeMillis();//时间单位为ms
	while(null != lnr.readLine()) {
	lineNumber+=1;
	}
	long timeE=System.currentTimeMillis();
	System.out.println("此种方法所耗时间为："+(timeE-timeS)+"ms,文件总条数为："+lineNumber+"条");
	lnr.close();
	}
	return lineNumber;
	}

}

以下是导入到数据库的贴图