Updated Branches: refs/heads/master 0d616fbe8 -> 4d521ae86
CAMEL-6366: Added outputType option to camel-sql to dictate what types to return when doing SQL SELECT to make it easier for end users. Thanks to Zemian Deng for the patch. Project: http://git-wip-us.apache.org/repos/asf/camel/repo Commit: http://git-wip-us.apache.org/repos/asf/camel/commit/4d521ae8 Tree: http://git-wip-us.apache.org/repos/asf/camel/tree/4d521ae8 Diff: http://git-wip-us.apache.org/repos/asf/camel/diff/4d521ae8 Branch: refs/heads/master Commit: 4d521ae862e9ace51d4d9e893679a9c37ae0c47d Parents: 0d616fb Author: Claus Ibsen <davscl...@apache.org> Authored: Wed Aug 7 11:16:43 2013 +0200 Committer: Claus Ibsen <davscl...@apache.org> Committed: Wed Aug 7 11:16:43 2013 +0200 ---------------------------------------------------------------------- .../apache/camel/component/sql/SqlConsumer.java | 59 ++++--- .../apache/camel/component/sql/SqlEndpoint.java | 73 ++++++++ .../camel/component/sql/SqlOutputType.java | 22 +++ .../apache/camel/component/sql/SqlProducer.java | 27 ++- .../camel/component/sql/ProjectModel.java | 48 +++++ .../SqlConsumerOutputTypeSelectListTest.java | 91 ++++++++++ .../sql/SqlConsumerOutputTypeSelectOneTest.java | 150 ++++++++++++++++ .../SqlProducerOutputTypeSelectListTest.java | 91 ++++++++++ .../sql/SqlProducerOutputTypeSelectOneTest.java | 175 +++++++++++++++++++ 9 files changed, 704 insertions(+), 32 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/camel/blob/4d521ae8/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 2bb1f4a..57e80e5 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 @@ -33,10 +33,8 @@ import org.apache.camel.spi.UriParam; import org.apache.camel.util.CastUtils; import org.apache.camel.util.ObjectHelper; import org.springframework.dao.DataAccessException; -import org.springframework.jdbc.core.ColumnMapRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.PreparedStatementCallback; -import org.springframework.jdbc.core.RowMapperResultSetExtractor; /** * @@ -100,29 +98,19 @@ public class SqlConsumer extends ScheduledBatchPollingConsumer { log.debug("Executing query: {}", preparedQuery); ResultSet rs = preparedStatement.executeQuery(); + SqlOutputType outputType = getEndpoint().getOutputType(); try { - log.trace("Got result list from query: {}", rs); - - RowMapperResultSetExtractor<Map<String, Object>> mapper = new RowMapperResultSetExtractor<Map<String, Object>>(new ColumnMapRowMapper()); - List<Map<String, Object>> data = mapper.extractData(rs); - - // create a list of exchange objects with the data - if (useIterator) { - for (Map<String, Object> item : data) { - Exchange exchange = createExchange(item); - DataHolder holder = new DataHolder(); - holder.exchange = exchange; - holder.data = item; - answer.add(holder); + log.trace("Got result list from query: {}, outputType={}", rs, outputType); + if (outputType == SqlOutputType.SelectList) { + List<Map<String, Object>> data = getEndpoint().queryForList(rs); + addListToQueue(data, answer); + } else if (outputType == SqlOutputType.SelectOne) { + Object data = getEndpoint().queryForObject(rs); + if (data != null) { + addListToQueue(data, answer); } } else { - if (!data.isEmpty() || routeEmptyResultSet) { - Exchange exchange = createExchange(data); - DataHolder holder = new DataHolder(); - holder.exchange = exchange; - holder.data = data; - answer.add(holder); - } + throw new IllegalArgumentException("Invalid outputType=" + outputType); } } finally { rs.close(); @@ -131,7 +119,7 @@ public class SqlConsumer extends ScheduledBatchPollingConsumer { // process all the exchanges in this batch try { int rows = processBatch(CastUtils.cast(answer)); - return Integer.valueOf(rows); + return rows; } catch (Exception e) { throw ObjectHelper.wrapRuntimeCamelException(e); } @@ -141,6 +129,30 @@ public class SqlConsumer extends ScheduledBatchPollingConsumer { return messagePolled; } + private void addListToQueue(Object data, Queue<DataHolder> answer) { + if (data instanceof List) { + // create a list of exchange objects with the data + List<?> list = (List)data; + if (useIterator) { + for (Object item : list) { + addItemToQueue(item, answer); + } + } else if (!list.isEmpty() || routeEmptyResultSet) { + addItemToQueue(list, answer); + } + } else { + // create single object as data + addItemToQueue(data, answer); + } + } + private void addItemToQueue(Object item, Queue<DataHolder> answer) { + Exchange exchange = createExchange(item); + DataHolder holder = new DataHolder(); + holder.exchange = exchange; + holder.data = item; + answer.add(holder); + } + protected Exchange createExchange(Object data) { final Exchange exchange = getEndpoint().createExchange(ExchangePattern.InOnly); Message msg = exchange.getIn(); @@ -300,4 +312,3 @@ public class SqlConsumer extends ScheduledBatchPollingConsumer { this.breakBatchOnConsumeFail = breakBatchOnConsumeFail; } } - http://git-wip-us.apache.org/repos/asf/camel/blob/4d521ae8/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlEndpoint.java ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlEndpoint.java b/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlEndpoint.java index 300079d..0ee261b 100644 --- a/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlEndpoint.java +++ b/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlEndpoint.java @@ -16,6 +16,12 @@ */ package org.apache.camel.component.sql; +import java.sql.ResultSet; +import java.sql.SQLDataException; +import java.sql.SQLException; +import java.util.List; +import java.util.Map; + import org.apache.camel.Component; import org.apache.camel.Consumer; import org.apache.camel.Processor; @@ -25,7 +31,11 @@ import org.apache.camel.spi.UriEndpoint; import org.apache.camel.spi.UriParam; import org.apache.camel.spi.UriPath; import org.apache.camel.util.UnsafeUriCharactersEncoder; +import org.springframework.jdbc.core.BeanPropertyRowMapper; +import org.springframework.jdbc.core.ColumnMapRowMapper; import org.springframework.jdbc.core.JdbcTemplate; +import org.springframework.jdbc.core.RowMapper; +import org.springframework.jdbc.core.RowMapperResultSetExtractor; /** * SQL Endpoint. Endpoint URI should contain valid SQL statement, but instead of @@ -35,6 +45,7 @@ import org.springframework.jdbc.core.JdbcTemplate; @UriEndpoint(scheme = "sql", consumerClass = SqlConsumer.class) public class SqlEndpoint extends DefaultPollingEndpoint { private JdbcTemplate jdbcTemplate; + @UriPath private String query; @UriParam @@ -57,6 +68,10 @@ public class SqlEndpoint extends DefaultPollingEndpoint { private boolean alwaysPopulateStatement; @UriParam private char separator = ','; + @UriParam + private SqlOutputType outputType = SqlOutputType.SelectList; + @UriParam + private String outputClass; public SqlEndpoint() { } @@ -193,9 +208,67 @@ public class SqlEndpoint extends DefaultPollingEndpoint { this.separator = separator; } + public SqlOutputType getOutputType() { + return outputType; + } + + public void setOutputType(SqlOutputType outputType) { + this.outputType = outputType; + } + + public String getOutputClass() { + return outputClass; + } + + public void setOutputClass(String outputClass) { + this.outputClass = outputClass; + } + @Override protected String createEndpointUri() { // Make sure it's properly encoded return "sql:" + UnsafeUriCharactersEncoder.encode(query); } + + protected List<Map<String, Object>> queryForList(ResultSet rs) throws SQLException { + ColumnMapRowMapper rowMapper = new ColumnMapRowMapper(); + RowMapperResultSetExtractor<Map<String, Object>> mapper = new RowMapperResultSetExtractor<Map<String, Object>>(rowMapper); + List<Map<String, Object>> data = mapper.extractData(rs); + return data; + } + + @SuppressWarnings("unchecked") + protected Object queryForObject(ResultSet rs) throws SQLException { + Object result = null; + if (outputClass == null) { + RowMapper rowMapper = new ColumnMapRowMapper(); + RowMapperResultSetExtractor<Map<String, Object>> mapper = new RowMapperResultSetExtractor<Map<String, Object>>(rowMapper); + List<Map<String, Object>> data = mapper.extractData(rs); + if (data.size() > 1) { + throw new SQLDataException("Query result not unique for outputType=SelectOne. Got " + data.size() + " count instead."); + } else if (data.size() == 1) { + // Set content depend on number of column from query result + Map<String, Object> row = data.get(0); + if (row.size() == 1) { + result = row.values().iterator().next(); + } else { + result = row; + } + } + } else { + Class<?> outputClzz = getCamelContext().getClassResolver().resolveClass(outputClass); + RowMapper rowMapper = new BeanPropertyRowMapper(outputClzz); + RowMapperResultSetExtractor<?> mapper = new RowMapperResultSetExtractor(rowMapper); + List<?> data = mapper.extractData(rs); + if (data.size() > 1) { + throw new SQLDataException("Query result not unique for outputType=SelectOne. Got " + data.size() + " count instead."); + } else if (data.size() == 1) { + result = data.get(0); + } + } + + // If data.size is zero, let result be null. + return result; + } + } http://git-wip-us.apache.org/repos/asf/camel/blob/4d521ae8/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlOutputType.java ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlOutputType.java b/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlOutputType.java new file mode 100644 index 0000000..86a1bf6 --- /dev/null +++ b/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlOutputType.java @@ -0,0 +1,22 @@ +/** + * 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; + +public enum SqlOutputType { + + SelectOne, SelectList +} \ No newline at end of file http://git-wip-us.apache.org/repos/asf/camel/blob/4d521ae8/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 97ff150..cf726a9 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 @@ -17,6 +17,7 @@ package org.apache.camel.component.sql; import java.sql.PreparedStatement; +import java.sql.ResultSet; import java.sql.SQLException; import java.util.Iterator; import java.util.List; @@ -24,10 +25,8 @@ import java.util.Map; import org.apache.camel.Exchange; import org.apache.camel.impl.DefaultProducer; -import org.springframework.jdbc.core.ColumnMapRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.PreparedStatementCallback; -import org.springframework.jdbc.core.RowMapperResultSetExtractor; public class SqlProducer extends DefaultProducer { private String query; @@ -89,12 +88,25 @@ public class SqlProducer extends DefaultProducer { } else { boolean isResultSet = ps.execute(); if (isResultSet) { - RowMapperResultSetExtractor<Map<String, Object>> mapper = new RowMapperResultSetExtractor<Map<String, Object>>(new ColumnMapRowMapper()); - List<Map<String, Object>> result = mapper.extractData(ps.getResultSet()); - exchange.getOut().setBody(result); - exchange.getIn().setHeader(SqlConstants.SQL_ROW_COUNT, result.size()); + ResultSet rs = ps.getResultSet(); + SqlOutputType outputType = getEndpoint().getOutputType(); + log.trace("Got result list from query: {}, outputType={}", rs, outputType); + if (outputType == SqlOutputType.SelectList) { + List<Map<String, Object>> data = getEndpoint().queryForList(ps.getResultSet()); + exchange.getOut().setBody(data); + exchange.getOut().setHeader(SqlConstants.SQL_ROW_COUNT, data.size()); + } else if (outputType == SqlOutputType.SelectOne) { + Object data = getEndpoint().queryForObject(ps.getResultSet()); + if (data != null) { + exchange.getOut().setBody(data); + exchange.getOut().setHeader(SqlConstants.SQL_ROW_COUNT, 1); + } + } else { + throw new IllegalArgumentException("Invalid outputType=" + outputType); + } + // preserve headers - exchange.getOut().setHeaders(exchange.getIn().getHeaders()); + exchange.getOut().getHeaders().putAll(exchange.getIn().getHeaders()); } else { exchange.getIn().setHeader(SqlConstants.SQL_UPDATE_COUNT, ps.getUpdateCount()); } @@ -105,5 +117,4 @@ public class SqlProducer extends DefaultProducer { } }); } - } http://git-wip-us.apache.org/repos/asf/camel/blob/4d521ae8/components/camel-sql/src/test/java/org/apache/camel/component/sql/ProjectModel.java ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/test/java/org/apache/camel/component/sql/ProjectModel.java b/components/camel-sql/src/test/java/org/apache/camel/component/sql/ProjectModel.java new file mode 100644 index 0000000..2160800 --- /dev/null +++ b/components/camel-sql/src/test/java/org/apache/camel/component/sql/ProjectModel.java @@ -0,0 +1,48 @@ +/** + * 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; + +public class ProjectModel { + + int id; + String project; + String license; + + public void setId(int id) { + this.id = id; + } + + public void setProject(String project) { + this.project = project; + } + + public void setLicense(String license) { + this.license = license; + } + + public int getId() { + return id; + } + + public String getProject() { + return project; + } + + public String getLicense() { + return license; + } +} http://git-wip-us.apache.org/repos/asf/camel/blob/4d521ae8/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlConsumerOutputTypeSelectListTest.java ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlConsumerOutputTypeSelectListTest.java b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlConsumerOutputTypeSelectListTest.java new file mode 100644 index 0000000..b5b451c --- /dev/null +++ b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlConsumerOutputTypeSelectListTest.java @@ -0,0 +1,91 @@ +/** + * 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.List; +import java.util.Map; + +import org.apache.camel.Exchange; +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 SqlConsumerOutputTypeSelectListTest extends CamelTestSupport { + + private 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 testOutputType() throws Exception { + MockEndpoint mock = getMockEndpoint("mock:result"); + mock.expectedMinimumMessageCount(3); + + assertMockEndpointsSatisfied(); + + List<Exchange> exchanges = mock.getReceivedExchanges(); + assertTrue(exchanges.size() >= 3); + + Map<String, Object> row = assertIsInstanceOf(Map.class, exchanges.get(0).getIn().getBody()); + assertEquals(1, row.get("ID")); + assertEquals("Camel", row.get("PROJECT")); + assertEquals("ASF", row.get("LICENSE")); + row = assertIsInstanceOf(Map.class, exchanges.get(1).getIn().getBody()); + assertEquals(2, row.get("ID")); + assertEquals("AMQ", row.get("PROJECT")); + assertEquals("ASF", row.get("LICENSE")); + row = assertIsInstanceOf(Map.class, exchanges.get(2).getIn().getBody()); + assertEquals(3, row.get("ID")); + assertEquals("Linux", row.get("PROJECT")); + assertEquals("XXX", row.get("LICENSE")); + } + + @Override + protected RouteBuilder createRouteBuilder() throws Exception { + return new RouteBuilder() { + @Override + public void configure() throws Exception { + getContext().getComponent("sql", SqlComponent.class).setDataSource(db); + + from("sql:select * from projects order by id?outputType=SelectList") + .to("mock:result"); + } + }; + } +} http://git-wip-us.apache.org/repos/asf/camel/blob/4d521ae8/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlConsumerOutputTypeSelectOneTest.java ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlConsumerOutputTypeSelectOneTest.java b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlConsumerOutputTypeSelectOneTest.java new file mode 100644 index 0000000..a74bf2d --- /dev/null +++ b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlConsumerOutputTypeSelectOneTest.java @@ -0,0 +1,150 @@ +/** + * 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.List; +import java.util.Map; + +import org.apache.camel.Exchange; +import org.apache.camel.builder.RouteBuilder; +import org.apache.camel.component.mock.MockEndpoint; +import org.apache.camel.impl.DefaultCamelContext; +import org.hamcrest.CoreMatchers; +import org.junit.After; +import org.junit.Assert; +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 SqlConsumerOutputTypeSelectOneTest { + + private EmbeddedDatabase db; + private DefaultCamelContext camel1; + + @Before + public void setUp() throws Exception { + db = new EmbeddedDatabaseBuilder() + .setType(EmbeddedDatabaseType.DERBY).addScript("sql/createAndPopulateDatabase.sql").build(); + + camel1 = new DefaultCamelContext(); + camel1.setName("camel-1"); + camel1.getComponent("sql", SqlComponent.class).setDataSource(db); + } + + @After + public void tearDown() throws Exception { + camel1.stop(); + db.shutdown(); + } + + @Test + public void testSelectOneWithClass() throws Exception { + camel1.addRoutes(new RouteBuilder() { + @Override + public void configure() throws Exception { + from("sql:select * from projects where id=3?outputType=SelectOne&outputClass=org.apache.camel.component.sql.ProjectModel") + .to("mock:result"); + } + }); + camel1.start(); + + MockEndpoint mock = (MockEndpoint) camel1.getEndpoint("mock:result"); + mock.expectedMessageCount(1); + mock.assertIsSatisfied(2000); + + List<Exchange> exchanges = mock.getReceivedExchanges(); + Assert.assertThat(exchanges.size(), CoreMatchers.is(1)); + + ProjectModel result = exchanges.get(0).getIn().getBody(ProjectModel.class); + Assert.assertThat(result.getId(), CoreMatchers.is(3)); + Assert.assertThat(result.getProject(), CoreMatchers.is("Linux")); + Assert.assertThat(result.getLicense(), CoreMatchers.is("XXX")); + } + + @Test + public void testSelectOneWithoutClass() throws Exception { + camel1.addRoutes(new RouteBuilder() { + @Override + public void configure() throws Exception { + from("sql:select * from projects where id=3?outputType=SelectOne") + .to("mock:result"); + } + }); + camel1.start(); + + MockEndpoint mock = (MockEndpoint) camel1.getEndpoint("mock:result"); + mock.expectedMessageCount(1); + mock.assertIsSatisfied(2000); + + List<Exchange> exchanges = mock.getReceivedExchanges(); + Assert.assertThat(exchanges.size(), CoreMatchers.is(1)); + + Map<String, Object> result = exchanges.get(0).getIn().getBody(Map.class); + Assert.assertThat((Integer) result.get("ID"), CoreMatchers.is(3)); + Assert.assertThat((String) result.get("PROJECT"), CoreMatchers.is("Linux")); + Assert.assertThat((String) result.get("LICENSE"), CoreMatchers.is("XXX")); + } + + @Test + public void testSelectOneSingleColumn() throws Exception { + camel1.addRoutes(new RouteBuilder() { + @Override + public void configure() throws Exception { + from("sql:select project from projects where id=3?outputType=SelectOne") + .to("mock:result"); + } + }); + camel1.start(); + + MockEndpoint mock = (MockEndpoint) camel1.getEndpoint("mock:result"); + mock.expectedMessageCount(1); + mock.assertIsSatisfied(2000); + + List<Exchange> exchanges = mock.getReceivedExchanges(); + Assert.assertThat(exchanges.size(), CoreMatchers.is(1)); + + String result = exchanges.get(0).getIn().getBody(String.class); + Assert.assertThat(result, CoreMatchers.is("Linux")); + } + + @Test + public void testSelectOneSingleColumnCount() throws Exception { + camel1.addRoutes(new RouteBuilder() { + @Override + public void configure() throws Exception { + from("sql:select count(*) from projects?outputType=SelectOne") + .to("mock:result"); + } + }); + camel1.start(); + + MockEndpoint mock = (MockEndpoint) camel1.getEndpoint("mock:result"); + mock.expectedMessageCount(1); + mock.assertIsSatisfied(2000); + + List<Exchange> exchanges = mock.getReceivedExchanges(); + Assert.assertThat(exchanges.size(), CoreMatchers.is(1)); + + Long result = exchanges.get(0).getIn().getBody(Long.class); + Assert.assertThat(result, CoreMatchers.is(3L)); + } +} http://git-wip-us.apache.org/repos/asf/camel/blob/4d521ae8/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerOutputTypeSelectListTest.java ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerOutputTypeSelectListTest.java b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerOutputTypeSelectListTest.java new file mode 100644 index 0000000..71de7d5 --- /dev/null +++ b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerOutputTypeSelectListTest.java @@ -0,0 +1,91 @@ +/** + * 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.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; + +/** + * @version + */ +public class SqlProducerOutputTypeSelectListTest extends CamelTestSupport { + + private 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 testOutputType() throws Exception { + MockEndpoint mock = getMockEndpoint("mock:result"); + mock.expectedMessageCount(1); + + template.sendBody("direct:start", "testmsg"); + + mock.assertIsSatisfied(); + + List<?> received = assertIsInstanceOf(List.class, mock.getReceivedExchanges().get(0).getIn().getBody()); + assertEquals(3, received.size()); + Map<String, Object> row = assertIsInstanceOf(Map.class, received.get(0)); + assertEquals(1, row.get("ID")); + assertEquals("Camel", row.get("PROJECT")); + assertEquals("ASF", row.get("LICENSE")); + row = assertIsInstanceOf(Map.class, received.get(1)); + assertEquals(2, row.get("ID")); + assertEquals("AMQ", row.get("PROJECT")); + assertEquals("ASF", row.get("LICENSE")); + row = assertIsInstanceOf(Map.class, received.get(2)); + assertEquals(3, row.get("ID")); + assertEquals("Linux", row.get("PROJECT")); + assertEquals("XXX", row.get("LICENSE")); + } + + @Override + protected RouteBuilder createRouteBuilder() throws Exception { + return new RouteBuilder() { + public void configure() { + getContext().getComponent("sql", SqlComponent.class).setDataSource(db); + + from("direct:start") + .to("sql:select * from projects order by id?outputType=SelectList") + .to("mock:result"); + } + }; + } +} http://git-wip-us.apache.org/repos/asf/camel/blob/4d521ae8/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerOutputTypeSelectOneTest.java ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerOutputTypeSelectOneTest.java b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerOutputTypeSelectOneTest.java new file mode 100644 index 0000000..ee733b4 --- /dev/null +++ b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerOutputTypeSelectOneTest.java @@ -0,0 +1,175 @@ +/** + * 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.List; +import java.util.Map; + +import org.apache.camel.Exchange; +import org.apache.camel.ProducerTemplate; +import org.apache.camel.builder.RouteBuilder; +import org.apache.camel.component.mock.MockEndpoint; +import org.apache.camel.impl.DefaultCamelContext; +import org.hamcrest.CoreMatchers; +import org.junit.After; +import org.junit.Assert; +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; + +/** + * @version + */ +public class SqlProducerOutputTypeSelectOneTest { + + private EmbeddedDatabase db; + private DefaultCamelContext camel1; + + @Before + public void setUp() throws Exception { + db = new EmbeddedDatabaseBuilder() + .setType(EmbeddedDatabaseType.DERBY).addScript("sql/createAndPopulateDatabase.sql").build(); + + camel1 = new DefaultCamelContext(); + camel1.setName("camel-1"); + camel1.getComponent("sql", SqlComponent.class).setDataSource(db); + } + + @After + public void tearDown() throws Exception { + camel1.stop(); + db.shutdown(); + } + + @Test + public void testSelectOneWithClass() throws Exception { + camel1.addRoutes(new RouteBuilder() { + @Override + public void configure() throws Exception { + from("direct:start") + .to("sql:select * from projects where id=3?outputType=SelectOne&outputClass=org.apache.camel.component.sql.ProjectModel") + .to("mock:result"); + } + }); + camel1.start(); + + ProducerTemplate template = camel1.createProducerTemplate(); + + MockEndpoint mock = (MockEndpoint) camel1.getEndpoint("mock:result"); + mock.expectedMessageCount(1); + + template.sendBody("direct:start", "testmsg"); + + mock.assertIsSatisfied(2000); + + List<Exchange> exchanges = mock.getReceivedExchanges(); + Assert.assertThat(exchanges.size(), CoreMatchers.is(1)); + + ProjectModel result = exchanges.get(0).getIn().getBody(ProjectModel.class); + Assert.assertThat(result.getId(), CoreMatchers.is(3)); + Assert.assertThat(result.getProject(), CoreMatchers.is("Linux")); + Assert.assertThat(result.getLicense(), CoreMatchers.is("XXX")); + } + + @Test + public void testSelectOneWithoutClass() throws Exception { + camel1.addRoutes(new RouteBuilder() { + @Override + public void configure() throws Exception { + from("direct:start") + .to("sql:select * from projects where id=3?outputType=SelectOne") + .to("mock:result"); + } + }); + camel1.start(); + + ProducerTemplate template = camel1.createProducerTemplate(); + + MockEndpoint mock = (MockEndpoint) camel1.getEndpoint("mock:result"); + mock.expectedMessageCount(1); + + template.sendBody("direct:start", "testmsg"); + + mock.assertIsSatisfied(2000); + + List<Exchange> exchanges = mock.getReceivedExchanges(); + Assert.assertThat(exchanges.size(), CoreMatchers.is(1)); + + Map<String, Object> result = exchanges.get(0).getIn().getBody(Map.class); + Assert.assertThat((Integer) result.get("ID"), CoreMatchers.is(3)); + Assert.assertThat((String) result.get("PROJECT"), CoreMatchers.is("Linux")); + Assert.assertThat((String) result.get("LICENSE"), CoreMatchers.is("XXX")); + } + + @Test + public void testSelectOneSingleColumn() throws Exception { + camel1.addRoutes(new RouteBuilder() { + @Override + public void configure() throws Exception { + from("direct:start") + .to("sql:select project from projects where id=3?outputType=SelectOne") + .to("mock:result"); + } + }); + camel1.start(); + + ProducerTemplate template = camel1.createProducerTemplate(); + + MockEndpoint mock = (MockEndpoint) camel1.getEndpoint("mock:result"); + mock.expectedMessageCount(1); + + template.sendBody("direct:start", "testmsg"); + + mock.assertIsSatisfied(2000); + + List<Exchange> exchanges = mock.getReceivedExchanges(); + Assert.assertThat(exchanges.size(), CoreMatchers.is(1)); + + String result = exchanges.get(0).getIn().getBody(String.class); + Assert.assertThat(result, CoreMatchers.is("Linux")); + } + + @Test + public void testSelectOneSingleColumnCount() throws Exception { + camel1.addRoutes(new RouteBuilder() { + @Override + public void configure() throws Exception { + from("direct:start") + .to("sql:select count(*) from projects?outputType=SelectOne") + .to("mock:result"); + } + }); + camel1.start(); + + ProducerTemplate template = camel1.createProducerTemplate(); + + MockEndpoint mock = (MockEndpoint) camel1.getEndpoint("mock:result"); + mock.expectedMessageCount(1); + + template.sendBody("direct:start", "testmsg"); + + mock.assertIsSatisfied(2000); + + List<Exchange> exchanges = mock.getReceivedExchanges(); + Assert.assertThat(exchanges.size(), CoreMatchers.is(1)); + + Long result = exchanges.get(0).getIn().getBody(Long.class); + Assert.assertThat(result, CoreMatchers.is(3L)); + } +}
