Apache Camel 2.18 中的 IN 子句 [英] IN clause in Apache Camel 2.18

问题描述

我正在尝试在路由中执行 sql 删除语句.该查询包含一个 IN 子句，我想将一组值传递给 IN 子句.我正在使用骆驼核 &骆驼-jdbc 2.18.1 和骆驼-sql 2.18.0

I am trying to execute a sql deletion statement within the route. The query contains an IN clause and I want to pass a Set of values to the IN clause. I am using camel-core & camel-jdbc 2.18.1 and camel-sql 2.18.0

from("direct:deleteRoute").setBody(simple("DELETE FROM TABLE_NAME WHERE ID IN (:#in:idSet)")).to(Constants.JDBC_DATA_SOURCE).end();

idSet 的值通过路由中的键 idSet 在交换的标头中传递，该路由调用 direct:deleteRoute.

Value for idSet is passed in the header of the exchange with key idSet from the route, which calls direct:deleteRoute.

但是当我执行时，我得到以下异常.

But when I execute I get the following exception.

java.sql.SQLException: A syntax error has occurred.
    at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:408)
    at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3132)
    at com.informix.jdbc.IfxSqli.D(IfxSqli.java:3412)
    at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2325)
    at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2250)
    at com.informix.jdbc.IfxSqli.executeExecute(IfxSqli.java:2153)
    at com.informix.jdbc.IfxSqli.executeExecute(IfxSqli.java:2133)
    at com.informix.jdbc.IfxResultSet.b(IfxResultSet.java:362)
    at com.informix.jdbc.IfxStatement.a(IfxStatement.java:1144)
    at com.informix.jdbc.IfxStatement.executeImpl(IfxStatement.java:1120)
    at com.informix.jdbc.IfxStatement.c(IfxStatement.java:866)
    at com.informix.jdbc.IfxStatement.execute(IfxStatement.java:784)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:483)
    at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
    at com.sun.proxy.$Proxy105.execute(Unknown Source)
    at org.apache.camel.component.jdbc.JdbcProducer.doCreateAndExecuteSqlStatement(JdbcProducer.java:220)
    at org.apache.camel.component.jdbc.JdbcProducer.createAndExecuteSqlStatement(JdbcProducer.java:125)
    at org.apache.camel.component.jdbc.JdbcProducer.processingSqlBySettingAutoCommit(JdbcProducer.java:86)
    at org.apache.camel.component.jdbc.JdbcProducer.process(JdbcProducer.java:67)
    at org.apache.camel.util.AsyncProcessorConverterHelper$ProcessorToAsyncProcessorBridge.process(AsyncProcessorConverterHelper.java:61)
    at org.apache.camel.processor.SendProcessor.process(SendProcessor.java:145)
    at org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:77)
    at org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:542)
    at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:197)
    at org.apache.camel.processor.Pipeline.process(Pipeline.java:120)
    at org.apache.camel.processor.Pipeline.process(Pipeline.java:83)
    at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:197)
    at org.apache.camel.component.direct.DirectProducer.process(DirectProducer.java:62)
    at org.apache.camel.processor.SendProcessor.process(SendProcessor.java:145)
    at org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:77)
    at org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:542)
    at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:197)
    at org.apache.camel.processor.Pipeline.process(Pipeline.java:120)
    at org.apache.camel.processor.Pipeline.process(Pipeline.java:83)
    at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:197)
    at org.apache.camel.component.direct.DirectProducer.process(DirectProducer.java:62)
    at org.apache.camel.processor.SendProcessor.process(SendProcessor.java:145)
    at org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:77)
    at org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:542)
    at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:197)
    at org.apache.camel.processor.Pipeline.process(Pipeline.java:120)
    at org.apache.camel.processor.Pipeline.process(Pipeline.java:83)
    at org.apache.camel.processor.ChoiceProcessor.process(ChoiceProcessor.java:117)
    at org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:77)
    at org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:542)
    at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:197)
    at org.apache.camel.processor.Pipeline.process(Pipeline.java:120)
    at org.apache.camel.processor.Pipeline.process(Pipeline.java:83)
    at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:197)
    at org.apache.camel.component.timer.TimerConsumer.sendTimerExchange(TimerConsumer.java:192)
    at org.apache.camel.component.timer.TimerConsumer$1.run(TimerConsumer.java:76)
    at java.util.TimerThread.mainLoop(Timer.java:555)
    at java.util.TimerThread.run(Timer.java:505)
Caused by: java.sql.SQLException: null
    at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:408)
    at com.informix.jdbc.IfxSqli.D(IfxSqli.java:3417)
    ... 53 common frames omitted

推荐答案

此堆栈跟踪来自 骆驼-jdbc 组件.该组件使用标准的 JDBC API，因此受到了一些限制.其中之一是，您不能将集合作为单个参数传递.
^{_{在Camel中，可以实现自定义JdbcPrepareStatementStrategy，使用uri参数prepareStatementStrategy进行注册，提供自定义的解析查询逻辑，但我不建议这样做.这会导致很多陷阱.}}

This stacktrace comes from camel-jdbc component. This component uses the standard JDBC API, so it takes some limitations from it. One of this is, that you cannot pass collection as single parameter.
^{_{In Camel, you can implement custom JdbcPrepareStatementStrategy, register it with uri parameter prepareStatementStrategy to provide custom logic of parsing query, but I wouldn't suggest it. This can lead to lot of traps.}}

最好使用 camel-sql 组件，它使用 spring-jdbc 并且自 Apache Camel 2.17 起就允许这样做.

Better use camel-sql component, which uses spring-jdbc and allows this natively since Apache Camel 2.17.

常量预处理语句示例:

from("direct:deleteIds")
        .to("sql:DELETE FROM TABLE_NAME WHERE ID IN (:#in:idSet)");

从正文创建语句的示例:

from("direct:deleteIds")
        .setBody(simple("DELETE FROM TABLE_NAME WHERE ID IN (:?in:idSet)")) // Note questionmark here for specifying parameter. This is difference against query in uri.  
        .to("direct:executeQuery");

from("direct:executeQuery")
        .setHeader(SqlConstants.SQL_QUERY, body())
        .to("sql:dummy?dataSource=#datasource");

这篇关于Apache Camel 2.18 中的 IN 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！