ivan ivan - 28 days ago 8
Java Question

Why is DataBase Connecton keeps closing

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

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.

Comments