MySQLNonTransientConnectionException in jdbc program at run time

Question

Hi I have created jdbc mysql connection. my program works fine for simple execution of query.

But if i run the same program for more than 10 hour and execute query then i receives the following mysql exception.

I have not used close() method anywhere. i created database connection and opened it forever and always execute query. there is no where that i explicitly mentioned timeout for connection. i am unable to identify the problem

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Connection.close() has already been called. Invalid operation in this state. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

sample code for database connection:

String driver = PropertyReader.getDriver(); String url = dbURLPath; Class.forName(driver); connectToServerDB = DriverManager.getConnection(url); connectToServerDB.setAutoCommit(false);

Answer 1

MySQL terminates a connection after 8 hour timeout. You can modify the timeout by setting wait_timeout variable in MySQL.

Yet, generally it is not such a good idea for an application to hold a connection for such a long time. A better approach is to set up a connection pool using a pooling API such as Apache DBCP and retrieve connections from the pool rather than directly though a driver. A connection pool will also take care about re-establishing a pooled connection if it dies for some reason, including timeouts.