Singleton is applicable in servlet/jsp project?

Question

I am using the following code to connect to a database for my simple jsp/servlet login project i.e. singleton. When I login for the frist time, it works after I logout successfully. Now problem begins when I again try to logon error is raised saying "Severe: Error message: No operations allowed after connection closed." But when I remove a code closing connection it again works fine. Please suggest me should I use it or avoid it.

public class ConnectionMgr {

    private static ConnectionMgr instance = null;

    private static final String USERNAME = "root";
    private static final String PASSWORD = "";
    private static final String M_CONN_STRING = "jdbc:mysql://localhost:3306/generator";
    private static final String H_CONN_STRING = "jdbc:hsqldb:data/generator";

    private DBType dbType = DBType.MYSQL;

    private Connection conn = null;


    private ConnectionMgr() {
    }

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

    public void setDBType(DBType dbType) {
        this.dbType = dbType;
    }

    private boolean openConnection() {
        try {
            switch (dbType) {

                case MYSQL:
                    Class.forName("com.mysql.jdbc.Driver");
                    conn = DriverManager.getConnection(M_CONN_STRING, USERNAME, PASSWORD);
                    return true;

                case HSQL:
                    conn = DriverManager.getConnection(H_CONN_STRING, USERNAME, PASSWORD);
                    return true;

                default:
                    return false;
            }
        } catch (SQLException | ClassNotFoundException e) {
            System.err.println(e);
            DBUtil.processException((SQLException) e);
            return false;
        }
    }

    public Connection getConnection() {
        if (conn == null) {
            if (openConnection()) {
                System.out.println("Connection opened");
                return conn;
            } else {
                return null;
            }
        }
        return conn;
    }

    public void processException(SQLException e) {
        System.err.println("Silgleton connection()Error -->");
        System.err.println("Erroe message:" + e.getMessage());
        System.err.println("Error code:" + e.getErrorCode());
        System.err.println("Error State:" + e.getSQLState());
    }

    public void close() {
        System.out.println("Closing connection");
        try {
            conn.close();
            conn = null;
        } catch (Exception e) {
        }
    }
}

Answer 1

Your connection manager is not thread safe. So while one user is trying to read data using the single instance, another user's thread may end up closing the in-use connection.

Instead use something like Apache DBCP that will also give you a connection pool.

If you still want to use the code above, change it your connection manager as a regular object and not as a singleton.

Answer 2

You probably are saving a reference to the "connection" instance of the first request, and then reusing that reference for your second request.

Try to make sure you are invoking getConnection() each time a new request comes to the servlet.

Add a System.out.println("your-method-name-") in all the methods of your ConnectionMgr class.