Create new java.sql.Connection for every new request

Question

Currently, in my spring boot application has the capability to create connection object based on the DataSource given. In my manager layer, I have annotated with @Transactional. I need to have a new connection when entering methods in manager, even though we have an existing connection.

Generally speaking that would be a bad idea. Setting up a connection is an expensive operation. It is far more efficient to reuse a connection from the pool. Is there any special requirement why you can't reuse a connection? — fhossfel, Aug 28 '17 at 16:36
Yes, it looks like a special requirement @fhossfel, we are using a temporary tables on the connection — Vinoth Rajendran, Aug 29 '17 at 04:12
Why don't you clean the temporary tables on commit? At least with Oracle that is the default. — fhossfel, Aug 29 '17 at 08:10

score 1 · Answer 1 · edited Aug 28 '17 at 17:09

Although it is not a good idea, disabling connection pooling should always return a new connection. How to completely disable Connection Pooling in Spring / Tomcat?

NOTE: This class is not an actual connection pool; it does not actually pool Connections. It just serves as simple replacement for a full-blown connection pool, implementing the same standard interface, but creating new Connections on every call.

https://github.com/spring-projects/spring-framework/blob/master/spring-jdbc/src/main/java/org/springframework/jdbc/datasource/SimpleDriverDataSource.java

score 0 · Answer 2 · answered Aug 28 '17 at 16:42

I would not create new connections every time you call any sort of transactional method. This is expensive and very error-prone. You should be defining your data sources and then choosing which one you want to use.

Also, see Spring Boot Configure and Use Two DataSources

Create new java.sql.Connection for every new request

2 Answers2