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

Mapping("/createDownloadProccess.do")
public String createDownloadProccess(HttpServletRequest request,
HttpServletResponse response) throws Exception {
request.setCharacterEncoding("UTF-8");
response.setContentType("text/xml; charset=UTF-8");
String filename = request.getParameter("downloadfilename").toString();
String contentType = "application/x-msdownload;";

FileOperateUtil.download(request, response, filename, contentType,
filename,"commonquery\\sqldownload\\");
return null;
}
@Override
public void setServletConfig(ServletConfig sc) {
this.servletConfig = sc;
}
}

CommonQueryService.java

package com.fitweber.service;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import net.sf.json.JSONObject;
import com.fitweber.dao.CommonQueryDao;
import com.fitweber.pojo.QuerySqlModel;
import com.fitweber.util.CommonUtils;
import com.fitweber.util.FileOperateUtil;
import com.fitweber.util.ZipUtils;
import com.fitweber.vo.CommonParam;
import com.fitweber.vo.CommonQueryReq;
import com.fitweber.vo.CommonQueryResp;
import com.fitweber.vo.CommonSQL;
/**
*
*


 * 通用查询Service。
 *

* @author wheatmark hajima11@163.com
* @version 1.00.00
*


 * 修改记录
 *    修改后版本:    修改人：  修改日期:    修改内容: 





 *

*/
public class CommonQueryService {
private CommonQueryDao commonQueryDao;
/**
* 参数查询支持分页返回所有字段
* @param requestData
* @return
* @throws IOException
* @throws SQLException
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
public String commonQueryByParam(String requestData) throws IOException, SQLException{
HashMap requestMap = new HashMap();
Map classMap = new HashMap();
classMap.put("paramArray", CommonParam.class);
JSONObject jsonObject =JSONObject.fromObject(requestData);
CommonQueryReq commonQueryReq = (CommonQueryReq) JSONObject.toBean(jsonObject,CommonQueryReq.class,classMap);
StringBuffer sql = new StringBuffer();
sql.append("SELECT * FROM ");
sql.append(commonQueryReq.getTableName());
sql.append(" WHERE 1=1 ");
ArrayList paramArray = commonQueryReq.getParamArray();
for(CommonParam cp:paramArray){
sql.append(cp.getParamLogic()+" ");
sql.append(cp.getParamName()+" = ");
sql.append("'"+cp.getParamValue()+"' ");
}
requestMap.put("BEIGNROW",String.valueOf((commonQueryReq.getPageNum()-1)*commonQueryReq.getPageSize()));
requestMap.put("ENDROW",String.valueOf(commonQueryReq.getPageNum()*commonQueryReq.getPageSize()));
requestMap.put("sql", sql.toString());
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();
backupSql.append("INSERT INTO "+commonQueryReq.getTableName()+" (");
for(i=0;ibackupSql.append(col

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