Why is DataBase Connecton keeps closing

Question

I am really stuck with this problem so I would be glad is someone could help me out!

when i login after a minute the system logs out when i try to login again I get this error: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.

How can i prevent this connection closed. and what will be the best connection pool for this

NB:I am still a learner doing my first CRUD! Database Util:

  private static Connection connet;
        public static Connection getConnection() {
            if( connet != null )
                return connet;

            InputStream inputStream = DButil.class.getClassLoader().getResourceAsStream( "/db.properties" );
            Properties properties = new Properties();
            try {
                properties.load( inputStream );
                String url = properties.getProperty("url");
                String driver = properties.getProperty("driver");
                String userName = properties.getProperty("user");
                String password = properties.getProperty("password");

                Class.forName(driver);
                connet = DriverManager.getConnection(url,userName,password);

            } catch (IOException | ClassNotFoundException | SQLException e) {

                e.printStackTrace();
            }

            return connet;      
    }
// connection commit 
    public static void commit() {
        try {
            connet.commit();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

      // rollback data
      public static void rollback() {
        if (connet != null) {
            try {
                connet.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    // close Connection
    public static void closeConnection( Connection toBeClosed ) {
        if( toBeClosed == null )
            return;
        try {
            toBeClosed.close();
        } catch (SQLException e) {
            e.printStackTrace();

        }
    }

DB properties:

url = jdbc:mysql://localhost:3306/dbname?autoReconnect=true
driver = com.mysql.jdbc.Driver
user = usernanem
password = password

DaoImplemetation:

private Connection connet;

    public UsersDaoImplementation()
    {
        connet=DButil.getConnection();
    }

    @Override
    public void addUser(Users user) {

        try {
            String query = "INSERT INTO Users (First_Name, Last_Name, Address, Phone_Number, UserName, Password, idRole, Date_of_Birth) VALUES (?,?,?,?,?,?,?,?)";

            PreparedStatement preparedStatement = connet.prepareStatement( query );
            preparedStatement.setString(1, user.getFirst_Name());
            preparedStatement.setString(2, user.getLast_Name());
            preparedStatement.setString(3,user.getAddress());
            preparedStatement.setInt(4, user.getPhone_Number());
            preparedStatement.setString(5, user.getUserName());
            preparedStatement.setString(6, user.getPassword());
            preparedStatement.setInt(7, user.getIdRole());
            //preparedStatement.setDate(8, (Date) user.getDate_of_Birth());
            //preparedStatement.setDate(8, (java.sql.Date) user.getDate_of_Birth());
            preparedStatement.setDate(8, new java.sql.Date (user.getDate_of_Birth().getTime()));

            preparedStatement.executeUpdate();
            preparedStatement.close();

        } catch (SQLException e) {

            e.printStackTrace();
        }

    }

    @Override
    public void deleteUser(int idUsers) {
        try {
            String query ="DELETE FROM Users WHERE idUsers = ?";
            PreparedStatement preparedStatement = connet.prepareStatement( query );
            preparedStatement.setInt(1, idUsers);
            preparedStatement.executeUpdate();
            preparedStatement.close();
        } catch (SQLException e) {

            e.printStackTrace();
        }


    }

    @Override
    public void updateUser(Users user) {
        try {
             String query = "UPDATE Users SET First_Name=?, Last_Name=?, Address=?, Phone_Number=?, UserName=?, Password=?, idRole=?, Date_of_Birth=?";

            PreparedStatement preparedStatement = connet.prepareStatement( query );
            preparedStatement.setString(1, user.getFirst_Name());
            preparedStatement.setString(2, user.getLast_Name());
            preparedStatement.setString(3,user.getAddress());
            preparedStatement.setInt(4, user.getPhone_Number());
            preparedStatement.setString(5, user.getUserName());
            preparedStatement.setString(6, user.getPassword());
            preparedStatement.setInt(7, user.getIdRole());
            preparedStatement.setDate(8, new java.sql.Date (user.getDate_of_Birth().getTime()));
            preparedStatement.executeUpdate();
            preparedStatement.close();
        } catch (SQLException e) {

            e.printStackTrace();
        }

    }

    @Override
    public List<Users> getAllUsers() {
        List<Users> users = new ArrayList<Users>();     
        try {
            Statement statement = connet.createStatement();
            ResultSet resultSet = statement.executeQuery( "SELECT * FROM Users" );
            while (resultSet.next())
            {
                Users user = new Users();
                user.setIdUsers(resultSet.getInt("idUsers"));
                user.setFirst_Name(resultSet.getString("First_Name"));
                user.setLast_Name(resultSet.getString("Last_Name"));
                user.setAddress(resultSet.getString("Address"));
                user.setPhone_Number(resultSet.getInt("Phone_Number"));
                user.setUserName(resultSet.getString("UserName"));
                user.setPassword(resultSet.getString("Password"));
                user.setIdRole(resultSet.getInt("idRole"));
                user.setDate_of_Birth(resultSet.getDate("Date_of_Birth"));
                users.add(user);
            }
            resultSet.close();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return users;
    }

    @Override
    public Users getUserbyId(int idUsers) {
Users user = new Users();

        try {
            String query = "SELECT * FROM Users WHERE idUsers=?";
            PreparedStatement preparedStatement = connet.prepareStatement( query );

            preparedStatement.setInt(1, idUsers);
            ResultSet resultSet = preparedStatement.executeQuery();

            while( resultSet.next() ) {
            user.setIdUsers(resultSet.getInt("idUsers"));
            user.setFirst_Name(resultSet.getString("First_Name"));
            user.setLast_Name(resultSet.getString("Last_Name"));
            user.setAddress(resultSet.getString("Address"));
            user.setPhone_Number(resultSet.getInt("Phone_Number"));
            user.setUserName(resultSet.getString("UserName"));
            user.setPassword(resultSet.getString("Password"));
            user.setIdRole(resultSet.getInt("idRole"));
            user.setDate_of_Birth(resultSet.getDate("Date_of_Birth"));

        } 
        resultSet.close();
        preparedStatement.close();

    }
        catch (SQLException e) {

            e.printStackTrace();
        }
        return user;
    }



    @Override
    public boolean validate(String UserName, String Password) {
        boolean status = false;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            String query =  "SELECT * FROM Users WHERE UserName=? and Password=?";
         preparedStatement = connet.prepareStatement( query );

            preparedStatement.setString(1, UserName);
            preparedStatement.setString(2, Password);
            resultSet = preparedStatement.executeQuery();

            status=resultSet.next();

        } catch (SQLException e) {

            e.printStackTrace();
        }finally {
            if (connet != null) {
                try {
                    connet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return status;
    }

Error :

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:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
    at com.mysql.jdbc.Util.getInstance(Util.java:360)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:924)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:870)
    at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1232)
    at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1225)
    at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4104)
    at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4073)
    at org.jupiterM.dao.UsersDaoImplementation.validate(UsersDaoImplementation.java:162)
    at org.jupiterM.controller.LoginJ.doPost(LoginJ.java:63)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:528)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1099)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:670)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1520)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1476)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:745)

Answer 1

You have a finally block that reads like this:

} finally {
    if (connet != null) {
        try {
            connet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    ...
}

Since connet is a field, the closed connection will remain on the instance; it would not be set to null, though.

The consequence of this is that the closed connection would be returned to you, because all you check is that it's not null.

You can fix this problem by changing the code that obtains a new connection:

if( connet != null && !connet.isClosed() )
    return connet;

Another thing you should do is setting connet to null when it has been closed.

Answer 2

You have one static attribute that is your connection. The object is being returned to the methods in your dao. When you close his connection, you are also closing the static one. (Because they share the same reference). You should create the connection object on each method, or look for a framework to controll your connections. Connection pool is really hard to achieve in a pure jdbc way.

Answer 3

No operations allowed after connection closed. How can I prevent this connection closed Exception ?

Option (1): Close connection in finally with a null check

You can prevent this by adding a null check and closing connections in finally block as below:

Connection conn = null;
try {
  //your code
} catch(SQLException exe) {
 //Log exceptions
} finally {
   try {
       conn.close();
    } catch (SQLException e) {
       e.printStackTrace();
    }
}

I have noticed in your code, sometimes, you are closing the connections inside try which is not recommended because if try block throws an SQLException in the middle, conn.close() will NOT get executed, which is a very big problem. So ensure that you are ALWAYS closing connections in finally block ONLY, otherwise it will create a RESOURCE LEAK (very soon you will run out of connections).

Option (2): You can use try with resources (Recommended as you don't need to worry about closing connection)

try(Connection conn = DButil.getConnection()) {
  //your code
} catch(SQLException exe) {
 //Log exceptions
}

Here, in Option(2), you can notice that there is NO finally block here and the conn object will be closed automatically, you can look here for more details on try with resources.

How can I handle connection pool for my code ?

Handling connections manually (like how you did) is NOT a best practice (as they are costly in terms of time), so you can try implementing connection pooling using Apache DBCP and you can refer here for more on this.

Answer 4

Fixing this error is a moot point - since the whole idea of your approach is wrong - sharing a single connection between different threads (and each request might come in a different thread) is plain forbidden. Even if you use one of other suggestions and get rid of this single error, you will get more errors - worse ones. And you will never be able to get it to work once you add transactions of any kind.

If you use a Java EE or Java EE Web Profile compliant server, it has a connection pool built in, along with facilities to configure it, create a new connection for each request, safely return the connection to the pool and perform a transaction (even a two-phase commit). The exact details on how to configure it are server-dependent, usually there's a proprietary configuration file or some admin gui.