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

 * 通用查询Controller。
 * 

* @author wheatmark [e-mail]
* @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