Repository: camel Updated Branches: refs/heads/camel-2.12.x 18ac8cd60 -> 88b77a363 refs/heads/camel-2.13.x 39744db54 -> f0fbc8430
[CAMEL-7313] Adding support to retrieve generated keys for camel-sql Project: http://git-wip-us.apache.org/repos/asf/camel/repo Commit: http://git-wip-us.apache.org/repos/asf/camel/commit/6919c57c Tree: http://git-wip-us.apache.org/repos/asf/camel/tree/6919c57c Diff: http://git-wip-us.apache.org/repos/asf/camel/diff/6919c57c Branch: refs/heads/camel-2.13.x Commit: 6919c57c7cdfeb8def9b429a64668183544b7d31 Parents: 39744db Author: Grzegorz Grzybek <gr.grzy...@gmail.com> Authored: Fri Mar 28 21:44:35 2014 +0100 Committer: Willem Jiang <willem.ji...@gmail.com> Committed: Mon Mar 31 14:16:43 2014 +0800 ---------------------------------------------------------------------- components/camel-sql/pom.xml | 5 + .../camel/component/sql/SqlConstants.java | 25 ++ .../apache/camel/component/sql/SqlProducer.java | 48 +++- .../component/sql/SqlGeneratedKeysTest.java | 261 +++++++++++++++++++ .../sql/createAndPopulateDatabase3.sql | 27 ++ parent/pom.xml | 7 + 6 files changed, 371 insertions(+), 2 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/camel/blob/6919c57c/components/camel-sql/pom.xml ---------------------------------------------------------------------- diff --git a/components/camel-sql/pom.xml b/components/camel-sql/pom.xml index 3d12c4c..8b48e23 100644 --- a/components/camel-sql/pom.xml +++ b/components/camel-sql/pom.xml @@ -81,6 +81,11 @@ <artifactId>derby</artifactId> <scope>test</scope> </dependency> + <dependency> + <groupId>org.hsqldb</groupId> + <artifactId>hsqldb</artifactId> + <scope>test</scope> + </dependency> </dependencies> <build> http://git-wip-us.apache.org/repos/asf/camel/blob/6919c57c/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlConstants.java ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlConstants.java b/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlConstants.java index aef93a2..e2fef78 100644 --- a/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlConstants.java +++ b/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlConstants.java @@ -27,6 +27,31 @@ public final class SqlConstants { public static final String SQL_ROW_COUNT = "CamelSqlRowCount"; + /** + * Boolean input header. + * Set its value to true to retrieve generated keys, default is false + */ + public static final String SQL_RETRIEVE_GENERATED_KEYS = "CamelSqlRetrieveGeneratedKeys"; + + /** + * <tt>String[]</tt> or <tt>int[]</tt> input header - optional + * Set it to specify the expected generated columns, see: + * + * @see <a href="http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#execute(java.lang.String, int[])"> + * java.sql.Statement.execute(java.lang.String, int[])</a> + * @see <a href="http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#execute(java.lang.String, java.lang.String[])"> + * java.sql.Statement.execute(java.lang.String, java.lang.String[])</a> + */ + public static final String SQL_GENERATED_COLUMNS = "CamelGeneratedColumns"; + + /** + * <tt>org.springframework.jdbc.support.KeyHolder</tt> output header containing the generated keys retrieved + * + * @see <a href="http://docs.spring.io/spring/docs/3.2.x/javadoc-api/org/springframework/jdbc/support/KeyHolder.html";> + * org.springframework.jdbc.support.KeyHolder</a> + */ + public static final String SQL_GENERATED_KEY_HOLDER = "CamelSqlGeneratedKeyHolder"; + private SqlConstants() { // Utility class } http://git-wip-us.apache.org/repos/asf/camel/blob/6919c57c/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 870e7b4..2df7941 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 @@ -16,9 +16,11 @@ */ package org.apache.camel.component.sql; +import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; +import java.sql.Statement; import java.util.Iterator; import java.util.List; import java.util.Map; @@ -27,6 +29,9 @@ import org.apache.camel.Exchange; import org.apache.camel.impl.DefaultProducer; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.PreparedStatementCallback; +import org.springframework.jdbc.core.PreparedStatementCreator; +import org.springframework.jdbc.support.GeneratedKeyHolder; +import org.springframework.jdbc.support.KeyHolder; public class SqlProducer extends DefaultProducer { private String query; @@ -57,7 +62,33 @@ public class SqlProducer extends DefaultProducer { final String sql = queryHeader != null ? queryHeader : query; final String preparedQuery = sqlPrepareStatementStrategy.prepareQuery(sql, getEndpoint().isAllowNamedParameters()); - jdbcTemplate.execute(preparedQuery, new PreparedStatementCallback<Map<?, ?>>() { + // CAMEL-7313 - check whether to return generated keys + final Boolean shouldRetrieveGeneratedKeys = + exchange.getIn().getHeader(SqlConstants.SQL_RETRIEVE_GENERATED_KEYS, false, Boolean.class); + + PreparedStatementCreator statementCreator = new PreparedStatementCreator() { + @Override + public PreparedStatement createPreparedStatement(Connection con) throws SQLException { + if (!shouldRetrieveGeneratedKeys) { + return con.prepareStatement(preparedQuery); + } else { + Object expectedGeneratedColumns = exchange.getIn().getHeader(SqlConstants.SQL_GENERATED_COLUMNS); + if (expectedGeneratedColumns == null) { + return con.prepareStatement(preparedQuery, Statement.RETURN_GENERATED_KEYS); + } else if (expectedGeneratedColumns instanceof String[]) { + return con.prepareStatement(preparedQuery, (String[]) expectedGeneratedColumns); + } else if (expectedGeneratedColumns instanceof int[]) { + return con.prepareStatement(preparedQuery, (int[]) expectedGeneratedColumns); + } else { + throw new IllegalArgumentException( + "Header specifying expected returning columns isn't an instance of String[] or int[] but " + + expectedGeneratedColumns.getClass()); + } + } + } + }; + + jdbcTemplate.execute(statementCreator, new PreparedStatementCallback<Map<?, ?>>() { public Map<?, ?> doInPreparedStatement(PreparedStatement ps) throws SQLException { int expected = parametersCount > 0 ? parametersCount : ps.getParameterMetaData().getParameterCount(); @@ -78,6 +109,8 @@ public class SqlProducer extends DefaultProducer { } } + boolean isResultSet = false; + // execute the prepared statement and populate the outgoing message if (batch) { int[] updateCounts = ps.executeBatch(); @@ -87,7 +120,7 @@ public class SqlProducer extends DefaultProducer { } exchange.getIn().setHeader(SqlConstants.SQL_UPDATE_COUNT, total); } else { - boolean isResultSet = ps.execute(); + isResultSet = ps.execute(); if (isResultSet) { // preserve headers first, so we can override the SQL_ROW_COUNT header exchange.getOut().getHeaders().putAll(exchange.getIn().getHeaders()); @@ -123,6 +156,17 @@ public class SqlProducer extends DefaultProducer { } } + if (shouldRetrieveGeneratedKeys) { + if (isResultSet) { + // we won't return generated keys for SELECT statements + exchange.getOut().setHeader(SqlConstants.SQL_GENERATED_KEY_HOLDER, new GeneratedKeyHolder()); + } else { + List<Map<String, Object>> generatedKeys = getEndpoint().queryForList(ps.getGeneratedKeys()); + KeyHolder holder = new GeneratedKeyHolder(generatedKeys); + exchange.getOut().setHeader(SqlConstants.SQL_GENERATED_KEY_HOLDER, holder); + } + } + // data is set on exchange so return null return null; } http://git-wip-us.apache.org/repos/asf/camel/blob/6919c57c/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlGeneratedKeysTest.java ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlGeneratedKeysTest.java b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlGeneratedKeysTest.java new file mode 100644 index 0000000..ce3326e --- /dev/null +++ b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlGeneratedKeysTest.java @@ -0,0 +1,261 @@ +/** + * 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.Endpoint; +import org.apache.camel.Exchange; +import org.apache.camel.builder.RouteBuilder; +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; +import org.springframework.jdbc.support.KeyHolder; + + +public class SqlGeneratedKeysTest extends CamelTestSupport { + + private EmbeddedDatabase db; + + @Before + public void setUp() throws Exception { + // Only HSQLDB seem to handle: + // - more than one generated column in row + // - return all keys generated in batch insert + db = new EmbeddedDatabaseBuilder() + .setType(EmbeddedDatabaseType.HSQL).addScript("sql/createAndPopulateDatabase3.sql").build(); + + super.setUp(); + } + + @After + public void tearDown() throws Exception { + super.tearDown(); + + db.shutdown(); + } + + @Override + protected RouteBuilder createRouteBuilder() throws Exception { + return new RouteBuilder() { + @Override + public void configure() throws Exception { + getContext().getComponent("sql", SqlComponent.class).setDataSource(db); + from("direct:insert").to("sql:insert into projects (project, license, description) values (#, #, #)").to("mock:result"); + from("direct:batch").to("sql:insert into projects (project, license, description) values (#, #, #)?batch=true").to("mock:result"); + from("direct:select").to("sql:select * from projects order by id asc").to("mock:result"); + from("direct:insert2").to("sql:insert into developers (name, position) values (#, #)").to("mock:result"); + } + }; + } + + @Test + public void testRetrieveGeneratedKey() throws Exception { + // first we create our exchange using the endpoint + Endpoint endpoint = context.getEndpoint("direct:insert"); + + Exchange exchange = endpoint.createExchange(); + exchange.getIn().setBody(new Object[] {"project x", "ASF", "new project"}); + exchange.getIn().setHeader(SqlConstants.SQL_RETRIEVE_GENERATED_KEYS, true); + + // now we send the exchange to the endpoint, and receives the response from Camel + Exchange out = template.send(endpoint, exchange); + + // assertions of the response + assertNotNull(out); + assertNotNull(out.getOut()); + assertNotNull(out.getIn().getHeader(SqlConstants.SQL_GENERATED_KEY_HOLDER)); + + KeyHolder generatedKeys = out.getIn().getHeader(SqlConstants.SQL_GENERATED_KEY_HOLDER, KeyHolder.class); + assertNotNull("out body could not be converted to a KeyHolder - was: " + + out.getIn().getBody(), generatedKeys); + assertEquals(1, generatedKeys.getKeyList().get(0).size()); + + Map<String, Object> row = generatedKeys.getKeys(); + assertEquals("auto increment value should be 3", Integer.valueOf(3), row.get("ID")); + + assertEquals("generated keys row count should be one", 1, generatedKeys.getKeyList().size()); + } + + @Test + public void testRetrieveGeneratedKeys() throws Exception { + // first we create our exchange using the endpoint + Endpoint endpoint = context.getEndpoint("direct:insert2"); + + Exchange exchange = endpoint.createExchange(); + exchange.getIn().setBody(new Object[] {"Star Swirl", "Wizard"}); + exchange.getIn().setHeader(SqlConstants.SQL_RETRIEVE_GENERATED_KEYS, true); + exchange.getIn().setHeader(SqlConstants.SQL_GENERATED_COLUMNS, new String[]{"ID1", "ID2"}); + + // now we send the exchange to the endpoint, and receives the response from Camel + Exchange out = template.send(endpoint, exchange); + + // assertions of the response + assertNotNull(out); + assertNotNull(out.getOut()); + assertNotNull(out.getIn().getHeader(SqlConstants.SQL_GENERATED_KEY_HOLDER)); + + KeyHolder generatedKeys = out.getIn().getHeader(SqlConstants.SQL_GENERATED_KEY_HOLDER, KeyHolder.class); + assertNotNull("out body could not be converted to a KeyHolder - was: " + + out.getIn().getBody(), generatedKeys); + assertEquals(2, generatedKeys.getKeyList().get(0).size()); + + Map<String, Object> row = generatedKeys.getKeys(); + assertEquals("auto increment value of ID1 should be 5", Integer.valueOf(5), row.get("ID1")); + assertEquals("auto increment value of ID2 should be 6", Integer.valueOf(6), row.get("ID2")); + + assertEquals("generated keys row count should be one", 1, generatedKeys.getKeyList().size()); + } + + @Test + public void testRetrieveGeneratedKeysForBatch() throws Exception { + // first we create our exchange using the endpoint + Endpoint endpoint = context.getEndpoint("direct:batch"); + + Exchange exchange = endpoint.createExchange(); + List<Object[]> payload = new ArrayList<Object[]>(4); + payload.add(new Object[] {"project x", "ASF", "new project x"}); + payload.add(new Object[] {"project y", "ASF", "new project y"}); + payload.add(new Object[] {"project z", "ASF", "new project z"}); + payload.add(new Object[] {"project q", "ASF", "new project q"}); + exchange.getIn().setBody(payload); + exchange.getIn().setHeader(SqlConstants.SQL_RETRIEVE_GENERATED_KEYS, true); + + // now we send the exchange to the endpoint, and receives the response from Camel + Exchange out = template.send(endpoint, exchange); + + // assertions of the response + assertNotNull(out); + assertNotNull(out.getOut()); + assertNotNull(out.getIn().getHeader(SqlConstants.SQL_GENERATED_KEY_HOLDER)); + + KeyHolder generatedKeys = out.getIn().getHeader(SqlConstants.SQL_GENERATED_KEY_HOLDER, KeyHolder.class); + assertNotNull("out body could not be converted to a KeyHolder - was: " + + out.getIn().getBody(), generatedKeys); + + // it seems not to work with Derby... + assertEquals(4, generatedKeys.getKeyList().size()); + + int id = 3; + for (Map<String, Object> row: generatedKeys.getKeyList()) { + assertEquals("auto increment value should be " + id, Integer.valueOf(id++), row.get("ID")); + } + + } + + @Test + public void testRetrieveGeneratedKeyWithStringGeneratedColumns() throws Exception { + // first we create our exchange using the endpoint + Endpoint endpoint = context.getEndpoint("direct:insert"); + + Exchange exchange = endpoint.createExchange(); + exchange.getIn().setBody(new Object[] {"project x", "ASF", "new project"}); + exchange.getIn().setHeader(SqlConstants.SQL_RETRIEVE_GENERATED_KEYS, true); + exchange.getIn().setHeader(SqlConstants.SQL_GENERATED_COLUMNS, new String[]{"ID"}); + + // now we send the exchange to the endpoint, and receives the response from Camel + Exchange out = template.send(endpoint, exchange); + + // assertions of the response + assertNotNull(out); + assertNotNull(out.getOut()); + assertNotNull(out.getIn().getHeader(SqlConstants.SQL_GENERATED_KEY_HOLDER)); + + KeyHolder generatedKeys = out.getIn().getHeader(SqlConstants.SQL_GENERATED_KEY_HOLDER, KeyHolder.class); + assertNotNull("out body could not be converted to a KeyHolder - was: " + + out.getIn().getBody(), generatedKeys); + assertEquals(1, generatedKeys.getKeyList().get(0).size()); + + Map<String, Object> row = generatedKeys.getKeys(); + assertEquals("auto increment value should be 3", Integer.valueOf(3), row.get("ID")); + + assertEquals("generated keys row count should be one", 1, generatedKeys.getKeyList().size()); + } + + @Test + public void testRetrieveGeneratedKeyWithIntGeneratedColumns() throws Exception { + // first we create our exchange using the endpoint + Endpoint endpoint = context.getEndpoint("direct:insert"); + + Exchange exchange = endpoint.createExchange(); + exchange.getIn().setBody(new Object[] {"project x", "ASF", "new project"}); + exchange.getIn().setHeader(SqlConstants.SQL_RETRIEVE_GENERATED_KEYS, true); + exchange.getIn().setHeader(SqlConstants.SQL_GENERATED_COLUMNS, new int[]{1}); + + // now we send the exchange to the endpoint, and receives the response from Camel + Exchange out = template.send(endpoint, exchange); + + // assertions of the response + assertNotNull(out); + assertNotNull(out.getIn().getHeader(SqlConstants.SQL_GENERATED_KEY_HOLDER)); + + KeyHolder generatedKeys = out.getIn().getHeader(SqlConstants.SQL_GENERATED_KEY_HOLDER, KeyHolder.class); + assertNotNull("out body could not be converted to a KeyHolder - was: " + + out.getIn().getBody(), generatedKeys); + assertEquals(1, generatedKeys.getKeyList().get(0).size()); + + Map<String, Object> row = generatedKeys.getKeys(); + assertEquals("auto increment value should be 3", Integer.valueOf(3), row.get("ID")); + + assertEquals("generated keys row count should be one", 1, generatedKeys.getKeyList().size()); + } + + @Test + public void testGivenAnInvalidGeneratedColumnsHeaderThenAnExceptionIsThrown() throws Exception { + // first we create our exchange using the endpoint + Endpoint endpoint = context.getEndpoint("direct:insert"); + + Exchange exchange = endpoint.createExchange(); + // then we set the SQL on the in body + exchange.getIn().setBody(new Object[] {"project x", "ASF", "new project"}); + exchange.getIn().setHeader(SqlConstants.SQL_RETRIEVE_GENERATED_KEYS, true); + + // set wrong data type for generated columns + exchange.getIn().setHeader(SqlConstants.SQL_GENERATED_COLUMNS, new Object[]{}); + + // now we send the exchange to the endpoint, and receives the response from Camel + template.send(endpoint, exchange); + + assertTrue(exchange.isFailed()); + } + + @Test + public void testNoKeysForSelect() throws Exception { + // first we create our exchange using the endpoint + Endpoint endpoint = context.getEndpoint("direct:select"); + + Exchange exchange = endpoint.createExchange(); + // then we set the SQL on the in body + exchange.getIn().setHeader(SqlConstants.SQL_RETRIEVE_GENERATED_KEYS, true); + + // now we send the exchange to the endpoint, and receives the response from Camel + Exchange out = template.send(endpoint, exchange); + + @SuppressWarnings("unchecked") + List<Map<String, Object>> result = out.getIn().getBody(List.class); + assertEquals("We should get 3 projects", 3, result.size()); + + KeyHolder holder = out.getIn().getHeader(SqlConstants.SQL_GENERATED_KEY_HOLDER, KeyHolder.class); + assertEquals("We should not get any keys", 0, holder.getKeyList().size()); + } +} http://git-wip-us.apache.org/repos/asf/camel/blob/6919c57c/components/camel-sql/src/test/resources/sql/createAndPopulateDatabase3.sql ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/test/resources/sql/createAndPopulateDatabase3.sql b/components/camel-sql/src/test/resources/sql/createAndPopulateDatabase3.sql new file mode 100644 index 0000000..d26fd27 --- /dev/null +++ b/components/camel-sql/src/test/resources/sql/createAndPopulateDatabase3.sql @@ -0,0 +1,27 @@ +-- ------------------------------------------------------------------------ +-- 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 GENERATED ALWAYS AS IDENTITY, project varchar(10), license varchar(5), description varchar(1000) default null); +insert into projects (project, license, description) values ('Camel', 'ASF', ''); +insert into projects (project, license, description) values ('AMQ', 'ASF', ''); +insert into projects (project, license, description) values ('Linux', 'XXX', ''); +-- END SNIPPET: e1 + +-- START SNIPPET: e2 +create table developers (id1 integer primary key GENERATED ALWAYS AS IDENTITY (START WITH 5), name varchar(20), position varchar(20), id2 integer GENERATED ALWAYS AS (id1+1)); +-- END SNIPPET: e2 \ No newline at end of file http://git-wip-us.apache.org/repos/asf/camel/blob/6919c57c/parent/pom.xml ---------------------------------------------------------------------- diff --git a/parent/pom.xml b/parent/pom.xml index eacf016..b7fa009 100644 --- a/parent/pom.xml +++ b/parent/pom.xml @@ -179,6 +179,7 @@ <hbase-bundle-version>0.94.6_1</hbase-bundle-version> <hibernate-validator-version>5.0.3.Final</hibernate-validator-version> <hibernate-version>4.2.9.Final</hibernate-version> + <hsqldb-version>2.3.2</hsqldb-version> <httpunit-version>1.7</httpunit-version> <httpcore4-version>4.3.2</httpcore4-version> <httpclient4-version>4.3.3</httpclient4-version> @@ -1930,6 +1931,12 @@ <artifactId>derby</artifactId> <version>${derby-version}</version> </dependency> + <dependency> + <groupId>org.hsqldb</groupId> + <artifactId>hsqldb</artifactId> + <version>${hsqldb-version}</version> + <scope>test</scope> + </dependency> <!-- optional SNMP4J --> <dependency>
