I'm working on a game server which stores accounts in a mysql DB thru JDBC.
At first, I initiated a connection to the database once and only once: at server start up. All went well until I noticed that after 8 hours of database inactivity, the connection to the database gets broken...
As my code wasn't ready for that behavior, I did a bit of refactoring. Now, every time my server has to perform some database operation, I open a connection. And when the operation is done, I close the database connection.
While refactoring, I was thinking "cool this is how I should have done since the beginning" ... it works but now I'm afraid of performance issue. And maybe other issues I didn't consider?
What do you think?
Designer and developer on Faëria: Strategy Card Game (www.faeria.net). The server is 100% Java.
@Jiem_ on Twitter (http://twitter.com/Jiem_)
Connection pools were designed for this. Application servers generally provide one. Actually, I don't know of any good introduction to connection pools off the top of my head, try to google around a bit or have a look at your application server documentation.
To add to martins comments
a connection pool stores connections for you (as creating a connection can be quite slow).
Before giving you one it checks if any of it's connections are still alive, if not it gets you an alive one. So it is a cross between your two methods of database connecting.