Stored Procedures

package org.springframework.integration.jdbc;

public class StoredProcExecutor implements BeanFactoryAware, InitializingBean {
    public StoredProcExecutor(DataSource dataSource);

    public void setStoredProcedureName(String storedProcedureName);
    public String getStoredProcedureName();
    public void setStoredProcedureNameExpression(Expression expression);
    public String getStoredProcedureNameExpressionAsString();

    public void setProcedureParameters(List<ProcedureParameter> procedureParameters);
    public void setSqlParameters(List<SqlParameter> sqlParameters);
    public void setIsFunction(boolean isFunction);
    public void setReturnValueRequired(boolean returnValueRequired);
    public void setUsePayloadAsParameterSource(boolean usePayloadAsParameterSource);
    public void setSqlParameterSourceFactory(SqlParameterSourceFactory factory);
    public void setReturningResultSetRowMappers(Map<String, RowMapper<?>> mappers);
    public void setIgnoreColumnMetaData(boolean ignoreColumnMetaData);
    public void setSkipUndeclaredResults(boolean skipUndeclaredResults);
    public void setJdbcCallOperationsCacheSize(int size);

    public Map<String, @Nullable Object> executeStoredProcedure();
    public Map<String, @Nullable Object> executeStoredProcedure(Message<?> message);
}

package org.springframework.integration.jdbc.storedproc;

public class ProcedureParameter {
    public ProcedureParameter();
    public ProcedureParameter(String name, Object value, String expression);

    public String getName();
    public void setName(String name);
    public Object getValue();
    public void setValue(Object value);
    public String getExpression();
    public void setExpression(String expression);

    public static Map<String, String> convertExpressions(Collection<ProcedureParameter> procedureParameters);
    public static Map<String, Object> convertStaticParameters(Collection<ProcedureParameter> procedureParameters);
}

import org.springframework.integration.jdbc.StoredProcExecutor;
import org.springframework.integration.jdbc.storedproc.ProcedureParameter;

// Basic procedure call with IN parameters
StoredProcExecutor executor = new StoredProcExecutor(dataSource);
executor.setStoredProcedureName("CREATE_ORDER");

// Define parameters from message
List<ProcedureParameter> params = List.of(
    new ProcedureParameter("customer_id", null, "payload.customerId"),
    new ProcedureParameter("order_date", null, "payload.orderDate"),
    new ProcedureParameter("total_amount", null, "payload.totalAmount")
);
executor.setProcedureParameters(params);

// Execute with message
Order order = new Order(123L, LocalDate.now(), new BigDecimal("499.99"));
Message<Order> message = MessageBuilder.withPayload(order).build();
Map<String, Object> results = executor.executeStoredProcedure(message);

import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.SqlOutParameter;
import java.sql.Types;

// Procedure with IN and OUT parameters
StoredProcExecutor executorWithOut = new StoredProcExecutor(dataSource);
executorWithOut.setStoredProcedureName("PROCESS_PAYMENT");

// Define SQL parameters (IN and OUT)
List<SqlParameter> sqlParams = List.of(
    new SqlParameter("order_id", Types.BIGINT),
    new SqlParameter("payment_method", Types.VARCHAR),
    new SqlOutParameter("transaction_id", Types.VARCHAR),
    new SqlOutParameter("status_code", Types.INTEGER),
    new SqlOutParameter("status_message", Types.VARCHAR)
);
executorWithOut.setSqlParameters(sqlParams);

// Define parameter values from message
List<ProcedureParameter> procParams = List.of(
    new ProcedureParameter("order_id", null, "payload.orderId"),
    new ProcedureParameter("payment_method", null, "payload.method")
);
executorWithOut.setProcedureParameters(procParams);

// Execute and extract OUT parameters
Payment payment = new Payment(456L, "CREDIT_CARD");
Message<Payment> paymentMsg = MessageBuilder.withPayload(payment).build();
Map<String, Object> result = executorWithOut.executeStoredProcedure(paymentMsg);

String transactionId = (String) result.get("transaction_id");
Integer statusCode = (Integer) result.get("status_code");
String statusMessage = (String) result.get("status_message");

import org.springframework.jdbc.core.RowMapper;

// Procedure returning result sets
StoredProcExecutor resultSetExecutor = new StoredProcExecutor(dataSource);
resultSetExecutor.setStoredProcedureName("GET_ORDER_SUMMARY");

// Configure row mappers for multiple result sets
Map<String, RowMapper<?>> rowMappers = Map.of(
    "orders", (rs, rowNum) -> new Order(
        rs.getLong("id"),
        rs.getString("order_number"),
        rs.getBigDecimal("amount")
    ),
    "order_items", (rs, rowNum) -> new OrderItem(
        rs.getLong("id"),
        rs.getString("product_name"),
        rs.getInt("quantity")
    )
);
resultSetExecutor.setReturningResultSetRowMappers(rowMappers);

// Set IN parameter
List<ProcedureParameter> sumParams = List.of(
    new ProcedureParameter("customer_id", null, "payload")
);
resultSetExecutor.setProcedureParameters(sumParams);

// Execute and extract result sets
Message<Long> customerMsg = MessageBuilder.withPayload(789L).build();
Map<String, Object> summaryResults = resultSetExecutor.executeStoredProcedure(customerMsg);

List<Order> orders = (List<Order>) summaryResults.get("orders");
List<OrderItem> items = (List<OrderItem>) summaryResults.get("order_items");

// Calling stored function (returns value)
StoredProcExecutor functionExecutor = new StoredProcExecutor(dataSource);
functionExecutor.setStoredProcedureName("CALCULATE_DISCOUNT");
functionExecutor.setIsFunction(true);
functionExecutor.setReturnValueRequired(true);

// Define function parameters
List<ProcedureParameter> funcParams = List.of(
    new ProcedureParameter("order_amount", null, "payload.amount"),
    new ProcedureParameter("customer_tier", null, "payload.tier")
);
functionExecutor.setProcedureParameters(funcParams);

// Execute function
OrderInfo info = new OrderInfo(new BigDecimal("1000"), "GOLD");
Message<OrderInfo> infoMsg = MessageBuilder.withPayload(info).build();
Map<String, Object> funcResult = functionExecutor.executeStoredProcedure(infoMsg);

// Return value is in the map with key "return" or database-specific key
BigDecimal discount = (BigDecimal) funcResult.get("return");

import org.springframework.expression.Expression;
import org.springframework.expression.spel.standard.SpelExpressionParser;

// Dynamic procedure name using SpEL
StoredProcExecutor dynamicExecutor = new StoredProcExecutor(dataSource);
SpelExpressionParser parser = new SpelExpressionParser();
dynamicExecutor.setStoredProcedureNameExpression(
    parser.parseExpression("'PROCESS_' + payload.type.toUpperCase()")
);

// Set generic parameters
List<ProcedureParameter> dynParams = List.of(
    new ProcedureParameter("entity_id", null, "payload.id"),
    new ProcedureParameter("action", null, "payload.action")
);
dynamicExecutor.setProcedureParameters(dynParams);

// Execute - procedure name determined at runtime
ProcessRequest request = new ProcessRequest("order", 123L, "CANCEL");
Message<ProcessRequest> dynMsg = MessageBuilder.withPayload(request).build();
// Calls PROCESS_ORDER procedure
Map<String, Object> dynResult = dynamicExecutor.executeStoredProcedure(dynMsg);

// Performance optimization: disable metadata lookup
StoredProcExecutor optimizedExecutor = new StoredProcExecutor(dataSource);
optimizedExecutor.setStoredProcedureName("BULK_PROCESS");
optimizedExecutor.setIgnoreColumnMetaData(true); // Skip JDBC metadata

// Must explicitly define SQL parameters when metadata disabled
List<SqlParameter> explicitParams = List.of(
    new SqlParameter("batch_id", Types.BIGINT),
    new SqlParameter("process_date", Types.DATE),
    new SqlOutParameter("processed_count", Types.INTEGER)
);
optimizedExecutor.setSqlParameters(explicitParams);

import org.springframework.integration.dsl.IntegrationFlow;
import org.springframework.integration.jdbc.StoredProcExecutor;
import org.springframework.integration.jdbc.outbound.StoredProcOutboundGateway;
import static org.springframework.integration.jdbc.dsl.Jdbc.*;

@Bean
public IntegrationFlow storedProcFlow(DataSource dataSource) {
    return IntegrationFlow
        .from("orderChannel")
        .handle(storedProcOutboundGateway(dataSource)
            .configurerStoredProcExecutor(spec -> spec
                .storedProcedureName("PROCESS_ORDER")
                .procedureParameter(new ProcedureParameter("order_id", null, "payload"))
                .sqlParameter(new SqlOutParameter("result_code", Types.INTEGER))
            ))
        .handle(msg -> {
            Map<String, Object> result = (Map<String, Object>) msg.getPayload();
            Integer resultCode = (Integer) result.get("result_code");
            System.out.println("Result code: " + resultCode);
        })
        .get();
}

// Old (deprecated):
import org.springframework.integration.jdbc.StoredProcMessageHandler;
import org.springframework.integration.jdbc.StoredProcOutboundGateway;

// New (recommended):
import org.springframework.integration.jdbc.outbound.StoredProcMessageHandler;
import org.springframework.integration.jdbc.outbound.StoredProcOutboundGateway;