You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@camel.apache.org by da...@apache.org on 2019/04/22 11:51:04 UTC
[camel] branch master updated: CAMEL-13260: Add JOOQ plain SQL
support
This is an automated email from the ASF dual-hosted git repository.
davsclaus pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/camel.git
The following commit(s) were added to refs/heads/master by this push:
new 4c85c36 CAMEL-13260: Add JOOQ plain SQL support
4c85c36 is described below
commit 4c85c36024930ed1682f71a3fdf64413afdc261e
Author: Denis Istomin <is...@gmail.com>
AuthorDate: Mon Mar 18 21:08:24 2019 +0500
CAMEL-13260: Add JOOQ plain SQL support
---
.../camel-jooq/src/main/docs/jooq-component.adoc | 35 +++++++-
.../camel/component/jooq/JooqConfiguration.java | 13 ++-
.../apache/camel/component/jooq/JooqEndpoint.java | 35 +++-----
.../apache/camel/component/jooq/JooqProducer.java | 33 ++++++--
.../camel/component/jooq/JooqPlainSQLTest.java | 96 ++++++++++++++++++++++
.../apache/camel/component/jooq/JooqXMLTest.java | 12 +++
.../src/test/resources/camel-context.xml | 20 +++++
7 files changed, 209 insertions(+), 35 deletions(-)
diff --git a/components/camel-jooq/src/main/docs/jooq-component.adoc b/components/camel-jooq/src/main/docs/jooq-component.adoc
index 6d8ae58..7656f8b 100644
--- a/components/camel-jooq/src/main/docs/jooq-component.adoc
+++ b/components/camel-jooq/src/main/docs/jooq-component.adoc
@@ -23,6 +23,15 @@ ResultQuery<Record> resultQuery = create.resultQuery("SELECT * FROM BOOK");
Result<Record> result = resultQuery.fetch();
----
+=== Plain SQL
+SQL could be executed using JOOQ's objects "Query" or "ResultQuery".
+Also SQL query could be specified inside URI:
+[source,java]
+----
+from("jooq://org.apache.camel.component.jooq.db.tables.records.BookStoreRecord?query=query=select * from book_store x where x.name = 'test'").to("bean:myBusinessLogic");
+----
+See examples below.
+
=== Consuming from endpoint
Consuming messages from a JOOQ consumer endpoint removes (or updates) entity beans in the database.
This allows you to use a database table as a logical queue: consumers take messages from the queue and then delete/update them to logically remove them from the queue.
@@ -64,12 +73,13 @@ with the following path and query parameters:
|===
-==== Query Parameters (20 parameters):
+==== Query Parameters (21 parameters):
[width="100%",cols="2,5,^1,2",options="header"]
|===
| Name | Description | Default | Type
+| *query* (common) | SQL query. | | String
| *bridgeErrorHandler* (consumer) | Allows for bridging the consumer to the Camel routing Error Handler, which mean any exceptions occurred while the consumer is trying to pickup incoming messages, or the likes, will now be processed as a message and handled by the routing Error Handler. By default the consumer will use the org.apache.camel.spi.ExceptionHandler to deal with exceptions, that will be logged at WARN or ERROR level and ignored. | false | boolean
| *consumeDelete* (consumer) | Delete entity after it is consumed. | true | boolean
| *sendEmptyMessageWhenIdle* (consumer) | If the polling consumer did not poll any files, you can enable this option to send an empty message (no body) instead. | false | boolean
@@ -109,7 +119,7 @@ When using Spring Boot make sure to use the following Maven dependency to have s
----
-The component supports 6 options, which are listed below.
+The component supports 7 options, which are listed below.
@@ -120,6 +130,7 @@ The component supports 6 options, which are listed below.
| *camel.component.jooq.configuration.database-configuration* | | | Configuration
| *camel.component.jooq.configuration.entity-type* | JOOQ entity class. | | Class
| *camel.component.jooq.configuration.operation* | Type of operation to execute on query: execute, fetch, etc. | | JooqOperation
+| *camel.component.jooq.configuration.query* | SQL query. | | String
| *camel.component.jooq.enabled* | Whether to enable auto configuration of the jooq component. This is enabled by default. | | Boolean
| *camel.component.jooq.resolve-property-placeholders* | Whether the component should resolve property placeholders on itself when starting. Only properties which are of String type can use property placeholders. | true | Boolean
|===
@@ -229,6 +240,26 @@ Camel context configuration:
<from uri="jooq://org.apache.camel.component.jooq.db.tables.records.BookStoreRecord?consumeDelete=false"/>
<log message="Consumed ${body}"/>
</route>
+
+ <!-- SQL: select -->
+ <route id="sql-select">
+ <from uri="direct:sql-select"/>
+ <to uri="jooq://org.apache.camel.component.jooq.db.tables.records.BookStoreRecord/fetch?query=select * from book_store x where x.name = 'test'"/>
+ <log message="Fetched ${body}"/>
+ </route>
+
+ <!-- SQL: delete -->
+ <route id="sql-delete">
+ <from uri="direct:sql-delete"/>
+ <to uri="jooq://org.apache.camel.component.jooq.db.tables.records.BookStoreRecord/execute?query=delete from book_store x where x.name = 'test'"/>
+ <log message="Fetched ${body}"/>
+ </route>
+
+ <!-- SQL: consume -->
+ <route id="sql-consume">
+ <from uri="jooq://org.apache.camel.component.jooq.db.tables.records.BookStoreRecord?query=select * from book_store x where x.name = 'test'"/>
+ <log message="Fetched ${body}"/>
+ </route>
</camelContext>
</beans>
----
diff --git a/components/camel-jooq/src/main/java/org/apache/camel/component/jooq/JooqConfiguration.java b/components/camel-jooq/src/main/java/org/apache/camel/component/jooq/JooqConfiguration.java
index c165eb4..e60cdf3 100644
--- a/components/camel-jooq/src/main/java/org/apache/camel/component/jooq/JooqConfiguration.java
+++ b/components/camel-jooq/src/main/java/org/apache/camel/component/jooq/JooqConfiguration.java
@@ -21,8 +21,6 @@ import org.apache.camel.spi.UriParam;
import org.apache.camel.spi.UriParams;
import org.apache.camel.spi.UriPath;
import org.jooq.Configuration;
-import org.jooq.Table;
-import org.jooq.UpdatableRecord;
@UriParams
public class JooqConfiguration implements Cloneable {
@@ -36,6 +34,9 @@ public class JooqConfiguration implements Cloneable {
@UriParam(label = "consumer", defaultValue = "true", description = "Delete entity after it is consumed.")
private boolean consumeDelete = true;
+ @UriParam(description = "SQL query.")
+ private String query;
+
private Configuration databaseConfiguration;
public JooqConfiguration() {
@@ -73,6 +74,14 @@ public class JooqConfiguration implements Cloneable {
this.consumeDelete = consumeDelete;
}
+ public String getQuery() {
+ return query;
+ }
+
+ public void setQuery(String query) {
+ this.query = query;
+ }
+
public JooqConfiguration copy() {
try {
return (JooqConfiguration)super.clone();
diff --git a/components/camel-jooq/src/main/java/org/apache/camel/component/jooq/JooqEndpoint.java b/components/camel-jooq/src/main/java/org/apache/camel/component/jooq/JooqEndpoint.java
index 44eb22a..50f1d58 100644
--- a/components/camel-jooq/src/main/java/org/apache/camel/component/jooq/JooqEndpoint.java
+++ b/components/camel-jooq/src/main/java/org/apache/camel/component/jooq/JooqEndpoint.java
@@ -100,32 +100,23 @@ public class JooqEndpoint extends ScheduledPollEndpoint {
type = ResultQuery.class;
break;
default:
- type = null;
+ throw new UnsupportedOperationException("Operation: " + configuration.getOperation());
}
- if (type == null) {
- return new Expression() {
- @Override
- public <T> T evaluate(Exchange exchange, Class<T> type) {
- return exchange.getMessage().getBody(type);
- };
- };
- } else {
- return new Expression() {
- public Object evaluate(Exchange exchange, Class asType) {
- Object answer = exchange.getIn().getBody(type);
- if (answer == null) {
- Object defaultValue = exchange.getIn().getBody();
- if (defaultValue != null) {
- throw RuntimeCamelException.wrapRuntimeCamelException(new NoTypeConversionAvailableException(defaultValue, type));
- }
-
- answer = exchange.getContext().getInjector().newInstance(type);
+ return new Expression() {
+ public Object evaluate(Exchange exchange, Class asType) {
+ Object answer = exchange.getIn().getBody(type);
+ if (answer == null) {
+ Object defaultValue = exchange.getIn().getBody();
+ if (defaultValue != null) {
+ throw RuntimeCamelException.wrapRuntimeCamelException(new NoTypeConversionAvailableException(defaultValue, type));
}
- return answer;
+
+ answer = exchange.getContext().getInjector().newInstance(type);
}
- };
- }
+ return answer;
+ }
+ };
}
@Override
diff --git a/components/camel-jooq/src/main/java/org/apache/camel/component/jooq/JooqProducer.java b/components/camel-jooq/src/main/java/org/apache/camel/component/jooq/JooqProducer.java
index d5238cd..af5906e 100644
--- a/components/camel-jooq/src/main/java/org/apache/camel/component/jooq/JooqProducer.java
+++ b/components/camel-jooq/src/main/java/org/apache/camel/component/jooq/JooqProducer.java
@@ -20,6 +20,7 @@ import org.apache.camel.Exchange;
import org.apache.camel.Expression;
import org.apache.camel.Message;
import org.apache.camel.support.DefaultProducer;
+import org.apache.camel.util.ObjectHelper;
import org.jooq.Configuration;
import org.jooq.DSLContext;
import org.jooq.Query;
@@ -40,28 +41,42 @@ public class JooqProducer extends DefaultProducer {
}
@Override
- public void process(Exchange exchange) throws Exception {
+ public void process(Exchange exchange) {
JooqConfiguration configuration = endpoint.getConfiguration();
Configuration dbConfig = configuration.getDatabaseConfiguration();
JooqOperation operation = configuration.getOperation();
+ String querySQL = configuration.getQuery();
switch (operation) {
case EXECUTE:
- Query query = expression.evaluate(exchange, Query.class);
- query.attach(dbConfig);
- query.execute();
+ if (ObjectHelper.isEmpty(querySQL)) {
+ Query query = expression.evaluate(exchange, Query.class);
+
+ query.attach(dbConfig);
+ query.execute();
+ } else {
+ DSLContext context = DSL.using(dbConfig);
+ context.execute(querySQL);
+ }
+
break;
case FETCH:
- ResultQuery resultQuery = expression.evaluate(exchange, ResultQuery.class);
- resultQuery.attach(dbConfig);
- Result result = resultQuery.fetch();
- Message target = exchange.getPattern().isOutCapable() ? exchange.getOut() : exchange.getIn();
+ Result result;
+ if (ObjectHelper.isEmpty(querySQL)) {
+ ResultQuery resultQuery = expression.evaluate(exchange, ResultQuery.class);
+ resultQuery.attach(dbConfig);
+ result = resultQuery.fetch();
+ } else {
+ DSLContext context = DSL.using(dbConfig);
+ result = context.fetch(querySQL);
+ }
+ Message target = exchange.getPattern().isOutCapable() ? exchange.getOut() : exchange.getIn();
target.setBody(result);
break;
case NONE:
- final UpdatableRecord entity = expression.evaluate(exchange, UpdatableRecord.class);
DSLContext context = DSL.using(dbConfig);
+ final UpdatableRecord entity = expression.evaluate(exchange, UpdatableRecord.class);
context.batchStore(entity).execute();
break;
default:
diff --git a/components/camel-jooq/src/test/java/org/apache/camel/component/jooq/JooqPlainSQLTest.java b/components/camel-jooq/src/test/java/org/apache/camel/component/jooq/JooqPlainSQLTest.java
new file mode 100644
index 0000000..c900919
--- /dev/null
+++ b/components/camel-jooq/src/test/java/org/apache/camel/component/jooq/JooqPlainSQLTest.java
@@ -0,0 +1,96 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.camel.component.jooq;
+
+import org.apache.camel.ExchangePattern;
+import org.apache.camel.ProducerTemplate;
+import org.apache.camel.RoutesBuilder;
+import org.apache.camel.builder.RouteBuilder;
+import org.apache.camel.component.jooq.db.tables.records.BookStoreRecord;
+import org.apache.camel.component.mock.MockEndpoint;
+import org.jooq.Result;
+import org.junit.Assert;
+import org.junit.Test;
+
+public class JooqPlainSQLTest extends BaseJooqTest {
+
+ @Test
+ public void testSQLConsumer() throws InterruptedException {
+ MockEndpoint mockResult = getMockEndpoint("mock:result");
+ mockResult.expectedMessageCount(1);
+
+ ProducerTemplate producerTemplate = context.createProducerTemplate();
+
+ // Insert
+ BookStoreRecord bookStoreRecord = new BookStoreRecord("test");
+ producerTemplate.sendBody(context.getEndpoint("direct:insert"), ExchangePattern.InOut, bookStoreRecord);
+
+ assertMockEndpointsSatisfied();
+ Assert.assertEquals(bookStoreRecord, mockResult.getExchanges().get(0).getMessage().getBody());
+ }
+
+ @Test
+ public void testSQLProducer() {
+ ProducerTemplate producerTemplate = context.createProducerTemplate();
+
+ // Insert
+ BookStoreRecord bookStoreRecord = new BookStoreRecord("test");
+ producerTemplate.sendBody(context.getEndpoint("direct:insert"), ExchangePattern.InOut, bookStoreRecord);
+
+ // Select
+ Result actual = (Result) producerTemplate.sendBody(context.getEndpoint("direct:selectSQL"), ExchangePattern.InOut, null);
+ Assert.assertEquals(bookStoreRecord, actual.get(0));
+
+ // Delete
+ actual = (Result) producerTemplate.sendBody(context.getEndpoint("direct:deleteSQL"), ExchangePattern.InOut, null);
+ Assert.assertEquals(null, actual);
+
+ // Select
+ actual = (Result) producerTemplate.sendBody(context.getEndpoint("direct:selectSQL"), ExchangePattern.InOut, null);
+ Assert.assertEquals(0, actual.size());
+ }
+
+ @Override
+ protected RoutesBuilder createRouteBuilder() {
+ JooqComponent jooqComponent = (JooqComponent) context().getComponent("jooq");
+ JooqConfiguration jooqConfiguration = new JooqConfiguration();
+ jooqConfiguration.setDatabaseConfiguration(create.configuration());
+ jooqComponent.setConfiguration(jooqConfiguration);
+
+ return new RouteBuilder() {
+ @Override
+ public void configure() {
+ // Book store
+ from("direct:insert")
+ .to("jooq://org.apache.camel.component.jooq.db.tables.records.BookStoreRecord");
+
+ // Producer SQL query select
+ from("direct:selectSQL")
+ .to("jooq://org.apache.camel.component.jooq.db.tables.records.BookStoreRecord/fetch?query=select * from book_store x where x.name = 'test'");
+
+ // Producer SQL query delete
+ from("direct:deleteSQL")
+ .to("jooq://org.apache.camel.component.jooq.db.tables.records.BookStoreRecord/execute?query=delete from book_store x where x.name = 'test'");
+
+ // Consumer SQL query
+ from("jooq://org.apache.camel.component.jooq.db.tables.records.BookStoreRecord?query=select * from book_store x where x.name = 'test'")
+ .to("mock:result");
+ }
+ };
+ }
+
+}
diff --git a/components/camel-jooq/src/test/java/org/apache/camel/component/jooq/JooqXMLTest.java b/components/camel-jooq/src/test/java/org/apache/camel/component/jooq/JooqXMLTest.java
index 22520c6..b8ecc3b 100644
--- a/components/camel-jooq/src/test/java/org/apache/camel/component/jooq/JooqXMLTest.java
+++ b/components/camel-jooq/src/test/java/org/apache/camel/component/jooq/JooqXMLTest.java
@@ -52,4 +52,16 @@ public class JooqXMLTest extends BaseJooqTest {
ProducerTemplate producerTemplate = context.createProducerTemplate();
producerTemplate.sendBody(context.getEndpoint("direct:fetch"), ExchangePattern.InOut, "empty");
}
+
+ @Test
+ public void testSQLSelect() {
+ ProducerTemplate producerTemplate = context.createProducerTemplate();
+ producerTemplate.sendBody(context.getEndpoint("direct:sql-select"), ExchangePattern.InOut, "empty");
+ }
+
+ @Test
+ public void testSQLDelete() {
+ ProducerTemplate producerTemplate = context.createProducerTemplate();
+ producerTemplate.sendBody(context.getEndpoint("direct:sql-delete"), ExchangePattern.InOut, "empty");
+ }
}
diff --git a/components/camel-jooq/src/test/resources/camel-context.xml b/components/camel-jooq/src/test/resources/camel-context.xml
index 40d5d55..714c390 100644
--- a/components/camel-jooq/src/test/resources/camel-context.xml
+++ b/components/camel-jooq/src/test/resources/camel-context.xml
@@ -70,5 +70,25 @@
<from uri="jooq://org.apache.camel.component.jooq.db.tables.records.BookStoreRecord?consumeDelete=false"/>
<log message="Consumed ${body}"/>
</route>
+
+ <!-- SQL: select -->
+ <route id="sql-select">
+ <from uri="direct:sql-select"/>
+ <to uri="jooq://org.apache.camel.component.jooq.db.tables.records.BookStoreRecord/fetch?query=select * from book_store x where x.name = 'test'"/>
+ <log message="Fetched ${body}"/>
+ </route>
+
+ <!-- SQL: delete -->
+ <route id="sql-delete">
+ <from uri="direct:sql-delete"/>
+ <to uri="jooq://org.apache.camel.component.jooq.db.tables.records.BookStoreRecord/execute?query=delete from book_store x where x.name = 'test'"/>
+ <log message="Fetched ${body}"/>
+ </route>
+
+ <!-- SQL: consume -->
+ <route id="sql-consume">
+ <from uri="jooq://org.apache.camel.component.jooq.db.tables.records.BookStoreRecord?query=select * from book_store x where x.name = 'test'"/>
+ <log message="Fetched ${body}"/>
+ </route>
</camelContext>
</beans>