Do I need to close the connection in each query?

Asked

Viewed 649 times

7

Currently I have a web application that queries multiple databases in the same controller. For each database I request a connection at the server startup and keep them in a static class where the respective Daos have used and closed on context Destroy of the application.

My question is, do I really need to open and close the connection to every controller call? The way I use it today won’t bring me performance problems?

java database performance
  • 1

    I have the same doubt, but beyond the performance I would still ask about the safety of keeping an open connection throughout the application’s lifetime.

  • 1

    You don’t have to, as long as you use the Try-with-Resources, because it closes for you. Now if you want to reuse, the interesting is to work with connections pool.

  • @diegofm would still not be opening and closing the connection?

  • 1

    would not be, because the connection pool tells the application that closed the connection but keeps it inside the pool to be reused. This is the magic of the connection pool to reuse them for better performance.

  • Yes, in case, the pool would be even the best solution. Thank you :)

1 answer

5


My question is, I really need to open and close the connection to every controller call?

Database connections are usually stateless. I mean, open up, do what you gotta do and close up.

I particularly see no reason to hold an open connection with the bank, even because there are datasource settings which precisely check whether there are idle connections and close them.

When I say "do what you have to do" it does not mean executing a single query. You can execute N queries, N transactions and etc.

If you have a customer list to register at the bank, for example, you do not need to open and close a connection for each item on that list. You can very well open a connection, iterate the list by entering all customers in the bank through that connection and then close it. The important thing is not to hold the connection open "forever".

The way I use it today won’t bring me performance problems?

If you use Pool of Connections no. The pool is designed precisely for the purpose of facilitating/making it less costly to redeem a bank connection.

  • 1

    Thank you very much, I had already thought about using Pool Connections. It seems that I will not be able to escape.

Browser other questions tagged java database performance

You are not signed in. Login or sign up in order to post.