Apache Camel 2.18中的IN子句 [英] IN clause in Apache Camel 2.18
问题描述
我正在尝试在路由内执行sql删除语句.该查询包含一个IN子句,我想将一组值传递给IN子句.我正在使用骆驼芯& camel-jdbc 2.18.1和camel-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
推荐答案
此堆栈跟踪来自 camel-jdbc
组件.该组件使用标准的JDBC API,因此受到一些限制.其中之一是,您无法将集合作为单个参数传递.
在Camel中,您可以实现自定义
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屋!