java - 如何将列表数据变量传递给存储过程并执行存储过程?
问题描述
我有 8 个变量列表并将它们收集到以下代码中的变量 ls :
Workbook workbook = new Workbook("D:/excel file/Mapping Prod Matriks _Group Sales Commercial.xlsx");
com.aspose.cells.Worksheet worksheet = workbook.getWorksheets().get(0);
com.aspose.cells.Cells cells = worksheet.getCells();
Range displayRange = cells.getMaxDisplayRange();
List<String> ParaObjGroup = new ArrayList<String>();
List<String> ParaObjCode = new ArrayList<String>();
List<String> ParaProdMatrixId = new ArrayList<String>();
List<String> ParaProdChannelId = new ArrayList<String>();
List<String> ParaProdSalesGroupId = new ArrayList<String>();
List<String> ParaCustGroup = new ArrayList<String>();
List<String> ParaSlsThroughId = new ArrayList<String>();
List<Integer> Active = new ArrayList<Integer>();
for(int row= displayRange.getFirstRow()+1;row<displayRange.getRowCount();row++){
ParaObjGroup.add(displayRange.get(row,1).getStringValue());
ParaObjCode.add(displayRange.get(row,3).getStringValue());
ParaProdMatrixId.add(displayRange.get(row,5).getStringValue());
ParaProdChannelId.add(displayRange.get(row,7).getStringValue());
ParaProdSalesGroupId.add(displayRange.get(row,9).getStringValue());
ParaCustGroup.add(displayRange.get(row,11).getStringValue());
ParaSlsThroughId.add(displayRange.get(row,13).getStringValue());
Active.add(displayRange.get(row,14).getIntValue());
}
List<Object[]> ls = new ArrayList<Object[]>();
for(int i=0;i<ParaObjGroup.size();i++){
ls.add(new Object[]{ParaObjGroup.get(i),ParaObjCode.get(i),ParaProdMatrixId.get(i),ParaProdChannelId.get(i),ParaProdSalesGroupId.get(i),ParaCustGroup.get(i),ParaSlsThroughId.get(i),Active.get(i)});
}
lovService.coba(ls);
然后在方法 coba 中发送变量 ls 作为参数输入:
@ServiceLog(schema = ConstantaVariable.DBDefinition_Var.PARAMS_DB_SCHEMA, sp = ConstantaVariable.PARAMSProcedure_VAR.PR_SP_FAHMI)
@Transactional(propagation=Propagation.REQUIRED, rollbackFor={Exception.class,SQLException.class})
public void coba(List<Object[]> lo){
Map<String, Object> mapInputParameter = new LinkedHashMap<String, Object>();
mapInputParameter.put("P_T_TABLE_UPLD_EXCEL", lo);
ParamsService.getService().executeSPForInsertData(null,ConstantaVariable.PARAMSProcedure_VAR.PR_SP_FAHMI,mapInputParameter);
}
下面的代码是执行存储过程的方法。我希望 ls 变量中的数据成为 inputParameter
@Autowired
DataSource paramsDataSourceBean;
@Transactional(propagation = Propagation.REQUIRED, rollbackFor ={SQLException.class,Exception.class})
public void executeSPForInsertData(DataSource ds,String procedureName,Map<String, Object> inputParameter){
SimpleJdbcCall jdbcCall = new SimpleJdbcCall(paramsDataSourceBean).withProcedureName(procedureName);
String[] path = procedureName.split(".");
if (path.length >= 2) {
jdbcCall.withSchemaName(path[0]);
jdbcCall.withCatalogName(path[1]);
jdbcCall.withProcedureName(path[2]);
jdbcCall.execute(inputParameter);
}
}
这是我的商店程序
PROCEDURE PR_SP_FAHMI (P_T_TABLE_UPLD_EXCEL IN PARAMS.EXCEL) is
P_LOGID VARCHAR2(255);
BEGIN
BEGIN
INSERT INTO PARAMS.EMPTY
SELECT
C.PARA_OBJT_GROUP ,
C.PARA_OBJT_CODE ,
C.PARA_PROD_MATRIX_ID ,
C.PARA_PROD_CHANNEL_ID ,
C.PARA_PROD_SALES_GROUP_ID ,
C.PARA_CUST_GROUP ,
C.PARA_SLS_THROUGH_ID ,
C.ACTIVE
FROM TABLE(P_T_TABLE_UPLD_EXCEL) C;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'ERROR-' || SQLERRM);
END;
END PR_SP_FAHMI;
在我运行之后,没有任何事情发生,也没有错误。
解决方案
推荐阅读
- c++ - 值向量的向量与指向值向量的指针向量
- json - 如何从nodeJS中的json doc数组中删除密钥
- java - 这是揭开 RSA 签名的正确方法吗?
- google-data-studio - 在 Data Studio 中 - 从多列创建饼图
- reactjs - 表材质ui中的多个数组
- ios - Ionic 4 - iOS 状态栏与顶部选项卡重叠
- android-studio - 停止 Android Studio 注释掉注释下粘贴的行
- python-3.x - FileNotFoundError:文件 b'/path/to/my/file.csv' 不存在
- python - 具有不同帮助选项的 Python Argparse
- java - 添加“为空”条件时,LocalDateTime 停止工作