How to solve Mysql exception No operations allowed after statement closed

This article mainly explains "how to solve Mysql exception No operations allowed after statement closed". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "how to solve Mysql exception No operations allowed after statement closed".

The reason for this exception is that Mysql has done a handling for ultra-long-term DB connections after 5, that is, if a DB connection has elapsed 8 hours without any operation, Mysql will automatically close the connection. So when using connection pooling, although the connection object is still there, it will always report this exception when linking to the database. The solution is simple and can be found on Mysql's official website. There are two ways.

# the first method is to add a parameter to the DB connection string.

In this way, if the current link is broken due to a timeout, the driver will automatically reconnect to the database.

Jdbc:mysql://localhost:3306/makhtutat?autoReconnect=true

However, Mysql does not recommend this method. Because after the failure of the first DB operation, if the reconnection effect occurs before the success of the second DB.

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 there is a reconnection, some user variables and temporary table information will also be lost. # another method is recommended by Mysql and requires programmers to handle exceptions manually.

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 FROM bar ORDER BY baz"; rs = stmt.executeQuery (query); while (rs.next ()) {} all.close () transactionCompleted = true;} catch (SQLException sqlEx) {String sqlState = sqlEx.getSQLState () / / this 08S01 is the abnormal sql state. Manual relink is fine by dealing with it separately. If ("08S01" .equals (sqlState) | | "40001" .equals (sqlState)) {retryCount--;} else {retryCount = 0 }} finally {all close:}} while (! transactionCompleted & & (retryCount > 0));}} at this point, I believe you have a better understanding of "how to solve Mysql exception No operations allowed after statement closed". You might as well do it in practice! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!