读取大csv文件数据插入到MySql或者Oracle数据库通用处理

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

import java.io.BufferedInputStream;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Properties;

public class TestDao{

static Properties prop = new Properties();

public static void main111(String[] args) {

String filePath = "D:\\shared\\other\\testaa.sql";
String filePathout = "D:\\shared\\other\\sqls\\testaaout";
BufferedReader bufferedReader = null;
BufferedWriter bufferedWriterMain = null;
BufferedWriter bufferedWriter = null;
try {

bufferedReader = new BufferedReader(new InputStreamReader(new FileInputStream(filePath)));
bufferedWriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(filePathout+"0.sql")));
bufferedWriterMain = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(filePathout+".sql")));
String line = null;
int i = 0;
int k = 0;
while ((line = bufferedReader.readLine()) != null) {
line = line.replace("`", "");

if (k==10000)
{
i++;
k=0;
bufferedWriter.close();
bufferedWriterMain.write("@"+filePathout+i+".sql\n");
bufferedWriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(filePathout+i+".sql")));
}
k++;
bufferedWriter.write(line+"\n");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
bufferedReader.close();
bufferedWriter.close();
bufferedWriterMain.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}

public static void main(String[] args) {
//args=new String[]{"D:\\shared\\other\\param.properties"};
String csvFile = "";
String dbDriver = "";
String dbUrl = "";
String dbName = "";
String dbPassWord = "";
String installSql= "";
String skipHead = "";

if (args.length == 1)
{
propertiesFile(args[0]);

csvFile = getPropertiesValue("csvFile");
dbDriver = getPropertiesValue("dbDriver");
dbUrl = getPropertiesValue("dbUrl");
dbName = getPropertiesValue("dbName");
dbPassWord = getPropertiesValue("dbPassWord");
installSql = getPropertiesValue("installSql");
skipHead = getPropertiesValue("skipHead");
}
else if(args.length == 7)
{
csvFile = args[0];
dbDriver = args[1];
dbUrl = args[2];
dbName = args[3];
dbPassWord = args[4];
installSql= args[5];
skipHead =args[6];
}
else
{
System.err.println("==method of calling one=================================================");
System.err.println("param 1:csv file url");
System.err.println("param 1 demo:D:\\work\\xxx.csv");
System.err.println("param 2:db driver");
System.err.println("param 2 demo:org.mariadb.jdbc.Driver");
System.err.println("param 3:db url");
System.err.println("param 3 demo:jdbc:mariadb://ip:3306/dbname?useUnicode=true&characterEncoding=UTF8&autoReconnect=true");
System.err.println("param 4:db name");
System.err.println("param 4 demo:esgscc");
System.err.println("param 5:db password");
System.err.println("param 5 demo:esgscc");
System.err.println("call demo:java -jar test.jar param1 param2 param3 param4 param 5");
System.err.println("==method of calling two=================================================");
System.err.println("param 1:properties file url");
System.err.println("param 1 demo:edit param.properties, update param value.");
System.err.println("call demo:java -jar test.jar param.properties");
return;
}

String filePath = csvFile; //"D:\\work\\2015\\12\fangwei.csv";
BufferedReader bufferedReader = null;
Connection conn = null;
String driver = dbDriver;//"org.mariadb.jdbc.Driver";
String url = dbUrl;//"jdbc:mariadb://10.202.6.76:3306/esgscc?useUnicode=true&characterEncoding=UTF8&autoReconnect=true";
int parCount = installSql.replace(installSql,"?").length();
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, dbName, dbPassWord);

bufferedReader = new BufferedReader(new InputStreamReader(new FileInputStream(filePath)));
String line = null;
int k = 0;
while ((line = bufferedReader.readLine()) != null) {
line = line.replace("\"", "");
String[] columns = line.split(",");
if(columns[0].trim().contains(skipHead)){
continue;
}
PreparedStatement pstmt = conn.prepareStatement(installSql);
int j = 1;

for (int i = 0; i < parCount; i++) {
pstmt.setString(j++, columns[i]);
}
k++;

pstmt.executeUpdate();

if (k>=299)
{
k = 0;
try
{
conn.close();
}
catch (Exception e) {
e.printStackTrace();
return;
}
conn = DriverManager.getConnection(url, dbName, dbPassWord);
}
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
conn.close();
bufferedReader.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}

private static void propertiesFile(String propertiesFileUrl)
{
try {
InputStream in = new BufferedInputStream(new FileInputStream(propertiesFileUrl));
prop.load(in);
} catch (IOException e) {
e.printStackTrace();
}
}

private static String getPropertiesValue(String key)
{
String value = "";
if (null != prop)
{
value = prop.getProperty(key);
System.out.println(key+value);
}

return null==value?"":value.trim();
}
}

配置文件

csvFile=D:\\shared\\other\\fangwei.csv
dbDriver=oracle.jdbc.driver.OracleDriver
dbUrl=jdbc:oracle:thin:@10.0.13.57:1521:cbs
dbName=name
dbPassWord=passwd
installSql=insert into testa(id) values (?)
skipHead=id_

执行

D:\shared\other>java -classpath classes12.jar;test.jar; TestDao param.properties