最近在做项目时需要每日更新大小额支持的银行列表信息，该文件从核心下载，并使用java项目解析放到数据库中，该文件是文本文件，一行为一条记录，记录中以逗号分隔字段，字段以双引号包裹，起初以这种规则自己写一个小程序也能用，于是写了几句代码，确实能运行解析出大部分内容，但有些特殊情况是没有解析出来的，例如：双引号包裹的的字段内容里面也有逗号，但这个逗号并不是字段之间的分隔符，而是字段内容的一部分。起初并不知道这其实是一种标准的csv格式，虽然曲折但是整好了记录下
package com.schedule;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.sql.DataSource;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import com.ynet.ifp.eams.core.util.FtpUtils;
import com.ynet.ifp.eams.utils.file.UncompressFileGZIP;
import org.springframework.stereotype.Component;
/**
* 更新大小额联行号信息 暂定每天晚上11点执行
*/
@Component(“firstUpdatePxdzfInfoSchedule”)
public class FirstUpdatePxdzfInfoScheduleImpl extends EamsScheduledService {
private static Logger log = Logger.getLogger(FirstUpdatePxdzfInfoScheduleImpl.class);
@Autowired
private com.ynet.ifp.core.utils.TxUtils txUtils;
@Autowired
private DataSource dataSource;
@Value(“${hx.bank.host}”)
private String host;
@Value(“${hx.bank.port}”)
private int port;
@Value(“${hx.bank.path}”)
private String path;
@Value(“${hx.bank.name}”)
private String name;
@Value(“${hx.bank.pwd}”)
private String pwd;
@Value(“${hx.bank.fileName}”)
private String fileName;
@Value(“${hx.bank.down.path}”)
private String downPath;
public String getCnName() {
return “定时任务–更新大小额联行号信息”;
}
@Override
protected void doService() throws Exception {
Connection con= null;
try{
log.info(“updatePxdzfInfoSchedule schedule is going to start…..”);
Date date = new Date();
//凌晨一点取昨天3点左右放的文件
Date tDay = DateUtils.addDays(date, -2);
DateFormat df = new SimpleDateFormat(“yyyyMMdd”);
String dateStr=df.format(tDay);
BufferedReader reader = null;
String path =downPath+”PXDZF.unl”;
String line= “”;
try {
reader = new BufferedReader (new InputStreamReader(new FileInputStream(new File(path)),”GBK”));
} catch (FileNotFoundException e) {
e.printStackTrace();
}
// 获取连接
con= dataSource.getConnection();
con.setAutoCommit(false);
String deleteSql=”delete from yc_pxdzf”;
PreparedStatement deleteStat= con.prepareStatement(deleteSql);
log.info(deleteSql);
deleteStat.execute();
con.commit();
String serialNumber = “yc_id_sequence.nextVal”;
String sql=”insert into YC_PXDZF (YC_FQHHO2, YC_ZHUANT, YC_JIGULB, YC_HANBDM, YC_C2ZCHH, YC_BHSJCY, YC_C2RHDM, YC_FBHHO2, YC_QSHHO2, YC_JIEDDM,”+
” YC_FKHMC1, YC_CXUMC1, YC_YLIUBZ, YC_SUSDDM, YC_DIZHI1, YC_YOUZBM, YC_TFDESC, YC_BYZDBE, YC_SXIORQ, YC_SHIXRQ, YC_BEIZXX, YC_BEIY40,”+
” YC_BYBZ01, YC_BYBZ02, YC_WEIHRQ, YC_WEIHSJ, YC_WEIHGY, YC_ROWIDD, YC_SHJNCH, YC_JILUZT,YC_ID) values “+
“(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,”+serialNumber+”)”;
PreparedStatement stat= con.prepareStatement(sql);
int t=0;
while((line=reader.readLine())!=null){
String regExp =getRegExp();
String str = “”;
Pattern pattern = Pattern.compile(regExp);
Matcher matcher = pattern.matcher(line);
List listTemp = new ArrayList();
while(matcher.find()) {
str = matcher.group();
str = str.trim();
if (str.endsWith(“,”)){
str = str.substring(0, str.length()-1);
str = str.trim();
}
if (str.startsWith(“\””) && str.endsWith(“\””)) {
str = str.substring(1, str.length()-1);
if (isExisted(“\”\””, str)) {
str = str.replaceAll(“\”\””, “\””);
}
}
if (!””.equals(str)) {
listTemp.add(str);
}
}
String[] pmsbankno = (String[]) listTemp.toArray(new String[listTemp.size()]);
for(int i=0;i<pmsbankno.length;i++){
String tempString=pmsbankno[i];
tempString=tempString.replaceAll(“\””, “”).trim();
pmsbankno[i]=tempString;
}
stat.setString(1,pmsbankno[0]);
stat.setString(2,pmsbankno[1]);
stat.setString(3, pmsbankno[2]);
stat.setString(4,pmsbankno[3]);
stat.setString(5,pmsbankno[4]);
stat.setString(6,pmsbankno[5]);
stat.setString(7,pmsbankno[6]);
stat.setString(8, pmsbankno[7]);
stat.setString(9,pmsbankno[8]);
stat.setString(10,pmsbankno[9]);
stat.setString(11,pmsbankno[10]);
stat.setString(12, pmsbankno[11]);
stat.setString(13, pmsbankno[12]);
stat.setString(14, pmsbankno[13]);
stat.setString(15, pmsbankno[14]);
stat.setString(16,pmsbankno[15]);
stat.setString(17, pmsbankno[16]);
stat.setString(18,pmsbankno[17]);
stat.setString(19,pmsbankno[18]);
stat.setString(20, pmsbankno[19]);
stat.setString(21,pmsbankno[20]);
stat.setString(22, pmsbankno[21]);
stat.setString(23,pmsbankno[22]);
stat.setString(24, pmsbankno[23]);
stat.setString(25,pmsbankno[24]);
stat.setString(26, pmsbankno[25]);
stat.setString(27, pmsbankno[26]);
stat.setString(28, pmsbankno[27]);
stat.setString(29, pmsbankno[28]);
stat.setString(30, pmsbankno[29]);
stat.addBatch();
if(t%100==0){
int[] resultInt=stat.executeBatch();
con.commit();
}
}
int[] resultInt=stat.executeBatch();
String updateSql=”update BATCH_JOB_EXECUTION e set e.end_time = sysdate where e.END_TIME is NULL and e.job_instance_id in (select i.job_instance_id from BATCH_JOB_INSTANCE i where i.job_name = ‘updatePxdzfInfoSchedule’)”;
PreparedStatement updatestat= con.prepareStatement(updateSql);
updatestat.execute();
con.commit();
con.close();
}catch(Exception e){
e.printStackTrace();
con.close();
}
}
private String getRegExp() {
String strRegExp = “”;
strRegExp =
“\”((“+ SPECIAL_CHAR_A + “*[,\\n ])*(“+ SPECIAL_CHAR_A + “*\”{2})*)*”+ SPECIAL_CHAR_A + “*\”[ ]*,[ ]*”
+”|”+ SPECIAL_CHAR_B + “*[ ]*,[ ]*”
+ “|\”((“+ SPECIAL_CHAR_A + “*[,\\n ])*(“+ SPECIAL_CHAR_A + “*\”{2})*)*”+ SPECIAL_CHAR_A + “*\”[ ]*”
+ “|”+ SPECIAL_CHAR_B + “*[ ]*”;
return strRegExp;
}
private static final String SPECIAL_CHAR_A = “[^\”,\\n ]”;
private static final String SPECIAL_CHAR_B = “[^\”,\\n]”;
private boolean isExisted(String argChar, String argStr) {
boolean blnReturnValue = false;
if ((argStr.indexOf(argChar) >= 0)
&& (argStr.indexOf(argChar) <= argStr.length())) {
blnReturnValue = true;
}
return blnReturnValue;
}
}