如何编写调用包函数的Java?对工作的Java代码做了一点更改

Question

在回答我之前的问题时提供了帮助：

尝试从Java 调用简单Oracle PROCEDURE的索引无效

我可以调用一个返回单个varchar2(1(输出参数的Oracle包过程，但现在我想将该过程更改为具有以下SPEC的FUNCTION，该SPEC返回CHAR:

FUNCTION is_running_in_production RETURN CHAR;

我如何修改下面的代码，该代码调用了返回单个输出参数的过程的早期版本，而不是调用这个调用返回CHAR字段的FUNCTION的过程？

public static final String IS_RUNNING_IN_PRODUCTION = "{call FDS_APPS.FDS_USR_SEC_PKG2.is_running_in_production2(?)}";
public String isRunningInProduction() throws DaoException {

String inProduction = "";

try {
SqlOutParameter isProd = new SqlOutParameter("v_is_prod", OracleTypes.VARCHAR);
List<SqlParameter> paramList = new ArrayList<SqlParameter>();
paramList.add(isProd); 

Map<String, Object> resultMap = jdbcTemplate.call(new CallableStatementCreator() {
public OracleCallableStatement createCallableStatement(Connection connection) throws SQLException {
OracleCallableStatement callableStatement = (OracleCallableStatement) connection
.prepareCall(IS_RUNNING_IN_PRODUCTION);

callableStatement.registerOutParameter(1, Types.VARCHAR);
return callableStatement;
}
}, paramList);
inProduction = (String)resultMap.get("v_is_prod");

} catch (Exception e) {
LOGGER.error("Error while determining if running in prod or not, PROC_NAME::[" + IS_RUNNING_IN_PRODUCTION + "]," + e);
}

return inProduction;    

}

由于jdbcTemplate.call过程似乎需要一个paramList，我注释掉了向paramList添加isProd，并传递了一个添加了零个参数的paramList。我还将callableStatement.registerOutParameter更改为callableStatement.registerReturnParameter，并将数据类型从Types.VARCHAR更改为Types.CHAR。如果这些都是正确的，我不确定如何提取结果来填充Production中的返回变量。

public String isRunningInProduction() throws DaoException {

String inProduction = "";

try {
//SqlOutParameter isProd = new SqlOutParameter("v_is_prod", OracleTypes.VARCHAR);                       
List<SqlParameter> paramList = new ArrayList<SqlParameter>();
//paramList.add(isProd);            

Map<String, Object> resultMap = jdbcTemplate.call(new CallableStatementCreator() {
public OracleCallableStatement createCallableStatement(Connection connection) throws SQLException {
OracleCallableStatement callableStatement = (OracleCallableStatement) connection
.prepareCall(IS_RUNNING_IN_PRODUCTION);


//callableStatement.registerOutParameter(1, Types.VARCHAR);
callableStatement.registerReturnParameter(1, Types.CHAR);
return callableStatement;
}
}, paramList);
inProduction = (String)resultMap.get("v_is_prod");  


} catch (Exception e) {
LOGGER.error("Error while determining if running in prod or not, PROC_NAME::[" + IS_RUNNING_IN_PRODUCTION + "]," + e);
throw new DaoException("Error while retreiving " + IS_RUNNING_IN_PRODUCTION + e);
}

return inProduction;    

}

在获取结果之前，我在初始化resultMap对象时遇到了以下错误。

org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL []; SQL state [99999]; error code [17090]; operation not allowed; nested exception is java.sql.SQLException: operation not allowed

Answer 1

为了调用函数，我建议采用与基于jdbcTemplate.call的链接问题类似但不同的方法。

例如，您可以尝试使用JdbcTemplate和CallableStatementCreator的execute方法，方法与您的示例非常相似，但对输出结果有更多的控制：

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
try {
String result = jdbcTemplate.<String>execute( new CallableStatementCreator() {
public CallableStatement createCallableStatement(Connection connection)
throws SQLException {
CallableStatement cs = connection.prepareCall("{? = call FDS_APPS.FDS_USR_SEC_PKG2.is_running_in_production2}");
// The first out parameter is the result of the function
// Set the appropriate type
cs.registerOutParameter(1, Types.VARCHAR);
// Set the rest of the arguments, if required
return cs;
}
},
new CallableStatementCallback<String>() {
public String doInCallableStatement(CallableStatement cs)  throws SQLException {
cs.execute();
String result = cs.getString(1);
// The value eturned here is the one returned by the execute method
return result;
}
}
);
System.out.printf("Result with CSC: '%s'", result);
} catch (Throwable t) {
// Please, forgive me for this
t.printStackTrace();
}

或者，用lambda表达式简化：

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
try {
String result = jdbcTemplate.<String>execute(connection -> {
CallableStatement cs = connection.prepareCall("{? = call FDS_APPS.FDS_USR_SEC_PKG2.is_running_in_production2}");
// The first out parameter is the result of the function
// Set the appropriate type
cs.registerOutParameter(1, Types.VARCHAR);
// Set the rest of the arguments, if required
return cs;
},
(CallableStatementCallback) cs -> {
cs.execute();
String result1 = cs.getString(1);
// The value returned here is the one returned by the execute method
return result1;
}
);
System.out.printf("Result with CSC: '%s'", result);
} catch (Throwable t) {
// Please, forgive me for this
t.printStackTrace();
}

请注意实际的SQL表达式是如何构造的：

{? = call FDS_APPS.FDS_USR_SEC_PKG2.is_running_in_production2}

使用SimpleJdbcCall类可以大大简化此代码。请考虑以下示例：

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
try {
SimpleJdbcCall isRunningInProductionFunction = new SimpleJdbcCall(jdbcTemplate)
.withSchemaName("FDS_APPS")
.withCatalogName("FDS_USR_SEC_PKG2")
.withFunctionName("is_running_in_production2");
// Indicate the return type. You can pass additional arguments if you need to
String result = isRunningInProductionFunction.executeFunction(String.class);
System.out.printf("Result as simple JDBC call: '%s'", result);
} catch (Throwable t) {
// Please, forgive me for this
t.printStackTrace();
}

请考虑查看Spring Data Access文档：它有很多示例和替代解决方案。