Author: davsclaus
Date: Sun Mar 25 14:12:18 2012
New Revision: 1305042
URL: http://svn.apache.org/viewvc?rev=1305042&view=rev
Log:
CAMEL-5003: Added support for generated keys in camel-jdbc. Thanks to Thomas
Gueze for the patch.
Added:
camel/trunk/components/camel-jdbc/src/test/java/org/apache/camel/component/jdbc/JdbcGeneratedKeysTest.java
(with props)
Modified:
camel/trunk/components/camel-jdbc/src/main/java/org/apache/camel/component/jdbc/JdbcConstants.java
camel/trunk/components/camel-jdbc/src/main/java/org/apache/camel/component/jdbc/JdbcProducer.java
camel/trunk/components/camel-jdbc/src/test/resources/sql/init.sql
Modified:
camel/trunk/components/camel-jdbc/src/main/java/org/apache/camel/component/jdbc/JdbcConstants.java
URL:
http://svn.apache.org/viewvc/camel/trunk/components/camel-jdbc/src/main/java/org/apache/camel/component/jdbc/JdbcConstants.java?rev=1305042&r1=1305041&r2=1305042&view=diff
==============================================================================
---
camel/trunk/components/camel-jdbc/src/main/java/org/apache/camel/component/jdbc/JdbcConstants.java
(original)
+++
camel/trunk/components/camel-jdbc/src/main/java/org/apache/camel/component/jdbc/JdbcConstants.java
Sun Mar 25 14:12:18 2012
@@ -25,6 +25,32 @@ public final class JdbcConstants {
public static final String JDBC_ROW_COUNT = "CamelJdbcRowCount";
+ /**
+ * Boolean input header.
+ * Set its value to true to retrieve generated keys, default is false
+ */
+ public static final String JDBC_RETRIEVE_GENERATED_KEYS =
"CamelRetrieveGeneratedKeys";
+
+ /**
+ * <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 JDBC_GENERATED_COLUMNS =
"CamelGeneratedColumns";
+
+ /**
+ * int output header giving the number of rows of generated keys
+ */
+ public static final String JDBC_GENERATED_KEYS_ROW_COUNT =
"CamelGeneratedKeysRowCount";
+
+ /**
+ * <tt>List<Map<String, Object>></tt> output header containing the
generated keys retrieved
+ */
+ public static final String JDBC_GENERATED_KEYS_DATA =
"CamelGeneratedKeysRows";
+
private JdbcConstants() {
// Utility class
}
Modified:
camel/trunk/components/camel-jdbc/src/main/java/org/apache/camel/component/jdbc/JdbcProducer.java
URL:
http://svn.apache.org/viewvc/camel/trunk/components/camel-jdbc/src/main/java/org/apache/camel/component/jdbc/JdbcProducer.java?rev=1305042&r1=1305041&r2=1305042&view=diff
==============================================================================
---
camel/trunk/components/camel-jdbc/src/main/java/org/apache/camel/component/jdbc/JdbcProducer.java
(original)
+++
camel/trunk/components/camel-jdbc/src/main/java/org/apache/camel/component/jdbc/JdbcProducer.java
Sun Mar 25 14:12:18 2012
@@ -70,8 +70,6 @@ public class JdbcProducer extends Defaul
private void processingSqlBySettingAutoCommit(Exchange exchange) throws
Exception {
String sql = exchange.getIn().getBody(String.class);
Connection conn = null;
- Statement stmt = null;
- ResultSet rs = null;
Boolean autoCommit = null;
try {
conn = dataSource.getConnection();
@@ -80,21 +78,8 @@ public class JdbcProducer extends Defaul
conn.setAutoCommit(false);
}
- stmt = conn.createStatement();
-
- if (parameters != null && !parameters.isEmpty()) {
- IntrospectionSupport.setProperties(stmt, parameters);
- }
+ createAndExecuteSqlStatement(exchange, sql, conn);
- LOG.debug("Executing JDBC statement: {}", sql);
-
- if (stmt.execute(sql)) {
- rs = stmt.getResultSet();
- setResultSet(exchange, rs);
- } else {
- int updateCount = stmt.getUpdateCount();
- exchange.getOut().setHeader(JdbcConstants.JDBC_UPDATE_COUNT,
updateCount);
- }
conn.commit();
} catch (Exception e) {
try {
@@ -102,12 +87,10 @@ public class JdbcProducer extends Defaul
conn.rollback();
}
} catch (SQLException sqle) {
- LOG.warn("Error on jdbc component rollback: " + sqle, sqle);
+ LOG.warn("Error occurred during jdbc rollback. This exception
will be ignored.", sqle);
}
throw e;
} finally {
- closeQuietly(rs);
- closeQuietly(stmt);
resetAutoCommit(conn, autoCommit);
closeQuietly(conn);
}
@@ -116,11 +99,18 @@ public class JdbcProducer extends Defaul
private void processingSqlWithoutSettingAutoCommit(Exchange exchange)
throws Exception {
String sql = exchange.getIn().getBody(String.class);
Connection conn = null;
- Statement stmt = null;
- ResultSet rs = null;
try {
conn = dataSource.getConnection();
+ createAndExecuteSqlStatement(exchange, sql, conn);
+ } finally {
+ closeQuietly(conn);
+ }
+ }
+ private void createAndExecuteSqlStatement(Exchange exchange, String sql,
Connection conn) throws Exception {
+ Statement stmt = null;
+ ResultSet rs = null;
+ try {
stmt = conn.createStatement();
if (parameters != null && !parameters.isEmpty()) {
@@ -129,17 +119,41 @@ public class JdbcProducer extends Defaul
LOG.debug("Executing JDBC statement: {}", sql);
- if (stmt.execute(sql)) {
+ Boolean shouldRetrieveGeneratedKeys =
+
exchange.getIn().getHeader(JdbcConstants.JDBC_RETRIEVE_GENERATED_KEYS, false,
Boolean.class);
+
+ boolean stmtExecutionResult;
+ if (shouldRetrieveGeneratedKeys) {
+ Object expectedGeneratedColumns =
exchange.getIn().getHeader(JdbcConstants.JDBC_GENERATED_COLUMNS);
+ if (expectedGeneratedColumns == null) {
+ stmtExecutionResult = stmt.execute(sql,
Statement.RETURN_GENERATED_KEYS);
+ } else if (expectedGeneratedColumns instanceof String[]) {
+ stmtExecutionResult = stmt.execute(sql, (String[])
expectedGeneratedColumns);
+ } else if (expectedGeneratedColumns instanceof int[]) {
+ stmtExecutionResult = stmt.execute(sql, (int[])
expectedGeneratedColumns);
+ } else {
+ throw new IllegalArgumentException(
+ "Header specifying expected returning columns
isn't an instance of String[] or int[] but "
+ + expectedGeneratedColumns.getClass());
+ }
+ } else {
+ stmtExecutionResult = stmt.execute(sql);
+ }
+
+ if (stmtExecutionResult) {
rs = stmt.getResultSet();
setResultSet(exchange, rs);
} else {
int updateCount = stmt.getUpdateCount();
exchange.getOut().setHeader(JdbcConstants.JDBC_UPDATE_COUNT,
updateCount);
}
+
+ if (shouldRetrieveGeneratedKeys) {
+ setGeneratedKeys(exchange, stmt.getGeneratedKeys());
+ }
} finally {
closeQuietly(rs);
closeQuietly(stmt);
- closeQuietly(conn);
}
}
@@ -152,7 +166,7 @@ public class JdbcProducer extends Defaul
}
}
}
-
+
private void closeQuietly(Statement stmt) {
if (stmt != null) {
try {
@@ -162,7 +176,7 @@ public class JdbcProducer extends Defaul
}
}
}
-
+
private void resetAutoCommit(Connection con, Boolean autoCommit) {
if (con != null && autoCommit != null) {
try {
@@ -172,7 +186,7 @@ public class JdbcProducer extends Defaul
}
}
}
-
+
private void closeQuietly(Connection con) {
if (con != null) {
try {
@@ -183,10 +197,41 @@ public class JdbcProducer extends Defaul
}
}
+
+ /**
+ * Sets the generated if any to the Exchange in headers :
+ * - {@link JdbcConstants#JDBC_GENERATED_KEYS_ROW_COUNT} : the row count
of generated keys
+ * - {@link JdbcConstants#JDBC_GENERATED_KEYS_DATA} : the generated keys
data
+ *
+ * @param exchange The exchange where to store the generated keys
+ * @param generatedKeys The result set containing the generated keys
+ */
+ protected void setGeneratedKeys(Exchange exchange, ResultSet
generatedKeys) throws SQLException {
+ if (generatedKeys != null) {
+ List<Map<String, Object>> data =
extractResultSetData(generatedKeys);
+
+
exchange.getOut().setHeader(JdbcConstants.JDBC_GENERATED_KEYS_ROW_COUNT,
data.size());
+
exchange.getOut().setHeader(JdbcConstants.JDBC_GENERATED_KEYS_DATA, data);
+ }
+ }
+
/**
* Sets the result from the ResultSet to the Exchange as its OUT body.
*/
protected void setResultSet(Exchange exchange, ResultSet rs) throws
SQLException {
+ List<Map<String, Object>> data = extractResultSetData(rs);
+
+ exchange.getOut().setHeader(JdbcConstants.JDBC_ROW_COUNT, data.size());
+ exchange.getOut().setBody(data);
+ }
+
+ /**
+ * Extract the result from the ResultSet
+ *
+ * @param rs rs produced by the SQL request
+ * @return All the resulting rows containing each field of the ResultSet
+ */
+ protected List<Map<String, Object>> extractResultSetData(ResultSet rs)
throws SQLException {
ResultSetMetaData meta = rs.getMetaData();
// should we use jdbc4 or jdbc3 semantics
@@ -218,8 +263,7 @@ public class JdbcProducer extends Defaul
data.add(row);
rowNumber++;
}
- exchange.getOut().setHeader(JdbcConstants.JDBC_ROW_COUNT, rowNumber);
- exchange.getOut().setBody(data);
+ return data;
}
}
Added:
camel/trunk/components/camel-jdbc/src/test/java/org/apache/camel/component/jdbc/JdbcGeneratedKeysTest.java
URL:
http://svn.apache.org/viewvc/camel/trunk/components/camel-jdbc/src/test/java/org/apache/camel/component/jdbc/JdbcGeneratedKeysTest.java?rev=1305042&view=auto
==============================================================================
---
camel/trunk/components/camel-jdbc/src/test/java/org/apache/camel/component/jdbc/JdbcGeneratedKeysTest.java
(added)
+++
camel/trunk/components/camel-jdbc/src/test/java/org/apache/camel/component/jdbc/JdbcGeneratedKeysTest.java
Sun Mar 25 14:12:18 2012
@@ -0,0 +1,148 @@
+/**
+ * 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 java.math.BigDecimal;
+import java.util.List;
+import java.util.Map;
+
+import org.apache.camel.Endpoint;
+import org.apache.camel.EndpointInject;
+import org.apache.camel.Exchange;
+import org.apache.camel.component.mock.MockEndpoint;
+import org.junit.Test;
+
+
+public class JdbcGeneratedKeysTest extends JdbcRouteTest {
+
+ @EndpointInject(uri = "mock:result")
+ private MockEndpoint mock;
+
+ @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));
+ }
+
+ @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));
+ }
+
+ @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));
+ }
+
+ @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
+ Exchange out = template.send(endpoint, exchange);
+
+ assertTrue(exchange.isFailed());
+ }
+}
Propchange:
camel/trunk/components/camel-jdbc/src/test/java/org/apache/camel/component/jdbc/JdbcGeneratedKeysTest.java
------------------------------------------------------------------------------
svn:eol-style = native
Propchange:
camel/trunk/components/camel-jdbc/src/test/java/org/apache/camel/component/jdbc/JdbcGeneratedKeysTest.java
------------------------------------------------------------------------------
svn:keywords = Rev Date
Modified: camel/trunk/components/camel-jdbc/src/test/resources/sql/init.sql
URL:
http://svn.apache.org/viewvc/camel/trunk/components/camel-jdbc/src/test/resources/sql/init.sql?rev=1305042&r1=1305041&r2=1305042&view=diff
==============================================================================
--- camel/trunk/components/camel-jdbc/src/test/resources/sql/init.sql (original)
+++ camel/trunk/components/camel-jdbc/src/test/resources/sql/init.sql Sun Mar
25 14:12:18 2012
@@ -18,4 +18,7 @@
create table customer (id varchar(15), name varchar(10));
insert into customer values('cust1','jstrachan');
insert into customer values('cust2','nsandhu');
-insert into customer values('cust3','willem');
\ No newline at end of file
+insert into customer values('cust3','willem');
+
+create table tableWithAutoIncr (id int not null GENERATED ALWAYS AS IDENTITY,
content varchar(10));
+insert into tableWithAutoIncr (content) values ('value1');
\ No newline at end of file
