Web程序实现简易版PL/SQL和Excel表配置备份SQL语句
Web程序实现简易版PL/SQL和Excel表配置备份SQL语句
项目发布的时候，把开发环境上的数据库配置迁移到部署环境。我们总要准备很多SQL脚本。（数据库为Oracle的情况下）通常的方法就是拿PL/SQL一个个地查，一个个地导。特别是增量发布的时候。操作特别麻烦。对于开发时就加班加点的程序猿。要抓破头地一遍遍地回想，这次发布我改了哪些数据库配置。往往忘了一条语句，客户就打电话过来骂娘。
为了应对发布，我习惯用Excel记录下每一次数据库的修改语句。但在发布频繁的时候，维护这份文档就显得十分困难。所以我想开发一套程序来帮我维护SQL脚本。对于数据库配置，每次的变动都是有规律可循的。如插入时间、修改时间、贯穿某个用例的业务号等等。只要把这些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’,......);
想法有了。很简单也很直接，就是用PL/SQL导的时候一遍遍地切换表名和点导入按钮相当繁琐。繁琐地事情就交给机器做吧。我就动手写了extend这个程序（额外工作的意思）。他的功能就相当于一个简易的PL/SQL。支持用Excel生成备份脚本。具体的实现是，用jxl.jar逐行读取Excel中配置的SQL，用mybatis查询回来数据。然后解析返回的数据，拼装成增量语句写入文件，生成指定文件。读取完所有的配置后将SQL文件打包返回。我不想用java写界面，所以写了一个Web程序。
核心的代码如下：
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;
/**
*
* <pre>
* 通用查询Controller。
* </pre>
* @author wheatmark hajima11@163.com
* @version 1.00.00
* <pre>
* 修改记录
* 修改后版本: 修改人： 修改日期: 修改内容:
* </pre>
*/
@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<String> elementList = CommonUtils.readExecel(queryexecelPath);
ArrayList<QuerySqlModel> querySqlList = new ArrayList<QuerySqlModel>();
int sqlSize = elementList.size(),i;
for(i=1;i<sqlSize;i++){//屏蔽表头
String[] 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<String> elementList = CommonUtils.readExecel(queryFilePath);
ArrayList<QuerySqlModel> querySqlList = new ArrayList<QuerySqlModel>();
int sqlSize = elementList.size(),i;
for(i=1;i<sqlSize;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("<html>"+message+"</html>");
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<halfpoint;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<loopsize;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
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;
/**
*
* <pre>
* 通用查询Service。
* </pre>
* @author wheatmark hajima11@163.com
* @version 1.00.00
* <pre>
* 修改记录
* 修改后版本: 修改人： 修改日期: 修改内容:
* </pre>
*/
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<String,String> requestMap = new HashMap<String, String>();
Map<String, Class> classMap = new HashMap<String, Class>();
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<CommonParam> 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<String> columns = new ArrayList<String>();
List<Map> 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;i<columnSize;i++){
backupSql.append(columns.get(i)+",");
}
backupSql.append(") VALUES (");
String columnsSQL = backupSql.toString().replace(",)", ")");
backupSql.setLength(0);
String columnType = "";
for(i=0;i<resultSize;i++){
backupSql.append(columnsSQL);
map = (Map)resultList.get(i);
for(j=0;j<columnSize;j++){
Object o = map.get(columns.get(j));
if(o!=null){
columnType = o.getClass().toString();
if("class java.lang.String".equals(columnType)){
backupSql.append("'"+(String) o+"',");
}else if("class java.sql.Timestamp".equals(columnType)){
backupSql.append("'"+CommonUtils.formatDate((java.sql.Timestamp) o)+"',");
}else if("class oracle.sql.CLOB".equals(columnType)){
backupSql.append("'"+((oracle.sql.CLOB)o).getSubString(1, (int)((oracle.sql.CLOB)o).length())+"',");
}
}
}
backupSql.append(");\n");
}
String backupContent = backupSql.toString().replace(",)", ")");
CommonUtils.saveFile(null, "backup.sql", backupContent,false);
CommonQueryResp resp = new CommonQueryResp();
resp.setTotalNum(commonQueryDao.commonQueryCount(requestMap));
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配置查询。\"}";
}
}
/**
* 自定义SQL语句查询 返回所有结果
* @param requestData
* @return
* @throws IOException
* @throws SQLException
*/
@SuppressWarnings({ "unchecked", "rawtypes" })
public String commonQueryBySQL(String requestData) throws IOException, SQLException {
HashMap<String,String> requestMap = new HashMap<String, String>();
JSONObject jsonObject =JSONObject.fromObject(requestData);
CommonSQL commonSQL = (CommonSQL) JSONObject.toBean(jsonObject,CommonSQL.class);
requestMap.put("sql", commonSQL.getSql());
requestMap.put("BEIGNROW",String.valueOf(( commonSQL.getPageNum()-1)*commonSQL.getPageSize()));
requestMap.put("ENDROW",String.valueOf(commonSQL.getPageNum()*commonSQL.getPageSize()));
int totalNum=commonQueryDao.commonQueryCount(requestMap);
ArrayList<String> columns = new ArrayList<String>();
List<Map> 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();
String sql = commonSQL.getSql().toUpperCase();
Pattern patternTableName1 = Pattern.compile("FROM (.*?)WHERE");
Pattern patternTableName2 = Pattern.compile("FROM (.*?)$");
Matcher matcher =patternTableName1.matcher(sql);
if(matcher.find()){
backupSql.append("INSERT INTO "+matcher.group(1)+" (");
}else{
matcher =patternTableName2.matcher(sql);
if(matcher.find()){
backupSql.append("INSERT INTO "+matcher.group(1)+" (");
}else{
backupSql.append("INSERT INTO NoTable (");
}
}
for(i=0;i<columnSize;i++){
backupSql.append(columns.get(i)+",");
}
backupSql.append(") VALUES (");
String columnsSQL = backupSql.toString().replace(",)", ")");
backupSql.setLength(0);
for(i=0;i<resultSize;i++){
backupSql.append(columnsSQL);
map = (Map)resultList.get(i);
for(j=0;j<columnSize;j++){
Object o = map.get(columns.get(j));
if(o!=null){
backupSql.append(getValueString(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<QuerySqlModel> querySqlList,String downloadPath,String originalFileName){
HashMap<String,String> requestMap = new HashMap<String, String>();
ArrayList<String> columns = new ArrayList<String>();
StringBuffer backupSql = new StringBuffer();
try {
int totalNum,pagecount,rest,i;
List<Map> 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;i<pagecount;i++){
requestMap.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<String> columns,List<Map> 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=matcher.group(1);
condition=matcher.group(2);
backupSql.append("INSERT INTO "+tableName+" (");
}else{
matcher =patternTableName2.matcher(sql);
if(matcher.find()){
tableName=matcher.group(1);
condition=" 1=1";
backupSql.append("INSERT INTO "+tableName+" (");
}else{
backupSql.append("INSERT INTO NoTable (");
}
}
if(tableName!=null){
deleteSQL="DELETE FROM "+tableName+" WHERE "+condition+";\n";
}
for(i=0;i<columnSize;i++){
backupSql.append(columns.get(i)+",");
}
backupSql.append(") VALUES (");
String columnsSQL = backupSql.toString().replace(",)", ")");
backupSql.setLength(0);
if(deleteSQL!=null){
backupSql.append(deleteSQL);
}
for(i=0;i<resultSize;i++){
backupSql.append(columnsSQL);
map = (Map)resultList.get(i);
o = map.get(columns.get(0));
if(o!=null){
backupSql.append(getValueString(o));
}else{
backupSql.append("''");
}
for(j=1;j<columnSize;j++){
o = map.get(columns.get(j));
if(o!=null){
backupSql.append(","+getValueString(o));
}else{
backupSql.append(",''");
}
}
backupSql.append(");\n");
}
CommonUtils.saveFile(null, downloadPath+"sources/"+q.getScriptFileName()+".sql", backupSql.toString(),true);
/*CommonQueryResp resp = new CommonQueryResp();
resp.setTotalNum(resultSize);
resp.setResultList(resultList);
resp.setColumns(columns);
String resultMessage = JSONObject.fromObject(resp).toString();
CommonUtils.saveFile(null, downloadPath+"query.log", resultMessage,false);*/
}
public String getValueString(Object o) throws SQLException{
String columnType = o.getClass().toString();
if("class java.lang.String".equals(columnType)){
return "'"+(String) o+"'";
}else if("class java.math.BigDecimal".equals(columnType)){
return "'"+((java.math.BigDecimal) o).toString()+"'";
}
else if("class java.sql.Timestamp".equals(columnType)){
return "to_date('"+ CommonUtils.formatDate((java.sql.Timestamp) o)+"', 'yyyy-mm-dd')";
}else if("class oracle.sql.CLOB".equals(columnType)){
return "'"+((oracle.sql.CLOB)o).getSubString(1, (int)((oracle.sql.CLOB)o).length())+"'";
}
return "''";
}
@SuppressWarnings({ "unchecked", "rawtypes" })
public String createFLZL(String[] sqls){
HashMap<String,String> requestMap = new HashMap<String, String>();
StringBuffer bf = new StringBuffer();
int listLen;
for(String s:sqls){
requestMap.put("sql", s);
List<Map> resultList = commonQueryDao.commonQuery(requestMap);
listLen=resultList.size();
if(listLen>0){
bf.append((String)resultList.get(0).get("FBZL_DM"));
/*for(Map m:resultList){
}*/
}
bf.append(" \n");
}
System.out.println(bf.toString());
return bf.toString();
}
public String uploadFile(){
return "succeese";
}
@SuppressWarnings("rawtypes")
public String getAllTableName(){
List resultList = commonQueryDao.getAllTableName();
Object[] resultArray = resultList.toArray();
StringBuffer resultMessage=new StringBuffer();
resultMessage.append("[");
for(Object str:resultArray){
resultMessage.append("\"" +str.toString()+ "\",");
}
resultMessage.append("]");
return resultMessage.toString().replace(",]", "]");
}
@SuppressWarnings("rawtypes")
public String getColumns(String tableName){
List resultList = commonQueryDao.getColumns(tableName);
Object[] resultArray = resultList.toArray();
StringBuffer resultMessage=new StringBuffer();
resultMessage.append("[");
for(Object str:resultArray){
resultMessage.append("\"" +str.toString()+ "\",");
}
resultMessage.append("]");
return resultMessage.toString().replace(",]", "]");
}
public CommonQueryDao getCommonQueryDao() {
return commonQueryDao;
}
public void setCommonQueryDao(CommonQueryDao commonQueryDao) {
this.commonQueryDao = commonQueryDao;
}
}
完整的源码在github维护，地址：https://github.com/ladykiller/Web-Assistant-For-Oracle。
写得不好，别见笑。欢迎批评指教。
可部署工程下载地址:
**************************************************************
下载在帮客之家的1号FTP服务器里，下载地址：
FTP地址：ftp://ftp1.bkjia.com
用户名：www.6688.cc
密码：www.bkjia.com
在 2013年LinuxIDC.com/6月/Web程序实现简易版PLSQL和Excel表配置备份SQL语句/
下载方法见 http://www.bkjia.net/thread-1187-1-1.html
**************************************************************
评论暂时关闭