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

为了应对发布，我习惯用Excel记录下每一次数据库的修改语句。但在发布频繁的时候，维护这份文档就显得十分困难。所以我想开发一套

项目发布的时候，把开发环境上的数据库配置迁移到部署环境。我们总要准备很多

为了应对发布，，我习惯用对于数据库配置，每次的变动都是有规律可循的。如插入时间、修改时间、贯穿某个用例的业务号等等。只要把这些Select出来，就是增量的内容了。我就可以做到按日期增量、按业务增量了。

如有一条增量数据：

select * from yewubiao where yewu_id in (’399001’,’399002’,’399003’)

生成的增量SQL就应该是：

delete from yewubiao where yewu_id in (’399001’,’399002’,’399003’);

insert into yewubiao (yewu_id,yewu_biaohao,......) values (‘399001’,’92330041’,......);

insert into yewubiao (yewu_id,yewu_biaohao,......) values (‘399002’,’92330042’,......);

insert into yewubiao (yewu_id,yewu_biaohao,......) values (‘399003’,’92330043’,......);

 

想法有了。很简单也很直接，就是用

核心的代码如下：

 

CommonQueryController.java

package com.fitweber.web;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import javax.annotation.Resource;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.context.ServletConfigAware;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import com.fitweber.pojo.QuerySqlModel;
import com.fitweber.service.CommonQueryService;
import com.fitweber.util.CommonUtils;
import com.fitweber.util.FileOperateUtil;
/**
 *
 *

 * @author wheatmark  hajima11@163.com
 * @version 1.00.00
 *
 */
@Controller
@RequestMapping("/commonQuery")
public class CommonQueryController implements ServletConfigAware {
@Resource(name = "commonQueryService")
private CommonQueryService commonQueryService;
private ServletConfig  servletConfig;
/**
 * logger
 */
private static Logger logger = Logger
.getLogger(CommonQueryController.class);
@RequestMapping("/getTableNames.do")
public void getTableNames(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
String resultMessage = commonQueryService.getAllTableName();
if (!logger.isDebugEnabled()) {
logger.debug(resultMessage);
}
PrintWriter out = response.getWriter();
out.write(resultMessage);
out.close();
}
@RequestMapping("/getColumns.do")
public void getColumns(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
response.setContentType("text/xml; charset=UTF-8");
String tableName = request.getParameter("tableName").toString();
String resultMessage = commonQueryService.getColumns(tableName);
if (!logger.isDebugEnabled()) {
logger.debug(resultMessage);
}
PrintWriter out = response.getWriter();
out.write(resultMessage);
out.close();
}
@RequestMapping("/commonQueryByParam.do")
public void commonQueryByParam(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException, SQLException {
request.setCharacterEncoding("UTF-8");
response.setContentType("text/xml; charset=UTF-8");
String requestData = request.getParameter("json").toString();
String resultMessage = commonQueryService.commonQueryByParam(requestData);
if (!logger.isDebugEnabled()) {
logger.debug(resultMessage);
}
PrintWriter out = response.getWriter();
out.write(resultMessage);
out.close();
}
@RequestMapping("/commonQueryBySQL.do")
public void commonQueryBySQL(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException, SQLException {
request.setCharacterEncoding("UTF-8");
response.setContentType("text/xml; charset=UTF-8");
String requestData = request.getParameter("json").toString();
String resultMessage = commonQueryService.commonQueryBySQL(requestData);
if (!logger.isDebugEnabled()) {
logger.debug(resultMessage);
}
PrintWriter out = response.getWriter();
out.write(resultMessage);
out.close();
}
@RequestMapping("/commonQueryFLZL.do")
public void commonQueryFLZL(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
response.setContentType("text/xml; charset=UTF-8");
String queryexecelPath = (request.getSession().getServletContext().getRealPath("")+"/commonquery/queryexecel/test.xls").replace("\\", "/");
String sqldownloadPath = (request.getSession().getServletContext().getRealPath("")+"/commonquery/sqldownload/").replace("\\", "/");
ArrayList elementList = CommonUtils.readExecel(queryexecelPath);
ArrayList querySqlList = new ArrayList();
int sqlSize = elementList.size(),i;
for(i=1;iString[] params = elementList.get(i).split("\t");
querySqlList.add(new QuerySqlModel(params[0], params[1].replace(";", ""), params[2]));
}
String resultMessage = commonQueryService.commonQueryByExcel(request,response,querySqlList,sqldownloadPath,"附列资料");
PrintWriter out = response.getWriter();
out.write(resultMessage);
out.close();
}
@RequestMapping("/createFLZL.do")
public void createFLZL(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException, RowsExceededException, WriteException {
request.setCharacterEncoding("UTF-8");
response.setContentType("text/xml; charset=UTF-8");
//String queryexecelPath = (request.getSession().getServletContext().getRealPath("")+"/commonquery/queryexecel/test.xls").replace("\\", "/");
String[] elementList = CommonUtils.createFlzl("flzl.xls");
String resultMessage = commonQueryService.createFLZL(elementList);
CommonUtils.writeExecel("flzl_1.xls",0,3,resultMessage.split("\n"));
PrintWriter out = response.getWriter();
out.write(resultMessage);
out.close();
}
@RequestMapping("/commonQueryByExcel.do")
public String commonQueryByExcel(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
response.setContentType("text/xml; charset=UTF-8");
String queryexecelPath = (request.getSession().getServletContext().getRealPath("")+"/commonquery/queryexecel/").replace("\\", "/");
String sqldownloadPath = (request.getSession().getServletContext().getRealPath("")+"/commonquery/sqldownload/").replace("\\", "/");
        MultipartHttpServletRequest multipartHttpservletRequest=(MultipartHttpServletRequest) request;
        MultipartFile multipartFile = multipartHttpservletRequest.getFile("execel_param");
        String originalFileName=multipartFile.getOriginalFilename();
        File file=new File(queryexecelPath);
        if(!file.exists()){
            file.mkdir();
        }
        try {
        //String queryFilePath  = file+"/queryexecel"+originalFileName.substring(originalFileName.lastIndexOf('.'),originalFileName.length());
        String queryFilePath  = file+"/"+originalFileName;
            FileOutputStream fileOutputStream=new FileOutputStream(queryFilePath);
            fileOutputStream.write(multipartFile.getBytes());
            fileOutputStream.flush();
            fileOutputStream.close();
           
    ArrayList elementList = CommonUtils.readExecel(queryFilePath);
    ArrayList querySqlList = new ArrayList();
    int sqlSize = elementList.size(),i;
    for(i=1;i    String[] params = elementList.get(i).split("\t");
    querySqlList.add(new QuerySqlModel(params[0], params[1].replace(";", ""), params[2]));
    }
    String timeStamp = CommonUtils.formatTime(new Date()).replace(":", "").replace("-", "").replace(" ", "");
    String message = commonQueryService.commonQueryByExcel(request,response,querySqlList,sqldownloadPath,originalFileName.substring(0,originalFileName.lastIndexOf('.'))+"_"+timeStamp);
    if(!"执行成功".equals(message)){
    PrintWriter out = response.getWriter();
    out.write(""+message+"");
    out.close();
    }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return "execelframe";
}
@RequestMapping("/createDownloadList.do")
public void createDownloadList(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
response.setContentType("text/xml; charset=UTF-8");
String path = request.getContextPath();
String basePath = request.getScheme() + "://"
+ request.getServerName() + ":" + request.getServerPort()
+ path + "/commonquery/sqldownload/";
String sqldownloadPath = (request.getSession().getServletContext().getRealPath("")+"/commonquery/sqldownload/").replace("\\", "/");
File file = new File(sqldownloadPath);
//if (file.exists()&&file.isDirectory()) {
//    String[] tempList = file.list();
//    for(String f:tempList){
//    System.out.println(basePath+f);
//}
//}
if (file.exists()&&file.isDirectory()) {
    String[] tempList = file.list();
   
    StringBuffer buf = new StringBuffer();
    int i,listSize = tempList.length;
    //数组倒序
    int halfpoint = listSize/2;
    String temp;
    for(i=0;i    temp=tempList[i];
    tempList[i]=tempList[listSize-1-i];
    tempList[listSize-1-i]=temp;
    }
    buf.append("[");
    int loopsize = listSize-2;
    for(i=0;i    buf.append("{\"filename\":\""+tempList[i]+"\"},");
}
    buf.append("{\"filename\":\""+tempList[loopsize]+"\"}]");
    PrintWriter out = response.getWriter();
    out.write(buf.toString());
    out.close();
}
}
@RequestMapping("/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