This is an automated email from the ASF dual-hosted git repository.
davsclaus pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/camel.git
The following commit(s) were added to refs/heads/main by this push:
new b4b62a5ee6fc CAMEL-26997: camel-jdbc - with outputType=StreamList the
producer commits when the exchange is done, not before the route reads the rows
(#30083)
b4b62a5ee6fc is described below
commit b4b62a5ee6fcb16826e7cd399b5642f76f48f889
Author: Federico Mariani <[email protected]>
AuthorDate: Sun Nov 15 23:04:01 2026 +0100
CAMEL-26997: camel-jdbc - with outputType=StreamList the producer commits
when the exchange is done, not before the route reads the rows (#30083)
* CAMEL-26997: camel-jdbc - with outputType=StreamList the producer com…
…mits when the exchange is done, not before the route reads the rows
* Merge remote-tracking branch 'origin/main' into CAMEL-26997-jdbc-stre…
…amlist-commit
* CAMEL-26997: camel-jdbc - say that a StreamList commit that fails whe…
…n the exchange is done does not fail it, guard commitQuietly against null,
and put the upgrade-guide entry next to CAMEL-26996
Co-Authored-By: Claude <[email protected]>
---
.../org/apache/camel/catalog/components/jdbc.json | 2 +-
.../camel/catalog/components/spring-jdbc.json | 2 +-
.../apache/camel/catalog/docs/jdbc-component.adoc | 11 ++
.../org/apache/camel/component/jdbc/jdbc.json | 2 +-
.../camel-jdbc/src/main/docs/jdbc-component.adoc | 11 ++
.../apache/camel/component/jdbc/JdbcEndpoint.java | 10 +-
.../apache/camel/component/jdbc/JdbcProducer.java | 48 +++++-
.../jdbc/JdbcProducerStreamListPostgresIT.java | 98 +++++++++++
.../JdbcProducerStreamListTransactionTest.java | 179 +++++++++++++++++++++
.../camel/component/spring/jdbc/spring-jdbc.json | 2 +-
.../ROOT/pages/camel-4x-upgrade-guide-4_24.adoc | 14 ++
.../endpoint/dsl/JdbcEndpointBuilderFactory.java | 26 +--
.../dsl/SpringJdbcEndpointBuilderFactory.java | 26 +--
13 files changed, 395 insertions(+), 36 deletions(-)
diff --git
a/catalog/camel-catalog/src/generated/resources/org/apache/camel/catalog/components/jdbc.json
b/catalog/camel-catalog/src/generated/resources/org/apache/camel/catalog/components/jdbc.json
index b499f54be0ef..3a483addd086 100644
---
a/catalog/camel-catalog/src/generated/resources/org/apache/camel/catalog/components/jdbc.json
+++
b/catalog/camel-catalog/src/generated/resources/org/apache/camel/catalog/components/jdbc.json
@@ -50,7 +50,7 @@
"outputType": { "index": 3, "kind": "parameter", "displayName": "Output
Type", "group": "producer", "label": "", "required": false, "type": "enum",
"javaType": "org.apache.camel.component.jdbc.JdbcOutputType", "enum": [
"SelectOne", "SelectList", "StreamList" ], "deprecated": false, "autowired":
false, "secret": false, "defaultValue": "SelectList", "description":
"Determines the output the producer should use." },
"parameters": { "index": 4, "kind": "parameter", "displayName":
"Parameters", "group": "producer", "label": "", "required": false, "type":
"object", "javaType": "java.util.Map<java.lang.String, java.lang.Object>",
"prefix": "statement.", "multiValue": true, "deprecated": false, "autowired":
false, "secret": false, "description": "Optional parameters to the
java.sql.Statement. For example to set maxRows, fetchSize etc. This is a
multi-value option with prefix: statement." },
"readSize": { "index": 5, "kind": "parameter", "displayName": "Read Size",
"group": "producer", "label": "", "required": false, "type": "integer",
"javaType": "int", "deprecated": false, "autowired": false, "secret": false,
"description": "The default maximum number of rows that can be read by a
polling query. The default value is 0." },
- "resetAutoCommit": { "index": 6, "kind": "parameter", "displayName":
"Reset Auto Commit", "group": "producer", "label": "", "required": false,
"type": "boolean", "javaType": "boolean", "deprecated": false, "autowired":
false, "secret": false, "defaultValue": true, "description": "Camel will set
the autoCommit on the JDBC connection to be false, commit the change after
executed the statement and reset the autoCommit flag of the connection at the
end, if the resetAutoCommit is true. If [...]
+ "resetAutoCommit": { "index": 6, "kind": "parameter", "displayName":
"Reset Auto Commit", "group": "producer", "label": "", "required": false,
"type": "boolean", "javaType": "boolean", "deprecated": false, "autowired":
false, "secret": false, "defaultValue": true, "description": "Camel will set
the autoCommit on the JDBC connection to be false, commit the change after
executed the statement and reset the autoCommit flag of the connection at the
end, if the resetAutoCommit is true. Wi [...]
"transacted": { "index": 7, "kind": "parameter", "displayName":
"Transacted", "group": "producer", "label": "", "required": false, "type":
"boolean", "javaType": "boolean", "deprecated": true, "deprecationNote": "Not
used: the producer does not read this option", "autowired": false, "secret":
false, "defaultValue": false, "description": "Whether transactions are in use.
Deprecation note: Not used: the producer does not read this option" },
"useGetBytesForBlob": { "index": 8, "kind": "parameter", "displayName":
"Use Get Bytes For Blob", "group": "producer", "label": "", "required": false,
"type": "boolean", "javaType": "boolean", "deprecated": false, "autowired":
false, "secret": false, "defaultValue": false, "description": "To read BLOB
columns as bytes (a byte[]) instead of a java.sql.Blob, which cannot be read
after the producer has closed the connection. This may be needed for certain
databases such as Oracle where [...]
"useHeadersAsParameters": { "index": 9, "kind": "parameter",
"displayName": "Use Headers As Parameters", "group": "producer", "label": "",
"required": false, "type": "boolean", "javaType": "boolean", "deprecated":
false, "autowired": false, "secret": false, "defaultValue": false,
"description": "Set this option to true to use the prepareStatementStrategy
with named parameters. This allows to define queries with named placeholders,
and use headers with the dynamic values for the query [...]
diff --git
a/catalog/camel-catalog/src/generated/resources/org/apache/camel/catalog/components/spring-jdbc.json
b/catalog/camel-catalog/src/generated/resources/org/apache/camel/catalog/components/spring-jdbc.json
index f0976172d55e..bec1fb82c776 100644
---
a/catalog/camel-catalog/src/generated/resources/org/apache/camel/catalog/components/spring-jdbc.json
+++
b/catalog/camel-catalog/src/generated/resources/org/apache/camel/catalog/components/spring-jdbc.json
@@ -50,7 +50,7 @@
"outputType": { "index": 3, "kind": "parameter", "displayName": "Output
Type", "group": "producer", "label": "", "required": false, "type": "enum",
"javaType": "org.apache.camel.component.jdbc.JdbcOutputType", "enum": [
"SelectOne", "SelectList", "StreamList" ], "deprecated": false, "autowired":
false, "secret": false, "defaultValue": "SelectList", "description":
"Determines the output the producer should use." },
"parameters": { "index": 4, "kind": "parameter", "displayName":
"Parameters", "group": "producer", "label": "", "required": false, "type":
"object", "javaType": "java.util.Map<java.lang.String, java.lang.Object>",
"prefix": "statement.", "multiValue": true, "deprecated": false, "autowired":
false, "secret": false, "description": "This is a multi-value option with
prefix: statement." },
"readSize": { "index": 5, "kind": "parameter", "displayName": "Read Size",
"group": "producer", "label": "", "required": false, "type": "integer",
"javaType": "int", "deprecated": false, "autowired": false, "secret": false,
"description": "The default maximum number of rows that can be read by a
polling query. The default value is 0." },
- "resetAutoCommit": { "index": 6, "kind": "parameter", "displayName":
"Reset Auto Commit", "group": "producer", "label": "", "required": false,
"type": "boolean", "javaType": "boolean", "deprecated": false, "autowired":
false, "secret": false, "defaultValue": true, "description": "Camel will set
the autoCommit on the JDBC connection to be false, commit the change after
executed the statement and reset the autoCommit flag of the connection at the
end, if the resetAutoCommit is true. If [...]
+ "resetAutoCommit": { "index": 6, "kind": "parameter", "displayName":
"Reset Auto Commit", "group": "producer", "label": "", "required": false,
"type": "boolean", "javaType": "boolean", "deprecated": false, "autowired":
false, "secret": false, "defaultValue": true, "description": "Camel will set
the autoCommit on the JDBC connection to be false, commit the change after
executed the statement and reset the autoCommit flag of the connection at the
end, if the resetAutoCommit is true. Wi [...]
"transacted": { "index": 7, "kind": "parameter", "displayName":
"Transacted", "group": "producer", "label": "", "required": false, "type":
"boolean", "javaType": "boolean", "deprecated": true, "deprecationNote": "Not
used: the producer does not read this option", "autowired": false, "secret":
false, "defaultValue": false, "description": "Deprecation note: Not used: the
producer does not read this option" },
"useGetBytesForBlob": { "index": 8, "kind": "parameter", "displayName":
"Use Get Bytes For Blob", "group": "producer", "label": "", "required": false,
"type": "boolean", "javaType": "boolean", "deprecated": false, "autowired":
false, "secret": false, "defaultValue": false, "description": "To read BLOB
columns as bytes (a byte[]) instead of a java.sql.Blob, which cannot be read
after the producer has closed the connection. This may be needed for certain
databases such as Oracle where [...]
"useHeadersAsParameters": { "index": 9, "kind": "parameter",
"displayName": "Use Headers As Parameters", "group": "producer", "label": "",
"required": false, "type": "boolean", "javaType": "boolean", "deprecated":
false, "autowired": false, "secret": false, "defaultValue": false,
"description": "Set this option to true to use the prepareStatementStrategy
with named parameters. This allows to define queries with named placeholders,
and use headers with the dynamic values for the query [...]
diff --git
a/catalog/camel-catalog/src/generated/resources/org/apache/camel/catalog/docs/jdbc-component.adoc
b/catalog/camel-catalog/src/generated/resources/org/apache/camel/catalog/docs/jdbc-component.adoc
index d92c924311c3..e8018682977d 100644
---
a/catalog/camel-catalog/src/generated/resources/org/apache/camel/catalog/docs/jdbc-component.adoc
+++
b/catalog/camel-catalog/src/generated/resources/org/apache/camel/catalog/docs/jdbc-component.adoc
@@ -307,6 +307,17 @@ YAML::
----
====
+The result set and the connection stay open until the exchange is done. With
`resetAutoCommit=true` (the default),
+the transaction of the statement ends then too: it is committed when the
exchange completes, or rolled back when it
+fails. A commit that fails at that time is logged at WARN and does not fail
the exchange, which is already complete.
+For a statement with side effects that returns rows (such as an `INSERT` or
`DELETE` with `RETURNING`), the route then
+reports success (and a consumer such as JMS acknowledges its message) although
the change is not committed: use
+another output type, such as `SelectList`, when a failed commit must fail the
exchange.
+
+Whether the rows are loaded as needed depends on the JDBC driver. For example,
the PostgreSQL driver fetches
+`statement.fetchSize` rows at a time only when auto-commit is off (as
`resetAutoCommit=true` sets it); otherwise it
+loads all the rows when the statement is executed.
+
=== Polling the database every minute
If we want to poll a database using the JDBC component, we need to
diff --git
a/components/camel-jdbc/src/generated/resources/META-INF/org/apache/camel/component/jdbc/jdbc.json
b/components/camel-jdbc/src/generated/resources/META-INF/org/apache/camel/component/jdbc/jdbc.json
index b499f54be0ef..3a483addd086 100644
---
a/components/camel-jdbc/src/generated/resources/META-INF/org/apache/camel/component/jdbc/jdbc.json
+++
b/components/camel-jdbc/src/generated/resources/META-INF/org/apache/camel/component/jdbc/jdbc.json
@@ -50,7 +50,7 @@
"outputType": { "index": 3, "kind": "parameter", "displayName": "Output
Type", "group": "producer", "label": "", "required": false, "type": "enum",
"javaType": "org.apache.camel.component.jdbc.JdbcOutputType", "enum": [
"SelectOne", "SelectList", "StreamList" ], "deprecated": false, "autowired":
false, "secret": false, "defaultValue": "SelectList", "description":
"Determines the output the producer should use." },
"parameters": { "index": 4, "kind": "parameter", "displayName":
"Parameters", "group": "producer", "label": "", "required": false, "type":
"object", "javaType": "java.util.Map<java.lang.String, java.lang.Object>",
"prefix": "statement.", "multiValue": true, "deprecated": false, "autowired":
false, "secret": false, "description": "Optional parameters to the
java.sql.Statement. For example to set maxRows, fetchSize etc. This is a
multi-value option with prefix: statement." },
"readSize": { "index": 5, "kind": "parameter", "displayName": "Read Size",
"group": "producer", "label": "", "required": false, "type": "integer",
"javaType": "int", "deprecated": false, "autowired": false, "secret": false,
"description": "The default maximum number of rows that can be read by a
polling query. The default value is 0." },
- "resetAutoCommit": { "index": 6, "kind": "parameter", "displayName":
"Reset Auto Commit", "group": "producer", "label": "", "required": false,
"type": "boolean", "javaType": "boolean", "deprecated": false, "autowired":
false, "secret": false, "defaultValue": true, "description": "Camel will set
the autoCommit on the JDBC connection to be false, commit the change after
executed the statement and reset the autoCommit flag of the connection at the
end, if the resetAutoCommit is true. If [...]
+ "resetAutoCommit": { "index": 6, "kind": "parameter", "displayName":
"Reset Auto Commit", "group": "producer", "label": "", "required": false,
"type": "boolean", "javaType": "boolean", "deprecated": false, "autowired":
false, "secret": false, "defaultValue": true, "description": "Camel will set
the autoCommit on the JDBC connection to be false, commit the change after
executed the statement and reset the autoCommit flag of the connection at the
end, if the resetAutoCommit is true. Wi [...]
"transacted": { "index": 7, "kind": "parameter", "displayName":
"Transacted", "group": "producer", "label": "", "required": false, "type":
"boolean", "javaType": "boolean", "deprecated": true, "deprecationNote": "Not
used: the producer does not read this option", "autowired": false, "secret":
false, "defaultValue": false, "description": "Whether transactions are in use.
Deprecation note: Not used: the producer does not read this option" },
"useGetBytesForBlob": { "index": 8, "kind": "parameter", "displayName":
"Use Get Bytes For Blob", "group": "producer", "label": "", "required": false,
"type": "boolean", "javaType": "boolean", "deprecated": false, "autowired":
false, "secret": false, "defaultValue": false, "description": "To read BLOB
columns as bytes (a byte[]) instead of a java.sql.Blob, which cannot be read
after the producer has closed the connection. This may be needed for certain
databases such as Oracle where [...]
"useHeadersAsParameters": { "index": 9, "kind": "parameter",
"displayName": "Use Headers As Parameters", "group": "producer", "label": "",
"required": false, "type": "boolean", "javaType": "boolean", "deprecated":
false, "autowired": false, "secret": false, "defaultValue": false,
"description": "Set this option to true to use the prepareStatementStrategy
with named parameters. This allows to define queries with named placeholders,
and use headers with the dynamic values for the query [...]
diff --git a/components/camel-jdbc/src/main/docs/jdbc-component.adoc
b/components/camel-jdbc/src/main/docs/jdbc-component.adoc
index d92c924311c3..e8018682977d 100644
--- a/components/camel-jdbc/src/main/docs/jdbc-component.adoc
+++ b/components/camel-jdbc/src/main/docs/jdbc-component.adoc
@@ -307,6 +307,17 @@ YAML::
----
====
+The result set and the connection stay open until the exchange is done. With
`resetAutoCommit=true` (the default),
+the transaction of the statement ends then too: it is committed when the
exchange completes, or rolled back when it
+fails. A commit that fails at that time is logged at WARN and does not fail
the exchange, which is already complete.
+For a statement with side effects that returns rows (such as an `INSERT` or
`DELETE` with `RETURNING`), the route then
+reports success (and a consumer such as JMS acknowledges its message) although
the change is not committed: use
+another output type, such as `SelectList`, when a failed commit must fail the
exchange.
+
+Whether the rows are loaded as needed depends on the JDBC driver. For example,
the PostgreSQL driver fetches
+`statement.fetchSize` rows at a time only when auto-commit is off (as
`resetAutoCommit=true` sets it); otherwise it
+loads all the rows when the statement is executed.
+
=== Polling the database every minute
If we want to poll a database using the JDBC component, we need to
diff --git
a/components/camel-jdbc/src/main/java/org/apache/camel/component/jdbc/JdbcEndpoint.java
b/components/camel-jdbc/src/main/java/org/apache/camel/component/jdbc/JdbcEndpoint.java
index d5a146c7986a..5fb81e79d68c 100644
---
a/components/camel-jdbc/src/main/java/org/apache/camel/component/jdbc/JdbcEndpoint.java
+++
b/components/camel-jdbc/src/main/java/org/apache/camel/component/jdbc/JdbcEndpoint.java
@@ -160,10 +160,12 @@ public class JdbcEndpoint extends DefaultEndpoint {
/**
* Camel will set the autoCommit on the JDBC connection to be false,
commit the change after executed the statement
- * and reset the autoCommit flag of the connection at the end, if the
resetAutoCommit is true. If the JDBC
- * connection doesn't support to reset the autoCommit flag, you can set
the resetAutoCommit flag to be false, and
- * Camel will not try to reset the autoCommit flag. When used with XA
transactions you most likely need to set it to
- * false so that the transaction manager is in charge of committing this
tx.
+ * and reset the autoCommit flag of the connection at the end, if the
resetAutoCommit is true. With
+ * outputType=StreamList, the route reads the rows after the statement is
executed, so the commit (or the rollback,
+ * when the exchange fails) happens when the exchange is done. If the JDBC
connection doesn't support to reset the
+ * autoCommit flag, you can set the resetAutoCommit flag to be false, and
Camel will not try to reset the autoCommit
+ * flag. When used with XA transactions you most likely need to set it to
false so that the transaction manager is
+ * in charge of committing this tx.
*/
public void setResetAutoCommit(boolean resetAutoCommit) {
this.resetAutoCommit = resetAutoCommit;
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 9c9f96b4a059..c7ce2c1aeadc 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
@@ -88,15 +88,14 @@ public class JdbcProducer extends DefaultProducer {
shouldCloseResources = createAndExecuteSqlStatement(exchange, sql,
conn);
- conn.commit();
- } catch (Exception e) {
- try {
- if (conn != null) {
- conn.rollback();
- }
- } catch (Exception sqle) {
- LOG.warn("Error occurred during JDBC rollback. This exception
will be ignored.", sqle);
+ // with outputType=StreamList the route reads the result set after
this producer: committing now would end
+ // the transaction, and with it the cursor the driver may read the
rows from (such as PostgreSQL with a
+ // fetch size), so the transaction then ends when the exchange is
done
+ if (shouldCloseResources) {
+ conn.commit();
}
+ } catch (Exception e) {
+ rollbackQuietly(conn);
throw e;
} finally {
if (shouldCloseResources) {
@@ -106,6 +105,19 @@ public class JdbcProducer extends DefaultProducer {
final Connection finalConn = conn;
final boolean finalAutoCommit = autoCommit;
exchange.getExchangeExtension().addOnCompletion(new
SynchronizationAdapter() {
+ @Override
+ public void onComplete(Exchange exchange) {
+ // the result set and its statement are closed already
(their on completion has a lower order)
+ commitQuietly(finalConn);
+ onDone(exchange);
+ }
+
+ @Override
+ public void onFailure(Exchange exchange) {
+ rollbackQuietly(finalConn);
+ onDone(exchange);
+ }
+
@Override
public void onDone(Exchange exchange) {
resetAutoCommit(finalConn, finalAutoCommit);
@@ -335,6 +347,26 @@ public class JdbcProducer extends DefaultProducer {
}
}
+ private void commitQuietly(Connection con) {
+ try {
+ if (con != null) {
+ con.commit();
+ }
+ } catch (Exception sqle) {
+ LOG.warn("Error occurred during JDBC commit. This exception will
be ignored.", sqle);
+ }
+ }
+
+ private void rollbackQuietly(Connection con) {
+ try {
+ if (con != null) {
+ con.rollback();
+ }
+ } catch (Exception sqle) {
+ LOG.warn("Error occurred during JDBC rollback. This exception will
be ignored.", sqle);
+ }
+ }
+
private void resetAutoCommit(Connection con, boolean autoCommit) {
if (con != null) {
try {
diff --git
a/components/camel-jdbc/src/test/java/org/apache/camel/component/jdbc/JdbcProducerStreamListPostgresIT.java
b/components/camel-jdbc/src/test/java/org/apache/camel/component/jdbc/JdbcProducerStreamListPostgresIT.java
new file mode 100644
index 000000000000..f099015dc5a1
--- /dev/null
+++
b/components/camel-jdbc/src/test/java/org/apache/camel/component/jdbc/JdbcProducerStreamListPostgresIT.java
@@ -0,0 +1,98 @@
+/*
+ * 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.sql.Connection;
+import java.sql.Statement;
+
+import org.apache.camel.EndpointInject;
+import org.apache.camel.Exchange;
+import org.apache.camel.builder.RouteBuilder;
+import org.apache.camel.component.mock.MockEndpoint;
+import org.apache.camel.spi.Registry;
+import org.apache.camel.test.infra.postgres.services.PostgresService;
+import org.apache.camel.test.infra.postgres.services.PostgresServiceFactory;
+import org.apache.camel.test.junit6.CamelTestSupport;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.extension.RegisterExtension;
+import org.postgresql.ds.PGSimpleDataSource;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertNull;
+
+/**
+ * {@code outputType=StreamList} with a fetch size on PostgreSQL: the driver
reads the rows through a cursor, a batch of
+ * {@code fetchSize} rows at a time, and the cursor lives until the end of the
transaction. With
+ * {@code resetAutoCommit=true} (the default) the producer must not commit
before the route has read the rows.
+ */
+class JdbcProducerStreamListPostgresIT extends CamelTestSupport {
+
+ private static final int ROWS = 30;
+
+ @RegisterExtension
+ static PostgresService postgres =
PostgresServiceFactory.createSingletonService();
+
+ @EndpointInject("mock:row")
+ private MockEndpoint row;
+
+ private PGSimpleDataSource dataSource;
+
+ @Override
+ protected void setupResources() throws Exception {
+ super.setupResources();
+ // no pool, so autocommit is on (the JDBC default) and the producer
turns it off for the statement
+ dataSource = new PGSimpleDataSource();
+ dataSource.setUrl(postgres.jdbcUrl());
+ dataSource.setUser(postgres.userName());
+ dataSource.setPassword(postgres.password());
+ try (Connection conn = dataSource.getConnection(); Statement stmt =
conn.createStatement()) {
+ stmt.execute("drop table if exists stream_items");
+ stmt.execute("create table stream_items (id int primary key)");
+ stmt.execute("insert into stream_items select generate_series(1, "
+ ROWS + ")");
+ }
+ }
+
+ @Override
+ protected void bindToRegistry(Registry registry) {
+ registry.bind("postgres", dataSource);
+ }
+
+ @Test
+ void streamListWithFetchSizeReadsEveryRow() throws Exception {
+ row.expectedMessageCount(ROWS);
+
+ Exchange out = template.send("direct:stream", e ->
e.getMessage().setBody("select id from stream_items order by id"));
+
+ assertNull(out.getException());
+ MockEndpoint.assertIsSatisfied(context);
+ assertEquals(ROWS, row.getReceivedExchanges().get(ROWS -
1).getMessage().getBody(Integer.class));
+ }
+
+ @Override
+ protected RouteBuilder createRouteBuilder() {
+ return new RouteBuilder() {
+ @Override
+ public void configure() {
+
from("direct:stream").to("jdbc:postgres?outputType=StreamList&statement.fetchSize=5")
+ .split(body()).streaming()
+ .setBody(simple("${body[id]}"))
+ .to("mock:row")
+ .end();
+ }
+ };
+ }
+}
diff --git
a/components/camel-jdbc/src/test/java/org/apache/camel/component/jdbc/JdbcProducerStreamListTransactionTest.java
b/components/camel-jdbc/src/test/java/org/apache/camel/component/jdbc/JdbcProducerStreamListTransactionTest.java
new file mode 100644
index 000000000000..f0d3673a7a2a
--- /dev/null
+++
b/components/camel-jdbc/src/test/java/org/apache/camel/component/jdbc/JdbcProducerStreamListTransactionTest.java
@@ -0,0 +1,179 @@
+/*
+ * 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.lang.reflect.InvocationHandler;
+import java.lang.reflect.InvocationTargetException;
+import java.lang.reflect.Method;
+import java.lang.reflect.Proxy;
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.List;
+import java.util.concurrent.CopyOnWriteArrayList;
+
+import javax.sql.DataSource;
+
+import org.apache.camel.BindToRegistry;
+import org.apache.camel.EndpointInject;
+import org.apache.camel.Exchange;
+import org.apache.camel.builder.RouteBuilder;
+import org.apache.camel.component.mock.MockEndpoint;
+import org.junit.jupiter.api.Test;
+import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.jdbc.datasource.DelegatingDataSource;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertInstanceOf;
+import static org.junit.jupiter.api.Assertions.assertNull;
+
+/**
+ * With {@code outputType=StreamList} and {@code resetAutoCommit=true} (the
default) the route reads the result set
+ * after the producer, so the transaction of the producer must end when the
exchange is done, not right after the
+ * statement is executed: a driver may close the result sets at commit (the
{@code CLOSE_CURSORS_AT_COMMIT}
+ * holdability), as PostgreSQL does with the cursor it reads the rows from
with a fetch size.
+ */
+public class JdbcProducerStreamListTransactionTest extends
AbstractJdbcTestSupport {
+
+ private static final String INSERT_RETURNING
+ = "select * from final table (insert into customer values
('cust4', 'hadrian'))";
+
+ @BindToRegistry("closeCursorsAtCommit")
+ private CloseCursorsAtCommitDataSource closeCursorsAtCommit;
+
+ @EndpointInject("mock:row")
+ private MockEndpoint row;
+
+ @Override
+ public void setupResources() {
+ super.setupResources();
+ closeCursorsAtCommit = new CloseCursorsAtCommitDataSource(db);
+ }
+
+ @Test
+ public void rowsAreReadBeforeTheCommit() throws Exception {
+ row.expectedBodiesReceived("cust1", "cust2", "cust3");
+
+ Exchange out = template.send("direct:closeCursorsAtCommit",
+ e -> e.getMessage().setBody("select * from customer order by
id"));
+
+ assertNull(out.getException());
+ MockEndpoint.assertIsSatisfied(context);
+ }
+
+ @Test
+ public void commitsWhenTheExchangeIsDone() throws Exception {
+ row.expectedBodiesReceived("cust4");
+
+ Exchange out = template.send("direct:stream", e ->
e.getMessage().setBody(INSERT_RETURNING));
+
+ assertNull(out.getException());
+ MockEndpoint.assertIsSatisfied(context);
+ assertEquals(4, customers());
+ }
+
+ @Test
+ public void rollsBackWhenTheExchangeFails() throws Exception {
+ row.expectedBodiesReceived("cust4");
+
+ Exchange out = template.send("direct:streamThenFail", e ->
e.getMessage().setBody(INSERT_RETURNING));
+
+ assertInstanceOf(IllegalStateException.class, out.getException());
+ MockEndpoint.assertIsSatisfied(context);
+ assertEquals(3, customers());
+ }
+
+ private int customers() {
+ return new JdbcTemplate(db).queryForObject("select count(*) from
customer", Integer.class);
+ }
+
+ @Override
+ protected RouteBuilder createRouteBuilder() {
+ return new RouteBuilder() {
+ @Override
+ public void configure() {
+
from("direct:closeCursorsAtCommit").to("jdbc:closeCursorsAtCommit?outputType=StreamList")
+ .split(body()).streaming()
+ .setBody(simple("${body[ID]}"))
+ .to("mock:row")
+ .end();
+
+ from("direct:stream").to("jdbc:testdb?outputType=StreamList")
+ .split(body()).streaming()
+ .setBody(simple("${body[ID]}"))
+ .to("mock:row")
+ .end();
+
+
from("direct:streamThenFail").to("jdbc:testdb?outputType=StreamList")
+ .split(body()).streaming()
+ .setBody(simple("${body[ID]}"))
+ .to("mock:row")
+ .end()
+ .throwException(new IllegalStateException("Forced"));
+ }
+ };
+ }
+
+ private static Object invoke(Object target, Method method, Object[] args)
throws Throwable {
+ try {
+ return method.invoke(target, args);
+ } catch (InvocationTargetException e) {
+ throw e.getCause();
+ }
+ }
+
+ private static Object proxy(Class<?> type, InvocationHandler handler) {
+ return Proxy.newProxyInstance(
+ JdbcProducerStreamListTransactionTest.class.getClassLoader(),
new Class<?>[] { type }, handler);
+ }
+
+ /**
+ * Its connections close their result sets when they commit, as JDBC
allows with the {@code CLOSE_CURSORS_AT_COMMIT}
+ * holdability.
+ */
+ private static final class CloseCursorsAtCommitDataSource extends
DelegatingDataSource {
+
+ private CloseCursorsAtCommitDataSource(DataSource database) {
+ super(database);
+ }
+
+ @Override
+ public Connection getConnection() throws SQLException {
+ Connection connection = super.getConnection();
+ List<ResultSet> resultSets = new CopyOnWriteArrayList<>();
+ return (Connection) proxy(Connection.class, (proxy, method, args)
-> {
+ if ("commit".equals(method.getName())) {
+ for (ResultSet resultSet : resultSets) {
+ resultSet.close();
+ }
+ }
+ Object result = invoke(connection, method, args);
+ if (result instanceof Statement statement &&
method.getReturnType().isInterface()) {
+ return proxy(method.getReturnType(), (p, m, a) -> {
+ Object answer = invoke(statement, m, a);
+ if (answer instanceof ResultSet resultSet) {
+ resultSets.add(resultSet);
+ }
+ return answer;
+ });
+ }
+ return result;
+ });
+ }
+ }
+}
diff --git
a/components/camel-spring-parent/camel-spring-jdbc/src/generated/resources/META-INF/org/apache/camel/component/spring/jdbc/spring-jdbc.json
b/components/camel-spring-parent/camel-spring-jdbc/src/generated/resources/META-INF/org/apache/camel/component/spring/jdbc/spring-jdbc.json
index f0976172d55e..bec1fb82c776 100644
---
a/components/camel-spring-parent/camel-spring-jdbc/src/generated/resources/META-INF/org/apache/camel/component/spring/jdbc/spring-jdbc.json
+++
b/components/camel-spring-parent/camel-spring-jdbc/src/generated/resources/META-INF/org/apache/camel/component/spring/jdbc/spring-jdbc.json
@@ -50,7 +50,7 @@
"outputType": { "index": 3, "kind": "parameter", "displayName": "Output
Type", "group": "producer", "label": "", "required": false, "type": "enum",
"javaType": "org.apache.camel.component.jdbc.JdbcOutputType", "enum": [
"SelectOne", "SelectList", "StreamList" ], "deprecated": false, "autowired":
false, "secret": false, "defaultValue": "SelectList", "description":
"Determines the output the producer should use." },
"parameters": { "index": 4, "kind": "parameter", "displayName":
"Parameters", "group": "producer", "label": "", "required": false, "type":
"object", "javaType": "java.util.Map<java.lang.String, java.lang.Object>",
"prefix": "statement.", "multiValue": true, "deprecated": false, "autowired":
false, "secret": false, "description": "This is a multi-value option with
prefix: statement." },
"readSize": { "index": 5, "kind": "parameter", "displayName": "Read Size",
"group": "producer", "label": "", "required": false, "type": "integer",
"javaType": "int", "deprecated": false, "autowired": false, "secret": false,
"description": "The default maximum number of rows that can be read by a
polling query. The default value is 0." },
- "resetAutoCommit": { "index": 6, "kind": "parameter", "displayName":
"Reset Auto Commit", "group": "producer", "label": "", "required": false,
"type": "boolean", "javaType": "boolean", "deprecated": false, "autowired":
false, "secret": false, "defaultValue": true, "description": "Camel will set
the autoCommit on the JDBC connection to be false, commit the change after
executed the statement and reset the autoCommit flag of the connection at the
end, if the resetAutoCommit is true. If [...]
+ "resetAutoCommit": { "index": 6, "kind": "parameter", "displayName":
"Reset Auto Commit", "group": "producer", "label": "", "required": false,
"type": "boolean", "javaType": "boolean", "deprecated": false, "autowired":
false, "secret": false, "defaultValue": true, "description": "Camel will set
the autoCommit on the JDBC connection to be false, commit the change after
executed the statement and reset the autoCommit flag of the connection at the
end, if the resetAutoCommit is true. Wi [...]
"transacted": { "index": 7, "kind": "parameter", "displayName":
"Transacted", "group": "producer", "label": "", "required": false, "type":
"boolean", "javaType": "boolean", "deprecated": true, "deprecationNote": "Not
used: the producer does not read this option", "autowired": false, "secret":
false, "defaultValue": false, "description": "Deprecation note: Not used: the
producer does not read this option" },
"useGetBytesForBlob": { "index": 8, "kind": "parameter", "displayName":
"Use Get Bytes For Blob", "group": "producer", "label": "", "required": false,
"type": "boolean", "javaType": "boolean", "deprecated": false, "autowired":
false, "secret": false, "defaultValue": false, "description": "To read BLOB
columns as bytes (a byte[]) instead of a java.sql.Blob, which cannot be read
after the producer has closed the connection. This may be needed for certain
databases such as Oracle where [...]
"useHeadersAsParameters": { "index": 9, "kind": "parameter",
"displayName": "Use Headers As Parameters", "group": "producer", "label": "",
"required": false, "type": "boolean", "javaType": "boolean", "deprecated":
false, "autowired": false, "secret": false, "defaultValue": false,
"description": "Set this option to true to use the prepareStatementStrategy
with named parameters. This allows to define queries with named placeholders,
and use headers with the dynamic values for the query [...]
diff --git
a/docs/user-manual/modules/ROOT/pages/camel-4x-upgrade-guide-4_24.adoc
b/docs/user-manual/modules/ROOT/pages/camel-4x-upgrade-guide-4_24.adoc
index eb619a3b9c95..99debe0ba6cd 100644
--- a/docs/user-manual/modules/ROOT/pages/camel-4x-upgrade-guide-4_24.adoc
+++ b/docs/user-manual/modules/ROOT/pages/camel-4x-upgrade-guide-4_24.adoc
@@ -2130,6 +2130,20 @@ the body no longer looks finished: `hasNext()` and
`next()` now throw a `Runtime
redelivered over the same iterator (an error handler with redeliveries) found
no more rows and the exchange succeeded
with only the rows read before the failure. It now fails once the redeliveries
are exhausted.
+=== camel-jdbc, camel-spring-jdbc - outputType=StreamList commits when the
exchange is done
+
+With `outputType=StreamList` and `resetAutoCommit=true` (the default), the
producer now commits the transaction of the
+statement when the exchange completes, and rolls it back when the exchange
fails, instead of committing right after
+the statement is executed, before the route has read the rows. A driver may
close the result set at commit: with
+PostgreSQL and `statement.fetchSize`, reading the rows after the first batch
failed with
+`ERROR: portal "C_1" does not exist`. A statement with side effects that
returns rows (such as an `INSERT` or `DELETE`
+with `RETURNING`) is now rolled back when the exchange fails.
+
+A commit that fails at that time is logged at WARN and does not fail the
exchange, which is already complete. Before,
+a failed commit failed the exchange. For a statement with side effects that
returns rows, the route now reports
+success (and a consumer such as JMS acknowledges its message) although the
change is not committed: use another
+output type, such as `SelectList`, when a failed commit must fail the exchange.
+
=== camel-jgroups-raft - a body that does not convert to a byte array fails
the exchange
The `jgroups-raft` producer skipped a message body that has no conversion to
`byte[]`, such as a `Map`, a POJO or a
diff --git
a/dsl/camel-endpointdsl/src/generated/java/org/apache/camel/builder/endpoint/dsl/JdbcEndpointBuilderFactory.java
b/dsl/camel-endpointdsl/src/generated/java/org/apache/camel/builder/endpoint/dsl/JdbcEndpointBuilderFactory.java
index f8dcb9c4cd27..74ef835a424e 100644
---
a/dsl/camel-endpointdsl/src/generated/java/org/apache/camel/builder/endpoint/dsl/JdbcEndpointBuilderFactory.java
+++
b/dsl/camel-endpointdsl/src/generated/java/org/apache/camel/builder/endpoint/dsl/JdbcEndpointBuilderFactory.java
@@ -196,11 +196,14 @@ public interface JdbcEndpointBuilderFactory {
* Camel will set the autoCommit on the JDBC connection to be false,
* commit the change after executed the statement and reset the
* autoCommit flag of the connection at the end, if the resetAutoCommit
- * is true. If the JDBC connection doesn't support to reset the
- * autoCommit flag, you can set the resetAutoCommit flag to be false,
- * and Camel will not try to reset the autoCommit flag. When used with
- * XA transactions you most likely need to set it to false so that the
- * transaction manager is in charge of committing this tx.
+ * is true. With outputType=StreamList, the route reads the rows after
+ * the statement is executed, so the commit (or the rollback, when the
+ * exchange fails) happens when the exchange is done. If the JDBC
+ * connection doesn't support to reset the autoCommit flag, you can set
+ * the resetAutoCommit flag to be false, and Camel will not try to
reset
+ * the autoCommit flag. When used with XA transactions you most likely
+ * need to set it to false so that the transaction manager is in charge
+ * of committing this tx.
*
* The option is a: <code>boolean</code> type.
*
@@ -218,11 +221,14 @@ public interface JdbcEndpointBuilderFactory {
* Camel will set the autoCommit on the JDBC connection to be false,
* commit the change after executed the statement and reset the
* autoCommit flag of the connection at the end, if the resetAutoCommit
- * is true. If the JDBC connection doesn't support to reset the
- * autoCommit flag, you can set the resetAutoCommit flag to be false,
- * and Camel will not try to reset the autoCommit flag. When used with
- * XA transactions you most likely need to set it to false so that the
- * transaction manager is in charge of committing this tx.
+ * is true. With outputType=StreamList, the route reads the rows after
+ * the statement is executed, so the commit (or the rollback, when the
+ * exchange fails) happens when the exchange is done. If the JDBC
+ * connection doesn't support to reset the autoCommit flag, you can set
+ * the resetAutoCommit flag to be false, and Camel will not try to
reset
+ * the autoCommit flag. When used with XA transactions you most likely
+ * need to set it to false so that the transaction manager is in charge
+ * of committing this tx.
*
* The option will be converted to a <code>boolean</code> type.
*
diff --git
a/dsl/camel-endpointdsl/src/generated/java/org/apache/camel/builder/endpoint/dsl/SpringJdbcEndpointBuilderFactory.java
b/dsl/camel-endpointdsl/src/generated/java/org/apache/camel/builder/endpoint/dsl/SpringJdbcEndpointBuilderFactory.java
index 4967214c07a4..4fe23797e89d 100644
---
a/dsl/camel-endpointdsl/src/generated/java/org/apache/camel/builder/endpoint/dsl/SpringJdbcEndpointBuilderFactory.java
+++
b/dsl/camel-endpointdsl/src/generated/java/org/apache/camel/builder/endpoint/dsl/SpringJdbcEndpointBuilderFactory.java
@@ -192,11 +192,14 @@ public interface SpringJdbcEndpointBuilderFactory {
* Camel will set the autoCommit on the JDBC connection to be false,
* commit the change after executed the statement and reset the
* autoCommit flag of the connection at the end, if the resetAutoCommit
- * is true. If the JDBC connection doesn't support to reset the
- * autoCommit flag, you can set the resetAutoCommit flag to be false,
- * and Camel will not try to reset the autoCommit flag. When used with
- * XA transactions you most likely need to set it to false so that the
- * transaction manager is in charge of committing this tx.
+ * is true. With outputType=StreamList, the route reads the rows after
+ * the statement is executed, so the commit (or the rollback, when the
+ * exchange fails) happens when the exchange is done. If the JDBC
+ * connection doesn't support to reset the autoCommit flag, you can set
+ * the resetAutoCommit flag to be false, and Camel will not try to
reset
+ * the autoCommit flag. When used with XA transactions you most likely
+ * need to set it to false so that the transaction manager is in charge
+ * of committing this tx.
*
* The option is a: <code>boolean</code> type.
*
@@ -214,11 +217,14 @@ public interface SpringJdbcEndpointBuilderFactory {
* Camel will set the autoCommit on the JDBC connection to be false,
* commit the change after executed the statement and reset the
* autoCommit flag of the connection at the end, if the resetAutoCommit
- * is true. If the JDBC connection doesn't support to reset the
- * autoCommit flag, you can set the resetAutoCommit flag to be false,
- * and Camel will not try to reset the autoCommit flag. When used with
- * XA transactions you most likely need to set it to false so that the
- * transaction manager is in charge of committing this tx.
+ * is true. With outputType=StreamList, the route reads the rows after
+ * the statement is executed, so the commit (or the rollback, when the
+ * exchange fails) happens when the exchange is done. If the JDBC
+ * connection doesn't support to reset the autoCommit flag, you can set
+ * the resetAutoCommit flag to be false, and Camel will not try to
reset
+ * the autoCommit flag. When used with XA transactions you most likely
+ * need to set it to false so that the transaction manager is in charge
+ * of committing this tx.
*
* The option will be converted to a <code>boolean</code> type.
*