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

该Java程序实现从大型CSV文件中读取数据,并分批写入MySQL数据库。通过配置文件设置数据库连接参数、SQL语句和跳过头部行。程序使用BufferedReader和PreparedStatement提高效率。
摘要由CSDN通过智能技术生成

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值