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) {
        }
    }
}

One of the criticisms I have against singletons is that they lead to issues exactly like this, where the lifespan of a contained object becomes unknown, when not properly designed for the application. When doing any database access from a web server, you will likely need to have multiple connections to the database in order to handle concurrent requests. This is why RDBMSes are used -- they allow you to do just that. Because client code is allowed to close the Connection, the Singleton effectively loses control and subsequent code breaks it. — Shotgun Ninja, Jun 15 '15 at 17:30
In addition, you should be using new Connections for each DB transaction, and closing them immediately afterwards; that way, you can establish access control, turn off auto-commit (for multiple queries in a single transaction) as needed, rollback the transaction if an error occurs, and ensure reasonably atomic updates, among other niceties. By reusing a connection without properly managing its state, you open the doors for all sorts of issues down the road. — Shotgun Ninja, Jun 15 '15 at 17:33

score 1 · Answer 1 · answered Jun 15 '15 at 17:32

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.

score 0 · Answer 2 · answered Jun 15 '15 at 17:47

0

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.

answered Jun 15 '15 at 17:47

Mecon

977
1
6
17

thankx a lot sir , now iam clear ,what if i use same code for desktop application? – user3811169 Jun 15 '15 at 18:14

Singleton is applicable in servlet/jsp project?

2 Answers2