mysql临时表导入csv文件_java调用sqlldr导入csv文件数据到临时表

package cn.com.file;

import java.io.BufferedReader;

import java.io.BufferedWriter;

import java.io.File;

import java.io.FileNotFoundException;

import java.io.FileReader;

import java.io.FileWriter;

import java.io.IOException;

public class Client {

public static void main(String args[]) throws FileNotFoundException

{

String path = "E:\\PORT";

String templateFile = "1.ctl";

String fileLists = "index.txt";

String commandText = "sqlldr.exe userid=webrescmxj/res4cmxj@resdb control=e:/PORT/tmp.ctl log=e:/PORT/1.log";

// 1.获取模板SQL

BufferedReader tReader = new BufferedReader(new FileReader(path + "\\" + templateFile));

StringBuffer buffer = new StringBuffer(); //记录模板SQL

String str = null;

try {

while((str = tReader.readLine())!=null){

buffer.append(str);

}

} catch (IOException e) {

e.printStackTrace();

}

//System.out.println(buffer.toString()); /* 测试打印使用 */

//2.拼装导入SQL

BufferedReader lsReader = new BufferedReader(new FileReader(path + "\\" + fileLists));

String tmpSQL = null; //记录模板SQL

str = null; //初始化

try {

while ((str = lsReader.readLine())!=null) {

tmpSQL = buffer.toString().replace("DEFAULT", path + "\\" + str);

System.out.println(tmpSQL);

//3. 将拼装好的SQL写入临时文件

File f = new File(path + "\\" + "tmp.ctl");

BufferedWriter writer = new BufferedWriter(new FileWriter(f));

writer.write(tmpSQL);

writer.flush();

writer.close(); //关闭写出

Process proc = Runtime.getRuntime().exec(commandText);  // 执行SQLLDR操作

//proc.waitFor();

}

} catch (IOException e) {

e.printStackTrace();

}

}

}

CTL文件内容:

load data infile 'PORT-20140307203455.csv'

append into table TRANS_PORT

(

CUID char terminated by ',' OPTIONALLY ENCLOSED BY '"',

LABEL_CN char terminated by ',' OPTIONALLY ENCLOSED BY '"',

RELATED_CARD char terminated by ',' OPTIONALLY ENCLOSED BY '"',

CARD_CUID char terminated by ',' OPTIONALLY ENCLOSED BY '"',

NE_NAME char terminated by ',' OPTIONALLY ENCLOSED BY '"',

RELATED_NE_CUID char terminated by ',' OPTIONALLY ENCLOSED BY '"',

EMS_NAME char terminated by ',' OPTIONALLY ENCLOSED BY '"',

RELATED_EMS_CUID char terminated by ',' OPTIONALLY ENCLOSED BY '"',

RATE char terminated by ',' OPTIONALLY ENCLOSED BY '"',

PORT_STATE char terminated by ',' OPTIONALLY ENCLOSED BY '"',

PORT_NO char terminated by ',' OPTIONALLY ENCLOSED BY '"',

PORT_TYPE char terminated by ',' OPTIONALLY ENCLOSED BY '"',

DOMAIN char terminated by ',' OPTIONALLY ENCLOSED BY '"',

MSTP_PORT_TYPE char terminated by ',' OPTIONALLY ENCLOSED BY '"',

CURRENTWAVENUM char terminated by ',' OPTIONALLY ENCLOSED BY '"',

WAVE_RATE char  terminated by whitespace OPTIONALLY ENCLOSED BY '"')

22222222222222222222222222222222222222222222222222222222222222

命令语句 "sqlldr user/pwd@sid control=a.ctl log=log.txt bad = bad.txt"

在windows的CMD命令行和UNIX里面直接调用没有任何问题。速度很快，可以上传成功。

但是我用java的Runtime.getRuntime().exec("sqlldr user/pwd@sid control=a.ctl log=log.txt bad = bad.txt"); 来调用sqlldr， 如果数据量小，没有任何问题，可以上传数据成功，但是数据大于2M的时候就会上传到一半突然停下来。LOG文件一直是空的，数据库也能看到上传了一部分数据，但是并没有上传成功。

感觉上是JAVA的Runtime.getRuntime().exec();被打断了。但是又没有什么办法测试。在windows里面用JAVA调也没问题，只有在UNIX里面出问题。。请教各位吧。有没有遇到过这种问题的帮忙一下。

问题补充：

那能不能说一下具体的解决方案呢？

我的平台是IBM AIX 400， jdk1.6，oracle 10g

---------------------解决方案：

正好解决了我心中的疑问，非常感谢！

我们的程序一开始就是exec完了接着waitFor()，但bat文件执行不完整：

Process proc = Runtime.getRuntime().exec(cmd);

proc.waitFor();

后面的build中在waitFor()之前读取了数据流，bat文件就可以完整执行了：

Process proc = Runtime.getRuntime().exec(cmd);

StreamGobbler errorGobbler = new StreamGobbler(proc.getErrorStream(), "Error");

StreamGobbler outputGobbler = new StreamGobbler(proc.getInputStream(), "Output");

errorGobbler.start();

outputGobbler.start();

proc.waitFor();

class StreamGobbler extends Thread {

InputStream is;

String type;

StreamGobbler(InputStream is, String type) {

this.is = is;

this.type = type;

}

public void run() {

try {

InputStreamReader isr = new InputStreamReader(is);

BufferedReader br = new BufferedReader(isr);

String line = null;

while ((line = br.readLine()) != null) {

if (type.equals("Error"))

LogManager.logError(line);

else

LogManager.logDebug(line);

}

} catch (IOException ioe) {

ioe.printStackTrace();

}

}

}

TestPrint.bat:

echo P1=%1  >D:\2.1.2env\2.1.2home\CompuSet\output\TestPrint.log

echo P2=%2 >>D:\2.1.2env\2.1.2home\CompuSet\output\TestPrint.log

echo P3&