如何使用Apache Camel SQL Component更新仅消耗的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组件的onConsume参数.在选择中，v_table是原始表t_table的视图，该表随后在更新中使用.因此，v_table和t_table中一行的id是相同的.要不更新t_table中的所有行，我在where id = :#id中使用where条件.

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

我尝试使用有效的test-id(例如"3")在数据库中手动执行更新，因此一般的SQL语法应该没问题.因此对于我来说，骆驼似乎无法替换:#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参数.有人知道如何解决此问题吗?我使用了此和

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";

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

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 Component更新仅消耗的sql行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！