How to use Connection Pooling with Hazelcast jet JDBC?

Question

I am trying to use connection pooling with hazelcast jet jdbc but it is not allowing me to do it. I have Datasource bean from which I am fetching connection but it is not working. Here is my code:

Connection conn = ((DataSource)Ds.getBean("dataSourceName").getConnection();
BatchSource<Object> jdbcSource = Sources
                .jdbc(() -> conn,
                    (con, parallelism, index) -> {
                       // query execution
               }, r -> this.mapResultSet1(r, metaData));

But when I execute it gives me below error:

java.lang.IllegalArgumentException: "newConnectionFn" must be serializable
at com.hazelcast.jet.impl.util.Util.checkSerializable(Util.java:203)
at com.hazelcast.jet.impl.connector.ReadJdbcP.supplier(ReadJdbcP.java:77)
at com.hazelcast.jet.core.processor.SourceProcessors.readJdbcP(SourceProcessors.java:433)
at com.hazelcast.jet.pipeline.Sources.jdbc(Sources.java:1327)
at com.aivdata.impl.JDBCDataSource.readSource(JDBCDataSource.java:70)
at com.aivdata.services.AivDataFactory.lambda$1(AivDataFactory.java:123)
at java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1384)
at java.util.stream.ReferencePipeline$Head.forEach(ReferencePipeline.java:647)
Caused by: java.io.NotSerializableException: org.apache.tomcat.dbcp.dbcp2.PoolingDataSource$PoolGuardConnectionWrapper
at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1184)
at java.io.ObjectOutputStream.writeArray(ObjectOutputStream.java:1378)
at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1174)
at java.io.ObjectOutputStream.defaultWriteFields(ObjectOutputStream.java:1548)
at java.io.ObjectOutputStream.writeSerialData(ObjectOutputStream.java:1509)
at java.io.ObjectOutputStream.writeOrdinaryObject(ObjectOutputStream.java:1432)
at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1178)
at java.io.ObjectOutputStream.writeObject(ObjectOutputStream.java:348)
at com.hazelcast.jet.impl.util.Util.checkSerializable(Util.java:201)

How can I achieve this how to use connection pooling with Hazelcast jet?

score 1 · Answer 1 · answered Nov 02 '21 at 14:05

1

This works for me if I take connection inside Source Jdbc but not sure if it is using Pooling or creating new one.

BatchSource<Object> jdbcSource = Sources
                .jdbc(() -> {
                      Connection conn = ((DataSource)Ds.getBean("dataSourceName").getConnection();
                      return conn;
                  },
                    (con, parallelism, index) -> {
                       // query execution
               }, r -> this.mapResultSet1(r, metaData));

answered Nov 02 '21 at 14:05

user3458271

638
12
31

1

Yes, if the `Ds.getBean` returns the same instance every time you call it then this is more or less what meant by "make connection pool instance available from the serializable newConnectionFn function" – František Hartman Nov 02 '21 at 15:11
@FrantišekHartman thank you for the confirmation :) – user3458271 Nov 02 '21 at 15:25

score 0 · Answer 2 · answered Nov 01 '21 at 08:52

The exception tells you that the following function is not serializable:

() -> conn

Instead, you should use something similar to:

() -> DriverManager.getConnection(dbConnectionUrl)

The JDBC source uses the provided function to create a connection for each processor instance - this means that on each cluster member it will create n connections where n is equal to the local parallelism of the JDBC source. The default value for local parallelism is 1. You can modify this value by calling com.hazelcast.jet.pipeline.BatchStage#setLocalParallelism on the JDBC source.

If your job is a one-time batch job or a long-running batch job then it doesn't make sense to use a connection pool.

It might make sense to use a connection pool if you execute many small instances of a job, where creating a connection for each job execution would cause too much overhead. In that case you need to make your connection pool instance available from the serializable newConnectionFn function, e.g. by making it a singleton in a static field, initialized lazily.

I have spring boot application and their can be multiple request at time for that I need connection pooling I cannot create a new connection on each request. Ans sorry I didn't get this point "In that case you need to make your connection pool instance available from the serializable newConnectionFn function, e.g. by making it a singleton in a static field, initialized lazily." — user3458271, Nov 01 '21 at 09:23

How to use Connection Pooling with Hazelcast jet JDBC?

2 Answers2