Connection Closed even when the connection is closed below the code

Question

I have a basic code snippet below but it is not working.What may be the problem with it.

public List<String> getStores() throws SQLException{
        List<String> store_id=new ArrayList<>();
        String query="select distinct(store_id) from stores";
        Connection con=ConnectDatabase.getDb2ConObj();
        Statement stmt=con.createStatement();
        java.sql.ResultSet rsResultSet=stmt.executeQuery(query);
        while(rsResultSet.next()){
            store_id.add(rsResultSet.getString(1));
        }
        con.close();
        return store_id;
    }

It is throwing the below exception

com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: No operations allowed after connection closed.
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:888)
    at com.mysql.jdbc.Connection.checkClosed(Connection.java:1931)
    at com.mysql.jdbc.Connection.createStatement(Connection.java:3087)
    at com.mysql.jdbc.Connection.createStatement(Connection.java:3069)
    at com.dao.StoreDao.getStores(StoreDao.java:52)
    at org.apache.jsp.adminViewAllStore_jsp._jspService(adminViewAllStore_jsp.java:119)

The code for ConnectDatabse is

public class ConnectDatabase {
     static Connection con=null;
     static String connectionString="jdbc:mysql://localhost:3306/ayurveda";
     static String username="root";
     static String password="";
     public static Connection getDb2ConObj(){
         if(con==null){
         try{
             Class.forName("com.mysql.jdbc.Driver");
             con=DriverManager.getConnection(connectionString,username,password);
         }
         catch(ClassNotFoundException | SQLException e)
         {
             System.out.println("Connect initialized with error"+e.getMessage());
             e.printStackTrace();
         }
         }
         return con;
     }

I cannot understand the reason for the same.What may be the problem.Since I am closing the connection after I am done with it.

You are probably caching your connection inside `ConnectDatabase.getDb2ConObj()` and then returning the same connection the next time this method is called ? You're better off using a connection pool instead of trying to maintain one of your own. Please post the code for `ConnectDatabase` and we can confirm this. — Deepak Bala, Jul 20 '14 at 14:34

score 0 · Accepted Answer · answered Jul 20 '14 at 14:31

0

It worked after I enclosed it in a try catch finally block.Changed the code as given below

public List<String> getStores() throws SQLException{
        List<String> store_id=new ArrayList<>();
        Connection con=ConnectDatabase.getDb2ConObj();
        try{
        String query="select distinct(store_id) from stores";
        Statement stmt=con.createStatement();
        java.sql.ResultSet rsResultSet=stmt.executeQuery(query);
        while(rsResultSet.next()){
            store_id.add(rsResultSet.getString(1));
        }
        }catch(Exception e){

        }finally{
            con.close();
        }
        return store_id;
    }

Thanks.

answered Jul 20 '14 at 14:31

Nilesh Chaudhary

37
5

2

First, this is not a good way of dealing with errors. Second, do you actually get data now? – Bart Friederichs Jul 20 '14 at 14:32
Hi Bart.Yes in the object I have defined the statements – Nilesh Chaudhary Jul 20 '14 at 14:33
`Class.forName("com.mysql.jdbc.Driver"); con=DriverManager.getConnection(connectionString,username,password);` – Nilesh Chaudhary Jul 20 '14 at 14:34

score 0 · Answer 2 · edited May 23 '17 at 10:25

Use Java 7 -The try-with-resources Statement

According to the oracle documentation, you can combine a try-with-resources block with a regular try block

The typical Java application manipulates several types of resources such as files, streams, sockets, and database connections. Such resources must be handled with great care, because they acquire system resources for their operations. Thus, you need to ensure that they get freed even in case of errors.

Indeed, incorrect resource management is a common source of failures in production applications, with the usual pitfalls being database connections and file descriptors remaining opened after an exception has occurred somewhere else in the code. This leads to application servers being frequently restarted when resource exhaustion occurs, because operating systems and server applications generally have an upper-bound limit for resources.

sample code:

try(Connection con = getConnection()) {
   ...
}

Read more Java 7 Automatic Resource Management JDBC

Close Statement and ResultSet as well.

Don't load driver class every time when connection is needed. Just load it once in static initialization block.

static {
    try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
}

I suggest you to use JNDI and DataSource to keep username and password outside the Java code to make it more manageable. Keep the database configuration in a separate xml/properties file instead of hard-coding in Java file.

See Java Tutorial on Connecting with DataSource Objects

I have already posted a nice ConnectionUtil class to manage all the connections in a single class for whole application.

In Java 6 its more tedious job to close the resources but that is handled in Java 7. — Braj, Jul 20 '14 at 14:36
In java 7 you don't need to close the resources it is automatically closed. — Braj, Jul 20 '14 at 14:37

score 0 · Answer 3 · answered Jul 20 '14 at 15:12

You can use this type of code...for solving your problem

     public void actionPerformed(ActionEvent ae)
     {
     String u=t1.getText();
     String p=t2.getText();
    if(ae.getSource()==b1)
     {
      try{
     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
     Connection con=DriverManager.getConnection("jdbc:odbc:newdsn");

      String stp="SELECT * FROM reg";
      Statement sa=con.createStatement();
      rs=sa.executeQuery(stp);
     while(rs.next())
      {
       String du=rs.getString(2);
       String dp=rs.getString(3);
       if(du.equals(u)&&dp.equals(p))
      {
       a=0;
     break;
     }else{ a=1;}
       }
     if(a==0){
     JOptionPane.showMessageDialog(this,"LOGIN PAGE","Login is successful",1);
       }
      if(a==1){

      JOptionPane.showMessageDialog(this,"LOGIN PAGE","Login is not successful",1);

      }}    
      catch(Exception e){}
      }}

if even the it is throwing exception then you check the system 32 bit or 64 bit..you should try if 64bit then please make your dsn in 32 bit and anduse ms access 2002-2003 version
then you get tour solution .....thank u

Connection Closed even when the connection is closed below the code

3 Answers3