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

umns.get(i)+",");
}
backupSql.append(") VALUES (");
String columnsSQL = backupSql.toString().replace(",)", ")");
backupSql.setLength(0);
String columnType = "";
for(i=0;ibackupSql.append(columnsSQL);
map = (Map)resultList.get(i);
for(j=0;jObject o = map.get(columns.get(j));
if(o!=null){
columnType = o.getClass().toString();
if("class java.lang.String".equals(columnType)){
backupSql.append("'"+(String) o+"',");
}else if("class java.sql.Timestamp".equals(columnType)){
backupSql.append("'"+CommonUtils.formatDate((java.sql.Timestamp) o)+"',");
}else if("class oracle.sql.CLOB".equals(columnType)){
backupSql.append("'"+((oracle.sql.CLOB)o).getSubString(1, (int)((oracle.sql.CLOB)o).length())+"',");
}
}
}
backupSql.append(");\n");
}
String backupContent = backupSql.toString().replace(",)", ")");
CommonUtils.saveFile(null, "backup.sql", backupContent,false);
CommonQueryResp resp = new CommonQueryResp();
resp.setTotalNum(commonQueryDao.commonQueryCount(requestMap));
resp.setResultList(resultList);
resp.setColumns(columns);
try {
String resultMessage = JSONObject.fromObject(resp).toString();
CommonUtils.saveFile(null, "query.log", resultMessage,false);
return resultMessage;
} catch (Exception e) {
return "{\"error\":\"查询表中含有BLOB或CLOB字段！程序无法解析。可用Execel配置查询。\"}";
}
}
/**
* 自定义SQL语句查询返回所有结果
* @param requestData
* @return
* @throws IOException
* @throws SQLException
*/
@SuppressWarnings({ "unchecked", "rawtypes" })
public String commonQueryBySQL(String requestData) throws IOException, SQLException {
HashMap requestMap = new HashMap();
JSONObject jsonObject =JSONObject.fromObject(requestData);

CommonSQL commonSQL = (CommonSQL) JSONObject.toBean(jsonObject,CommonSQL.class);
requestMap.put("sql", commonSQL.getSql());
requestMap.put("BEIGNROW",String.valueOf(( commonSQL.getPageNum()-1)*commonSQL.getPageSize()));
requestMap.put("ENDROW",String.valueOf(commonSQL.getPageNum()*commonSQL.getPageSize()));
int totalNum=commonQueryDao.commonQueryCount(requestMap);
ArrayList columns = new ArrayList();
List resultList = commonQueryDao.commonQueryByPage(requestMap);
if(resultList!=null&&resultList.size()>0){
Map map = resultList.get(0);
Iterator it = map.keySet().iterator();
columns.add("RN");
while(it.hasNext()){
String str = (String) it.next();
if(!"RN".equals(str)){
columns.add(str);
}
}
}
StringBuffer backupSql = new StringBuffer();
Map map;
int i,j,columnSize=columns.size(),resultSize=resultList.size();
String sql = commonSQL.getSql().toUpperCase();
Pattern patternTableName1 = Pattern.compile("FROM (.* )WHERE");
Pattern patternTableName2 = Pattern.compile("FROM (.* )$");
Matcher matcher =patternTableName1.matcher(sql);
if(matcher.find()){
backupSql.append("INSERT INTO "+matcher.group(1)+" (");
}else{
matcher =patternTableName2.matcher(sql);
if(matcher.find()){
backupSql.append("INSERT INTO "+matcher.group(1)+" (");
}else{
backupSql.append("INSERT INTO NoTable (");
}
}
for(i=0;ibackupSql.append(columns.get(i)+",");
}
backupSql.append(") VALUES (");
String columnsSQL = backupSql.toString().replace(",)", ")");
backupSql.setLength(0);
for(i=0;ibackupSql.append(columnsSQL);
map = (Map)resultList.get(i);
for(j=0;jObject o = map.get(columns.get(j));
if(o!=null){
backupSql.append(getValueStri

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