0

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

Avyaan
  • 1,285
  • 6
  • 20
  • 47

1 Answers1

0

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.

Forketyfork
  • 7,416
  • 1
  • 26
  • 33
  • suppose in my case transaction is not required. only GET operations(select operations) are there. – Avyaan Jul 04 '22 at 09:42
  • To make the code future-proof and not depend on the way the connection pool works, I would still recommend to reuse the connection. Also, since you say you have multiple operations inside the same method, there's still benefit in using transactions to make sure the results of those operations return consistent results, even if they are all selects. – Forketyfork Jul 04 '22 at 09:45