Java mysql connection time

Question

I have a java program running 24/7. It accesses mysql database every 3 seconds only from 9.am to 3.PM. In this case when should I should open and close the MySql connection?

1. Should I open and close every 3 sec?

2. Should I open at 9.am and close at 3.pm?

3. Should I open once when the program start and never close it. But reconnect when connection is closed automatically and exceptions is thrown?

Answer 1

Why don't you simply use a connection pool. If that is too tedious since the connection will be frequently used you can reuse the same one imho.

Answer 2

1. While it is true that setting up and tearing down a MySQL connection is relatively cheap (when compared to, for example, Oracle), doing it every 3 seconds is a waste of resources. I'd cache the connection and save the overhead of creating a new database connection every time.
2. This depends very much on the situation. Do you connect over a WAN, is the MySQL server shared with other applications or will you be the only user (or at least will your application create most of the load?) If the database is mostly yours and it is near enough, there is little benefit in setting up and tearing down the connection daily.
3. This is what most applications do and this is what I'd recommend you do by default.

If you do not want to leave connections open overnight, you might able to configure your connection pool to open connections on demand and close them when they have been idle for a certain period of time -- say, 15 minutes. This would give you the benefit of being able to query the database whenever you wish and not having too many idle connections.