MySQL exception: mysqlnontransientconnectionexception: No operations allowed after statement closed

Mysqlnontransientconnectionexception: No operations allowed after statement closed

The reason for this exception is that MySQL has done a processing for the long-time dB connection after 5, that is, if a DB connection has passed 8 hours without any operation, mySQL automatically closes the connection. Therefore, when using the connection pool, although the connection object is still connected to the database, this exception will always be reported. The solution is easy to find on the official MySQL website.

There are two methods,

First, add a parameter after the DB connection string.

In this case, if the current link is disconnected due to timeout, the driver will automatically reconnect to the database.

JDBC: mysql: // localhost: 3306/makhtutat? Autoreconnect = true

However, this method is not recommended for MySQL. This is because if the first database operation fails and the second database is successfully reconnected. This failed operation will not be in the same transaction. If the second db operation is successful, the transaction will be committed.

conn.createStatement().execute(  "UPDATE checking_account SET balance = balance - 1000.00 WHERE customer='Smith'");conn.createStatement().execute(  "UPDATE savings_account SET balance = balance + 1000.00 WHERE customer='Smith'");conn.commit();

Of course, if a reconnection occurs, some user variables and temporary table information will also be lost.

Another method is recommended by MySQL, which requires programmers to manually handle exceptions.

Public void dobusinessop () throws sqlexception {connection conn = NULL; statement stmt = NULL; resultset rs = NULL; int retrycount = 5; Boolean transactioncompleted = false; do {try {conn = getconnection (); // assume getting this from a // javax. SQL. datasource, or the // Java. SQL. drivermanager Conn. setautocommit (false); retrycount = 0; stmt = Conn. createstatement (); string query = "select Foo fr Om bar order by Baz "; RS = stmt.exe cutequery (query); While (RS. next () {} All. close () transactioncompleted = true;} catch (sqlexception sqlex) {string sqlstate = sqlex. getsqlstate (); // The 08s01 is the SQL status of this exception. You can simply manually reconnect the link. If ("08s01 ". equals (sqlstate) | "40001 ". equals (sqlstate) {retrycount --;} else {retrycount = 0 ;}} finally {all close :}} while (! Transactioncompleted & (retrycount> 0 ));}}