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

2024-02-23 22:38
文章标签 sql 配置 web 简易版 备份 语句 database 程序实现 pl execel

本文主要是介绍Web程序实现简易版PL/SQL和Execel表配置备份SQL语句，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

项目发布的时候，把开发环境上的数据库配置迁移到部署环境。我们总要准备很多SQL脚本。（数据库为Oracle的情况下）通常的方法就是拿PL/SQL一个个地查，一个个地导。特别是增量发布的时候。操作特别麻烦。对于开发时就加班加点的程序猿。要抓破头地一遍遍地回想，这次发布我改了哪些数据库配置。往往忘了一条语句，客户就打电话过来骂娘。

为了应对发布，我习惯用Execel记录下每一次数据库的修改语句。但在发布频繁的时候，维护这份文档就显得十分困难。所以我想开发一套程序来帮我维护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。

可部署工程在csdn免积分下载，地址:http://download.csdn.net/detail/super2007/5364499。写得不好，别见笑。欢迎批评指教。

这篇关于Web程序实现简易版PL/SQL和Execel表配置备份SQL语句的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/740161。

相关文章

Mysql虚拟列的使用场景

Mysql虚拟列的使用场景

《Mysql虚拟列的使用场景》MySQL虚拟列是一种在查询时动态生成的特殊列,它不占用存储空间,可以提高查询效率和数据处理便利性,本文给大家介绍Mysql虚拟列的相关知识,感兴趣的朋友一起看看吧... 目录1. 介绍mysql虚拟列1.1 定义和作用1.2 虚拟列与普通列的区别2. MySQL虚拟列的类型2
阅读更多...
mysql数据库分区的使用

mysql数据库分区的使用

《mysql数据库分区的使用》MySQL分区技术通过将大表分割成多个较小片段,提高查询性能、管理效率和数据存储效率,本文就来介绍一下mysql数据库分区的使用,感兴趣的可以了解一下... 目录【一】分区的基本概念【1】物理存储与逻辑分割【2】查询性能提升【3】数据管理与维护【4】扩展性与并行处理【二】分区的
阅读更多...
windos server2022的配置故障转移服务的图文教程

windos server2022的配置故障转移服务的图文教程

《windosserver2022的配置故障转移服务的图文教程》本文主要介绍了windosserver2022的配置故障转移服务的图文教程,以确保服务和应用程序的连续性和可用性,文中通过图文介绍的非... 目录准备环境：步骤故障转移群集是 Windows Server 2022 中提供的一种功能，用于在多个
阅读更多...
windos server2022里的DFS配置的实现

windos server2022里的DFS配置的实现

《windosserver2022里的DFS配置的实现》DFS是WindowsServer操作系统提供的一种功能,用于在多台服务器上集中管理共享文件夹和文件的分布式存储解决方案,本文就来介绍一下wi... 目录什么是DFS？优势：应用场景：DFS配置步骤什么是DFS？DFS指的是分布式文件系统（Distr
阅读更多...
MySQL中时区参数time_zone解读

MySQL中时区参数time_zone解读

《MySQL中时区参数time_zone解读》MySQL时区参数time_zone用于控制系统函数和字段的DEFAULTCURRENT_TIMESTAMP属性,修改时区可能会影响timestamp类型... 目录前言1.时区参数影响2.如何设置3.字段类型选择总结前言mysql 时区参数 time_zon
阅读更多...
Python MySQL如何通过Binlog获取变更记录恢复数据

Python MySQL如何通过Binlog获取变更记录恢复数据

《PythonMySQL如何通过Binlog获取变更记录恢复数据》本文介绍了如何使用Python和pymysqlreplication库通过MySQL的二进制日志（Binlog）获取数据库的变更记录... 目录python mysql通过Binlog获取变更记录恢复数据1.安装pymysqlreplicat
阅读更多...
使用SQL语言查询多个Excel表格的操作方法

使用SQL语言查询多个Excel表格的操作方法

《使用SQL语言查询多个Excel表格的操作方法》本文介绍了如何使用SQL语言查询多个Excel表格,通过将所有Excel表格放入一个.xlsx文件中,并使用pandas和pandasql库进行读取和... 目录如何用SQL语言查询多个Excel表格如何使用sql查询excel内容1. 简介2. 实现思路3
阅读更多...
关于Maven中pom.xml文件配置详解

关于Maven中pom.xml文件配置详解

《关于Maven中pom.xml文件配置详解》pom.xml是Maven项目的核心配置文件,它描述了项目的结构、依赖关系、构建配置等信息,通过合理配置pom.xml,可以提高项目的可维护性和构建效率... 目录1. POM文件的基本结构1.1 项目基本信息2. 项目属性2.1 引用属性3. 项目依赖4. 构
阅读更多...
Spring常见错误之Web嵌套对象校验失效解决办法

Spring常见错误之Web嵌套对象校验失效解决办法

《Spring常见错误之Web嵌套对象校验失效解决办法》：本文主要介绍Spring常见错误之Web嵌套对象校验失效解决的相关资料,通过在Phone对象上添加@Valid注解,问题得以解决,需要的朋... 目录问题复现案例解析问题修正总结  问题复现当开发一个学籍管理系统时，我们会提供了一个 API 接口去
阅读更多...
龙蜥操作系统Anolis OS-23.x安装配置图解教程(保姆级)

龙蜥操作系统Anolis OS-23.x安装配置图解教程(保姆级)

《龙蜥操作系统AnolisOS-23.x安装配置图解教程(保姆级)》：本文主要介绍了安装和配置AnolisOS23.2系统，包括分区、软件选择、设置root密码、网络配置、主机名设置和禁用SELinux的步骤，详细内容请阅读本文，希望能对你有所帮助... ‌AnolisOS‌是由阿里云推出的开源操作系统，旨
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2