如何使用 Apache Camel SQL 组件仅更新消耗的 sql 行? [英] How to update only consumed sql rows with Apache Camel SQL Component?

问题描述

我是 Camel 新手，我尝试处理 SQL 数据.如果 SQL 消耗(选择)完成，我会尝试更新消耗的行，但我只会收到错误的 SQL 语法"异常.

I'm new in Camel and i try to process SQL data. If the SQL consume (select) is done, i try to update the consumed rows, but i just get a "bad SQL grammar" exception.

我使用 Apache Camel SQL 组件，其中起始端点是 sql select 语句.要将它们标记为已使用，我使用 SQL-Component 的 onConsume 参数.在select中，v_table是原表t_table的视图，后面更新时会用到.所以v_table和t_table中一行的id是一样的.为了不更新 t_table 中的所有行，我使用 where 条件和 where id = :#id.

I use the Apache Camel SQL-Component, where the starting endpoint is a sql select statement. To mark them as consumed, i use the onConsume parameter of the SQL-Component. In the select, v_table is the view of the original table t_table, which is used afterwards in the update. So the id of a row in v_table and t_table is the same. To update not all rows in t_table, i use the where condition with where id = :#id.

String sqlSelect = "select * from v_table where camel_is_read = 0";
String sqlUpdate = "update t_table set camel_is_read = 1, date_checked = sysdate where id = :#id";
from("sql:"+sqlSelect+"?dataSource=myDataSource&onConsume="+sqlUpdate)
.process(new Processor() {
    public void process(Exchange exchange) throws Exception {
        System.out.println(exchange.getIn().getBody().toString());
    }
})                    
.errorHandler(deadLetterChannel("direct:moveFailedOut").useOriginalMessage())
.bean("orderToJms")
.to(jmsURI)
.bean("validate")
.to(ftpOut);

如果我执行这个，我会得到以下异常:

If i execute this, i get the follwoing exception:

WARN  Error executing onConsume/onConsumeFailed query update t_table set camel_is_read = 1, date_checked = sysdate where id = :?id. Caused by: [org.springframework.jdbc.BadSqlGrammarException - PreparedStatementCallback; bad SQL grammar [update t_table set camel_is_read = 1, date_checked = sysdate where id = ?]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00904: "YSDATEHERE": ungültiger Bezeichner
]
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [update t_table set camel_is_read = 1, date_checked = sysdate where id = ? exception is java.sql.SQLSyntaxErrorException: ORA-00904: "YSDATEHERE": ungültiger Bezeichner

    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:237)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617)
    at org.apache.camel.component.sql.DefaultSqlProcessingStrategy.commit(DefaultSqlProcessingStrategy.java:46)
    at org.apache.camel.component.sql.SqlConsumer.processBatch(SqlConsumer.java:195)
    at org.apache.camel.component.sql.SqlConsumer$1.doInPreparedStatement(SqlConsumer.java:118)
    at org.apache.camel.component.sql.SqlConsumer$1.doInPreparedStatement(SqlConsumer.java:91)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:589)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617)
    at org.apache.camel.component.sql.SqlConsumer.poll(SqlConsumer.java:91)
    at org.apache.camel.impl.ScheduledPollConsumer.doRun(ScheduledPollConsumer.java:174)
    at org.apache.camel.impl.ScheduledPollConsumer.run(ScheduledPollConsumer.java:101)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
    at java.util.concurrent.FutureTask$Sync.innerRunAndReset(FutureTask.java:351)
    at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:178)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:178)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:724)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "YSDATEHERE": ungültiger Bezeichner

我尝试使用测试 ID(例如3")在数据库中手动执行更新，该操作有效，因此通用 SQL 语法应该没问题.所以在我看来，Camel 不能替换 :#id 参数.

I tried to execute the update manually in the database with a test-id (e. g. "3"), which works, so the generall SQL grammar should be fine. So it seems for me, that Camel can't replace the :#id parameter.

我添加了一个处理器，以检查选择的结果:

I added a processor, to check the outcome of the select:

{ID=3, [...] CAMEL_IS_READ=0}

在这里我可以看到，选择成功捕获了必要的id.我不明白为什么 Camel 不能将 :#id 参数替换为 3 的 id 值.有谁知道如何解决这个问题?我使用了 this 和 this 作为粗略的示例/模板.还是这种方法总体上是错误的?

Here i can see, that the select successfully catched the necessary id. I don't understand why Camel is not able to replace the :#id parameter with the id value of 3. Does anyone know how to fix this problem? I used this and this as a rough example/template. Or is this approach wrong in general?

推荐答案

最后，通过将 sysdate 语句包围在 to_date() 函数中，它对我有用:

Finally it works for me by surrounding the sysdate statement with the to_date() function:

String sqlSelect = "select * from v_table where camel_is_read = 0";
String sqlUpdate = "update t_table set camel_is_read = 1, date_checked = to_date(sysdate) where id = :#id";
from("sql:"+sqlSelect+"?dataSource=myDataSource&onConsume="+sqlUpdate)
.process(new Processor() {
    public void process(Exchange exchange) throws Exception {
        System.out.println(exchange.getIn().getBody().toString());
    }
})                    
.errorHandler(deadLetterChannel("direct:moveFailedOut").useOriginalMessage())
.bean("orderToJms")
.to(jmsURI)
.bean("validate")
.to(ftpOut);

一些注意事项，什么不起作用/不是一个好主意(在我看来)并且可以节省您的时间:
在某些情况下，onConsumeBatchComplete 可能是一个解决方案.如果您知道条件，您可能会暗示，如果所有具有此条件的行都通过骆驼传递，则可以更新此行.这与 sysdate 一起使用，不需要 id 占位符.这种解决方案的缺点是，它是隐含的，如果同时添加新行，骆驼没有通过，它们也会在批处理完成后更新.所以我不能推荐这个.

在 Java 中设置时间也不起作用，如下所示:

Some notes, what doesn't work / is not a good idea (in my opinion) and saves you maybe time:
In some situations onConsumeBatchComplete could be a solution. If you know the condition, you can maybe implicate, that if all rows with this condition are passed through camel, this rows can be updated. This works together with sysdate and doesn't need the id placeholder. Disadvantage of this solution is, that it is implicate and if in the same time new rows are added, which camel didnt't pass, they will be also updated after the batch is completed. So i can't recommend this.

It also doesn't work to set the time in Java, like this:

String sqlUpdate = "update t_table date_checked = " + today + " where id = :#id";

如果您不是每天都重新开始路线，骆驼路线会在几天、几周或几年内处于活动状态.在这种情况下，骆驼路线将在启动时生成，此后，今天"日期总是与这天日期固定.因此它将始终使用相同的日期进行数据库更新.

If you don't restart your route every day, the camel route is active over some days, weeks or years. In this case, the camel route will be generated on startup and after this, there is the "todays" date always fix with this days date. So it will always use the same date for the database update.

这篇关于如何使用 Apache Camel SQL 组件仅更新消耗的 sql 行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！