How to fix com.mysql.jdbc.exceptions.jdbc4.MySQLNo

问题:

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.

     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.

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:

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.

• Make the methods that write stuff with JDBC.putData() standalone.
• Do the same with 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.

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

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!

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.

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.