如何修复com.mysql.jdbc.exceptions.jdbc4。MySQLNonTransientConnectionException:连接关闭后不允许操作。例外吗?

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 closed exception? 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.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)

1 个解决方案

#1

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.

您是否意识到您实际上正在从GUI中访问后端?您正在将参数从文本字段直接传递到数据库。这是主要的fucup的来源。至少验证你的输入，或者小博比表会提前结束你的工作合同。

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.
• 使用独立于JDBC.putData()的方法。
• Do the same with JDBC.getData().
• 对JDBC.getData()执行相同的操作。
• See a pattern emerging.
• 看到一个新兴模式。

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.

我想这是对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

很抱歉没有提供解决方案，但是从长远来看，您最好遵守一些代码原则。学习他们!越快越好，因为我需要更多的业力:阅读罗伯特·c·马丁的《清洁代码》。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!

你将会在启蒙的道路上，因此使用道工厂(暗示)和道设计模式(也暗示)并成为一个编码器的上帝。恭喜!

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(如果需要的话)，并让所有的数据库访问流通过超级dao。这不是最好的设计，但它不是太抽象，您可以很容易地看到关注点的分离如何使您的代码更易于阅读和维护。