3

I have a java web server with a lot of methods querying each my mysql database through JDBC. At the end of each method called, the Statement and the Resultset are automatically closed. Nevertheless, I am never closing the Connection since the whole application is using the very same connection to the database.

Here is thus my questions :

  • Am I doing good regarding the connection or should I create and close a connection in each method (meaning almost after each query) ?

  • In case I create a connection for each method, won't this be really expensive for my server low ressources ?

PS : None of the already asked questions on JDBC that I found was tackling this issue.

user3793589
  • 418
  • 3
  • 14
  • 1
    Consider using some kind of connection pool, this way, when you "close" the connection it is returned to the pool. It will also allow you to limit the number of active connections your application might be able to make – MadProgrammer Feb 08 '15 at 08:12
  • see http://stackoverflow.com/questions/2835090/how-to-establish-a-connection-pool-in-jdbc – Steve Atkinson Feb 08 '15 at 08:13
  • What do you mean, automatically closed? – user253751 Feb 08 '15 at 08:14
  • Thank you for your answer @MadProgrammer. So, I should close the connection for each method. Right ? – user3793589 Feb 08 '15 at 08:14
  • @immibis, I am actually closing them myself right away after I get what I want. – user3793589 Feb 08 '15 at 08:16
  • 1
    no, you use your connection pool api to return the connection to the pool. In a nutshell, when your app starts there are no connections. As you process requests, you request a connection. If there is already one open, that is returned to you, otherwise a new one is created (up to a limit) and that is returned to you. in your servlet destry() method you terminate all the connections in the pool – Steve Atkinson Feb 08 '15 at 08:16
  • @user3793589 Yes and no. `Connection`s can go stale over a period of time and may be closed by the database without notification to you, so the next time you use a `Connection`, you'll get some kind of `Exception`, but on the other hand, establishing a `Connection` can be expensive, which might slow down the application. Better to use a `Connection` pool and let it deal with it... – MadProgrammer Feb 08 '15 at 08:17
  • @SteveAtkinson, MadProgrammer, thank you for your inputs. I will read a bit on the connection pool concept. Will be right back. Regards, – user3793589 Feb 08 '15 at 08:24

1 Answers1

1

MadProgrammer and Steve Atkinson 's comments under the main questions helped me. Actually having a pool of connections is a better practice than having to open and close a connection for each queries or using the very same connection for the whole application.

user3793589
  • 418
  • 3
  • 14