Home > Database > Mysql Tutorial > body text

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

WBOY
Release: 2016-06-07 17:29:28
Original
1406 people have browsed it

为了应对发布,我习惯用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;
/**
 *
 *

<br> * 通用查询Controller。<br> * 
Copy after login

 * @author wheatmark  hajima11@163.com
 * @version 1.00.00
 *
<br> * 修改记录<br> *    修改后版本:    修改人:  修改日期:    修改内容: <br> * 
Copy after login

 */
@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

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template