java - 无法在提供的参数值中找到 'in_id' 对应的参数值

Question

我正在尝试实现以下教程 https://www.tutorialspoint.com/spring/calling_stored_procedure.htm对于带有 postgres 的 simplejdbccall(需要将现有应用程序从 Oracle 迁移到使用 simplejdbccall 的 Postgres)，但出现以下异常

 INFO: Unable to locate the corresponding parameter value for 'in_id' within the parameter values provided: [inID]
 Exception in thread "main" org.springframework.dao.InvalidDataAccessApiUsageException: Required input parameter 'in_id' is missing

我使用以下代码来提取信息:

 public void setDataSource(DataSource dataSource) {
    this.dataSource = dataSource;
 //   this.jdbcCall =  new SimpleJdbcCall(dataSource).withProcedureName("getRecord");
    this.jdbcCall =  new SimpleJdbcCall(dataSource).withCatalogName("public").withFunctionName("getrecord1");
}

public Student getStudent(Integer id) {
    //SqlParameterSource in = new MapSqlParameterSource().addValue("in_id", id);
    System.out.println("----In getStudent-----"+id );
    SqlParameterSource in = new MapSqlParameterSource().addValue("inID", id);
    /* SqlParameterSource in = new MapSqlParameterSource().addValue("id", id, Types.INTEGER)
            .addValue("name",Types.VARCHAR)
            .addValue("age", Types.INTEGER);


    */

    Map<String, Object> out = jdbcCall.execute(in);

    Student student = new Student();
    student.setId(id);
    student.setName((String) out.get("out_name"));
    student.setAge((Integer) out.get("out_age"));
    return student;
}

我在 postgres 数据库中有以下功能:

 CREATE function getrecord1 (in_id INTEGER, OUT out_name character, 
                        OUT out_age Integer)
  as

  $$SELECT name, age from student where id=in_id$$
   LANGUAGE sql VOLATILE
   COST 100;

我已经尝试过Receiving Message Unable to locate the Corresponding Parameter when calling Stored Procedure但也使用下面的代码我遇到了问题

   SqlParameterSource in = new MapSqlParameterSource().addValue("id", id, Types.INTEGER)
            .addValue("name",Types.VARCHAR)
            .addValue("age", Types.INTEGER);

Answer 1

我已按照步骤从 https://www.mkyong.com/jdbc/jdbc-callablestatement-postgresql-stored-function/ 创建函数如下图

public class getConnectionDetails {

    public static void main(String[] args) {

        String createFunction = "CREATE OR REPLACE FUNCTION environment_management.get_connection_details(p_application_name VARCHAR , OUT lv_conn_record refcursor) "
            + " RETURNS  refcursor "
            + " AS $$ "
            + " BEGIN "
            + " OPEN lv_conn_record FOR SELECT CONNECTION_ID, CONNECTION_TYPE, CONNECTION_NAME, DESCRIPTION, MAX_RETRY_ATTEMPT, RETRY_DELAY_IN_SECONDS,ENABLE_DBMS_OUTPUT from  public.CONNECTION_SPECIFICATION "
            + " where APPLICATION_NAME=p_application_name"
            + " AND ACTIVE_FLAG = 'Y';"
            + " END; "
            + " $$ "
            + " LANGUAGE plpgsql";




    try (Connection conn = DriverManager.getConnection(
            "jdbc:postgresql://a302-3354-7677.ldn.swissbank.com:5453/pg_emea_at28400_dev_177", "pgdbo", "X9u9CZmgW3b%6AcYHtfESFBf");

         Statement statement = conn.createStatement();

    ) {



        // create function
        statement.execute(createFunction);




    } catch (SQLException e) {
        System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    }

}

}

并调用函数如下所示:

   public final Collection<ConnectionDto> getApplicationConnections(String applicationName) {

        System.out.println("Fetch Connections from the database for applicationName: " + applicationName);

  this.jdbcCall = new SimpleJdbcCall(dataSource).withSchemaName("environment_management").withFunctionName("get_connection_details").withoutProcedureColumnMetaDataAccess()
            .declareParameters(
                    new SqlOutParameter("lv_conn_record",  Types.OTHER),
                    new SqlParameter("p_application_name", Types.VARCHAR));


    SqlParameterSource in = new MapSqlParameterSource().addValue("lv_conn_record", Types.REF_CURSOR)
         .addValue("p_application_name", applicationName);

    Map<String, Object> applicationConnectionsResultMap = jdbcCall.execute(in);

    System.out.println("----3333333333333333333 in getApplicationConnections-----" +applicationConnectionsResultMap);

    return createApplicationConnectionsFromResultMap(applicationConnectionsResultMap);
}

我的 Bean.xml 看起来像:

    <property name="driverClassName" value="org.postgresql.Driver"/>
    <property name = "url" value = "jdbc:postgresql://a302-3354-7677.ldn:5453/pg"/>
    <property name = "username" value = "pgdbo"/>
    <property name = "password" value = "abcde"/>

    <property name="initialSize" value="10"/>
    <property name="maxActive" value="5"/>
    <!-- added for postgres -->
    <property name="defaultAutoCommit" value="false" />

</bean>

<bean id = "studentJDBCTemplate1"
      class = "com.tutorialspoint.StudentJDBCTemplate1">
    <property name = "dataSource" ref = "dataSource" />
</bean>