如何修复com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException：连接关闭后不允许任何操作。例外？ [英] How to fix com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed. exception?
问题描述
这是我保存记录的查询。
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();
}
}
这是我的JDBC类。
This is my JDBC class.
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;
}
}
这是我的首先尝试在mysql中进行事务处理。我知道这不是借口。但我对这种连接处理只有一个粗略的想法。如果我的代码不完善，请给我一个演示答案。
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.
如何解决此问题连接关闭后不允许进行任何操作例外？谢谢。
添加了堆栈跟踪。我认为还有另外2个地方也发现了同样的错误。他们在运行此查询之前来了。
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.access$1200(NewOrder.java:28)
at lottery.NewOrder$13.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.access$200(EventQueue.java:103)
at java.awt.EventQueue$3.run(EventQueue.java:688)
at java.awt.EventQueue$3.run(EventQueue.java:686)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:87)
at java.awt.EventQueue$4.run(EventQueue.java:702)
at java.awt.EventQueue$4.run(EventQueue.java:700)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.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)
推荐答案
您是否意识到您实际上是在GUI中访问后端？您正在将文本字段中的参数直接传递到数据库。这是主要的fuckups的来源。至少验证您的输入，或 Little Bobby Tables 将提前终止您的工作合同。
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 fuckups. At least validate your input, or Little Bobby Tables will prematurely end your working contract.
关于你的错误：对不起，但是这段代码需要重大修改。单独按行计数这个代码做得太多了。第一条黄金法则：保持你的方法简短。第二个黄金法则：缩短它们。
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.
- 使用JDBC.putData（）独立编写内容的方法。
- Do与JDBC.getData（）相同。
- 查看新出现的模式。
我猜它是JDBC中对connection.close（）的过早调用。通过将操作分成更原子的操作，您可以更好地推理您的代码，从而理解手头的错误。
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.
很抱歉没有提供解决方案，但从长远来看通过遵守一些代码原则，你会感觉更好。学习他们！越快越好，因为我需要更多的业力：
阅读Robert C. Martin的Clean-Code。
http://www.amazon.de/Clean -Code-Handbook-Software-Craftsmanship / dp / 0132350882
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
然后你将走上启蒙的道路，从而使用DAOFactory（暗示）和DAO设计模式（也暗示）并成为编码器之神。恭喜！
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!
嗯，这里有一些关于重构如何看的指南。没有完成和未经测试，我想我已经搞砸了SQL插入序列（不知道在哪里使用了transactionId）。但我希望你能得到一个主意。
祝您有愉快的一天，欢迎来到牙买加！
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!
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();
}
}
当然，旅程并不止于此。我没有完成JDBC saveTotal（）。我刚开始做，剩下的就是我。
Of course the journey does not stop there. I did not finish the JDBC saveTotal(). I just started it, you make the rest.
请注意，我没有针对数据库测试此代码（某些sql ddl文件丢失）。另外，我没有使用回滚机制。此外，saveTotal（）存在于JDBC中，它不属于它。在GUI中使用saveTotal（如果需要），让所有数据库访问都通过SuperDAO。这不是最好的设计，但它不是太抽象，您可以轻松地看到关注点的分离如何使您的代码更具可读性和可维护性。
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.
这篇关于如何修复com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException：连接关闭后不允许任何操作。例外？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
