All geek questions in one place

Why DataBase Connecton Closes

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

when I logged in a minute after logging out, when I try to log in again, I get this error: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: no action is allowed after closing the connection.

How can I prevent this connection from closing. and what will be the best connection pool for this

NB: I'm still a student doing my first CRUD! Database Utility:

  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)
+4
source share
4 answers

You have a block finallythat reads as follows:

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

connet - , ; null.

, , , , null.

, , :

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

, , - connet null, .

+2

, . dao. , . ( ). . jdbc.

+2

. ?

(1):

, finally null, :

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

, , try, , try block SQLException , conn.close() , , , finally , RESOURCE LEAK ( ).

(2): try (, )

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

, (2), , NO finally, conn object closed , try with resources.

?

(, ) ( ), Apache DBCP, , .

+2

- - ( ) . , - . , .

If you use a Java EE or Java EE compatible web profile, it has a unified connection pool, as well as the means to configure it, create a new connection for each request, safely return the connection to the pool and complete the transaction (even two-phase commit). The exact information on how to configure it depends on the server, usually there is your own configuration file or some admin.

0
source

Source: https://habr.com/ru/post/1660008/

More articles:


All Articles