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双主搭建+keepalived高可用的实现

MySQL双主搭建+keepalived高可用的实现

《MySQL双主搭建+keepalived高可用的实现》本文主要介绍了MySQL双主搭建+keepalived高可用的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,... 目录一、测试环境准备二、主从搭建1.创建复制用户2.创建复制关系3.开启复制，确认复制是否成功4.同
阅读更多...
SpringCloud动态配置注解@RefreshScope与@Component的深度解析

SpringCloud动态配置注解@RefreshScope与@Component的深度解析

《SpringCloud动态配置注解@RefreshScope与@Component的深度解析》在现代微服务架构中,动态配置管理是一个关键需求,本文将为大家介绍SpringCloud中相关的注解@Re... 目录引言1. @RefreshScope 的作用与原理1.1 什么是 @RefreshScope1.
阅读更多...
MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

《MyBatis动态SQL优化之标签的实战与技巧(常见用法)》本文通过详细的示例和实际应用场景,介绍了如何有效利用这些标签来优化MyBatis配置,提升开发效率,确保SQL的高效执行和安全性,感... 目录动态SQL详解一、动态SQL的核心概念1.1 什么是动态SQL？1.2 动态SQL的优点1.3 动态S
阅读更多...
Mysql表的简单操作(基本技能)

Mysql表的简单操作(基本技能)

《Mysql表的简单操作(基本技能)》在数据库中,表的操作主要包括表的创建、查看、修改、删除等,了解如何操作这些表是数据库管理和开发的基本技能,本文给大家介绍Mysql表的简单操作,感兴趣的朋友一起看... 目录3.1 创建表 3.2 查看表结构3.3 修改表3.4 实践案例：修改表在数据库中，表的操作主要
阅读更多...
JSON Web Token在登陆中的使用过程

JSON Web Token在登陆中的使用过程

《JSONWebToken在登陆中的使用过程》：本文主要介绍JSONWebToken在登陆中的使用过程,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录JWT 介绍微服务架构中的 JWT 使用结合微服务网关的 JWT 验证1. 用户登录，生成 JWT2. 自定义过滤
阅读更多...
SpringBoot日志配置SLF4J和Logback的方法实现

SpringBoot日志配置SLF4J和Logback的方法实现

《SpringBoot日志配置SLF4J和Logback的方法实现》日志记录是不可或缺的一部分,本文主要介绍了SpringBoot日志配置SLF4J和Logback的方法实现,文中通过示例代码介绍的非... 目录一、前言二、案例一：初识日志三、案例二：使用Lombok输出日志四、案例三：配置Logback一
阅读更多...
springboot security之前后端分离配置方式

springboot security之前后端分离配置方式

《springbootsecurity之前后端分离配置方式》：本文主要介绍springbootsecurity之前后端分离配置方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的... 目录前言自定义配置认证失败自定义处理登录相关接口匿名访问前置文章总结前言spring boot secu
阅读更多...
一文详解SpringBoot响应压缩功能的配置与优化

一文详解SpringBoot响应压缩功能的配置与优化

《一文详解SpringBoot响应压缩功能的配置与优化》SpringBoot的响应压缩功能基于智能协商机制,需同时满足很多条件,本文主要为大家详细介绍了SpringBoot响应压缩功能的配置与优化,需... 目录一、核心工作机制1.1 自动协商触发条件1.2 压缩处理流程二、配置方案详解2.1 基础YAML
阅读更多...
springboot简单集成Security配置的教程

springboot简单集成Security配置的教程

《springboot简单集成Security配置的教程》：本文主要介绍springboot简单集成Security配置的教程,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,... 目录集成Security安全框架引入依赖编写配置类WebSecurityConfig（自定义资源权限规则
阅读更多...
SpringBoot中封装Cors自动配置方式

SpringBoot中封装Cors自动配置方式

《SpringBoot中封装Cors自动配置方式》：本文主要介绍SpringBoot中封装Cors自动配置方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录SpringBoot封装Cors自动配置背景实现步骤1. 创建 GlobalCorsProperties
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2