解决Java调用自定义参数Record参数类型存储过程的方法
在Java应用程序中，与数据库进行交互时，经常需要调用存储过程来执行特定的数据库操作。有时候，这些存储过程可能包含自定义参数类型，如Record类型，这就需要我们在Java中进行一些额外的处理。在本文中，我们将讨论如何解决Java调用存储过程时遇到的自定义参数类型Record的问题。
背景
Java通过JDBC（Java Database Connectivity）来实现与数据库的连接和交互。在调用存储过程时，通常使用CallableStatement来执行。然而，当存储过程的参数中包含自定义的Record类型时，就需要采取一些特殊的步骤。
1.java调用存储过程，本次改造有4.9万个存储包，每个存储包中有若干个存储过程，每个存储过程有若干个参数，
参数类型有: number,varchar2,Record,Table 以及这四种参数混合在一个存储过程中。那么这样复杂的存储过程我们如何调用呢，如何构造这样复杂的参数呢？
接下来我们将会解决这样复杂类型的存储过程java如何调用。
解决方案:
解决步骤，需要动态将RECORD 类型的参数 创建为OBJECT类型，TABLE 类型的也需要重新处理，Number 和varchar2 类型不用处理直接调用。
本次需求网上找了很多资料都没有直接的解决方案，直到最后没有办法之后我就想着包一层，然后在调用，没想到问客户是如何调用这样复杂的存储过程，他们的回复也是包一层，甚至给了一个调用复杂存储过程的例子。这样我就更加确定我包一层在调用，这样的方案是可行的。
解决步骤
步骤一，通过判断 ，如果该RECORD 类型已经创建一层，那么就不需要创建，如果没有创建，那么就需要创建包一层设置类型为OBJECT
下面直接看代码
这个代码是创建类型的代码； 如果类型不存在就创建。
private void createType(List<ParentRecord> parentRecords, TemplateMapper mapper) {
Map<String, Object> params = new HashMap<>();
String produceName = parentRecords.get(0).getObjectName();
for (ParentRecord parentRecord : parentRecords) {
String dataTypeResult = parentRecord.getDataType();
if ("VARCHAR2".equals(dataTypeResult) || "NUMBER".equals(dataTypeResult)) {
continue;
}
String typeName = "APPS." + parentRecord.getTypeSubname() + "_P2";
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.append("CREATE OR REPLACE TYPE ").append(typeName).append(" AS OBJECT (");
List<ParentRecord> childParams = parentRecord.getChildParams();
for (ParentRecord childParam : childParams) {
String fieldName = childParam.getArgumentName();
String dataType = childParam.getDataType();
String length = childParam.getDataLength();
sqlBuilder.append("\n ").append(fieldName);
sqlBuilder.append(" ").append(dataType);
if ("VARCHAR2".equals(dataType)) {
sqlBuilder.append("(").append(length).append(")");
}
sqlBuilder.append(",");
}
if (parentRecord.getChildParams().size() > 0) {
sqlBuilder.deleteCharAt(sqlBuilder.length() - 1);
}
if ("PL/SQL TABLE".equals(parentRecord.getDataType())) {
List<ParentRecord> childParams1 = parentRecord.getChildParams().get(0).getChildParams();
// 创建子级关系
sqlBuilder.setLength(0);
sqlBuilder.append("CREATE OR REPLACE TYPE ").append(parentRecord.getChildParams().get(0).getTypeSubname() + "_P2").append(" AS OBJECT (");
for (ParentRecord record : childParams1) {
String fieldName = record.getArgumentName();
String dataType = record.getDataType();
String length = record.getDataLength();
sqlBuilder.append("\n ").append(fieldName);
sqlBuilder.append(" ").append(dataType);
if ("VARCHAR2".equals(dataType)) {
sqlBuilder.append("(").append(length).append(")");
}
sqlBuilder.append(",");
}
if (parentRecord.getChildParams().size() > 0) {
sqlBuilder.deleteCharAt(sqlBuilder.length() - 1);
}
sqlBuilder.append("\n);");
createPrdureType(sqlBuilder.toString(), mapper, params, produceName);
// 创建父级关系
sqlBuilder.setLength(0);
sqlBuilder.append("CREATE OR REPLACE TYPE ").
append(typeName)
.append(" AS TABLE OF ").append(parentRecord.getChildParams().get(0).getTypeSubname() + "_P2").append(";\n");
} else {
sqlBuilder.append("\n);");
}
System.out.println(sqlBuilder.toString());
createPrdureType(sqlBuilder.toString(), mapper, params, produceName);
}
}
下面片是创建动态创建存储过程代码
private String createTypeTemplate(List<ParentRecord> parentRecords,TemplateMapper mapper) {
Map<String, Object> params = new HashMap<>();
String produceName = parentRecords.get(0).getObjectName();
String produceBackage = parentRecords.get(0).getPackageName();
createType( parentRecords, mapper);
//1 构建完整的存储过程sql
StringBuilder headerBuilder = new StringBuilder();
headerBuilder.append("CREATE OR REPLACE PROCEDURE APPS.");
headerBuilder.append(produceName + "_P2").append("(\n");
for (ParentRecord parentRecord : parentRecords) {
String typeName = parentRecord.getTypeSubname();
String paramName = parentRecord.getArgumentName();
String paramDirection = parentRecord.getInOut();
String dataType = parentRecord.getDataType();
headerBuilder.append(" ").append(paramName).append(" ");
if ("IN".equals(paramDirection)) {
headerBuilder.append(" " + paramDirection + " ");
}
if ("OUT".equals(paramDirection)) {
headerBuilder.append(" " + paramDirection + " NOCOPY\t");
}
if ("IN/OUT".equals(paramDirection)) {
headerBuilder.append(" IN OUT NOCOPY ");
}
if ("VARCHAR2".equals(dataType) || "NUMBER".equals(dataType)) {
headerBuilder.append(dataType);
} else {
headerBuilder.append(typeName + "_P2");
}
headerBuilder.append(",").append("\n");
}
if (headerBuilder.length() > 2) {
headerBuilder.delete(headerBuilder.length() - 2, headerBuilder.length());
}
headerBuilder.append("\n) AS\n");
//2,构建参数映射
StringBuilder variableBuilder = new StringBuilder();
for (ParentRecord parentRecord : parentRecords) {
String dataType = parentRecord.getDataType();
List<ParentRecord> childParams = parentRecord.getChildParams();
if ("PL/SQL TABLE".equals(dataType)) {
for (ParentRecord childParam : childParams) {
String typeSubname = childParam.getTypeSubname();
if ("PL/SQL TABLE".equals(dataType)) {
String parenparamName = parentRecord.getTypeSubname();
String argumentName = parentRecord.getArgumentName();
variableBuilder.append("\n");
variableBuilder.append(argumentName + "_tmp ")
.append(produceBackage + ".")
.append(parenparamName)
.append(";");
}
variableBuilder.append("\n");
variableBuilder.append(typeSubname + "_tmp ")
.append(typeSubname+"_P2")
.append(";");
}
} else if("PL/SQL RECORD".equals(dataType)){
variableBuilder.append("\n")
.append(parentRecord.getArgumentName() + "_tmp ")
.append((produceBackage + "."))
.append(parentRecord.getTypeSubname())
.append(";");
}else {
logger.info("NUMBER 和VARCHAR2 不需要初始化");
}
}
// 3.开始构建体
StringBuilder beginBuilder = new StringBuilder();
beginBuilder.append("\nBEGIN\n");
for (ParentRecord parentRecord : parentRecords) {
String inOut = parentRecord.getInOut();
if (!inOut.contains("IN")) {
continue;
}
String dataType = parentRecord.getDataType();
List<ParentRecord> childParams = parentRecord.getChildParams();
if ("PL/SQL RECORD".equals(dataType)) {
for (ParentRecord childParam : childParams) {
String paramExp = parentRecord.getArgumentName();
String paramName = childParam.getArgumentName();
beginBuilder.append(paramExp + "_tmp.")
.append(paramName).append(" := ").append(paramExp + ".")
.append(paramName).append(";\n");
}
beginBuilder.append("\n");
} else if("PL/SQL TABLE".equals(dataType)){
ParentRecord parentRecord1 = childParams.get(0);
String paramName = parentRecord.getArgumentName();
beginBuilder.append("\nFOR idx IN ")
.append(paramName).append(".FIRST .. ")
.append(paramName).append(".LAST LOOP\n");
List<ParentRecord> childParams1 = parentRecord1.getChildParams();
for (ParentRecord record : childParams1) {
String fieldName = record.getArgumentName();
beginBuilder.append(paramName + "_tmp")
.append("(idx).").append(fieldName).append(" := ")
.append(paramName).append("(idx).").append(fieldName).append(";\n");
}
beginBuilder.append("END LOOP;\n\n");
}else {
logger.info("NUMBER 和VARCHAR2 不需要初始化");
}
}
// 4.1拼接调用存储过程
StringBuilder callBuilder = new StringBuilder();
callBuilder.append("\n").append(produceBackage).append(".").append(produceName + "(");
for (ParentRecord parentRecord : parentRecords) {
String argumentName = parentRecord.getArgumentName();
String dataType = parentRecord.getDataType();
if("PL/SQL TABLE".equals(dataType)||"PL/SQL RECORD".equals(dataType)){
callBuilder.append(argumentName + "_tmp").append(", ");
}else {
callBuilder.append(argumentName).append(", ");
}
}
if (callBuilder.length() > 2) {
callBuilder.delete(callBuilder.length() - 2, callBuilder.length());
}
callBuilder.append(");\n\n");
// 5 出参初始化
StringBuilder initOutBuilder = new StringBuilder();
for (ParentRecord parentRecord : parentRecords) {
String inOut = parentRecord.getInOut();
String typeSubname = parentRecord.getTypeSubname();
if (!inOut.contains("OUT")) {
continue;
}
String dataType = parentRecord.getDataType();
List<ParentRecord> childParams = parentRecord.getChildParams();
String paramExp = parentRecord.getArgumentName();
if ("PL/SQL RECORD".equals(dataType)) {
initOutBuilder.append(paramExp)
.append(" := ")
.append(typeSubname + "_P2")
.append("(");
for (ParentRecord childParam : childParams) {
initOutBuilder.append("NULL, ");
}
if (initOutBuilder.length() > 2) {
initOutBuilder.delete(initOutBuilder.length() - 2, initOutBuilder.length());
}
initOutBuilder.append(");\n");
} else if ("PL/SQL TABLE".equals(dataType)) {
List<ParentRecord> childParams1 = parentRecord.getChildParams().get(0).getChildParams();
String typeName = parentRecord.getChildParams().get(0).getTypeSubname();
initOutBuilder.append(typeName + "_tmp")
.append(" := ")
.append(typeName + "_P2")
.append("(");
for (ParentRecord childParam : childParams1) {
initOutBuilder.append("NULL, ");
}
if (initOutBuilder.length() > 2) {
initOutBuilder.delete(initOutBuilder.length() - 2, initOutBuilder.length());
}
initOutBuilder.append(");\n");
String argumentName = parentRecord.getArgumentName();
initOutBuilder.append(argumentName + " := ")
.append(parentRecord.getTypeSubname() + "_P2();\n");
initOutBuilder.append("FOR i IN ")
.append(argumentName + "_tmp").append(".FIRST .. ")
.append(argumentName + "_tmp").append(".LAST LOOP\n ")
.append(argumentName).append(".EXTEND;\n")
.append(argumentName + "(" + argumentName + ".LAST) := ")
.append(typeName + "_tmp;\n")
.append("END LOOP;\n");
}
}
// 6.出参赋值
StringBuilder outValueBuilder = new StringBuilder();
for (ParentRecord parentRecord : parentRecords) {
String inOut = parentRecord.getInOut();
if (!inOut.contains("OUT")) {
continue;
}
String dataType = parentRecord.getDataType();
String argumentName = parentRecord.getArgumentName();
List<ParentRecord> childParams = parentRecord.getChildParams();
if ("PL/SQL RECORD".equals(dataType)) {
for (ParentRecord childParam : childParams) {
String childArgument = childParam.getArgumentName();
outValueBuilder.append(argumentName).append(".").append(childArgument)
.append(" := ")
.append(argumentName + "_tmp").append(".").append(childArgument).append(";\n");
}
outValueBuilder.append("\n");
} else if ("PL/SQL TABLE".equals(dataType)) {
List<ParentRecord> childParams1 = childParams.get(0).getChildParams();
outValueBuilder.append("\nFOR idx IN ")
.append(argumentName + "_tmp.FIRST .. " + argumentName + "_tmp.last LOOP\n\n");
for (ParentRecord record : childParams1) {
String childArgument = record.getArgumentName();
outValueBuilder.append(argumentName + "(idx).")
.append(childArgument)
.append(" := ")
.append(argumentName + "_tmp")
.append("(idx).")
.append(childArgument + ";\n");
}
outValueBuilder.append("END LOOP;\n");
}
}
// 7.最后commit
StringBuilder finalBuilder = new StringBuilder();
finalBuilder.append("COMMIT;\n").append("END ").append(produceName + "_P2").append(";\n");
StringBuilder ScriptFinalBuilder = new StringBuilder();
ScriptFinalBuilder
.append(headerBuilder)
.append(variableBuilder)
.append(beginBuilder)
.append(callBuilder)
.append(initOutBuilder)
.append(outValueBuilder)
.append(finalBuilder);
System.out.println("===================================");
System.out.println(ScriptFinalBuilder.toString());
// 创建一个完整的存储过程
params.clear();
params.put("sql", ScriptFinalBuilder.toString());
mapper.createProcedure(ScriptFinalBuilder.toString(), params);
第三步，类型和存储过程创建完成之后，就是创建一个动态的模版，调用存储过程
下面代码是调用Record 参数类型和Table类型，以及混合类型的参数，java调用存储过程的代码。调用完成之后解析返回值。
public Object recordProcedures(Map<String, Object> resquestparams){
//1,校验构建参数
List<ParentRecord> parentRecordList = checkParaProcedure(resquestparams);
//2.获取配置信息
ApiConfig config = getConfig();
String parasql = config.getParaSql();
//根据存储包和存储过程名查询参数详细信息
logger.info("========自定义执行的sql为:" + parasql);
// 获取存储过程字段信息
Map<String, Object> hashMap1 = new LinkedHashMap<>();
// 3. 构建JDBC参数执行 复杂类型存储过程
try (DruidPooledConnection connection = dynamicDataSource.get().getConnection()) {
OracleConnection conn = connection.unwrap(OracleConnection.class);
// 构造 SQL 语句，
CallableStatement cs = conn.prepareCall(parasql);
for (ParentRecord parentRecord : parentRecordList) {
Integer paramIndex = parentRecord.getPosition();
String typeSubname = parentRecord.getTypeSubname();
String argumentName = parentRecord.getArgumentName();
String dataType = parentRecord.getDataType();
String inOut = parentRecord.getInOut();
String paraType = typeSubname + "_P2";
List<ParentRecord> childParams = parentRecord.getChildParams();
if(inOut.contains("IN")){
if ("PL/SQL RECORD".equals(dataType)){
StructDescriptor structDesc = StructDescriptor.createDescriptor(paraType, conn);
String javaObjectClassName = StructDescriptor.getJavaObjectClassName(conn, structDesc);
STRUCT struct = new STRUCT(structDesc, conn, getStructAttributes(childParams.size(), resquestparams, typeSubname));
cs.setObject(paramIndex, struct);
} else if ("PL/SQL TABLE".equals(dataType)) {
String tableTypeSubname = parentRecord.getTypeSubname();
Map<String, Map<String, Object>> data = (Map<String, Map<String, Object>>) resquestparams.get("data");
List<Map<String, Object> >mapList = (List<Map<String, Object>>) data.get(tableTypeSubname);
int size = mapList.size();
ParentRecord childRecord = parentRecord.getChildParams().get(0);
String childtypeSubname = childRecord.getTypeSubname();
List<ParentRecord> childParams2 = childRecord.getChildParams();
// TODO TABLE
// 创建并设置输入参数
STRUCT[] structArray = new STRUCT[size];
// 这里假设L_Esb_Info_Rec_Type和L_Header_Tbl_Type都是STRUCT类型
StructDescriptor headerRecTypePara = StructDescriptor.createDescriptor(childtypeSubname+"_P2", conn);
for (int i = 0; i < size; i++) {
Map<String, Object> mapValue = mapList.get(i);
STRUCT headerRecTypeStruct = new STRUCT(headerRecTypePara, conn, getStructAttributes(childParams2.size(), mapValue));
structArray[i] = headerRecTypeStruct;
}
ArrayDescriptor headerTblTypePara = ArrayDescriptor.createDescriptor(tableTypeSubname+"_P2", conn);
ARRAY array = new ARRAY(headerTblTypePara, conn, structArray);
cs.setObject(paramIndex, array);
} else if ("NUMBER".equals(dataType)) {
Integer paramInt = (Integer) resquestparams.get(argumentName);
cs.setInt(paramIndex, paramInt);
} else if ("VARCHAR2".equals(dataType)) {
String paramString = (String) resquestparams.get(argumentName);
cs.setString(paramIndex, paramString);
}
}else {
// 处理OUT参数
if ("PL/SQL RECORD".equals(dataType)){
cs.registerOutParameter(paramIndex, OracleTypes.STRUCT, paraType);
} else if ("PL/SQL TABLE".equals(dataType)) {
cs.registerOutParameter(paramIndex, OracleTypes.ARRAY, paraType);
}else if("NUMBER".equals(dataType)){
cs.registerOutParameter(paramIndex, OracleTypes.INTEGER);
} else if ("VARCHAR2".equals(dataType)) {
cs.registerOutParameter(paramIndex, OracleTypes.VARCHAR);
}
}
}
// 执行存储过程
cs.execute();
//解析返回结果 并返回给用户
for (ParentRecord parentRecord : parentRecordList) {
String dataType = parentRecord.getDataType();
String inOut = parentRecord.getInOut();
String typeSubname = parentRecord.getTypeSubname();
String parentArgument = parentRecord.getArgumentName();
if(!inOut.contains("OUT")){
continue;
}
int paramIndex = parentRecord.getPosition();
if("PL/SQL RECORD".equals(dataType)){
Map<String, Object> result = new HashMap<>();
Struct outputValue = (Struct) cs.getObject(paramIndex);
Object[] attributes = outputValue.getAttributes();
for (int i = 0; i < attributes.length; i++) {
// 具体的值 包装之后返回
String argumentName = parentRecord.getChildParams().get(i).getArgumentName();
Object attributeValue = attributes[i];
result.put(argumentName,attributeValue);
}
hashMap1.put(typeSubname,result);
} else if ("PL/SQL TABLE".equals(dataType)) {
List<Map<String, Object>> result = new ArrayList<>();
Array basetable = cs.getArray(paramIndex);
Object[] arrayData = (Object[]) basetable.getArray();
for (Object arrayDatum : arrayData) {
if (arrayDatum == null) {
continue;
}
STRUCT row = (STRUCT) arrayDatum;
Object[] attributes = row.getAttributes();
// 处理输出数据
System.out.println("===================");
HashMap<String, Object> hashMap = new HashMap<>();
for (int i = 0; i < attributes.length; i++) {
Object attributeValue = attributes[i];
String outArgumentName = parentRecord.getChildParams().get(0).getChildParams().get(i).getArgumentName();
hashMap.put(outArgumentName,attributeValue);
}
result.add(hashMap);
}
hashMap1.put(typeSubname, result);
} else if ("NUMBER".equals(dataType)) {
int intValue = cs.getInt(paramIndex);
hashMap1.put(parentArgument,intValue);
} else if ("VARCHAR2".equals(dataType)) {
String stringValue = cs.getString(paramIndex);
hashMap1.put(parentArgument,stringValue);
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return hashMap1;
}
总结
通过以上步骤，你可以成功地在Java中调用带有自定义参数类型Record的存储过程。确保在代码中适当处理异常，并根据具体情况调整参数类型和名称。这样，你就能够顺利地与数据库进行交互，执行包含自定义Record类型Table类型，Record类型混合Table类型，Record类型混合Table类型混合Number类型混合varchar类型的存储过程。
希望上面文章能够帮助到您，如果有疑问欢迎评论留言讨论。最后祝您工作顺利。
1415
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
