Web程序实现简易版PL/SQL和Excel表配置备份SQL语句 - 数据库编程

matcher.group(1);
condition=matcher.group(2);
backupSql.append("INSERT INTO "+tableName+" (");
}else{
matcher =patternTableName2.matcher(sql);
if(matcher.find()){
tableName=matcher.group(1);
condition=" 1=1";
backupSql.append("INSERT INTO "+tableName+" (");
}else{
backupSql.append("INSERT INTO NoTable (");
}
}
if(tableName!=null){
deleteSQL="DELETE FROM "+tableName+" WHERE "+condition+";\n";
}
for(i=0;ibackupSql.append(columns.get(i)+",");
}
backupSql.append(") VALUES (");
String columnsSQL = backupSql.toString().replace(",)", ")");
backupSql.setLength(0);
if(deleteSQL!=null){
backupSql.append(deleteSQL);
}
for(i=0;ibackupSql.append(columnsSQL);
map = (Map)resultList.get(i);
o = map.get(columns.get(0));
if(o!=null){
backupSql.append(getValueString(o));
}else{
backupSql.append("''");
}
for(j=1;jo = map.get(columns.get(j));
if(o!=null){
backupSql.append(","+getValueString(o));
}else{
backupSql.append(",''");
}
}
backupSql.append(");\n");
}
CommonUtils.saveFile(null, downloadPath+"sources/"+q.getScriptFileName()+".sql", backupSql.toString(),true);
/*CommonQueryResp resp = new CommonQueryResp();
resp.setTotalNum(resultSize);
resp.setResultList(resultList);
resp.setColumns(columns);
String resultMessage = JSONObject.fromObject(resp).toString();
CommonUtils.saveFile(null, downloadPath+"query.log", resultMessage,false);*/
}
public String getValueString(Object o) throws SQLException{
String columnType = o.getClass().toString();
if("class java.lang.String".equals(columnType)){

return "'"+(String) o+"'";
}else if("class java.math.BigDecimal".equals(columnType)){
return "'"+((java.math.BigDecimal) o).toString()+"'";
}
else if("class java.sql.Timestamp".equals(columnType)){
return "to_date('"+ CommonUtils.formatDate((java.sql.Timestamp) o)+"', 'yyyy-mm-dd')";
}else if("class oracle.sql.CLOB".equals(columnType)){
return "'"+((oracle.sql.CLOB)o).getSubString(1, (int)((oracle.sql.CLOB)o).length())+"'";
}
return "''";
}
@SuppressWarnings({ "unchecked", "rawtypes" })
public String createFLZL(String[] sqls){
HashMap requestMap = new HashMap();
StringBuffer bf = new StringBuffer();
int listLen;
for(String s:sqls){
requestMap.put("sql", s);
List resultList = commonQueryDao.commonQuery(requestMap);
listLen=resultList.size();
if(listLen>0){
bf.append((String)resultList.get(0).get("FBZL_DM"));
/*for(Map m:resultList){
}*/
}
bf.append(" \n");
}
System.out.println(bf.toString());
return bf.toString();
}
public String uploadFile(){
return "succeese";
}
@SuppressWarnings("rawtypes")
public String getAllTableName(){
List resultList = commonQueryDao.getAllTableName();
Object[] resultArray = resultList.toArray();
StringBuffer resultMessage=new StringBuffer();
resultMessage.append("[");
for(Object str:resultArray){
resultMessage.append("\"" +str.toString()+ "\",");
}
resultMessage.append("]");
return resultMessage.toString().replace(",]", "]");
}
@SuppressWarnings("rawtypes")
public String getColumns(String tableName){
List resultList = commonQueryDao.getColumns(tableName);
Object[] resultArray = resultList.toArray();
StringBuffer resultMessage=new StringBuffer();
resultMessage.append("[");
for(Object str:resultArray){
resultMessage.append("\"" +str.toString()+ "\",");
}
resultMessage.append("]

Web程序实现简易版PL/SQL和Excel表配置备份SQL语句(八)