Oracle迁移PostgreSQL时SimpleJdbcCall调用存储过程异常如何解决？

我正在进行从Oracle到PostgreSQL的数据库迁移，原有在Oracle环境下运行正常的SimpleJdbcCall调用存储过程代码，切换到PostgreSQL后运行报错，以下为相关代码及异常信息。

报错代码 #

SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate);
simpleJdbcCall.withSchemaName(schema).withProcedureName("PRC_SERVICES");
simpleJdbcCall.declareParameters(new SqlParameter("P_ID", Types.VARCHAR), 
    new SqlParameter("SERVICE_ID",Types.NUMERIC), new SqlParameter("TEMP_ID",Types.NUMERIC), 
    new SqlParameter("ENABLE",Types.VARCHAR), new SqlParameter("REMOVE",Types.VARCHAR),
    new SqlParameter("URL", Types.VARCHAR), new SqlOutParameter("STATUS",Types.NUMERIC),
    new SqlOutParameter("MSG",Types.VARCHAR));
queryParams = baseTransformation.getParamMapForUserServiceProcedure(queryParams);
resultsMap = simpleJdbcCall.execute(queryParams);
result = Integer.parseInt(String.valueOf(resultsMap.get("P_STATUS")));

异常信息 #

[INFO ] 2021-08-31 22:44:27.628 [http-nio-8080-exec-2] UserServiceImpl

• Action=updateUserService Request={enable=Y, thermostat_id=64, serviceid=3, userid=1, remove=Y}
  [DEBUG] 2021-08-31 22:44:27.661 [http-nio-8080-exec-2] SimpleJdbcCall - Added declared parameter for [PRC_SERVICES]: P_ID
  [DEBUG] 2021-08-31 22:44:27.661 [http-nio-8080-exec-2] SimpleJdbcCall - Added declared parameter for [PRC_SERVICES]: SERVICE_ID
  [DEBUG] 2021-08-31 22:44:27.661 [http-nio-8080-exec-2] SimpleJdbcCall - Added declared parameter for [PRC_SERVICES]: TEMP_ID
  [DEBUG] 2021-08-31 22:44:27.661 [http-nio-8080-exec-2] SimpleJdbcCall - Added declared parameter for [PRC_SERVICES]: ENABLE
  [DEBUG] 2021-08-31 22:44:27.661 [http-nio-8080-exec-2] SimpleJdbcCall - Added declared parameter for [PRC_SERVICES]: REMOVE
  [DEBUG] 2021-08-31 22:44:27.662 [http-nio-8080-exec-2] SimpleJdbcCall - Added declared parameter for [PRC_SERVICES]: URL
  [DEBUG] 2021-08-31 22:44:27.662 [http-nio-8080-exec-2] SimpleJdbcCall - Added declared parameter for [PRC_SERVICES]: STATUS
  [DEBUG] 2021-08-31 22:44:27.662 [http-nio-8080-exec-2] SimpleJdbcCall - Added declared parameter for [PRC_SERVICES]: MSG
  [DEBUG] 2021-08-31 22:44:27.662 [http-nio-8080-exec-2] SimpleJdbcCall - JdbcCall call not compiled before execution - invoking compile
  [DEBUG] 2021-08-31 22:44:27.667 [http-nio-8080-exec-2] DataSourceUtils - Fetching JDBC Connection from DataSource
  [DEBUG] 2021-08-31 22:44:40.893 [http-nio-8080-exec-2] CallMetaDataProviderFactory - Using org.springframework.jdbc.core.metadata.PostgresCallMetaDataProvider
  [DEBUG] 2021-08-31 22:44:40.893 [http-nio-8080-exec-2] CallMetaDataProvider - Retrieving metadata for null/mz_ods/prc_services
  [WARN ] 2021-08-31 22:44:41.868 [http-nio-8080-exec-2] CallMetaDataProvider - Error while retrieving metadata for procedure columns: org.postgresql.util.PSQLException: Cannot cast to boolean: "2"
  [DEBUG] 2021-08-31 22:44:41.868 [http-nio-8080-exec-2] DataSourceUtils - Returning JDBC Connection to DataSource
  [DEBUG] 2021-08-31 22:44:41.868 [http-nio-8080-exec-2] SimpleJdbcCall - Compiled stored procedure. Call string is [{call mz_ods.prc_services()}]
  [DEBUG] 2021-08-31 22:44:41.869 [http-nio-8080-exec-2] SimpleJdbcCall - SqlCall for procedure [PRC_SERVICES] compiled
  [DEBUG] 2021-08-31 22:44:41.869 [http-nio-8080-exec-2] CallMetaDataContext - Unable to locate the corresponding IN or IN-OUT parameter for "SERV_REMOVE" in the parameters used: []
  [DEBUG] 2021-08-31 22:44:41.869 [http-nio-8080-exec-2] CallMetaDataContext - Unable to locate the corresponding IN or IN-OUT parameter for "SERV_ENABLE" in the parameters used: []
  [DEBUG] 2021-08-31 22:44:41.869 [http-nio-8080-exec-2] CallMetaDataContext - Unable to locate the corresponding IN or IN-OUT parameter for "USER_ID" in the parameters used: []
  [DEBUG] 2021-08-31 22:44:41.869 [http-nio-8080-exec-2] CallMetaDataContext - Unable to locate the corresponding IN or IN-OUT parameter for "SERVICE_ID" in the parameters used: []
  [DEBUG] 2021-08-31 22:44:41.869 [http-nio-8080-exec-2] CallMetaDataContext - Unable to locate the corresponding IN or IN-OUT parameter for "THERMOSTAT_ID" in the parameters used: []
  [DEBUG] 2021-08-31 22:44:41.869 [http-nio-8080-exec-2] CallMetaDataContext - Matching [SERV_REMOVE, SERV_ENABLE, USER_ID, SERVICE_ID, THERMOSTAT_ID, REPORT_URL] with []
  [DEBUG] 2021-08-31 22:44:41.869 [http-nio-8080-exec-2] CallMetaDataContext - Found match for []
  [DEBUG] 2021-08-31 22:44:41.870 [http-nio-8080-exec-2] SimpleJdbcCall
• The following parameters are used for call {call mz_ods.prc_services()} with {}
  [DEBUG] 2021-08-31 22:44:41.870 [http-nio-8080-exec-2] JdbcTemplate - Calling stored procedure [{call mz_ods.prc_services()}]
  [DEBUG] 2021-08-31 22:44:41.870 [http-nio-8080-exec-2] DataSourceUtils - Fetching JDBC Connection from DataSource

• Spring JDBC的SimpleJdbcCall默认会自动读取数据库存储过程的元数据，低版本Spring JDBC和PostgreSQL 11+的系统表结构不兼容，读取元数据时抛出Cannot cast to boolean: "2"错误，元数据读取失败后手动声明的参数全部失效，最终生成的调用语句不带任何参数，导致找不到匹配的传入参数。
• 手动声明的参数名和传入参数Map的key不匹配，代码里声明的入参名为ENABLE、REMOVE、TEMP_ID等，实际传入的参数key为SERV_ENABLE、SERV_REMOVE、THERMOSTAT_ID，名称不一致无法匹配。
• 出参取值错误，代码声明的出参名为STATUS，实际取值时用了P_STATUS，名称不匹配无法获取返回值。
• 未明确指定存储过程类型，PostgreSQL的存储过程（PROCEDURE）和函数（FUNCTION）调用语法不同，未正确指定会导致调用失败。

1. 关闭SimpleJdbcCall的自动元数据读取功能，避免版本兼容报错，新增withoutProcedureColumnMetaDataAccess()配置。
2. 对齐参数名，要么修改declareParameters里的参数名和queryParams的key完全一致，要么修改getParamMapForUserServiceProcedure方法返回的Map key和声明的参数名匹配，注意PostgreSQL默认将标识符转为小写存储，如果建存储过程时用双引号保留了大写，参数名需要严格匹配大小写。
3. 修正出参取值逻辑，将resultsMap.get("P_STATUS")改为resultsMap.get("STATUS")和声明的出参名一致。
4. 明确指定调用类型，如果是CREATE PROCEDURE创建的存储过程设置withFunction(false)，如果是CREATE FUNCTION创建的函数设置withFunction(true)。

修正后完整代码 #

SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate);
simpleJdbcCall.withSchemaName(schema)
        .withProcedureName("PRC_SERVICES")
        // 关闭自动读取元数据，解决PG版本兼容报错
        .withoutProcedureColumnMetaDataAccess()
        // 按实际类型配置是存储过程还是函数
        .withFunction(false);

simpleJdbcCall.declareParameters(
    new SqlParameter("P_ID", Types.VARCHAR), 
    new SqlParameter("SERVICE_ID",Types.NUMERIC), 
    new SqlParameter("TEMP_ID",Types.NUMERIC), 
    new SqlParameter("ENABLE",Types.VARCHAR), 
    new SqlParameter("REMOVE",Types.VARCHAR),
    new SqlParameter("URL", Types.VARCHAR), 
    new SqlOutParameter("STATUS",Types.NUMERIC),
    new SqlOutParameter("MSG",Types.VARCHAR)
);
queryParams = baseTransformation.getParamMapForUserServiceProcedure(queryParams);
resultsMap = simpleJdbcCall.execute(queryParams);
// 出参名和声明一致
result = Integer.parseInt(String.valueOf(resultsMap.get("STATUS")));

内容的提问来源于stack exchange，提问作者vaishnavi banait