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

ng(o)+",");
}
}
backupSql.append(");\n");
}
String backupContent = backupSql.toString().replace(",)", ")");
CommonUtils.saveFile(null, "backup.sql", backupContent,false);
CommonQueryResp resp = new CommonQueryResp();
resp.setTotalNum(totalNum);
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配置查询。\"}";
}
}
@SuppressWarnings({ "unchecked", "rawtypes" })
public String commonQueryByExcel(HttpServletRequest request,
HttpServletResponse response,ArrayList querySqlList,String downloadPath,String originalFileName){
HashMap requestMap = new HashMap();
ArrayList columns = new ArrayList();
StringBuffer backupSql = new StringBuffer();
try {
int totalNum,pagecount,rest,i;
List resultList;
CommonUtils.delFolder(downloadPath+"sources/");
for(QuerySqlModel q :querySqlList){
requestMap.put("sql", q.getScriptContent());
totalNum = commonQueryDao.commonQueryCount(requestMap);
if(totalNum>2000){
pagecount=totalNum/2000;
rest=totalNum%2000;
for(i=0;irequestMap.put("BEIGNROW",String.valueOf(i*2000));
requestMap.put("ENDROW",String.valueOf((i+1)*2000-1));
resultList = commonQueryDao.commonQueryByPage(requestMap);
if(columns.size()==0){
if(resultList!=null&&resultList.size()>0){
Map newmap = resultList.get(0);
Iterator it = newmap.keySet().iterator();
while(it.hasNext()){
String str = (String) it.next();
columns.add(str);
}
}

}
saveData(columns, resultList, backupSql, q, downloadPath);
backupSql.setLength(0);
}
requestMap.put("BEIGNROW",String.valueOf(pagecount*2000));
requestMap.put("ENDROW",String.valueOf(pagecount*2000+rest));
resultList = commonQueryDao.commonQueryByPage(requestMap);
saveData(columns, resultList, backupSql, q, downloadPath);
backupSql.setLength(0);
}else{
resultList = commonQueryDao.commonQuery(requestMap);
if(resultList!=null&&resultList.size()>0){
Map newmap = resultList.get(0);
Iterator it = newmap.keySet().iterator();
while(it.hasNext()){
String str = (String) it.next();
columns.add(str);
}
}
saveData(columns, resultList, backupSql, q, downloadPath);
backupSql.setLength(0);
}
columns.clear();
}
ZipUtils zipUtils = new ZipUtils(downloadPath+"/"+originalFileName+".zip");
zipUtils.compress(downloadPath+"sources/");
FileOperateUtil.download(request, response, originalFileName+".zip", "application/x-msdownload;",
originalFileName+".zip","commonquery\\sqldownload\\");
} catch (Exception e) {
e.printStackTrace();
return "文件异常，请检查文件格式和内容";
}
return "执行成功";
}
@SuppressWarnings("rawtypes")
public void saveData(ArrayList columns,List resultList,StringBuffer backupSql,QuerySqlModel q,String downloadPath) throws IOException, SQLException{
int columnSize=columns.size(),resultSize=resultList.size();
String sql = q.getScriptContent().toUpperCase();
Map map;
Object o;
int i,j;
String tableName=null,condition=null,deleteSQL=null;
Pattern patternTableName1 = Pattern.compile("FROM (.* )WHERE(.* )$");
Pattern patternTableName2 = Pattern.compile("FROM (.* )$");
Matcher matcher =patternTableName1.matcher(sql);
if(matcher.find()){
tableName=

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