Repository: camel Updated Branches: refs/heads/camel-2.18.x 7c89c38fd -> 4cc5bc065 refs/heads/master d3954d6c2 -> cdd35f0f5
CAMEL-10499 camel-sql - error in multiple dynamic IN replacement Project: http://git-wip-us.apache.org/repos/asf/camel/repo Commit: http://git-wip-us.apache.org/repos/asf/camel/commit/cdd35f0f Tree: http://git-wip-us.apache.org/repos/asf/camel/tree/cdd35f0f Diff: http://git-wip-us.apache.org/repos/asf/camel/diff/cdd35f0f Branch: refs/heads/master Commit: cdd35f0f538d70d03a752a790d80038782f7fbf4 Parents: d3954d6 Author: Tomohisa Igarashi <[email protected]> Authored: Mon Nov 21 19:56:19 2016 +0900 Committer: Claus Ibsen <[email protected]> Committed: Mon Nov 21 12:21:17 2016 +0100 ---------------------------------------------------------------------- .../sql/DefaultSqlPrepareStatementStrategy.java | 4 +- .../sql/SqlProducerInMultiExpressionTest.java | 38 ++++ .../SqlProducerInMultiQueryEndpointTest.java | 38 ++++ .../component/sql/SqlProducerInMultiTest.java | 172 +++++++++++++++++++ .../sql/createAndPopulateDatabase6.sql | 29 ++++ .../resources/sql/selectProjectsInMulti.sql | 6 + .../sql/selectProjectsInMultiExpression.sql | 6 + 7 files changed, 292 insertions(+), 1 deletion(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/camel/blob/cdd35f0f/components/camel-sql/src/main/java/org/apache/camel/component/sql/DefaultSqlPrepareStatementStrategy.java ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/main/java/org/apache/camel/component/sql/DefaultSqlPrepareStatementStrategy.java b/components/camel-sql/src/main/java/org/apache/camel/component/sql/DefaultSqlPrepareStatementStrategy.java index 755ef21..72f8ac3 100644 --- a/components/camel-sql/src/main/java/org/apache/camel/component/sql/DefaultSqlPrepareStatementStrategy.java +++ b/components/camel-sql/src/main/java/org/apache/camel/component/sql/DefaultSqlPrepareStatementStrategy.java @@ -75,7 +75,9 @@ public class DefaultSqlPrepareStatementStrategy implements SqlPrepareStatementSt csb.append("\\?"); } String replace = csb.toString(); - query = matcher.replaceAll(replace); + String foundEscaped = found.replace("$", "\\$").replace("{", "\\{").replace("}", "\\}"); + Matcher paramMatcher = Pattern.compile("\\:\\?in\\:" + foundEscaped, Pattern.MULTILINE).matcher(query); + query = paramMatcher.replaceAll(replace); } } } http://git-wip-us.apache.org/repos/asf/camel/blob/cdd35f0f/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerInMultiExpressionTest.java ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerInMultiExpressionTest.java b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerInMultiExpressionTest.java new file mode 100644 index 0000000..ecf9b04 --- /dev/null +++ b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerInMultiExpressionTest.java @@ -0,0 +1,38 @@ +/** + * 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.sql; + +import org.apache.camel.builder.RouteBuilder; + +public class SqlProducerInMultiExpressionTest extends SqlProducerInMultiTest { + + @Override + protected RouteBuilder createRouteBuilder() throws Exception { + return new RouteBuilder() { + @Override + public void configure() throws Exception { + // required for the sql component + getContext().getComponent("sql", SqlComponent.class).setDataSource(db); + + from("direct:query") + .to("sql:classpath:sql/selectProjectsInMultiExpression.sql") + .to("log:query") + .to("mock:query"); + } + }; + } +} http://git-wip-us.apache.org/repos/asf/camel/blob/cdd35f0f/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerInMultiQueryEndpointTest.java ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerInMultiQueryEndpointTest.java b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerInMultiQueryEndpointTest.java new file mode 100644 index 0000000..710c815 --- /dev/null +++ b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerInMultiQueryEndpointTest.java @@ -0,0 +1,38 @@ +/** + * 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.sql; + +import org.apache.camel.builder.RouteBuilder; + +public class SqlProducerInMultiQueryEndpointTest extends SqlProducerInMultiTest { + + @Override + protected RouteBuilder createRouteBuilder() throws Exception { + return new RouteBuilder() { + @Override + public void configure() throws Exception { + // required for the sql component + getContext().getComponent("sql", SqlComponent.class).setDataSource(db); + + from("direct:query") + .to("sql:select * from projects where project in (:#in:names) and license in (:#in:licenses) order by id") + .to("log:query") + .to("mock:query"); + } + }; + } +} http://git-wip-us.apache.org/repos/asf/camel/blob/cdd35f0f/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerInMultiTest.java ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerInMultiTest.java b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerInMultiTest.java new file mode 100644 index 0000000..88098e4 --- /dev/null +++ b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerInMultiTest.java @@ -0,0 +1,172 @@ +/** + * 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.sql; + +import java.util.ArrayList; +import java.util.Arrays; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + +import org.apache.camel.builder.RouteBuilder; +import org.apache.camel.component.mock.MockEndpoint; +import org.apache.camel.test.junit4.CamelTestSupport; +import org.junit.After; +import org.junit.Before; +import org.junit.Test; +import org.springframework.jdbc.datasource.embedded.EmbeddedDatabase; +import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder; +import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType; + +public class SqlProducerInMultiTest extends CamelTestSupport { + + EmbeddedDatabase db; + + @Before + public void setUp() throws Exception { + db = new EmbeddedDatabaseBuilder() + .setType(EmbeddedDatabaseType.DERBY).addScript("sql/createAndPopulateDatabase6.sql").build(); + + super.setUp(); + } + + @After + public void tearDown() throws Exception { + super.tearDown(); + + db.shutdown(); + } + + @Test + public void testQueryInArray() throws InterruptedException { + MockEndpoint mock = getMockEndpoint("mock:query"); + mock.expectedMessageCount(1); + + Map<String, Object> headers = new HashMap<>(); + headers.put("names", new String[]{"Camel", "AMQ"}); + headers.put("licenses", new String[]{"ASF", "XXX", "YYY"}); + template.requestBodyAndHeaders("direct:query", "Hi there!", headers); + + assertMockEndpointsSatisfied(); + + List list = mock.getReceivedExchanges().get(0).getIn().getBody(List.class); + assertEquals(6, list.size()); + Map row = (Map) list.get(0); + assertEquals("Camel", row.get("PROJECT")); + assertEquals("ASF", row.get("license")); + row = (Map) list.get(1); + assertEquals("Camel", row.get("PROJECT")); + assertEquals("XXX", row.get("license")); + row = (Map) list.get(2); + assertEquals("Camel", row.get("PROJECT")); + assertEquals("YYY", row.get("license")); + row = (Map) list.get(3); + assertEquals("AMQ", row.get("PROJECT")); + assertEquals("ASF", row.get("license")); + row = (Map) list.get(4); + assertEquals("AMQ", row.get("PROJECT")); + assertEquals("XXX", row.get("license")); + row = (Map) list.get(5); + assertEquals("AMQ", row.get("PROJECT")); + assertEquals("YYY", row.get("license")); + } + + @Test + public void testQueryInList() throws InterruptedException { + MockEndpoint mock = getMockEndpoint("mock:query"); + mock.expectedMessageCount(1); + + Map<String, Object> headers = new HashMap<>(); + headers.put("names", Arrays.asList("Camel", "AMQ")); + headers.put("licenses", Arrays.asList("ASF", "XXX", "YYY")); + + template.requestBodyAndHeaders("direct:query", "Hi there!", headers); + + assertMockEndpointsSatisfied(); + + List list = mock.getReceivedExchanges().get(0).getIn().getBody(List.class); + assertEquals(6, list.size()); + Map row = (Map) list.get(0); + assertEquals("Camel", row.get("PROJECT")); + assertEquals("ASF", row.get("license")); + row = (Map) list.get(1); + assertEquals("Camel", row.get("PROJECT")); + assertEquals("XXX", row.get("license")); + row = (Map) list.get(2); + assertEquals("Camel", row.get("PROJECT")); + assertEquals("YYY", row.get("license")); + row = (Map) list.get(3); + assertEquals("AMQ", row.get("PROJECT")); + assertEquals("ASF", row.get("license")); + row = (Map) list.get(4); + assertEquals("AMQ", row.get("PROJECT")); + assertEquals("XXX", row.get("license")); + row = (Map) list.get(5); + assertEquals("AMQ", row.get("PROJECT")); + assertEquals("YYY", row.get("license")); + } + + @Test + public void testQueryInString() throws InterruptedException { + MockEndpoint mock = getMockEndpoint("mock:query"); + mock.expectedMessageCount(1); + + HashMap<String, Object> headers = new HashMap<>(); + headers.put("names", "Camel,AMQ"); + headers.put("licenses", "ASF,XXX,YYY"); + template.requestBodyAndHeaders("direct:query", "Hi there!", headers); + + assertMockEndpointsSatisfied(); + + List list = mock.getReceivedExchanges().get(0).getIn().getBody(List.class); + assertEquals(6, list.size()); + Map row = (Map) list.get(0); + assertEquals("Camel", row.get("PROJECT")); + assertEquals("ASF", row.get("license")); + row = (Map) list.get(1); + assertEquals("Camel", row.get("PROJECT")); + assertEquals("XXX", row.get("license")); + row = (Map) list.get(2); + assertEquals("Camel", row.get("PROJECT")); + assertEquals("YYY", row.get("license")); + row = (Map) list.get(3); + assertEquals("AMQ", row.get("PROJECT")); + assertEquals("ASF", row.get("license")); + row = (Map) list.get(4); + assertEquals("AMQ", row.get("PROJECT")); + assertEquals("XXX", row.get("license")); + row = (Map) list.get(5); + assertEquals("AMQ", row.get("PROJECT")); + assertEquals("YYY", row.get("license")); + } + + @Override + protected RouteBuilder createRouteBuilder() throws Exception { + return new RouteBuilder() { + @Override + public void configure() throws Exception { + // required for the sql component + getContext().getComponent("sql", SqlComponent.class).setDataSource(db); + + from("direct:query") + .to("sql:classpath:sql/selectProjectsInMulti.sql") + .to("log:query") + .to("mock:query"); + } + }; + } +} http://git-wip-us.apache.org/repos/asf/camel/blob/cdd35f0f/components/camel-sql/src/test/resources/sql/createAndPopulateDatabase6.sql ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/test/resources/sql/createAndPopulateDatabase6.sql b/components/camel-sql/src/test/resources/sql/createAndPopulateDatabase6.sql new file mode 100644 index 0000000..de487b7 --- /dev/null +++ b/components/camel-sql/src/test/resources/sql/createAndPopulateDatabase6.sql @@ -0,0 +1,29 @@ +-- ------------------------------------------------------------------------ +-- 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. +-- ------------------------------------------------------------------------ + +-- START SNIPPET: e1 +create table projects (id integer primary key, project varchar(10), license varchar(5)); +insert into projects values (1, 'Camel', 'ASF'); +insert into projects values (2, 'Camel', 'XXX'); +insert into projects values (3, 'Camel', 'YYY'); +insert into projects values (4, 'Camel', 'ZZZ'); +insert into projects values (5, 'AMQ', 'ASF'); +insert into projects values (6, 'AMQ', 'XXX'); +insert into projects values (7, 'AMQ', 'YYY'); +insert into projects values (8, 'AMQ', 'ZZZ'); +insert into projects values (9, 'Linux', 'XXX'); +-- END SNIPPET: e1 \ No newline at end of file http://git-wip-us.apache.org/repos/asf/camel/blob/cdd35f0f/components/camel-sql/src/test/resources/sql/selectProjectsInMulti.sql ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/test/resources/sql/selectProjectsInMulti.sql b/components/camel-sql/src/test/resources/sql/selectProjectsInMulti.sql new file mode 100644 index 0000000..df2a46d --- /dev/null +++ b/components/camel-sql/src/test/resources/sql/selectProjectsInMulti.sql @@ -0,0 +1,6 @@ +-- this is a comment +select * +from projects +where project in (:#in:names) +and license in (:#in:licenses) +order by id \ No newline at end of file http://git-wip-us.apache.org/repos/asf/camel/blob/cdd35f0f/components/camel-sql/src/test/resources/sql/selectProjectsInMultiExpression.sql ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/test/resources/sql/selectProjectsInMultiExpression.sql b/components/camel-sql/src/test/resources/sql/selectProjectsInMultiExpression.sql new file mode 100644 index 0000000..99d20be --- /dev/null +++ b/components/camel-sql/src/test/resources/sql/selectProjectsInMultiExpression.sql @@ -0,0 +1,6 @@ +-- this is a comment +select * +from projects +where project in (:#in:${header.names}) +and license in (:#in:${header.licenses}) +order by id \ No newline at end of file
