Repository: camel Updated Branches: refs/heads/master 1cc93e5f6 -> f7fe07b5b
[CAMEL-7382] Enable retrieving auto generated keys in JDBC component when using named parameters Thanks Pascal Klink 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/1985d995 Tree: http://git-wip-us.apache.org/repos/asf/camel/tree/1985d995 Diff: http://git-wip-us.apache.org/repos/asf/camel/diff/1985d995 Branch: refs/heads/master Commit: 1985d995f96be383270859aa9a22399635939891 Parents: 1cc93e5 Author: Grzegorz Grzybek <gr.grzy...@gmail.com> Authored: Tue Apr 22 13:16:26 2014 +0200 Committer: Willem Jiang <willem.ji...@gmail.com> Committed: Thu Apr 24 14:34:23 2014 +0800 ---------------------------------------------------------------------- .../camel/component/jdbc/JdbcProducer.java | 26 ++- .../jdbc/AbstractJdbcGeneratedKeysTest.java | 157 +++++++++++++++++++ .../component/jdbc/JdbcGeneratedKeysTest.java | 122 +++----------- ...JdbcParameterizedQueryGeneratedKeysTest.java | 63 ++++++++ 4 files changed, 263 insertions(+), 105 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/camel/blob/1985d995/components/camel-jdbc/src/main/java/org/apache/camel/component/jdbc/JdbcProducer.java ---------------------------------------------------------------------- diff --git a/components/camel-jdbc/src/main/java/org/apache/camel/component/jdbc/JdbcProducer.java b/components/camel-jdbc/src/main/java/org/apache/camel/component/jdbc/JdbcProducer.java index d573587..15850e8 100644 --- a/components/camel-jdbc/src/main/java/org/apache/camel/component/jdbc/JdbcProducer.java +++ b/components/camel-jdbc/src/main/java/org/apache/camel/component/jdbc/JdbcProducer.java @@ -125,7 +125,27 @@ public class JdbcProducer extends DefaultProducer { try { final String preparedQuery = getEndpoint().getPrepareStatementStrategy().prepareQuery(sql, getEndpoint().isAllowNamedParameters()); - ps = conn.prepareStatement(preparedQuery); + + Boolean shouldRetrieveGeneratedKeys = + exchange.getIn().getHeader(JdbcConstants.JDBC_RETRIEVE_GENERATED_KEYS, false, Boolean.class); + + if (shouldRetrieveGeneratedKeys) { + Object expectedGeneratedColumns = exchange.getIn().getHeader(JdbcConstants.JDBC_GENERATED_COLUMNS); + if (expectedGeneratedColumns == null) { + ps = conn.prepareStatement(preparedQuery, Statement.RETURN_GENERATED_KEYS); + } else if (expectedGeneratedColumns instanceof String[]) { + ps = conn.prepareStatement(preparedQuery, (String[]) expectedGeneratedColumns); + } else if (expectedGeneratedColumns instanceof int[]) { + ps = conn.prepareStatement(preparedQuery, (int[]) expectedGeneratedColumns); + } else { + throw new IllegalArgumentException( + "Header specifying expected returning columns isn't an instance of String[] or int[] but " + + expectedGeneratedColumns.getClass()); + } + } else { + ps = conn.prepareStatement(preparedQuery); + } + int expectedCount = ps.getParameterMetaData().getParameterCount(); if (expectedCount > 0) { @@ -143,6 +163,10 @@ public class JdbcProducer extends DefaultProducer { int updateCount = ps.getUpdateCount(); exchange.getOut().setHeader(JdbcConstants.JDBC_UPDATE_COUNT, updateCount); } + + if (shouldRetrieveGeneratedKeys) { + setGeneratedKeys(exchange, ps.getGeneratedKeys()); + } } finally { closeQuietly(rs); closeQuietly(ps); http://git-wip-us.apache.org/repos/asf/camel/blob/1985d995/components/camel-jdbc/src/test/java/org/apache/camel/component/jdbc/AbstractJdbcGeneratedKeysTest.java ---------------------------------------------------------------------- diff --git a/components/camel-jdbc/src/test/java/org/apache/camel/component/jdbc/AbstractJdbcGeneratedKeysTest.java b/components/camel-jdbc/src/test/java/org/apache/camel/component/jdbc/AbstractJdbcGeneratedKeysTest.java new file mode 100644 index 0000000..791dd59 --- /dev/null +++ b/components/camel-jdbc/src/test/java/org/apache/camel/component/jdbc/AbstractJdbcGeneratedKeysTest.java @@ -0,0 +1,157 @@ +package org.apache.camel.component.jdbc; + +import java.math.BigDecimal; +import java.util.List; +import java.util.Map; + +import org.apache.camel.Endpoint; +import org.apache.camel.Exchange; +import org.apache.camel.builder.RouteBuilder; + +public abstract class AbstractJdbcGeneratedKeysTest extends AbstractJdbcTestSupport { + + @SuppressWarnings("unchecked") + protected void testRetrieveGeneratedKeys(String query, Map<String, Object> parameters) throws Exception { + // first we create our exchange using the endpoint + Endpoint endpoint = context.getEndpoint("direct:hello"); + + Exchange exchange = endpoint.createExchange(); + // then we set the SQL on the in body and add possible parameters + exchange.getIn().setBody(query); + exchange.getIn().setHeader(JdbcConstants.JDBC_RETRIEVE_GENERATED_KEYS, true); + setHeaders(exchange, parameters); + + // 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.getOut().getHeader(JdbcConstants.JDBC_GENERATED_KEYS_DATA)); + assertNotNull(out.getOut().getHeader(JdbcConstants.JDBC_GENERATED_KEYS_ROW_COUNT)); + + List<Map<String, Object>> generatedKeys = out.getOut().getHeader(JdbcConstants.JDBC_GENERATED_KEYS_DATA, List.class); + assertNotNull("out body could not be converted to an ArrayList - was: " + + out.getOut().getBody(), generatedKeys); + assertEquals(1, generatedKeys.size()); + + Map<String, Object> row = generatedKeys.get(0); + assertEquals("auto increment value should be 2", BigDecimal.valueOf(2), row.get("1")); + + assertEquals("generated keys row count should be one", 1, out.getOut().getHeader(JdbcConstants.JDBC_GENERATED_KEYS_ROW_COUNT)); + } + + protected void testRetrieveGeneratedKeys(String query) throws Exception { + testRetrieveGeneratedKeys(query, null); + } + + @SuppressWarnings("unchecked") + protected void testRetrieveGeneratedKeysWithStringGeneratedColumns(String query, + Map<String, Object> parameters) throws Exception { + // first we create our exchange using the endpoint + Endpoint endpoint = context.getEndpoint("direct:hello"); + + Exchange exchange = endpoint.createExchange(); + // then we set the SQL on the in body and add possible parameters + exchange.getIn().setBody(query); + exchange.getIn().setHeader(JdbcConstants.JDBC_RETRIEVE_GENERATED_KEYS, true); + exchange.getIn().setHeader(JdbcConstants.JDBC_GENERATED_COLUMNS, new String[]{"ID"}); + setHeaders(exchange, parameters); + + // 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.getOut().getHeader(JdbcConstants.JDBC_GENERATED_KEYS_DATA)); + assertNotNull(out.getOut().getHeader(JdbcConstants.JDBC_GENERATED_KEYS_ROW_COUNT)); + + List<Map<String, Object>> generatedKeys = out.getOut().getHeader(JdbcConstants.JDBC_GENERATED_KEYS_DATA, List.class); + assertNotNull("out body could not be converted to an ArrayList - was: " + + out.getOut().getBody(), generatedKeys); + assertEquals(1, generatedKeys.size()); + + Map<String, Object> row = generatedKeys.get(0); + assertEquals("auto increment value should be 2", BigDecimal.valueOf(2), row.get("1")); + + assertEquals("generated keys row count should be one", 1, out.getOut().getHeader(JdbcConstants.JDBC_GENERATED_KEYS_ROW_COUNT)); + } + + protected void testRetrieveGeneratedKeysWithStringGeneratedColumns(String query) throws Exception { + testRetrieveGeneratedKeysWithStringGeneratedColumns(query, null); + } + + @SuppressWarnings("unchecked") + protected void testRetrieveGeneratedKeysWithIntGeneratedColumns(String query, + Map<String, Object> parameters) throws Exception { + // first we create our exchange using the endpoint + Endpoint endpoint = context.getEndpoint("direct:hello"); + + Exchange exchange = endpoint.createExchange(); + // then we set the SQL on the in body and add possible parameters + exchange.getIn().setBody(query); + exchange.getIn().setHeader(JdbcConstants.JDBC_RETRIEVE_GENERATED_KEYS, true); + exchange.getIn().setHeader(JdbcConstants.JDBC_GENERATED_COLUMNS, new int[]{1}); + setHeaders(exchange, parameters); + + // 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.getOut().getHeader(JdbcConstants.JDBC_GENERATED_KEYS_DATA)); + assertNotNull(out.getOut().getHeader(JdbcConstants.JDBC_GENERATED_KEYS_ROW_COUNT)); + + List<Map<String, Object>> generatedKeys = out.getOut().getHeader(JdbcConstants.JDBC_GENERATED_KEYS_DATA, List.class); + assertNotNull("out body could not be converted to an ArrayList - was: " + + out.getOut().getBody(), generatedKeys); + assertEquals(1, generatedKeys.size()); + + Map<String, Object> row = generatedKeys.get(0); + assertEquals("auto increment value should be 2", BigDecimal.valueOf(2), row.get("1")); + + assertEquals("generated keys row count should be one", 1, out.getOut().getHeader(JdbcConstants.JDBC_GENERATED_KEYS_ROW_COUNT)); + } + + protected void testRetrieveGeneratedKeysWithIntGeneratedColumns(String query) throws Exception { + testRetrieveGeneratedKeysWithIntGeneratedColumns(query, null); + } + + protected void testGivenAnInvalidGeneratedColumnsHeaderThenAnExceptionIsThrown(String query, + Map<String, Object> parameters) throws Exception { + // first we create our exchange using the endpoint + Endpoint endpoint = context.getEndpoint("direct:hello"); + + Exchange exchange = endpoint.createExchange(); + // then we set the SQL on the in body and add possible parameters + exchange.getIn().setBody(query); + exchange.getIn().setHeader(JdbcConstants.JDBC_RETRIEVE_GENERATED_KEYS, true); + setHeaders(exchange, parameters); + + // set wrong data type for generated columns + exchange.getIn().setHeader(JdbcConstants.JDBC_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()); + } + + protected void testGivenAnInvalidGeneratedColumnsHeaderThenAnExceptionIsThrown(String query) throws Exception { + testGivenAnInvalidGeneratedColumnsHeaderThenAnExceptionIsThrown(query, null); + } + + private void setHeaders(Exchange exchange, Map<String, Object> parameters) { + if (parameters != null) { + for (Map.Entry<String, Object> parameter : parameters.entrySet()) { + exchange.getIn().setHeader(parameter.getKey(), parameter.getValue()); + } + } + } + + @Override + protected abstract RouteBuilder createRouteBuilder() throws Exception; + +} http://git-wip-us.apache.org/repos/asf/camel/blob/1985d995/components/camel-jdbc/src/test/java/org/apache/camel/component/jdbc/JdbcGeneratedKeysTest.java ---------------------------------------------------------------------- diff --git a/components/camel-jdbc/src/test/java/org/apache/camel/component/jdbc/JdbcGeneratedKeysTest.java b/components/camel-jdbc/src/test/java/org/apache/camel/component/jdbc/JdbcGeneratedKeysTest.java index b4d20b3..435a00b 100644 --- a/components/camel-jdbc/src/test/java/org/apache/camel/component/jdbc/JdbcGeneratedKeysTest.java +++ b/components/camel-jdbc/src/test/java/org/apache/camel/component/jdbc/JdbcGeneratedKeysTest.java @@ -16,128 +16,42 @@ */ package org.apache.camel.component.jdbc; -import java.math.BigDecimal; -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.junit.Test; -public class JdbcGeneratedKeysTest extends JdbcRouteTest { +public class JdbcGeneratedKeysTest extends AbstractJdbcGeneratedKeysTest { @Test - @SuppressWarnings("unchecked") public void testRetrieveGeneratedKeys() throws Exception { - // first we create our exchange using the endpoint - Endpoint endpoint = context.getEndpoint("direct:hello"); - - Exchange exchange = endpoint.createExchange(); - // then we set the SQL on the in body - exchange.getIn().setBody("insert into tableWithAutoIncr (content) values ('value2')"); - exchange.getIn().setHeader(JdbcConstants.JDBC_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.getOut().getHeader(JdbcConstants.JDBC_GENERATED_KEYS_DATA)); - assertNotNull(out.getOut().getHeader(JdbcConstants.JDBC_GENERATED_KEYS_ROW_COUNT)); - - List<Map<String, Object>> generatedKeys = out.getOut().getHeader(JdbcConstants.JDBC_GENERATED_KEYS_DATA, List.class); - assertNotNull("out body could not be converted to an ArrayList - was: " - + out.getOut().getBody(), generatedKeys); - assertEquals(1, generatedKeys.size()); - - Map<String, Object> row = generatedKeys.get(0); - assertEquals("auto increment value should be 2", BigDecimal.valueOf(2), row.get("1")); - - assertEquals("generated keys row count should be one", 1, out.getOut().getHeader(JdbcConstants.JDBC_GENERATED_KEYS_ROW_COUNT)); + super.testRetrieveGeneratedKeys("insert into tableWithAutoIncr (content) values ('value2')"); } @Test - @SuppressWarnings("unchecked") public void testRetrieveGeneratedKeysWithStringGeneratedColumns() throws Exception { - // first we create our exchange using the endpoint - Endpoint endpoint = context.getEndpoint("direct:hello"); - - Exchange exchange = endpoint.createExchange(); - // then we set the SQL on the in body - exchange.getIn().setBody("insert into tableWithAutoIncr (content) values ('value2')"); - exchange.getIn().setHeader(JdbcConstants.JDBC_RETRIEVE_GENERATED_KEYS, true); - exchange.getIn().setHeader(JdbcConstants.JDBC_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.getOut().getHeader(JdbcConstants.JDBC_GENERATED_KEYS_DATA)); - assertNotNull(out.getOut().getHeader(JdbcConstants.JDBC_GENERATED_KEYS_ROW_COUNT)); - - List<Map<String, Object>> generatedKeys = out.getOut().getHeader(JdbcConstants.JDBC_GENERATED_KEYS_DATA, List.class); - assertNotNull("out body could not be converted to an ArrayList - was: " - + out.getOut().getBody(), generatedKeys); - assertEquals(1, generatedKeys.size()); - - Map<String, Object> row = generatedKeys.get(0); - assertEquals("auto increment value should be 2", BigDecimal.valueOf(2), row.get("1")); - - assertEquals("generated keys row count should be one", 1, out.getOut().getHeader(JdbcConstants.JDBC_GENERATED_KEYS_ROW_COUNT)); + super.testRetrieveGeneratedKeysWithStringGeneratedColumns("insert into tableWithAutoIncr (content) values ('value2')"); } @Test - @SuppressWarnings("unchecked") public void testRetrieveGeneratedKeysWithIntGeneratedColumns() throws Exception { - // first we create our exchange using the endpoint - Endpoint endpoint = context.getEndpoint("direct:hello"); - - Exchange exchange = endpoint.createExchange(); - // then we set the SQL on the in body - exchange.getIn().setBody("insert into tableWithAutoIncr (content) values ('value2')"); - exchange.getIn().setHeader(JdbcConstants.JDBC_RETRIEVE_GENERATED_KEYS, true); - exchange.getIn().setHeader(JdbcConstants.JDBC_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.getOut()); - assertNotNull(out.getOut().getHeader(JdbcConstants.JDBC_GENERATED_KEYS_DATA)); - assertNotNull(out.getOut().getHeader(JdbcConstants.JDBC_GENERATED_KEYS_ROW_COUNT)); - - List<Map<String, Object>> generatedKeys = out.getOut().getHeader(JdbcConstants.JDBC_GENERATED_KEYS_DATA, List.class); - assertNotNull("out body could not be converted to an ArrayList - was: " - + out.getOut().getBody(), generatedKeys); - assertEquals(1, generatedKeys.size()); - - Map<String, Object> row = generatedKeys.get(0); - assertEquals("auto increment value should be 2", BigDecimal.valueOf(2), row.get("1")); - - assertEquals("generated keys row count should be one", 1, out.getOut().getHeader(JdbcConstants.JDBC_GENERATED_KEYS_ROW_COUNT)); + super.testRetrieveGeneratedKeysWithIntGeneratedColumns("insert into tableWithAutoIncr (content) values ('value2')"); } @Test public void testGivenAnInvalidGeneratedColumnsHeaderThenAnExceptionIsThrown() throws Exception { - // first we create our exchange using the endpoint - Endpoint endpoint = context.getEndpoint("direct:hello"); - - Exchange exchange = endpoint.createExchange(); - // then we set the SQL on the in body - exchange.getIn().setBody("insert into tableWithAutoIncr (content) values ('value2')"); - exchange.getIn().setHeader(JdbcConstants.JDBC_RETRIEVE_GENERATED_KEYS, true); - - // set wrong data type for generated columns - exchange.getIn().setHeader(JdbcConstants.JDBC_GENERATED_COLUMNS, new Object[]{}); - - // now we send the exchange to the endpoint, and receives the response from Camel - template.send(endpoint, exchange); + super.testGivenAnInvalidGeneratedColumnsHeaderThenAnExceptionIsThrown("insert into tableWithAutoIncr (content) values ('value2')"); + } - assertTrue(exchange.isFailed()); + @Override + protected RouteBuilder createRouteBuilder() throws Exception { + return new RouteBuilder() { + // START SNIPPET: route + // lets add simple route + public void configure() throws Exception { + from("direct:hello").to("jdbc:testdb?readSize=100"); + } + // END SNIPPET: route + }; } + } http://git-wip-us.apache.org/repos/asf/camel/blob/1985d995/components/camel-jdbc/src/test/java/org/apache/camel/component/jdbc/JdbcParameterizedQueryGeneratedKeysTest.java ---------------------------------------------------------------------- diff --git a/components/camel-jdbc/src/test/java/org/apache/camel/component/jdbc/JdbcParameterizedQueryGeneratedKeysTest.java b/components/camel-jdbc/src/test/java/org/apache/camel/component/jdbc/JdbcParameterizedQueryGeneratedKeysTest.java new file mode 100644 index 0000000..04d7bb1 --- /dev/null +++ b/components/camel-jdbc/src/test/java/org/apache/camel/component/jdbc/JdbcParameterizedQueryGeneratedKeysTest.java @@ -0,0 +1,63 @@ +/** + * 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.jdbc; + +import org.apache.camel.builder.RouteBuilder; +import org.junit.Test; + +import java.util.HashMap; +import java.util.Map; + +public class JdbcParameterizedQueryGeneratedKeysTest extends AbstractJdbcGeneratedKeysTest { + + private static final Map<String, Object> valueMap; + + static { + valueMap = new HashMap<String, Object>(); + valueMap.put("value", "testValue"); + } + + @Test + public void testRetrieveGeneratedKeys() throws Exception { + super.testRetrieveGeneratedKeys("insert into tableWithAutoIncr (content) values (:?value)", valueMap); + } + + @Test + public void testRetrieveGeneratedKeysWithStringGeneratedColumns() throws Exception { + super.testRetrieveGeneratedKeysWithStringGeneratedColumns("insert into tableWithAutoIncr (content) values (:?value)", valueMap); + } + + @Test + public void testRetrieveGeneratedKeysWithIntGeneratedColumns() throws Exception { + super.testRetrieveGeneratedKeysWithIntGeneratedColumns("insert into tableWithAutoIncr (content) values (:?value)", valueMap); + } + + @Test + public void testGivenAnInvalidGeneratedColumnsHeaderThenAnExceptionIsThrown() throws Exception { + super.testGivenAnInvalidGeneratedColumnsHeaderThenAnExceptionIsThrown("insert into tableWithAutoIncr (content) values (:?value)", valueMap); + } + + @Override + protected RouteBuilder createRouteBuilder() throws Exception { + return new RouteBuilder() { + public void configure() { + from("direct:hello").to("jdbc:testdb?useHeadersAsParameters=true&readSize=100"); + } + }; + } + +}
