Java MYSQL错误连接关闭后不允许任何操作

Question

我真的被这个问题困住了，所以我很高兴有人能帮我！

我用java创建了一个登录页面，并在其中创建了一个MYSQL连接。如果您第一次尝试登录，代码可以正常工作，但是在您注销并再次尝试登录之后，我得到了这个错误:com.mysql.jdbc.exceptions.jdbc4.mysqlnontransientconnectionException：连接关闭后不允许任何操作。

This is my code:
import java.sql.*;
import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

public class LoginServlet extends HttpServlet {

    private static final long serialVersionUID = 1L;
    public static Connection conn = connectionDb.getInstance().getConnection();

    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        // get username and pass from the html page.
        response.setContentType("text/html");
        PrintWriter out = response.getWriter();
        String userName = request.getParameter("username");
        String password = request.getParameter("userpass");

        // put the username and variables in the getset.
        getset sets = new getset();
        sets.setUserName(userName);
        sets.setPassword(password);

        // send the data to the database 
        try {
            DbManager.Read(sets);
            // if the userinfo is correct following actions will perform:
            if (DbManager.authentication == true) {
                HttpSession session = request.getSession();
                session.setAttribute("user", "");
                // setting session to expiry in 30 mins
                session.setMaxInactiveInterval(30 * 60);
                Cookie username = new Cookie("user", userName);
                response.addCookie(username);
                response.sendRedirect("MyAccount.jsp");
                DbManager.authentication = false;
                // if the userinfo wasn't correct the following actions will perform:
            } else if (DbManager.authentication == false) {
                out.print("<p style=\"color:red\">Sorry username or password error</p>");
                RequestDispatcher rd = request
                        .getRequestDispatcher("login.jsp");
                rd.include(request, response);
            }
            // catches erros.
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();//
        } finally {
            if (conn != null) {
                connectionDb.getInstance().close();
            }
        }
    }
}

和DBManager

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.http.Cookie;
import javax.servlet.http.HttpSession;

public class DbManager {

    public static Connection conn = connectionDb.getInstance().getConnection();
    public static boolean authentication;
    public static ResultSet rs;
    public static PreparedStatement pstmt, pstmt1, pstmt2;

    public static void Insert(getset set) throws ClassNotFoundException,
            SQLException {
        try {

            // insert username and password
            String sql = "INSERT INTO logininfo(username, password) VALUES (?,?)";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, set.getUserName());
            pstmt.setString(2, set.getPassword());
            pstmt.executeUpdate();
            // insert user info
            String sql1 = "INSERT INTO userinfo(fullName, email, dateOfBirth, phoneNumber, companyName, companyEmail, paymentMethod) VALUES (?,?,?,?,?,?,?)";
            pstmt1 = conn.prepareStatement(sql1);
            pstmt1.setString(1, set.getFullName());
            pstmt1.setString(2, set.getEmail());
            pstmt1.setString(3, set.getDateOfBirth());
            pstmt1.setString(4, set.getPhoneNumber());
            pstmt1.setString(5, set.getCompanyName());
            pstmt1.setString(6, set.getCompanyEmail());
            pstmt1.setString(7, set.getPaymentMethod());
            pstmt1.executeUpdate();
            connectionDb.getInstance().close();
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void Read(getset set) throws ClassNotFoundException,
            SQLException {
        try {
            String sql = "SELECT * FROM logininfo where username='"+ set.getUserName() +"' and password='"+ set.getPassword() +"';";
            pstmt2 = conn.prepareStatement(sql);
            System.out.println(set.getUserName());
            rs = pstmt2.executeQuery(sql);

            if (rs.next()) {
                authentication = true;
                connectionDb.getInstance().close();
            } else {
                authentication = false;
                connectionDb.getInstance().close();
            }
            pstmt2.close();
            rs.close();
            conn.close();
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            try {
                if (pstmt2 != null)
                    pstmt2.close();
            } catch (SQLException se2) {
            }
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }
}

and connectionDb
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class connectionDb {

    private static connectionDb instance = null;

    private final String USERNAME = "root";
    private final String PASSWORD = "!i1w2g3w#";
    private final String CONN_STRING = "jdbc:mysql://localhost:3306/mydb";

    private Connection conn = null;

    private connectionDb() {
    }

    public static connectionDb getInstance() {
        if (instance == null) {
            instance = new connectionDb();
        }
        return instance;
    }

    private boolean openConnection() {
        try {
            String driver = "com.mysql.jdbc.Driver";
            try {
                Class.forName(driver).newInstance();
                conn = DriverManager.getConnection(CONN_STRING, USERNAME, PASSWORD);
            } catch (IllegalAccessException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (InstantiationException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return true;
    }

    public Connection getConnection() {
        if (conn == null) {
            if (openConnection()) {
                System.out.println("Connection Opened");
                return conn;
            } else {
                return null;
            }
        }
        return conn;
    }
        //return conn;
    //}
    public void close(){
        System.out.println("Close connection");
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        conn=null;
    }

}

Answer 1

问题在于连接对象的类级实例化，在解决问题的方法中实例化。此外，在servlet中不应该有连接关闭，从servlet中删除连接的实例化和关闭，因为这段代码应该在db管理器中。