待补充
导入sqlloader数据例子(when条件的,只能使用and连接,插入的顺序要跟文件中的列的顺序相同):
options(skip=1)
load data
characterset UTF8
infile 'd:\xxkCommon\20120310_hb_cust_basic_data_month.csv'
replace
INTO TABLE T_CUSTOMER_ALL_TEMP
when CUSTOMER_TYPE != '政企' and CUSTOMER_TYPE!='公务政企' and CUSTOMER_TYPE!='公用'
FIELDS terminated by ',' OPTIONALLY ENCLOSED BY '"' --,分隔符
TRAILING NULLCOLS ( DT_DATE date 'yyyy-MM-dd',
CUST_ID,
phone1,
CUSTOMER_NAME,
ADDRESS,
ACNT_ADDR_FULL_TXT,
INCOME,
PROD_INST_TYPE)
2,(使用序列,序列放在最后一列进行自动添加)
options(skip=1)
load data
characterset UTF8
infile 'd:\xxkCommon\daily_subtotal_2012-03-27.txt'
append
INTO TABLE t_traffic_detail
fields terminated by X'09' --一个tab,分隔符
TRAILING NULLCOLS (
CALL_TIME date 'yyyy-MM-dd',
WAS_CALL_NUMBER,
CALL_NUMBER,
CALL_COUNT,
TRAFFIC_DETAIL_ID "SEQ_TRAFFIC_DETAIL.NEXTVAL"
)
使用该命令行,首先确保服务器安装oracle客户端,并且保证环境变量配置正确,例如:
path:D:\oracle\product\10.2.0\db_1\bin;%JAVA_HOME%/bin;%JAVA_HOME%/jre/bin;
同时在cmd中测试sqlldr,时候能够使用,如果能够使用则使用该命令:
sqlldr userid=user1/user1@database1 control='d:\input.ctl' log='d:\inputLog.log'
使用该命令则可以进行客户端导入,同时可观察日子文件中的信息,根据日志信息进行修改更正。
B:在java定时任务中调用该方法
protected void executeInternal(JobExecutionContext arg0)
throws JobExecutionException {
String fileName="";
try {
fileName = ftpUtils.downFileCSV(ConstantConfig.COMMONFILEPATH, "_hb_cust_basic_data_month.csv", DateTime.getSssq());
if (fileName!=null&&!fileName.equals("")) {
//文件名称加上地址
fileName=ConstantConfig.COMMONFILEPATH+fileName;
//得到下载到本机的地址
File file = new File(fileName);
if (file.exists()) {
long start = System.currentTimeMillis();
String sqlCtlStr = "options(skip=1) \r\n" +
"load data \r\n" +
"characterset UTF8 \r\n"+
"infile '"+fileName+"'"+"\r\n"+
"replace \r\n"+
"INTO TABLE T_CUSTOMER_ALL_TEMP \r\n" +
"when CUSTOMER_TYPE != '政企' and CUSTOMER_TYPE!='公务政企' and CUSTOMER_TYPE!='公用'"+"\r\n"+
"FIELDS terminated by ',' OPTIONALLY ENCLOSED BY '\"' \r\n"+
"TRAILING NULLCOLS "+
"( " +
"DT_DATE date 'yyyy-MM-dd',"+"\r\n"+
"CUST_ID,"+"\r\n"+
"phone1,"+"\r\n"+
"CUSTOMER_NAME,"+"\r\n"+
"ADDRESS,"+"\r\n"+
"ACNT_ADDR_FULL_TXT,"+"\r\n"+
"POSTCODE,"+"\r\n"+
"DEV_BUREAU_NAME,"+"\r\n"+
"DEV_SUB_BUREAU_NAME,"+"\r\n"+
"CARD_TYPE,"+"\r\n"+
"CARD_CODE,"+"\r\n"+
"sex,"+"\r\n"+
"age,"+"\r\n"+
"CUSTOMER_TYPE,"+"\r\n"+
"TOTAL_DATA date 'yyyy-MM-dd',"+"\r\n"+
"PROD_INST_TYPE" +
")";
String fileCtl = ConstantConfig.COMMONFILEPATH+"customerMonthLoader.ctl";
String fileLog = ConstantConfig.COMMONFILEPATH+"customerMonthLog.log" ;
//写入文件
ConstantConfig.setCtlContent(fileCtl, sqlCtlStr);
String sqlLoaderCommand = "sqlldr " + ConstantConfig.SQLUSERID + " control=" + fileCtl + " log="+fileLog;
Process ldr= Runtime.getRuntime().exec(sqlLoaderCommand);
InputStream stderr = ldr.getInputStream();
InputStreamReader isr = new InputStreamReader(stderr);
BufferedReader br = new BufferedReader(isr);
String line = null;
while ( (line = br.readLine()) != null){
System.out.println("*** " + line);
}
stderr.close();
isr.close();
br.close();
ldr.waitFor();
//删除下载到本地的文件
if (file.exists()) {
file.delete();
}
long end = System.currentTimeMillis();
long timeLoss = (end - start) / 1000;
logger.info("[1.月客户流量CTL脚本执行结束,已导入数据,生成日志文件" + fileLog + ",耗时" + timeLoss
+ "s!]");
//执行存储过程,比对数据进入到正式库中,并且添加流量信息
custAgrDAO.addCust("1");
long end2 = System.currentTimeMillis();
timeLoss = (end2 - end) / 1000;
logger.info("[1.周客户流量CTL脚本执行结束,已导入数据,生成日志文件" + fileLog + ",耗时" + timeLoss
+ "s!]");
}
}
} catch (IOException e) {
e.printStackTrace();
logger.error("导入月客户流量文件,名称为:"+fileName+"发生IO异常,异常原因为:"+e.getMessage());
} catch (InterruptedException e) {
e.printStackTrace();
logger.error("导入月客户流量文件,名称为:"+fileName+"发生IO异常,异常原因为:"+e.getMessage());
}
}
public static void setCtlContent(String fileName,String sqlCtlStr){
File file = new File(fileName);
BufferedWriter writer;
try {
if (!file.exists()) {
file.createNewFile();
}
writer = new BufferedWriter(new FileWriter(file, false));
writer.append(sqlCtlStr);
writer.flush();
writer.close();
} catch (IOException e1) {
e1.printStackTrace();
}
}