You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
273 lines
15 KiB
273 lines
15 KiB
2 years ago
|
package com.fr.plugin.db.procedure.controller;
|
||
|
|
|
||
|
|
import com.fr.base.ParameterMapNameSpace;
|
||
|
|
import com.fr.base.StoreProcedureParameter;
|
||
|
|
import com.fr.data.core.DataCoreUtils;
|
||
|
|
import com.fr.data.core.db.TableProcedure;
|
||
|
|
import com.fr.data.core.db.dialect.base.key.check.DataBaseDetail;
|
||
|
|
import com.fr.data.core.db.dialect.base.key.check.DataBaseType;
|
||
|
|
import com.fr.data.impl.NameDatabaseConnection;
|
||
|
|
import com.fr.data.impl.storeproc.ProcedureDataModel;
|
||
|
|
import com.fr.data.operator.DataOperator;
|
||
|
|
import com.fr.decision.webservice.Response;
|
||
|
|
import com.fr.decision.webservice.annotation.LoginStatusChecker;
|
||
|
|
import com.fr.file.ConnectionConfig;
|
||
|
|
import com.fr.general.ComparatorUtils;
|
||
|
|
import com.fr.json.JSONObject;
|
||
|
|
import com.fr.log.FineLoggerFactory;
|
||
|
|
import com.fr.plugin.context.PluginContexts;
|
||
|
|
import com.fr.plugin.db.procedure.designframe.widgets.ConnectionCombox;
|
||
|
|
import com.fr.plugin.db.procedure.entity.TemplateProcedureDBAUtil;
|
||
|
|
import com.fr.plugin.db.procedure.entity.TemplateProcedureEntity;
|
||
|
|
import com.fr.plugin.db.procedure.stableKey.ExecuteProcedureAction;
|
||
|
|
import com.fr.script.Calculator;
|
||
|
|
import com.fr.stable.ArrayUtils;
|
||
|
|
import com.fr.stable.StringUtils;
|
||
|
|
import com.fr.third.alibaba.druid.support.json.JSONUtils;
|
||
|
|
import com.fr.third.springframework.stereotype.Controller;
|
||
|
|
import com.fr.third.springframework.web.bind.annotation.RequestBody;
|
||
|
|
import com.fr.third.springframework.web.bind.annotation.RequestMapping;
|
||
|
|
import com.fr.third.springframework.web.bind.annotation.RequestMethod;
|
||
|
|
import com.fr.third.springframework.web.bind.annotation.ResponseBody;
|
||
|
|
import com.fr.web.core.SessionPoolManager;
|
||
|
|
import com.fr.web.core.WidgetSessionIDInfor;
|
||
|
|
import com.fr.workspace.WorkContext;
|
||
|
|
import javax.servlet.http.HttpServletRequest;
|
||
|
|
import javax.servlet.http.HttpServletResponse;
|
||
|
|
import java.util.*;
|
||
|
|
@Controller
|
||
|
|
@LoginStatusChecker(
|
||
|
|
required = false
|
||
|
|
)
|
||
|
|
public class DbController {
|
||
|
|
/**
|
||
|
|
* 获取模式，如果数据库是支持模式的话，优先从配置的名称里面获取模式，这样性能会好点，要不然获取表去对比会有点慢，性能有点慢
|
||
|
|
* @param datasource
|
||
|
|
* @param produceName
|
||
|
|
* @return
|
||
|
|
*/
|
||
|
|
private static String[] getSchema(com.fr.data.impl.Connection datasource, String produceName) {
|
||
|
|
int i = produceName.indexOf(".");
|
||
|
|
String schemaName = StringUtils.EMPTY;
|
||
|
|
if (i > 0) {
|
||
|
|
schemaName = produceName.substring(0, i);
|
||
|
|
}
|
||
|
|
String[] schemas = DataCoreUtils.getDatabaseSchema(datasource);
|
||
|
|
boolean isOracleSystemSpace = true;//DesignerEnvManager.getEnvManager().isOracleSystemSpace();
|
||
|
|
DataBaseDetail detail = DataOperator.getInstance().getDataBaseDetail(datasource, isOracleSystemSpace);
|
||
|
|
if (ArrayUtils.isNotEmpty(detail.getSchemas())) {
|
||
|
|
schemas = detail.getSchemas();
|
||
|
|
}
|
||
|
|
if (schemas != null) {
|
||
|
|
for (String schema : schemas) {
|
||
|
|
if (ComparatorUtils.equals(schema, schemaName)) {
|
||
|
|
return new String[]{schema};
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
return schemas;
|
||
|
|
}
|
||
|
|
public static String beginExecuteProcedure(HttpReqBean reqBean) throws Exception {
|
||
|
|
HashMap jsonObject = new HashMap();
|
||
|
|
FineLoggerFactory.getLogger().info("executeProcedure:procedureId=" + reqBean.getProcedureId() + ",reportSessionID=" + reqBean.getReportSessionID() + ",otherPara=" + reqBean.getOtherPara());
|
||
|
|
TemplateProcedureEntity entity = TemplateProcedureDBAUtil.getInstance().getEntityByprocedureId(reqBean.getProcedureId());
|
||
|
|
if (entity == null) {
|
||
|
|
jsonObject.put("errorCode", "100000");
|
||
|
|
jsonObject.put("errorMsg", "没有找到" + reqBean.getProcedureId() + "对应的配置");
|
||
|
|
return JSONUtils.toJSONString(jsonObject);
|
||
|
|
} else {
|
||
|
|
FineLoggerFactory.getLogger().info("entity info:" + entity.toString());
|
||
|
|
WidgetSessionIDInfor widgetSessionIDInfor = SessionPoolManager.getSessionIDInfor(reqBean.getReportSessionID(), WidgetSessionIDInfor.class);
|
||
|
|
if (widgetSessionIDInfor == null) {
|
||
|
|
jsonObject.put("errorCode", "100005");
|
||
|
|
jsonObject.put("errorMsg", "模板session过期了");
|
||
|
|
return JSONUtils.toJSONString(jsonObject);
|
||
|
|
}
|
||
|
|
String templatePath = "reportlets/" + widgetSessionIDInfor.getRelativePath();
|
||
|
|
FineLoggerFactory.getLogger().info("templatePath:" + templatePath);
|
||
|
|
if (templatePath.equals(entity.getReportPath()) == false) {
|
||
|
|
jsonObject.put("errorCode", "100001");
|
||
|
|
jsonObject.put("errorMsg", "模板路径对不上");
|
||
|
|
return JSONUtils.toJSONString(jsonObject);
|
||
|
|
}
|
||
|
|
String connectName = entity.getConnectionName();
|
||
|
|
FineLoggerFactory.getLogger().info("配置信息为:templatePath=" + templatePath + ",connectName=" + connectName + ",procedureName:" + entity.getProcedureName());
|
||
|
|
ArrayList<String> connectNames = ConnectionCombox.getConnectNames();
|
||
|
|
boolean isExist = false;
|
||
|
|
for (String remoteConnectName : connectNames) {
|
||
|
|
if (remoteConnectName.equals(connectName)) {
|
||
|
|
isExist = true;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
FineLoggerFactory.getLogger().info("当初配置的connect现在还存在吗:" + isExist);
|
||
|
|
if (isExist == false) {
|
||
|
|
jsonObject.put("errorCode", "100002");
|
||
|
|
jsonObject.put("errorMsg", "数据连接:" + connectName + ",已经不存在了");
|
||
|
|
return JSONUtils.toJSONString(jsonObject);
|
||
|
|
}
|
||
|
|
com.fr.data.impl.Connection datasource = ConnectionConfig.getInstance().getConnection(connectName);
|
||
|
|
String[] schemas = getSchema(datasource, entity.getProcedureName());
|
||
|
|
boolean isOracleSystemSpace = true;//DesignerEnvManager.getEnvManager().isOracleSystemSpace();
|
||
|
|
DataBaseDetail detail = DataOperator.getInstance().getDataBaseDetail(datasource, isOracleSystemSpace);
|
||
|
|
boolean isOracle = DataBaseType.ORACLE.equals(detail.getType());
|
||
|
|
boolean isOracleSysSpace = true;//DesignerEnvManager.getEnvManager().isOracleSystemSpace();
|
||
|
|
List sqlTablees = DataCoreUtils.getProcedures(datasource, schemas, isOracle, isOracleSysSpace);
|
||
|
|
// TableProcedure[] tableProcedures = (TableProcedure[]) sqlTablees.get(0);
|
||
|
|
List<TableProcedure> procedureList = new ArrayList<>();
|
||
|
|
for (Object sqlTablee : sqlTablees) {
|
||
|
|
TableProcedure[] procedureArray = (TableProcedure[]) sqlTablee;
|
||
|
|
procedureList.addAll(Arrays.asList(procedureArray));
|
||
|
|
}
|
||
|
|
TableProcedure tableProcedure = null;
|
||
|
|
for (TableProcedure p : procedureList) {
|
||
|
|
String name = isOracle ? (p.getSchema() + "." + p.getName()) : p.getName();
|
||
|
|
FineLoggerFactory.getLogger().info("查回来存储过程名字 :" + p.getName()+",现在的名字:"+name);
|
||
|
|
if (name.equalsIgnoreCase(entity.getProcedureName())) {
|
||
|
|
tableProcedure = p;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
if (tableProcedure == null) {
|
||
|
|
jsonObject.put("errorCode", "100003");
|
||
|
|
jsonObject.put("errorMsg", "找不到存储过程:" + entity.getProcedureName());
|
||
|
|
return JSONUtils.toJSONString(jsonObject);
|
||
|
|
}
|
||
|
|
String procedureText = tableProcedure.toString();//DataOperator.getInstance().getProcedureText(connectName, tableProcedure.toString());
|
||
|
|
String proceduceContent = DataOperator.getInstance().getProcedureText(connectName, tableProcedure.toString());
|
||
|
|
FineLoggerFactory.getLogger().info( "procedureText:" + procedureText);
|
||
|
|
FineLoggerFactory.getLogger().info( "proceduceContent:" + proceduceContent);
|
||
|
|
String parameterDefaultValue = "";
|
||
|
|
if (StringUtils.isNotEmpty(procedureText)) {
|
||
|
|
int index_begin = procedureText.indexOf("BEGIN");
|
||
|
|
String defaulValueStr = index_begin == -1 ? procedureText : procedureText.substring(0, index_begin);
|
||
|
|
String[] strs = defaulValueStr.split("'");
|
||
|
|
parameterDefaultValue = strs.length > 1 ? strs[1] : parameterDefaultValue;
|
||
|
|
}
|
||
|
|
StoreProcedureParameter[] newparameters = DataOperator.getInstance().getStoreProcedureDeclarationParameters(connectName, tableProcedure.toString(), parameterDefaultValue);
|
||
|
|
FineLoggerFactory.getLogger().info("参数个数:"+newparameters.length);
|
||
|
|
MyStoreProcedure sp = new MyStoreProcedure();
|
||
|
|
sp.setDatabaseConnection(new NameDatabaseConnection(connectName));
|
||
|
|
sp.setParameters(newparameters);
|
||
|
|
sp.setQuery(procedureText);
|
||
|
|
sp.setShare(false);
|
||
|
|
sp.setMaxMemRowCount(200);
|
||
|
|
sp.setCalculating(false);
|
||
|
|
String otherParam = reqBean.getOtherPara();
|
||
|
|
LinkedHashMap reqMap = (LinkedHashMap) JSONUtils.parse(otherParam);
|
||
|
|
Map parameterMap = new HashMap();
|
||
|
|
for (StoreProcedureParameter p : newparameters) {
|
||
|
|
if (reqMap.containsKey(p.getName())) {
|
||
|
|
parameterMap.put(p.getName(), reqMap.get(p.getName()));
|
||
|
|
} else {
|
||
|
|
parameterMap.put(p.getName(), p.getValue());
|
||
|
|
}
|
||
|
|
FineLoggerFactory.getLogger().info("存储过程参数:key=" + p.getName() + ",value=" + parameterMap.get(p.getName()));
|
||
|
|
}
|
||
|
|
try {
|
||
|
|
//测试连接通不通
|
||
|
|
// boolean status = DataOperator.getInstance().testConnection(sp.getDatabaseConnection());
|
||
|
|
// if(status){
|
||
|
|
// sp.resetDataModelList();
|
||
|
|
// }
|
||
|
|
// ProcedureDataModel[] dataModels = DesignTableDataManager.createLazyDataModel(sp, true);
|
||
|
|
sp.setFirstExpand(false);
|
||
|
|
Calculator var4 = Calculator.createCalculator();
|
||
|
|
ParameterMapNameSpace var5 = ParameterMapNameSpace.create(parameterMap);
|
||
|
|
var4.pushNameSpace(var5);
|
||
|
|
ProcedureDataModel[] result = sp.creatDataModel(var4);
|
||
|
|
/*
|
||
|
|
if (result != null) {
|
||
|
|
if (result.length > 0) {
|
||
|
|
for(ProcedureDataModel dataModel : result){
|
||
|
|
int rowCount = dataModel.getRowCount();
|
||
|
|
if(rowCount > 0){
|
||
|
|
FineLoggerFactory.getLogger().info("结果数据有"+rowCount+"行");
|
||
|
|
int columnCount = dataModel.getColumnCount();
|
||
|
|
ArrayList<String> columnNames = new ArrayList();
|
||
|
|
for(int columnIndex = 0 ; columnIndex < columnCount;columnIndex++){
|
||
|
|
String columnName = dataModel.getColumnName(columnIndex);
|
||
|
|
columnNames.add(columnName);
|
||
|
|
FineLoggerFactory.getLogger().info("columnName:"+columnName);
|
||
|
|
}
|
||
|
|
|
||
|
|
for(int rowIndex = 0 ; rowIndex < rowCount;rowIndex++){
|
||
|
|
FineLoggerFactory.getLogger().info("显示第:"+(rowIndex+1)+"行数据");
|
||
|
|
String descn = "";
|
||
|
|
for(int columnIndex = 0 ; columnIndex < columnCount; columnIndex++){
|
||
|
|
|
||
|
|
descn+=(columnNames.get(columnIndex)+"="+dataModel.getValueAt(rowIndex,columnIndex)+",");
|
||
|
|
}
|
||
|
|
FineLoggerFactory.getLogger().info(descn);
|
||
|
|
}
|
||
|
|
}
|
||
|
|
else{
|
||
|
|
FineLoggerFactory.getLogger().info("结果没数据");
|
||
|
|
}
|
||
|
|
}
|
||
|
|
ProcedureDataModel dataModel = result[0];
|
||
|
|
FineLoggerFactory.getLogger().info("存储过程执行结果last result :"+dataModel.getColumnName(0));
|
||
|
|
} else {
|
||
|
|
FineLoggerFactory.getLogger().info("存储过程执行结果last result length 0:");
|
||
|
|
}
|
||
|
|
}
|
||
|
|
*/
|
||
|
|
FineLoggerFactory.getLogger().info("存储过程执行结果last result:" + result.length);
|
||
|
|
} catch (Exception ex) {
|
||
|
|
ex.printStackTrace();
|
||
|
|
jsonObject.put("errorCode", "100004");
|
||
|
|
jsonObject.put("errorMsg", ex.getMessage());
|
||
|
|
return JSONUtils.toJSONString(jsonObject);
|
||
|
|
}
|
||
|
|
// previewProcedureDataModel 的逻辑如下
|
||
|
|
// public ProcedureDataModel[] previewProcedureDataModel(StoreProcedure var1, Map var2, int var3) {
|
||
|
|
// Calculator var4 = Calculator.createCalculator();
|
||
|
|
// ParameterMapNameSpace var5 = ParameterMapNameSpace.create(var2);
|
||
|
|
// var4.pushNameSpace(var5);
|
||
|
|
// return var1.creatDataModel(var4);
|
||
|
|
// }
|
||
|
|
// Object[] result = dialect.remoteProcedureCall(connection,entity.getProcedureName());
|
||
|
|
FineLoggerFactory.getLogger().info("result");
|
||
|
|
//TemplateFileTree
|
||
|
|
//DesignerFrameFileDealerPane
|
||
|
|
// TableDataTreePane
|
||
|
|
// ConnectionTableProcedurePane
|
||
|
|
// ProcedureDataPane
|
||
|
|
//调用存储过程...
|
||
|
|
}
|
||
|
|
jsonObject.put("status", "ok");
|
||
|
|
return JSONUtils.toJSONString(jsonObject);
|
||
|
|
}
|
||
|
|
@RequestMapping(
|
||
|
|
value = {"/executeProcedure"},
|
||
|
|
method = {RequestMethod.POST}
|
||
|
|
)
|
||
|
|
@ResponseBody
|
||
|
|
public Response executeProcedure(HttpServletRequest req, HttpServletResponse res, @RequestBody HttpReqBean reqBean) throws Exception {
|
||
|
|
if (PluginContexts.currentContext().isAvailable() == false) {
|
||
|
|
return Response.error("100008", "插件需要注册");
|
||
|
|
}
|
||
|
|
if (WorkContext.getCurrent().isLocal()) {
|
||
|
|
FineLoggerFactory.getLogger().info("本地开始执行存储过程");
|
||
|
|
String resultJsonStr = DbController.beginExecuteProcedure(reqBean);
|
||
|
|
FineLoggerFactory.getLogger().info("本地存储过程执行状态结果:" + resultJsonStr);
|
||
|
|
return Response.ok(resultJsonStr);
|
||
|
|
} else {
|
||
|
|
FineLoggerFactory.getLogger().info("远程开始执行存储过程");
|
||
|
|
String jsonParam = reqBean.toJson();
|
||
|
|
FineLoggerFactory.getLogger().info("jsonParam:" + jsonParam);
|
||
|
|
String data = WorkContext.getCurrent().get(ExecuteProcedureAction.class).execute(jsonParam);
|
||
|
|
FineLoggerFactory.getLogger().info("远程存储过程执行状态结果:" + data);
|
||
|
|
return Response.ok(data);
|
||
|
|
}
|
||
|
|
}
|
||
|
|
@RequestMapping(
|
||
|
|
value = {"/removeAll"},
|
||
|
|
method = {RequestMethod.GET}
|
||
|
|
)
|
||
|
|
@ResponseBody
|
||
|
|
public Response removeAll(HttpServletRequest req, HttpServletResponse res) throws Exception {
|
||
|
|
TemplateProcedureDBAUtil.getInstance().removeAll();
|
||
|
|
return Response.ok("ok");
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|