Open or close the connection instantly after DB operation or use same connection throughout method

Question

i am working on a java web application where Database connection pooling is managed by tomcat-jdbc. I need to know which one is best practice 1.use same connection for multiple DB interactions in same method. 2. Open and close the DB connection per DB operation

Answer 1

Since the connection pool is managed by tomcat-jdbc, you'll most likely get an already existing connection from the pool instead of opening/closing a new connection each time, so from the performance point of view it shouldn't really matter if you get a new connection per operation or reuse the same connection per multiple operations.

But you'll most likely need to execute all operations in the same method within the same database transaction (this depends on your use case). This implies that you'll also have to use the same connection for all of those operations.

In general I would recommend to reuse the same connection for the scope of the method. An even better approach would be to use some high level framework that takes care of both database connections and transactions for you, e.g. Spring.