Camel存储过程调用能否用变量？动态设置存储过程名咨询

Hey there! Let's tackle your two Camel stored procedure questions one by one:

Absolutely not—Camel’s sql-stored component does support variables for input parameters, result mapping, and more. The key is using Camel’s expression syntax to reference headers, body fields, or exchange properties in your stored procedure call.

For example, if you want to pass a user ID from a header to a stored procedure, here’s how you’d do it in Java DSL:

from("direct:callUserProc")
    .to("sql-stored:GET_USER_DETAILS?parameterTypes=INTEGER&parameters=#${header.userId}")
    .log("User details: ${body}");

Or in XML:

<route>
    <from uri="direct:callUserProc"/>
    <to uri="sql-stored:GET_USER_DETAILS?parameterTypes=INTEGER&parameters=#${header.userId}"/>
    <log message="User details: ${body}"/>
</route>

Here, #${header.userId} pulls the value from the Camel header userId and binds it to the stored procedure’s parameter. You can also reference POJO body properties with #${body.someField} if needed.

Static endpoint URIs don’t support dynamic procedure names out of the box, but Camel’s dynamic routing features make this totally doable. The most straightforward approaches are using toD() (dynamic to) or recipientList.

Using toD() (Recommended for Most Scenarios) #

The toD() method lets you construct the sql-stored endpoint URI dynamically using Camel expressions. You can pull the procedure name from a REST parameter, header, or any exchange property.

Here’s an example of a REST-to-stored-procedure bridge in Java DSL:

from("rest:get:/execute-proc/{procName}")
    // Critical: Validate the procedure name to prevent SQL injection!
    .filter(simple("${header.procName} in ('GET_USER', 'UPDATE_ORDER', 'DELETE_PRODUCT')"))
        .toD("sql-stored:#${header.procName}?parameterTypes=VARCHAR,INTEGER&parameters=#${header.param1},#${header.param2}")
        .marshal().json()
    .end()
    .otherwise()
        .setBody(constant("Invalid procedure name"))
        .setHeader(Exchange.HTTP_RESPONSE_CODE, constant(400));

Key details here:

• The REST path parameter procName is used to dynamically set the stored procedure name.
• The filter step whitelists valid procedure names—never skip this, as unvalidated dynamic names are a massive SQL injection risk.
• Adjust parameterTypes and parameters to match the input requirements of your stored procedures.

Using recipientList #

If you need more granular routing logic (like choosing between endpoints based on complex conditions), recipientList works too:

from("direct:dynamicProcCall")
    .recipientList(simple("sql-stored:#${header.procName}?parameterTypes=INTEGER&parameters=#${header.id}"))
    .parallelProcessing(false);

Important Considerations #

1. Security First: Always validate or whitelist dynamic procedure names to avoid SQL injection attacks.
2. Parameter Consistency: If your stored procedures have varying parameter counts/types, use a choice() block to adjust parameterTypes and parameters based on the procedure name, or build the entire endpoint URI programmatically.
3. Component Version: Ensure you’re using a recent version of Camel’s SQL component—older versions may have limited support for dynamic endpoint elements.

内容的提问来源于stack exchange，提问作者Kristof Jozsa