private String toValue(String[] oneObjects, String[] file_columns, InsertColumn ic)
throws SQLException {
String value = "";
if (ic.getLctype().equals("File")) {
value = file_columns[Integer.parseInt(ic.getExpstr())];
} else if (ic.getLctype().equals("Camp")) {
value = oneObjects[Integer.parseInt(ic.getExpstr())];
}
return value;
}
public int run() {
int rt = -1;
String step = leadsfile.split("_")[2];
if (!"01".equals(step)) {
logger.info(
"Leads setp :" + step + " skip leadsInsert",
leadsfile,
"",
"2-1",
"",
"名单营销波次为:" + step + ",跳过LeadsInsert.");
return 0;
}
LineIterator it = null;
JDBCExecute jdbcExecute =
new JDBCExecute(
conf.getProperty("leads.insert.db.driverClass"),
conf.getProperty("leads.insert.db.databaseURL"),
new KeyMapResource().get(conf.getProperty("leads.insert.db.databaseUser")),
new KeyMapResource().get(conf.getProperty("leads.insert.db.databasePassword")));
String getCampSQL = conf.getProperty("leads.insert.get.camp.sql");
// JDBCExecute jdbcExecute1 = new JDBCExecute(
// conf.getProperty("leads.insert.dwdb.driverClass"),
// conf.getProperty("leads.insert.dwdb.databaseURL"),
// new KeyMapResource().get(conf.getProperty("leads.insert.dwdb.databaseUser")),
// new
// KeyMapResource().get(conf.getProperty("leads.insert.dwdb.databasePassword"))
// );
// logger.info("LeadsInsert.getCampSQL:"+getCampSQL,
// leadsfile,
// "",
// "2-1",
// "",
// "LeadsInsert.getCampSQL:"+getCampSQL);
List<String[]> sqls = new ArrayList<String[]>();
List<String> file_sqls = new ArrayList<String>();
try {
File readfile = new File(conf.getProperty("leads.insert.readpath") + leadsfile);
File outfile = new File(conf.getProperty("leads.insert.savepath") + insertfile);
String tableName = conf.getProperty("leads.insert.db.table.name");
logger.info(
"Leads Insert Table Name:" + tableName,
leadsfile,
"",
"2-1",
"",
"名单要插入的表为:" + tableName);
// INSERT INTO ABC(C1,C1) VALUES ('1','2');
StringBuffer sqlColumnBuffer = new StringBuffer("INSERT INTO");
sqlColumnBuffer.append(" ");
sqlColumnBuffer.append(tableName);
sqlColumnBuffer.append("(");
StringBuffer sqlValuesBuffer = new StringBuffer("VALUES (");
int i = 1;
String tempStr = conf.getProperty("leads.insert.column." + i);
String[] tempArr;
List<InsertColumn> insertColumns = new ArrayList<InsertColumn>();
InsertColumn ic;
while (tempStr != null) {
ic = new InsertColumn();
tempArr = tempStr.split("\\|", -1);
// System.out.println(tempStr);
if (i != 1) {
sqlColumnBuffer.append(",");
}
ic.setName(tempArr[0]);
ic.setLctype(tempArr[1]);
ic.setExpstr(tempArr[2]);
insertColumns.add(ic);
// System.out.println(tempArr[0]);
sqlColumnBuffer.append(tempArr[0]);
i++;
tempStr = conf.getProperty("leads.insert.column." + i);
// System.out.println(tempArr);
}
sqlColumnBuffer.append(") ");
it = FileUtils.lineIterator(readfile, conf.getProperty("leads.insert.file.encoding"));
String[] sql;
String line;
String[] file_columns;
String[] oneObjects = null;
String value;
String selectSQL = conf.getProperty("leads.insert.check.sql");
String newSelectSQL;
boolean isOneQuery = false;
while (it.hasNext()) {
line = it.nextLine();
file_columns = line.split("\\|", -1);
if (!isOneQuery) { // 文件读取循环开始时，执行一次。
String tempsql = SqlStrUtils.toSQL(getCampSQL, file_columns);
logger.info(
"LeadsInsert.getCampSQL:" + tempsql.replaceAll("\'", ""),
leadsfile,
"",
"2-1",
"",
"LeadsInsert.getCampSQL:" + tempsql.replaceAll("\'", ""));
oneObjects = jdbcExecute.getOneObject(tempsql);
isOneQuery = true;
}
for (int j = 0; j < insertColumns.size(); j++) { // int index : indexStrs){
// indexStrs[j];
if (j != 0) {
sqlValuesBuffer.append(",");
}
value = toValue(oneObjects, file_columns, insertColumns.get(j));
if (value != null
&& !value.equals("")) { // toValue(camp_resultSet,file_columns,insertColumns.get(j))
sqlValuesBuffer.append("'" + value + "'");
} else {
sqlValuesBuffer.append("NULL");
}
}
sqlValuesBuffer.append(")");
newSelectSQL = toExpstrValue(selectSQL, file_columns);
sql = new String[2];
sql[0] = sqlColumnBuffer.toString() + sqlValuesBuffer.toString();
sql[1] = newSelectSQL;
file_sqls.add(sql[0]);
sqls.add(sql);
sqlValuesBuffer.delete(8, sqlValuesBuffer.length());
}
// FileUtils.writeLines(outfile,sqls,conf.getProperty("leads.insert.file.encoding"));
FileUtils.writeLines(outfile, conf.getProperty("leads.insert.file.encoding"), file_sqls);
jdbcExecute.execute(sqls);
logger.info(
"Leads Insert Table[" + tableName + "] success. row count:" + sqls.size(),
leadsfile,
"",
"2-1",
"",
"名单要插入的表[" + tableName + "]完成!插入笔数:" + sqls.size());
logger.info(
"Leads Insert run success!gen .SQL file:" + insertfile + " ",
leadsfile,
"",
"2-1",
"LINST00001",
"LeadsInsert运行成功!生成 " + insertfile + " 完成!");
rt = 0;
// List<String[]> leadsList = new ArrayList<String[]>();
} catch (Exception e) {
if (e instanceof FileNotFoundException) {
rt = 805;
logger.error(
e.getMessage().replaceAll("\'", ""),
leadsfile,
"",
"2-1",
"805",
"没有找到指定文件!" + e.getMessage().replaceAll("\'", ""));
} else if (e instanceof SQLIntegrityConstraintViolationException) {
rt = 815;
logger.error(
e.getMessage().replaceAll("\'", ""),
leadsfile,
"",
"2-1",
"815",
"插入数据库发生异常!" + e.getMessage().replaceAll("\'", ""));
} else {
rt = 809;
e.printStackTrace();
logger.error(
"leadsInsert error:" + e.getMessage().replaceAll("\'", ""),
leadsfile,
"",
"2-1",
"809",
"程序发生异常!" + e.getMessage().replaceAll("\'", ""));
}
} finally {
LineIterator.closeQuietly(it);
jdbcExecute.closeConnection();
}
return rt;
}