Repository: camel Updated Branches: refs/heads/master 924d1f2f9 -> 17fd2363a
CAMEL-9711: camel-sql - Add support for SQL IN in producer with dynamic values Project: http://git-wip-us.apache.org/repos/asf/camel/repo Commit: http://git-wip-us.apache.org/repos/asf/camel/commit/17fd2363 Tree: http://git-wip-us.apache.org/repos/asf/camel/tree/17fd2363 Diff: http://git-wip-us.apache.org/repos/asf/camel/diff/17fd2363 Branch: refs/heads/master Commit: 17fd2363a45141fc73ec459de42f4df66998e8e3 Parents: 924d1f2 Author: Claus Ibsen <davscl...@apache.org> Authored: Fri Mar 18 10:22:39 2016 +0100 Committer: Claus Ibsen <davscl...@apache.org> Committed: Fri Mar 18 11:05:29 2016 +0100 ---------------------------------------------------------------------- .../elsql/ElsqlSqlPrepareStatementStrategy.java | 2 +- .../sql/DefaultSqlPrepareStatementStrategy.java | 133 +++++++++++++++---- .../sql/DefaultSqlProcessingStrategy.java | 4 +- .../apache/camel/component/sql/SqlConsumer.java | 2 +- .../camel/component/sql/SqlInIterator.java | 56 ++++++++ .../sql/SqlPrepareStatementStrategy.java | 3 +- .../apache/camel/component/sql/SqlProducer.java | 2 +- .../sql/SqlProducerInExpressionTest.java | 38 ++++++ .../sql/SqlProducerInQueryEndpointTest.java | 38 ++++++ .../camel/component/sql/SqlProducerInTest.java | 122 +++++++++++++++++ .../src/test/resources/sql/selectProjectsIn.sql | 5 + .../sql/selectProjectsInExpression.sql | 5 + 12 files changed, 379 insertions(+), 31 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/camel/blob/17fd2363/components/camel-elsql/src/main/java/org/apache/camel/component/elsql/ElsqlSqlPrepareStatementStrategy.java ---------------------------------------------------------------------- diff --git a/components/camel-elsql/src/main/java/org/apache/camel/component/elsql/ElsqlSqlPrepareStatementStrategy.java b/components/camel-elsql/src/main/java/org/apache/camel/component/elsql/ElsqlSqlPrepareStatementStrategy.java index d2ef797..0b998f2 100644 --- a/components/camel-elsql/src/main/java/org/apache/camel/component/elsql/ElsqlSqlPrepareStatementStrategy.java +++ b/components/camel-elsql/src/main/java/org/apache/camel/component/elsql/ElsqlSqlPrepareStatementStrategy.java @@ -25,7 +25,7 @@ import org.apache.camel.component.sql.SqlPrepareStatementStrategy; public class ElsqlSqlPrepareStatementStrategy implements SqlPrepareStatementStrategy { - public String prepareQuery(String query, boolean allowNamedParameters) throws SQLException { + public String prepareQuery(String query, boolean allowNamedParameters, Exchange exchange) throws SQLException { return query; } http://git-wip-us.apache.org/repos/asf/camel/blob/17fd2363/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 bed55d1..7e91e72 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 @@ -30,6 +30,8 @@ import java.util.regex.Pattern; import org.apache.camel.Exchange; import org.apache.camel.RuntimeExchangeException; import org.apache.camel.language.simple.SimpleLanguage; +import org.apache.camel.util.CollectionStringBuffer; +import org.apache.camel.util.ObjectHelper; import org.apache.camel.util.StringQuoteHelper; import org.slf4j.Logger; import org.slf4j.LoggerFactory; @@ -41,7 +43,9 @@ import org.springframework.jdbc.core.ArgumentPreparedStatementSetter; public class DefaultSqlPrepareStatementStrategy implements SqlPrepareStatementStrategy { private static final Logger LOG = LoggerFactory.getLogger(DefaultSqlPrepareStatementStrategy.class); + private static final Pattern REPLACE_IN_PATTERN = Pattern.compile("\\:\\?in\\:(\\w+|\\$\\{[^\\}]+\\})", Pattern.MULTILINE); private static final Pattern REPLACE_PATTERN = Pattern.compile("\\:\\?\\w+|\\:\\?\\$\\{[^\\}]+\\}", Pattern.MULTILINE); + private static final Pattern NAME_IN_PATTERN = Pattern.compile("\\:\\?(in\\:(\\w+|\\$\\{[^\\}]+\\}))", Pattern.MULTILINE); private static final Pattern NAME_PATTERN = Pattern.compile("\\:\\?(\\w+|\\$\\{[^\\}]+\\})", Pattern.MULTILINE); private final char separator; @@ -54,9 +58,27 @@ public class DefaultSqlPrepareStatementStrategy implements SqlPrepareStatementSt } @Override - public String prepareQuery(String query, boolean allowNamedParameters) throws SQLException { + public String prepareQuery(String query, boolean allowNamedParameters, final Exchange exchange) throws SQLException { String answer; if (allowNamedParameters && hasNamedParameters(query)) { + if (exchange != null) { + // replace all :?in:word with a number of placeholders for how many values are expected in the IN values + Matcher matcher = REPLACE_IN_PATTERN.matcher(query); + while (matcher.find()) { + String found = matcher.group(1); + Object parameter = lookupParameter(found, exchange, exchange.getIn().getBody()); + if (parameter != null) { + Iterator it = createInParameterIterator(parameter); + CollectionStringBuffer csb = new CollectionStringBuffer(","); + while (it.hasNext()) { + it.next(); + csb.append("\\?"); + } + String replace = csb.toString(); + query = matcher.replaceAll(replace); + } + } + } // replace all :?word and :?${foo} with just ? answer = REPLACE_PATTERN.matcher(query).replaceAll("\\?"); } else { @@ -104,12 +126,27 @@ public class DefaultSqlPrepareStatementStrategy implements SqlPrepareStatementSt while (iterator != null && iterator.hasNext()) { Object value = iterator.next(); - LOG.trace("Setting parameter #{} with value: {}", argNumber, value); - if (argNumber <= expectedParams) { - args[i] = value; + + // special for SQL IN where we need to set dynamic number of values + if (value instanceof SqlInIterator) { + Iterator it = (Iterator) value; + while (it.hasNext()) { + Object val = it.next(); + LOG.trace("Setting parameter #{} with value: {}", argNumber, val); + if (argNumber <= expectedParams) { + args[i] = val; + } + argNumber++; + i++; + } + } else { + LOG.trace("Setting parameter #{} with value: {}", argNumber, value); + if (argNumber <= expectedParams) { + args[i] = value; + } + argNumber++; + i++; } - argNumber++; - i++; } if (argNumber - 1 != expectedParams) { throw new SQLException("Number of parameters mismatch. Expected: " + expectedParams + ", was: " + (argNumber - 1)); @@ -127,19 +164,63 @@ public class DefaultSqlPrepareStatementStrategy implements SqlPrepareStatementSt private static final class NamedQueryParser { + private final Matcher inMatcher; private final Matcher matcher; + // which mode are we in + private boolean in; + private boolean name; private NamedQueryParser(String query) { + this.inMatcher = NAME_IN_PATTERN.matcher(query); this.matcher = NAME_PATTERN.matcher(query); } public String next() { - if (!matcher.find()) { - return null; + if (!name && inMatcher.find()) { + // turn on in mode, so we only match using in matcher next time + in = true; + return inMatcher.group(1); + } else if (!in && matcher.find()) { + // turn on name mode, so we only match using name matcher next time + name = true; + return matcher.group(1); } - return matcher.group(1); + return null; + } + } + + protected static Object lookupParameter(String nextParam, Exchange exchange, Object body) { + Map<?, ?> bodyMap = safeMap(exchange.getContext().getTypeConverter().tryConvertTo(Map.class, body)); + Map<?, ?> headersMap = safeMap(exchange.getIn().getHeaders()); + + Object answer = null; + if (nextParam.startsWith("${") && nextParam.endsWith("}")) { + answer = SimpleLanguage.expression(nextParam).evaluate(exchange, Object.class); + } else if (bodyMap.containsKey(nextParam)) { + answer = bodyMap.get(nextParam); + } else if (headersMap.containsKey(nextParam)) { + answer = headersMap.get(nextParam); + } + + return answer; + } + + private static Map<?, ?> safeMap(Map<?, ?> map) { + return (map == null || map.isEmpty()) ? Collections.emptyMap() : map; + } + + protected static Iterator createInParameterIterator(Object value) { + Iterator it; + // if the body is a String then honor quotes etc. + if (value instanceof String) { + String[] tokens = StringQuoteHelper.splitSafeQuote((String) value, ',', true); + List<String> list = Arrays.asList(tokens); + it = list.iterator(); + } else { + it = ObjectHelper.createIterator(value, null); } + return new SqlInIterator(it); } private static final class PopulateIterator implements Iterator<Object> { @@ -148,17 +229,14 @@ public class DefaultSqlPrepareStatementStrategy implements SqlPrepareStatementSt private final String query; private final NamedQueryParser parser; private final Exchange exchange; - private final Map<?, ?> bodyMap; - private final Map<?, ?> headersMap; + private final Object body; private String nextParam; private PopulateIterator(String query, Exchange exchange, Object body) { this.query = query; - this.parser = new NamedQueryParser(query); this.exchange = exchange; - this.bodyMap = safeMap(exchange.getContext().getTypeConverter().tryConvertTo(Map.class, body)); - this.headersMap = safeMap(exchange.getIn().getHeaders()); - + this.body = body; + this.parser = new NamedQueryParser(query); this.nextParam = parser.next(); } @@ -173,18 +251,26 @@ public class DefaultSqlPrepareStatementStrategy implements SqlPrepareStatementSt throw new NoSuchElementException(); } + boolean in = false; + if (nextParam.startsWith("in:")) { + in = true; + nextParam = nextParam.substring(3); + } + + Object next = null; try { - if (nextParam.startsWith("${") && nextParam.endsWith("}")) { - return SimpleLanguage.expression(nextParam).evaluate(exchange, Object.class); - } else if (bodyMap.containsKey(nextParam)) { - return bodyMap.get(nextParam); - } else if (headersMap.containsKey(nextParam)) { - return headersMap.get(nextParam); + next = lookupParameter(nextParam, exchange, body); + if (in && next != null) { + next = createInParameterIterator(next); + } + if (next == null) { + throw new RuntimeExchangeException(String.format(MISSING_PARAMETER_EXCEPTION, nextParam, query), exchange); } - throw new RuntimeExchangeException(String.format(MISSING_PARAMETER_EXCEPTION, nextParam, query), exchange); } finally { nextParam = parser.next(); } + + return next; } @Override @@ -192,8 +278,5 @@ public class DefaultSqlPrepareStatementStrategy implements SqlPrepareStatementSt throw new UnsupportedOperationException(); } - private static Map<?, ?> safeMap(Map<?, ?> map) { - return (map == null || map.isEmpty()) ? Collections.emptyMap() : map; - } } } http://git-wip-us.apache.org/repos/asf/camel/blob/17fd2363/components/camel-sql/src/main/java/org/apache/camel/component/sql/DefaultSqlProcessingStrategy.java ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/main/java/org/apache/camel/component/sql/DefaultSqlProcessingStrategy.java b/components/camel-sql/src/main/java/org/apache/camel/component/sql/DefaultSqlProcessingStrategy.java index 1d7f96d..281bba1 100644 --- a/components/camel-sql/src/main/java/org/apache/camel/component/sql/DefaultSqlProcessingStrategy.java +++ b/components/camel-sql/src/main/java/org/apache/camel/component/sql/DefaultSqlProcessingStrategy.java @@ -41,7 +41,7 @@ public class DefaultSqlProcessingStrategy implements SqlProcessingStrategy { @Override public int commit(final DefaultSqlEndpoint endpoint, final Exchange exchange, final Object data, final JdbcTemplate jdbcTemplate, final String query) throws Exception { - final String preparedQuery = sqlPrepareStatementStrategy.prepareQuery(query, endpoint.isAllowNamedParameters()); + final String preparedQuery = sqlPrepareStatementStrategy.prepareQuery(query, endpoint.isAllowNamedParameters(), exchange); return jdbcTemplate.execute(preparedQuery, new PreparedStatementCallback<Integer>() { public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException { @@ -67,7 +67,7 @@ public class DefaultSqlProcessingStrategy implements SqlProcessingStrategy { @Override public int commitBatchComplete(final DefaultSqlEndpoint endpoint, final JdbcTemplate jdbcTemplate, final String query) throws Exception { - final String preparedQuery = sqlPrepareStatementStrategy.prepareQuery(query, endpoint.isAllowNamedParameters()); + final String preparedQuery = sqlPrepareStatementStrategy.prepareQuery(query, endpoint.isAllowNamedParameters(), null); return jdbcTemplate.execute(preparedQuery, new PreparedStatementCallback<Integer>() { public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException { http://git-wip-us.apache.org/repos/asf/camel/blob/17fd2363/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlConsumer.java ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlConsumer.java b/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlConsumer.java index a9bc8ea..74bad30 100644 --- a/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlConsumer.java +++ b/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlConsumer.java @@ -106,7 +106,7 @@ public class SqlConsumer extends ScheduledBatchPollingConsumer { shutdownRunningTask = null; pendingExchanges = 0; - final String preparedQuery = sqlPrepareStatementStrategy.prepareQuery(resolvedQuery, getEndpoint().isAllowNamedParameters()); + final String preparedQuery = sqlPrepareStatementStrategy.prepareQuery(resolvedQuery, getEndpoint().isAllowNamedParameters(), null); final PreparedStatementCallback<Integer> callback = new PreparedStatementCallback<Integer>() { @Override public Integer doInPreparedStatement(PreparedStatement preparedStatement) throws SQLException, DataAccessException { http://git-wip-us.apache.org/repos/asf/camel/blob/17fd2363/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlInIterator.java ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlInIterator.java b/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlInIterator.java new file mode 100644 index 0000000..32136bb --- /dev/null +++ b/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlInIterator.java @@ -0,0 +1,56 @@ +/** + * 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.Iterator; +import java.util.function.Consumer; + +/** + * Iterator used for SQL IN query. + * <p/> + * This ensures we know the parameters is an IN parameter and the values are dynamic and must be + * set using this iterator. + */ +public class SqlInIterator implements Iterator { + + private final Iterator it; + + public SqlInIterator(Iterator it) { + this.it = it; + } + + @Override + public boolean hasNext() { + return it.hasNext(); + } + + @Override + public Object next() { + return it.next(); + } + + @Override + public void remove() { + it.remove(); + } + + @Override + @SuppressWarnings("unchecked") + public void forEachRemaining(Consumer action) { + it.forEachRemaining(action); + } +} http://git-wip-us.apache.org/repos/asf/camel/blob/17fd2363/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlPrepareStatementStrategy.java ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlPrepareStatementStrategy.java b/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlPrepareStatementStrategy.java index 8e52157..07cda80 100644 --- a/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlPrepareStatementStrategy.java +++ b/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlPrepareStatementStrategy.java @@ -32,9 +32,10 @@ public interface SqlPrepareStatementStrategy { * * @param query the query which may contain named query parameters * @param allowNamedParameters whether named parameters is allowed + * @param exchange the current exchange * @return the query to actually use, which must be accepted by the JDBC driver. */ - String prepareQuery(String query, boolean allowNamedParameters) throws SQLException; + String prepareQuery(String query, boolean allowNamedParameters, Exchange exchange) throws SQLException; /** * Creates the iterator to use when setting query parameters on the prepared statement. http://git-wip-us.apache.org/repos/asf/camel/blob/17fd2363/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlProducer.java ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlProducer.java b/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlProducer.java index 3d70acd..6e17cd0 100644 --- a/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlProducer.java +++ b/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlProducer.java @@ -76,7 +76,7 @@ public class SqlProducer extends DefaultProducer { String queryHeader = exchange.getIn().getHeader(SqlConstants.SQL_QUERY, String.class); sql = queryHeader != null ? queryHeader : resolvedQuery; } - final String preparedQuery = sqlPrepareStatementStrategy.prepareQuery(sql, getEndpoint().isAllowNamedParameters()); + final String preparedQuery = sqlPrepareStatementStrategy.prepareQuery(sql, getEndpoint().isAllowNamedParameters(), exchange); // CAMEL-7313 - check whether to return generated keys final Boolean shouldRetrieveGeneratedKeys = http://git-wip-us.apache.org/repos/asf/camel/blob/17fd2363/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerInExpressionTest.java ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerInExpressionTest.java b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerInExpressionTest.java new file mode 100644 index 0000000..6fd0e94 --- /dev/null +++ b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerInExpressionTest.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 SqlProducerInExpressionTest extends SqlProducerInTest { + + @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/selectProjectsInExpression.sql") + .to("log:query") + .to("mock:query"); + } + }; + } +} http://git-wip-us.apache.org/repos/asf/camel/blob/17fd2363/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerInQueryEndpointTest.java ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerInQueryEndpointTest.java b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerInQueryEndpointTest.java new file mode 100644 index 0000000..78b0021 --- /dev/null +++ b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerInQueryEndpointTest.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 SqlProducerInQueryEndpointTest extends SqlProducerInTest { + + @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) order by id") + .to("log:query") + .to("mock:query"); + } + }; + } +} http://git-wip-us.apache.org/repos/asf/camel/blob/17fd2363/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerInTest.java ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerInTest.java b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerInTest.java new file mode 100644 index 0000000..401a35f --- /dev/null +++ b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerInTest.java @@ -0,0 +1,122 @@ +/** + * 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.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 SqlProducerInTest extends CamelTestSupport { + + EmbeddedDatabase db; + + @Before + public void setUp() throws Exception { + db = new EmbeddedDatabaseBuilder() + .setType(EmbeddedDatabaseType.DERBY).addScript("sql/createAndPopulateDatabase.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); + + template.requestBodyAndHeader("direct:query", "Hi there!", "names", new String[]{"Camel", "AMQ"}); + + assertMockEndpointsSatisfied(); + + List list = mock.getReceivedExchanges().get(0).getIn().getBody(List.class); + assertEquals(2, list.size()); + Map row = (Map) list.get(0); + assertEquals("Camel", row.get("PROJECT")); + row = (Map) list.get(1); + assertEquals("AMQ", row.get("PROJECT")); + } + + @Test + public void testQueryInList() throws InterruptedException { + MockEndpoint mock = getMockEndpoint("mock:query"); + mock.expectedMessageCount(1); + + List<String> names = new ArrayList<String>(); + names.add("Camel"); + names.add("AMQ"); + + template.requestBodyAndHeader("direct:query", "Hi there!", "names", names); + + assertMockEndpointsSatisfied(); + + List list = mock.getReceivedExchanges().get(0).getIn().getBody(List.class); + assertEquals(2, list.size()); + Map row = (Map) list.get(0); + assertEquals("Camel", row.get("PROJECT")); + row = (Map) list.get(1); + assertEquals("AMQ", row.get("PROJECT")); + } + + @Test + public void testQueryInString() throws InterruptedException { + MockEndpoint mock = getMockEndpoint("mock:query"); + mock.expectedMessageCount(1); + + template.requestBodyAndHeader("direct:query", "Hi there!", "names", "Camel,AMQ"); + + assertMockEndpointsSatisfied(); + + List list = mock.getReceivedExchanges().get(0).getIn().getBody(List.class); + assertEquals(2, list.size()); + Map row = (Map) list.get(0); + assertEquals("Camel", row.get("PROJECT")); + row = (Map) list.get(1); + assertEquals("AMQ", row.get("PROJECT")); + } + + @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/selectProjectsIn.sql") + .to("log:query") + .to("mock:query"); + } + }; + } +} http://git-wip-us.apache.org/repos/asf/camel/blob/17fd2363/components/camel-sql/src/test/resources/sql/selectProjectsIn.sql ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/test/resources/sql/selectProjectsIn.sql b/components/camel-sql/src/test/resources/sql/selectProjectsIn.sql new file mode 100644 index 0000000..202bac5 --- /dev/null +++ b/components/camel-sql/src/test/resources/sql/selectProjectsIn.sql @@ -0,0 +1,5 @@ +-- this is a comment +select * +from projects +where project in (:#in:names) +order by id \ No newline at end of file http://git-wip-us.apache.org/repos/asf/camel/blob/17fd2363/components/camel-sql/src/test/resources/sql/selectProjectsInExpression.sql ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/test/resources/sql/selectProjectsInExpression.sql b/components/camel-sql/src/test/resources/sql/selectProjectsInExpression.sql new file mode 100644 index 0000000..1a35d9b --- /dev/null +++ b/components/camel-sql/src/test/resources/sql/selectProjectsInExpression.sql @@ -0,0 +1,5 @@ +-- this is a comment +select * +from projects +where project in (:#in:${header.names}) +order by id \ No newline at end of file
