1, 其中csv文件就相当于excel中的另一种保存形式,其中在插入的时候是和数据库中的表相对应的,这里面的colunm 就相当于数据库中的一列,对应csv表中的一列。
2,在我的数据库表中分别创建了两列A ,B属性为varchar。
3,在这里面中,表使用无事务的myISAM 和支持事务innodb都可以,但是MyISAM速度较快。
4, String sql = "load data infile 'E://test.csv' replace into table demo fields terminated by ',' enclosed by '\\'' lines terminated by '\\r\\n' (`A`,`B`) "; 这句话是MySql的脚本在java中的使用,这个插入速度特别快,JDBC自动解析该段代码进行数据的读出,并且插入到数据库。要注意在load data中转义字符的使用。 如果要使用load
data直接进行执行一下这句话,(不过要记得更改成自己的文件名 和 表名)就可以把文件中的内容插入,速度特别快。值得一试哦
下面是我给出的一段最基本的 通过io进行插入的程序,比较详细。
package com.util.user;
import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.LineNumberReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class OfChangeCSVToMysql {
/**
* @author clq
*/
public static void main(String[] args) {
try {
long start = System.currentTimeMillis();
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/girlandboy?rewriteBatchedStatements=true","root","1234");
conn.setAutoCommit(false);
List<String> listData = readFile("E://test.csv");//read a file return every line
for(String list:listData){
System.out.println(list);
/*
* in the csv file default splited with ',',every column to the database table's column
*/
String []column = list.split(",");
//The 'A' is the column in the csv,and in the database table
if(column[0].trim().equalsIgnoreCase("A")){
continue;
}
String sql = "insert into demo(A, B) values('"+column[0]+"','"+column[1]+"')";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.execute();
/ pstmt.addBatch()//this is the batch
}
/ / pstmt.executeBatch();
conn.commit();
System.out.println(System.currentTimeMillis() - start);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public static List<String> readFile(String filePathAndName)throws IOException {
import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.LineNumberReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class OfChangeCSVToMysql {
/**
* @author clq
*/
public static void main(String[] args) {
try {
long start = System.currentTimeMillis();
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/girlandboy?rewriteBatchedStatements=true","root","1234");
conn.setAutoCommit(false);
List<String> listData = readFile("E://test.csv");//read a file return every line
for(String list:listData){
System.out.println(list);
/*
* in the csv file default splited with ',',every column to the database table's column
*/
String []column = list.split(",");
//The 'A' is the column in the csv,and in the database table
if(column[0].trim().equalsIgnoreCase("A")){
continue;
}
String sql = "insert into demo(A, B) values('"+column[0]+"','"+column[1]+"')";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.execute();
/ pstmt.addBatch()//this is the batch
}
/ / pstmt.executeBatch();
conn.commit();
System.out.println(System.currentTimeMillis() - start);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public static List<String> readFile(String filePathAndName)throws IOException {
FileInputStream fis = new FileInputStream(filePathAndName);
InputStreamReader isr = new InputStreamReader(fis, "UTF-8");
BufferedReader br = new BufferedReader(isr);
LineNumberReader lnr = new LineNumberReader(br);
List<String> returnValue = new ArrayList<String>();
int cnt = 0;
while (true) {
cnt++;
String tempStr = lnr.readLine();
if (tempStr == null)
break;
if (tempStr.length() < 2)
continue;
returnValue.add(tempStr);
}
lnr.close();
br.close();
isr.close();
fis.close();
return returnValue;
}
}