Java 如何修复 com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException：连接关闭后不允许操作。例外？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27908953/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to fix com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed. exception?
提问by Search Results Web results Pi
This is is my query to save a record.
这是我保存记录的查询。
public void saveTotal(JTextField txtGtotal, JTextField txtPTotal) {
try {
ResultSet rs = JDBC.getData("select MAX(or_id) as or_id from `order`");
if (rs.first()) {
if (rs.getInt("or_id") > 0) {
try {
String date1 = new Validation().today();
boolean b1 = JDBC.putData("insert into transaction(tr_date, amount, tr_type) values ('" + date1 + "' , '" + txtGtotal.getText() + "' , 'order')");
if (b1) {
try {
ResultSet rs1 = JDBC.getData("select MAX(tr_id) as tr_id from transaction");
if (rs1.first()) {
try {
boolean b2 = JDBC.putData("insert into transaction(tr_date, amount, tr_type) values ('" + date1 + "' , '" + txtPTotal.getText() + "' , 'profit')");
if (b2) {
try {
ResultSet rs2 = JDBC.getData("select MAX(tr_id) as tr_id from transaction");
if (rs2.first()) {
try {
boolean b3 = JDBC.putData("insert into o_de(or_id, tr_id, oday, gtotal) values ('" + rs.getInt("or_id") + "' , '" + rs1.getInt("tr_id") + "','" + date1 + "','" + txtGtotal.getText() + "' )");
if (b3) {
try {
boolean b4 = JDBC.putData("insert into order_profit(or_id, tr_id, ptotal) values ('" + rs.getInt("or_id") + "' , '" + rs1.getInt("tr_id") + "','" + txtPTotal.getText() + "' )");
if (b4) {
JDBC.commit();
JOptionPane.showMessageDialog(null, "Order Saved Sucessfully..");
JDBC.putClose();
JDBC.conClose();
}
} catch (Exception e) {
JDBC.rollback();
e.printStackTrace();
} finally {
JDBC.putClear();
JDBC.conClear();
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
} catch (Exception e) {
JDBC.rollback();
e.printStackTrace();
}
}
} catch (Exception e) {
JDBC.rollback();
e.printStackTrace();
}
}
} catch (Exception e) {
JDBC.rollback();
e.printStackTrace();
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
This is my JDBC class.
这是我的 JDBC 类。
package Modle;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JDBC {
static Connection con = null;
static boolean b;
static PreparedStatement state;
public static void setCon() {
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/lottery", "root", "123");
} catch (Exception ex) {
ex.printStackTrace();
}
}
public static Connection getCon() throws Exception {
if (con == null) {
setCon();
}
return con;
}
public static boolean putData(String sql) {
try {
getCon().setAutoCommit(false);
state = getCon().prepareStatement(sql);
state.executeUpdate();
b = true;
} catch (Exception e) {
e.printStackTrace();
b = false;
}
return b;
}
// connection commit
public static void commit() {
try {
con.commit();
} catch (Exception e) {
e.printStackTrace();
}
}
// rollback data
public static void rollback() {
if (con != null) {
try {
con.rollback();
} catch (SQLException ex) {
Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
// close statement
public static void putClose() {
try {
state.close();
} catch (SQLException ex) {
Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex);
}
}
// close connection
public static void conClose() {
try {
con.setAutoCommit(true);
con.close();
} catch (SQLException ex) {
Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex);
}
}
// clear prepared statement
public static void putClear() {
try {
if (state != null && !state.isClosed()) {
state.close();
}
} catch (SQLException ex) {
Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex);
}
}
// clear the connection
public static void conClear() {
try {
if (con != null && !con.isClosed()) {
con.setAutoCommit(true);
con.close();
}
} catch (SQLException ex) {
Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex);
}
}
public static ResultSet getData(String sql) throws Exception {
Statement state = getCon().createStatement();
ResultSet rs = state.executeQuery(sql);
return rs;
}
}
This is my first attempt to transaction handling in mysql. I know it is not an excuse. But I only have a rough idea on this connection handling. If my code is imperfect, please give me a demo answer.
这是我第一次尝试在 mysql 中进行事务处理。我知道这不是借口。但我对这种连接处理只有一个粗略的想法。如果我的代码不完善，请给我一个演示答案。
How to fix this No operations allowed after connection closedexception? Thank you.
如何解决此 连接关闭异常后不允许操作？谢谢你。
Added stack trace too. I think there are another 2 places found the same error too. They came before run this query.
也添加了堆栈跟踪。我认为还有另外两个地方也发现了同样的错误。他们在运行这个查询之前就来了。
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1206)
at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1198)
at com.mysql.jdbc.ConnectionImpl.createStatement(ConnectionImpl.java:2484)
at com.mysql.jdbc.ConnectionImpl.createStatement(ConnectionImpl.java:2466)
at Modle.JDBC.getData(JDBC.java:115)
at Controler.NewOrderCon2.saveTotal(NewOrderCon2.java:196)
at lottery.NewOrder.jButton6ActionPerformed(NewOrder.java:2350)
at lottery.NewOrder.access00(NewOrder.java:28)
at lottery.NewOrder.actionPerformed(NewOrder.java:537)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2018)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2341)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
at java.awt.Component.processMouseEvent(Component.java:6505)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3321)
at java.awt.Component.processEvent(Component.java:6270)
at java.awt.Container.processEvent(Container.java:2229)
at java.awt.Component.dispatchEventImpl(Component.java:4861)
at java.awt.Container.dispatchEventImpl(Container.java:2287)
at java.awt.Component.dispatchEvent(Component.java:4687)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4832)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4492)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4422)
at java.awt.Container.dispatchEventImpl(Container.java:2273)
at java.awt.Window.dispatchEventImpl(Window.java:2719)
at java.awt.Component.dispatchEvent(Component.java:4687)
at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:729)
at java.awt.EventQueue.access0(EventQueue.java:103)
at java.awt.EventQueue.run(EventQueue.java:688)
at java.awt.EventQueue.run(EventQueue.java:686)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.security.ProtectionDomain.doIntersectionPrivilege(ProtectionDomain.java:87)
at java.awt.EventQueue.run(EventQueue.java:702)
at java.awt.EventQueue.run(EventQueue.java:700)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:699)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:242)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:161)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:150)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:146)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:138)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:91)
采纳答案by easyDaMan
Are you aware of the fact that you are actually accessing the backend from within your GUI? You are passing parameters from textfields directly to your database. This is a source for major fworups. At least validate your input, or Little Bobby Tableswill prematurely end your working contract.
您是否意识到您实际上是从 GUI 内部访问后端？您将参数从文本字段直接传递到数据库。这是一个大搞砸的来源。至少验证您的输入，否则Little Bobby Tables会提前终止您的工作合同。
As to your error: Sorry, but this code needs major refactoring. Alone by the line count this code does too much. First golden rule: Keep your methods short. Second golden rule: Make them shorter.
至于您的错误：抱歉，此代码需要进行重大重构。仅凭行数，这段代码就做得太多了。第一条黄金法则：保持你的方法简短。第二个黄金法则：让它们更短。
The fact that you yourself do not understand what is going on is a big red light for you and shows that you need to reconsider your design.
您自己不了解正在发生的事情这一事实对您来说是一个大红灯，表明您需要重新考虑您的设计。
- Make the methods that write stuff with JDBC.putData() standalone.
- Do the same with JDBC.getData().
- See a pattern emerging.
- 使使用 JDBC.putData() 编写内容的方法独立。
- 对 JDBC.getData() 执行相同的操作。
- 看到一个正在出现的模式。
I guess its a premature call to connection.close() in JDBC. By fractioning your operations to more atomic ones you can reason about your code better, thus understanding the error at hand.
我猜这是对 JDBC 中 connection.close() 的过早调用。通过将您的操作分解为更原子的操作，您可以更好地推理您的代码，从而了解手头的错误。
Sorry for not delivering a solution, but in the long run you are better off by adhering to some code principles. Learn them! The sooner the better and since I need some more karma: Read "Clean-Code" by Robert C. Martin. http://www.amazon.de/Clean-Code-Handbook-Software-Craftsmanship/dp/0132350882
很抱歉没有提供解决方案，但从长远来看，坚持一些代码原则会更好。学习他们！越快越好，因为我需要更多的业力：阅读 Robert C. Martin 的“Clean-Code”。 http://www.amazon.de/Clean-Code-Handbook-Software-Craftsmanship/dp/0132350882
You will then be on the path of enlightenment and thus use a DAOFactory (hinting) and the DAO design pattern (also hinting) and become a coder god. Congrats!
然后你将走上启蒙之路，从而使用DAOFactory（暗示）和DAO设计模式（也暗示）并成为编码之神。恭喜！
Well, here's a little guide on how the refactoring could look. Not finished and untested, and I guess I fukked up the SQL insertion sequence (do not know which transactionId is used where). But I hope you will get an idea. Have a nice day and welcome to Jamaica!
好吧，这里有一个关于重构外观的小指南。未完成且未经测试，我想我搞砸了 SQL 插入序列（不知道在哪里使用了哪个 transactionId）。但我希望你会有一个想法。祝您有美好的一天，欢迎来到牙买加！
package mysqlfix;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JTextField;
public class JDBC {
static Connection con = null;
static boolean b;
static PreparedStatement state;
public static void setCon() {
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/lottery", "root", "123");
} catch (Exception ex) {
ex.printStackTrace();
}
}
public static Connection getCon() throws Exception {
if (con == null) {
setCon();
}
return con;
}
public static boolean putData(String sql) {
try {
getCon().setAutoCommit(false);
state = getCon().prepareStatement(sql);
state.executeUpdate();
getCon().commit();
b = true;
} catch (Exception e) {
e.printStackTrace();
b = false;
}
return b;
}
// connection commit
public static void commit() {
try {
con.commit();
} catch (Exception e) {
e.printStackTrace();
}
}
// rollback data
public static void rollback() {
if (con != null) {
try {
con.rollback();
} catch (SQLException ex) {
Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
// close statement
public static void putClose() {
try {
state.close();
} catch (SQLException ex) {
Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex);
}
}
// close connection
public static void conClose() {
try {
con.setAutoCommit(true);
con.close();
} catch (SQLException ex) {
Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex);
}
}
// clear prepared statement
public static void putClear() {
try {
if (state != null && !state.isClosed()) {
state.close();
}
} catch (SQLException ex) {
Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex);
}
}
// clear the connection
public static void conClear() {
try {
if (con != null && !con.isClosed()) {
con.setAutoCommit(true);
con.close();
}
} catch (SQLException ex) {
Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex);
}
}
public static ResultSet getData(String sql) throws Exception {
Statement state = getCon().createStatement();
ResultSet rs = state.executeQuery(sql);
return rs;
}
public void saveTotal(JTextField txtGtotal, JTextField txtPTotal) {
SuperDAO superDAO = new SuperDAO();
if (superDAO.getMaxIdFromOrder() > 0) {
Date date1;
date1 = new Date();
String txtGTotalFromTextField = txtGtotal.getText();
String txtPTotalFromTextField = txtPTotal.getText();
boolean b1 = false;
//regarding the transaction id...
//this changes whilst updating the table transaction.
int transactionId = -1;
if (txtGTotalFromTextField.matches("[a-zA-Z]")) {
transactionId = superDAO.insertOrderIntoTransaction(date1, txtGTotalFromTextField);
//b1 = JDBC.putData("insert into transaction(tr_date, amount, tr_type) values ('" + date1 + "' , '" + txtGTotalFromTextField + "' , 'order')");
}
if (transactionId > 0) {
try {
} catch (Exception ex) {
Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex);
}
if (txtPTotalFromTextField.matches("[a-zA-Z]")) {
transactionId = superDAO.insertProfitIntoTransaction(date1, txtGTotalFromTextField);
}
JDBC.putData("insert into o_de(or_id, tr_id, oday, gtotal) values ('" + superDAO.getMaxIdFromOrder() + "' , '" + transactionId + "','" + date1 + "','" + txtGtotal.getText() + "' )");
JDBC.putData("insert into order_profit(or_id, tr_id, ptotal) values ('" + superDAO.getMaxIdFromOrder() + "' , '" + transactionId + "','" + txtPTotal.getText() + "' )");
//JDBC.commit();
//JOptionPane.showMessageDialog(null, "Order Saved Sucessfully..");
JDBC.putClose();
JDBC.conClose();
}
}
}
}
package mysqlfix;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
*
* @author edm
*/
public class SuperDAO {
Connection conn;
public SuperDAO() {
try {
this.conn = JDBC.getCon();
} catch (Exception ex) {
Logger.getLogger(SuperDAO.class.getName()).log(Level.SEVERE, null, ex);
}
}
public int getMaxIdFromOrder() {
try {
ResultSet rs = JDBC.getData("select MAX(or_id) as or_id from `order`");
if (rs.first()) {
return rs.getInt("or_id");
}
} catch (SQLException ex) {
Logger.getLogger(SuperDAO.class.getName()).log(Level.SEVERE, null, ex);
} catch (Exception ex) {
Logger.getLogger(SuperDAO.class.getName()).log(Level.SEVERE, null, ex);
}
return -1;
}
public int getMaxIdFromTransaction() {
ResultSet rs;
try {
rs = JDBC.getData("select MAX(tr_id) as tr_id from transaction");
if (rs.first()) {
return rs.getInt("tr_id");
}
} catch (Exception ex) {
Logger.getLogger(SuperDAO.class.getName()).log(Level.SEVERE, null, ex);
}
return -1;
}
public int insertOrderIntoTransaction(Date date, String text) {
JDBC.putData("insert into transaction(tr_date, amount, tr_type) values ('" + date + "' , '" + text + "' , 'order')");
return getMaxIdFromTransaction();
}
public int insertProfitIntoTransaction(Date date, String text) {
JDBC.putData("insert into transaction(tr_date, amount, tr_type) values ('" + date + "' , '" + text + "' , 'profit')");
return getMaxIdFromTransaction();
}
}
Of course the journey does not stop there. I did not finish the JDBC saveTotal(). I just started it, you make the rest.
当然，旅程并不止于此。我没有完成 JDBC saveTotal()。我刚开始，剩下的你来做。
Please note that I did not test this code against a database (certain sql ddl files were missing). Also, I did not use the rollback mechanism. Furthermore, saveTotal() lives in JDBC, where it does not belong. Use saveTotal in your GUI (if needs be) and let all database accesses flow through SuperDAO. This is not the best design but it is not too abstract and you can easily see how the separation of concern makes your code a little bit more readable and maintainable.
请注意，我没有针对数据库测试此代码（某些 sql ddl 文件丢失）。另外，我没有使用回滚机制。此外， saveTotal() 存在于 JDBC 中，它不属于它。在您的 GUI 中使用 saveTotal（如果需要）并让所有数据库访问都通过 SuperDAO。这不是最好的设计，但它不是太抽象，您可以很容易地看到关注点分离如何使您的代码更具可读性和可维护性。
