spring integration dsl :: stored procedure passing input parameter

Question

I am trying to convert existing stored proc outbound gateway xml into dsl.

    <int-jdbc:stored-proc-outbound-gateway  id="my-proc"
                                        request-channel="myChannel"
                                        data-source="datasource"
                                        stored-procedure-name="SAMPLE_SP"
                                        expect-single-result="false"
                                        ignore-column-meta-data="true">
    <!-- Parameter Definitions -->                                      
    <int-jdbc:sql-parameter-definition  name="V_TEST_ID" direction="IN"/>
    <int-jdbc:sql-parameter-definition name="O_MSG" direction="OUT"/>

    <!-- Parameter Mappings Before Passing & Receiving -->                              
    <int-jdbc:parameter name="V_TEST_ID" expression="payload.testId"/>
</int-jdbc:stored-proc-outbound-gateway>

can you please throw some light how to pass input parameters to dsl?

    @Bean
public StoredProcOutboundGateway spGateway(){
    StoredProcOutboundGateway storedProcOutboundGateway = new StoredProcOutboundGateway(storedProcExecutor());
    storedProcOutboundGateway.setExpectSingleResult(true);
    storedProcOutboundGateway.setRequiresReply(true);
    return storedProcOutboundGateway;
}

@Bean
public StoredProcExecutor storedProcExecutor() {
    StoredProcExecutor storedProcExecutor = new StoredProcExecutor(this.datasource);
    storedProcExecutor.setStoredProcedureName("SAMPLE_SP2");
    storedProcExecutor.setIsFunction(false);
    storedProcExecutor.setReturningResultSetRowMappers(..);
    return storedProcExecutor;
}

score 2 · Accepted Answer · answered Sep 18 '18 at 10:56

You need to create the procedure parameters, and the sql parameters...

@Bean
public StoredProcExecutor storedProcExecutor() {
    StoredProcExecutor storedProcExecutor = new StoredProcExecutor(this.datasource);
    storedProcExecutor.setStoredProcedureName("SAMPLE_SP2");
    storedProcExecutor.setIsFunction(false);
    storedProcExecutor.setReturningResultSetRowMappers(..);

    List<ProcedureParameter> procedureParameters = new ArrayList<>();
    procedureParameters.add(new ProcedureParameter("cdc_group_name", groupName, null));
    // TODO set output_limit from property file
    procedureParameters.add(new ProcedureParameter("output_limit", 500, null));
    storedProcExecutor.setProcedureParameters(procedureParameters);

    List<SqlParameter> sqlParameters = new ArrayList<>();
    sqlParameters.add(new SqlParameter("cdc_group_name", Types.CHAR));
    sqlParameters.add(new SqlParameter("output_limit", Types.BIGINT));
    storedProcExecutor.setSqlParameters(sqlParameters);

    return storedProcExecutor;
}

spring integration dsl :: stored procedure passing input parameter

1 Answers1