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.
272 lines
15 KiB
272 lines
15 KiB
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"); |
|
} |
|
} |
|
|
|
|