Java – persistent connection with JDBC to MySQL

I have an application that uses JDBC to connect to MySQL In some cases, the JDBC connection is idle for hours (or even days) and it loses its connection to MySQL, except when trying to execute a query Is this the best solution?

Solution

Keeping connected during open hours is a bad practice The database will be forced to shut down after being opened for a period of time You should write your JDBC code so that it always closes the connections (and statements and result sets) in the finally block of the identical try block you have obtained them to prevent resource leakage

However, getting a connection on each hiccup is really an expensive task, so you want to use connection pooling Decent connection pooling will automatically manage open, test, reuse and close connections But that doesn't mean you can change the JDBC code to never close You still need to close them because this will actually release the underlying connections back to the pool for future reuse

There are several connection pools, such as Apache DBCP for single thread, which has poor performance, and c3p0 for multithreading, which has better performance. Tomcat JDBC uses Tomcat for you and does not like to use built-in DBCP because of poor performance

You can create a connection pool programmatically. The following is an example of c3p0:

ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setDriverClass("com.MysqL.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:MysqL://localhost:3306/dbname");
dataSource.setUser("username");
dataSource.setPassword("password");

Execute once during application startup, and then use it as follows:

Connection connection = null;
// ...

try {
    connection = dataSource.getConnection();
    // ...
} finally {
    // ...
    if (connection != null) try { connection.close(); } catch (sqlException ignore) {}
}

When you run in a JNDI function container (such as a servlet container) (such as Tomcat), you can also declare it as Java sql. Datasource (Tomcat specific manual here) It will then use the connection pooling facilities provided by the servlet container The data source can then be obtained as follows:

DataSource dataSource = (DataSource) new InitialContext().lookup("java:comp/env/jdbc/YourDataSourceName");