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 43abcc7955ee CAMEL-26991: camel-sql - outputType=StreamList takes part
in the route transaction and applies the template options (#30044)
43abcc7955ee is described below
commit 43abcc7955ee0c97e8f03e5f7a1774eb42e5ab70
Author: Federico Mariani <[email protected]>
AuthorDate: Sun Nov 15 23:05:36 2026 +0100
CAMEL-26991: camel-sql - outputType=StreamList takes part in the route
transaction and applies the template options (#30044)
* CAMEL-26991: camel-sql - outputType=StreamList takes part in the rout…
…e transaction and applies the template options
StreamList gets and releases its connection with DataSourceUtils and
applies the JdbcTemplate statement settings.
The CamelSqlDataSource header keeps the template options of the endpoint.
* CAMEL-26991: camel-sql - docs: in a transacted route, read the Stream…
…List rows within the transacted route
The commit at the end of the transaction releases the connection of the
iterator.
Co-Authored-By: Claude <[email protected]>
---
.../apache/camel/catalog/docs/sql-component.adoc | 16 +++
.../camel-sql/src/main/docs/sql-component.adoc | 16 +++
.../camel/component/sql/DefaultSqlEndpoint.java | 13 ++-
.../camel/component/sql/ResultSetIterator.java | 17 ++-
.../apache/camel/component/sql/SqlProducer.java | 44 +++++--
.../sql/SqlProducerStreamListJdbcTemplateTest.java | 128 +++++++++++++++++++++
.../ROOT/pages/camel-4x-upgrade-guide-4_24.adoc | 13 +++
7 files changed, 236 insertions(+), 11 deletions(-)
diff --git
a/catalog/camel-catalog/src/generated/resources/org/apache/camel/catalog/docs/sql-component.adoc
b/catalog/camel-catalog/src/generated/resources/org/apache/camel/catalog/docs/sql-component.adoc
index 6cd103995580..413895a4eff9 100644
---
a/catalog/camel-catalog/src/generated/resources/org/apache/camel/catalog/docs/sql-component.adoc
+++
b/catalog/camel-catalog/src/generated/resources/org/apache/camel/catalog/docs/sql-component.adoc
@@ -274,6 +274,22 @@ YAML::
----
====
+Outside a transaction, the iterator keeps the connection and the result set
open until the exchange is complete.
+In a transacted route, the query runs on the connection of the transaction,
and the commit at the end of the
+transaction releases that connection: read the rows within the transacted
route (for example with the Splitter EIP),
+not after it (for example in a route that calls the transacted route).
+The query gets the `fetchSize` and `template.*` options (such as
`template.maxRows` and `template.queryTimeout`)
+like the other output types.
+No `CamelSqlRowCount` header is set, as the number of rows is not known up
front.
+
+Whether the rows are loaded as needed depends on the JDBC driver. For example,
the PostgreSQL driver only fetches
+`fetchSize` rows at a time when the connection has auto-commit off (as in a
transacted route); otherwise it loads all
+the rows when the query is executed.
+
+The consumer supports `outputType=StreamList` too, but its iterator can only
be used while the poll runs: process it
+in the route of the consumer (for example with the Splitter EIP), and do not
hand it over to another thread (such as a
+`seda` endpoint).
+
=== Generated keys
If you insert data using SQL INSERT, then the RDBMS may support auto
diff --git a/components/camel-sql/src/main/docs/sql-component.adoc
b/components/camel-sql/src/main/docs/sql-component.adoc
index 6cd103995580..413895a4eff9 100644
--- a/components/camel-sql/src/main/docs/sql-component.adoc
+++ b/components/camel-sql/src/main/docs/sql-component.adoc
@@ -274,6 +274,22 @@ YAML::
----
====
+Outside a transaction, the iterator keeps the connection and the result set
open until the exchange is complete.
+In a transacted route, the query runs on the connection of the transaction,
and the commit at the end of the
+transaction releases that connection: read the rows within the transacted
route (for example with the Splitter EIP),
+not after it (for example in a route that calls the transacted route).
+The query gets the `fetchSize` and `template.*` options (such as
`template.maxRows` and `template.queryTimeout`)
+like the other output types.
+No `CamelSqlRowCount` header is set, as the number of rows is not known up
front.
+
+Whether the rows are loaded as needed depends on the JDBC driver. For example,
the PostgreSQL driver only fetches
+`fetchSize` rows at a time when the connection has auto-commit off (as in a
transacted route); otherwise it loads all
+the rows when the query is executed.
+
+The consumer supports `outputType=StreamList` too, but its iterator can only
be used while the poll runs: process it
+in the route of the consumer (for example with the Splitter EIP), and do not
hand it over to another thread (such as a
+`seda` endpoint).
+
=== Generated keys
If you insert data using SQL INSERT, then the RDBMS may support auto
diff --git
a/components/camel-sql/src/main/java/org/apache/camel/component/sql/DefaultSqlEndpoint.java
b/components/camel-sql/src/main/java/org/apache/camel/component/sql/DefaultSqlEndpoint.java
index 68e9d19576bc..edb42b47d149 100644
---
a/components/camel-sql/src/main/java/org/apache/camel/component/sql/DefaultSqlEndpoint.java
+++
b/components/camel-sql/src/main/java/org/apache/camel/component/sql/DefaultSqlEndpoint.java
@@ -573,13 +573,22 @@ public abstract class DefaultSqlEndpoint extends
DefaultPollingEndpoint implemen
}
public ResultSetIterator queryForStreamList(Connection connection,
Statement statement, ResultSet rs) throws SQLException {
+ return queryForStreamList(connection, null, statement, rs);
+ }
+
+ /**
+ * @param dataSource the data source the connection was obtained from, so
the iterator releases the connection
+ * without closing the connection of a transaction
+ */
+ public ResultSetIterator queryForStreamList(Connection connection,
DataSource dataSource, Statement statement, ResultSet rs)
+ throws SQLException {
if (outputClass == null) {
RowMapper<?> rowMapper = rowMapperFactory.newColumnRowMapper();
- return new ResultSetIterator(connection, statement, rs, rowMapper);
+ return new ResultSetIterator(connection, dataSource, statement,
rs, rowMapper);
} else {
Class<?> outputClazz =
getCamelContext().getClassResolver().resolveClass(outputClass);
RowMapper<?> rowMapper =
rowMapperFactory.newBeanRowMapper(outputClazz);
- return new ResultSetIterator(connection, statement, rs, rowMapper);
+ return new ResultSetIterator(connection, dataSource, statement,
rs, rowMapper);
}
}
diff --git
a/components/camel-sql/src/main/java/org/apache/camel/component/sql/ResultSetIterator.java
b/components/camel-sql/src/main/java/org/apache/camel/component/sql/ResultSetIterator.java
index 078b0c3c67fe..5ae7fa6ed6ee 100644
---
a/components/camel-sql/src/main/java/org/apache/camel/component/sql/ResultSetIterator.java
+++
b/components/camel-sql/src/main/java/org/apache/camel/component/sql/ResultSetIterator.java
@@ -25,16 +25,20 @@ import java.util.Iterator;
import java.util.NoSuchElementException;
import java.util.concurrent.atomic.AtomicBoolean;
+import javax.sql.DataSource;
+
import org.apache.camel.RuntimeCamelException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.RowMapper;
+import org.springframework.jdbc.datasource.DataSourceUtils;
public class ResultSetIterator implements Iterator<Object>, Closeable {
private static final Logger LOG =
LoggerFactory.getLogger(ResultSetIterator.class);
private final Connection connection;
+ private final DataSource dataSource;
private final Statement statement;
private final ResultSet resultSet;
private final RowMapper<?> rowMapper;
@@ -45,7 +49,18 @@ public class ResultSetIterator implements Iterator<Object>,
Closeable {
public ResultSetIterator(Connection connection, Statement statement,
ResultSet resultSet,
RowMapper<?> rowMapper) throws SQLException {
+ this(connection, null, statement, resultSet, rowMapper);
+ }
+
+ /**
+ * @param dataSource the data source the connection was obtained from with
{@link DataSourceUtils}, so the
+ * connection is released with {@link
DataSourceUtils#releaseConnection}, which does not close the
+ * connection of a transaction
+ */
+ public ResultSetIterator(Connection connection, DataSource dataSource,
Statement statement, ResultSet resultSet,
+ RowMapper<?> rowMapper) throws SQLException {
this.connection = connection;
+ this.dataSource = dataSource;
this.statement = statement;
this.resultSet = resultSet;
this.rowMapper = rowMapper;
@@ -120,7 +135,7 @@ public class ResultSetIterator implements Iterator<Object>,
Closeable {
private void safeCloseConnection() {
try {
- connection.close();
+ DataSourceUtils.doReleaseConnection(connection, dataSource);
} catch (SQLException e) {
LOG.warn("Error by closing connection: {}", e, e);
}
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 c85c6379b3ec..cbbcb85247f8 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
@@ -37,8 +37,8 @@ import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.PreparedStatementCreator;
+import org.springframework.jdbc.datasource.DataSourceUtils;
-import static org.springframework.jdbc.support.JdbcUtils.closeConnection;
import static org.springframework.jdbc.support.JdbcUtils.closeResultSet;
import static org.springframework.jdbc.support.JdbcUtils.closeStatement;
@@ -251,22 +251,27 @@ public class SqlProducer extends DefaultProducer {
LOG.trace("processStreamList: {}", preparedQuery);
// do not use the jdbcTemplate as it will auto-close connection/ps/rs
when exiting the execute method
- // and we need to keep the connection alive while routing and close it
when the Exchange is done being routed
+ // and we need to keep the connection alive while routing and close it
when the Exchange is done being routed.
+ // Get and release the connection with DataSourceUtils, as the
jdbcTemplate does, so a transacted route
+ // uses the connection of its transaction, and does not close it
+ JdbcTemplate template = fetchJdbcTemplate(exchange);
+ DataSource ds = template.getDataSource();
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
- con = fetchJdbcTemplate(exchange).getDataSource().getConnection();
+ con = DataSourceUtils.doGetConnection(ds);
ps = statementCreator.createPreparedStatement(con);
ResultSetIterator iterator = null;
+ applyStatementSettings(ps, template);
populateStatement(ps, exchange, sql, preparedQuery);
boolean isResultSet = ps.execute();
if (isResultSet) {
rs = ps.getResultSet();
- iterator = getEndpoint().queryForStreamList(con, ps, rs);
+ iterator = getEndpoint().queryForStreamList(con, ds, ps, rs);
// we do not know the row count so we cannot set a ROW_COUNT
header
// defer closing the iterator when the exchange is complete
@@ -276,18 +281,32 @@ public class SqlProducer extends DefaultProducer {
// and return the connection to the pool right away
exchange.getIn().setHeader(SqlConstants.SQL_UPDATE_COUNT,
ps.getUpdateCount());
closeStatement(ps);
- closeConnection(con);
+ DataSourceUtils.releaseConnection(con, ds);
}
return iterator;
} catch (Exception e) {
// in case of exception then close all this before rethrow
- closeConnection(con);
- closeStatement(ps);
closeResultSet(rs);
+ closeStatement(ps);
+ DataSourceUtils.releaseConnection(con, ds);
throw e;
}
}
+ /**
+ * Applies the statement settings of the template (template.* options), as
the template does for the statements it
+ * creates.
+ */
+ private static void applyStatementSettings(PreparedStatement ps,
JdbcTemplate template) throws SQLException {
+ if (template.getFetchSize() != -1) {
+ ps.setFetchSize(template.getFetchSize());
+ }
+ if (template.getMaxRows() != -1) {
+ ps.setMaxRows(template.getMaxRows());
+ }
+ DataSourceUtils.applyTimeout(ps, template.getDataSource(),
template.getQueryTimeout());
+ }
+
private void populateStatement(PreparedStatement ps, Exchange exchange,
String sql, String preparedQuery)
throws SQLException {
@@ -344,7 +363,16 @@ public class SqlProducer extends DefaultProducer {
protected JdbcTemplate fetchJdbcTemplate(Exchange exchange) {
DataSource ds =
exchange.getMessage().getHeader(SqlConstants.SQL_DATA_SOURCE, DataSource.class);
if (ds != null) {
- return new JdbcTemplate(ds);
+ // same settings (template.* options) as the template of the
endpoint
+ JdbcTemplate answer = new JdbcTemplate(ds);
+ answer.setFetchSize(jdbcTemplate.getFetchSize());
+ answer.setMaxRows(jdbcTemplate.getMaxRows());
+ answer.setQueryTimeout(jdbcTemplate.getQueryTimeout());
+ answer.setIgnoreWarnings(jdbcTemplate.isIgnoreWarnings());
+
answer.setSkipResultsProcessing(jdbcTemplate.isSkipResultsProcessing());
+
answer.setSkipUndeclaredResults(jdbcTemplate.isSkipUndeclaredResults());
+
answer.setResultsMapCaseInsensitive(jdbcTemplate.isResultsMapCaseInsensitive());
+ return answer;
}
return jdbcTemplate;
}
diff --git
a/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerStreamListJdbcTemplateTest.java
b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerStreamListJdbcTemplateTest.java
new file mode 100644
index 000000000000..9645b885c1d6
--- /dev/null
+++
b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlProducerStreamListJdbcTemplateTest.java
@@ -0,0 +1,128 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.camel.component.sql;
+
+import java.util.List;
+import java.util.Map;
+
+import org.apache.camel.Exchange;
+import org.apache.camel.builder.RouteBuilder;
+import org.apache.camel.component.mock.MockEndpoint;
+import org.apache.camel.spi.Registry;
+import org.apache.camel.spring.spi.SpringTransactionPolicy;
+import org.apache.camel.test.junit6.CamelTestSupport;
+import org.junit.jupiter.api.Test;
+import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.jdbc.datasource.DataSourceTransactionManager;
+import org.springframework.jdbc.datasource.embedded.EmbeddedDatabase;
+import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;
+import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertFalse;
+
+/**
+ * With outputType=StreamList the producer gets its connection and statement
settings like the other output types: it
+ * takes part in the transaction of the route and applies the template options
of the endpoint.
+ */
+public class SqlProducerStreamListJdbcTemplateTest extends CamelTestSupport {
+
+ private static final String SELECT = "select * from projects order by id";
+
+ private EmbeddedDatabase db;
+
+ @Override
+ protected void bindToRegistry(Registry registry) throws Exception {
+ db = new EmbeddedDatabaseBuilder()
+ .setName(getClass().getSimpleName())
+ .setType(EmbeddedDatabaseType.H2)
+ .addScript("sql/createAndPopulateDatabase.sql").build();
+ registry.bind("db", db);
+
+ DataSourceTransactionManager txMgr = new
DataSourceTransactionManager(db);
+ SpringTransactionPolicy txPolicy = new SpringTransactionPolicy();
+ txPolicy.setTransactionManager(txMgr);
+ txPolicy.setPropagationBehaviorName("PROPAGATION_REQUIRED");
+ registry.bind("required", txPolicy);
+ }
+
+ @Override
+ public void doPostTearDown() throws Exception {
+ if (db != null) {
+ db.shutdown();
+ }
+ }
+
+ @Test
+ public void testStreamListSeesTheRowsOfTheTransaction() throws Exception {
+ MockEndpoint mock = getMockEndpoint("mock:tx");
+ mock.expectedMessageCount(1);
+
+ Exchange out = template.send("direct:tx", e ->
e.getIn().setBody(null));
+
+ assertFalse(out.isFailed(), "exchange failed: " + out.getException());
+ mock.assertIsSatisfied();
+ assertEquals(99, ((Number)
mock.getExchanges().get(0).getIn().getBody(Map.class).get("ID")).intValue());
+ // the statement after the stream used the connection of the
transaction, which is committed
+ JdbcTemplate jdbc = new JdbcTemplate(db);
+ assertEquals(2, jdbc.queryForObject("select count(*) from projects
where id in (99, 100)", Integer.class));
+ }
+
+ @Test
+ public void testStreamListAppliesTemplateMaxRows() throws Exception {
+ MockEndpoint mock = getMockEndpoint("mock:row");
+ mock.expectedMessageCount(1);
+ mock.setAssertPeriod(100);
+
+ template.sendBody("direct:streamMaxRows", null);
+
+ mock.assertIsSatisfied();
+ }
+
+ @Test
+ public void testDataSourceHeaderKeepsTemplateMaxRows() {
+ List<?> rows = template.requestBodyAndHeader("direct:selectMaxRows",
null, SqlConstants.SQL_DATA_SOURCE, db,
+ List.class);
+
+ assertEquals(1, rows.size());
+ }
+
+ @Override
+ protected RouteBuilder createRouteBuilder() {
+ return new RouteBuilder() {
+ @Override
+ public void configure() {
+ from("direct:tx")
+ .transacted("required")
+ .to("sql:insert into projects values (99, 'TX',
'ASF')?dataSource=#db")
+ .to("sql:select * from projects where id =
99?dataSource=#db&outputType=StreamList")
+ .split(body()).streaming()
+ .to("mock:tx")
+ .end()
+ .to("sql:insert into projects values (100, 'TX',
'ASF')?dataSource=#db");
+
+ from("direct:streamMaxRows")
+ .to("sql:" + SELECT +
"?dataSource=#db&outputType=StreamList&template.maxRows=1")
+ .split(body()).streaming()
+ .to("mock:row");
+
+ from("direct:selectMaxRows")
+ .to("sql:" + SELECT +
"?dataSource=#db&outputType=SelectList&template.maxRows=1");
+ }
+ };
+ }
+}
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 36f0865ad185..cbfee955c23d 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
@@ -3759,6 +3759,19 @@ A single `Map` body is now one batch item, and so is a
single `Map` in the `Came
(sql-stored). Before, its entries were used as the batch items and the call
failed (sql: no value found for the named
parameters, sql-stored: an `SQLException`).
+=== camel-sql - outputType=StreamList takes part in the transaction and
applies the template options
+
+The producer with `outputType=StreamList` now gets its connection like the
other output types: in a transacted route
+the query runs on the connection of the transaction, so it sees the changes
made earlier in that transaction, and the
+connection is no longer closed when the iterator is done. Before, the query
ran on a connection of its own, outside the
+transaction. In a transacted route, read the rows within the transacted route
(for example with the Splitter EIP), as
+the commit at the end of the transaction releases the connection: they can no
longer be read after it, for example in
+a route that calls the transacted route. The `template.*` options (such as
`template.maxRows`, `template.fetchSize`
+and `template.queryTimeout`) now apply to the query too: before, they were
ignored.
+
+With the `CamelSqlDataSource` header, the `template.*` options of the endpoint
now apply for every output type. Before,
+they were ignored for the `DataSource` of the header.
+
=== camel-sql - JdbcKeyValueRepository: deleteIfExpiredString, and putIfAbsent
after a duplicate key
When `JdbcKeyValueRepository` reads an entry that has expired, it deletes the
row with the new `deleteIfExpiredString`