CODEWITHSUNDEEP
javaandroidmysqljdbc
Stephen
Stephen

Reputation: 10079

Operation not allowed after ResultSet closed[SQL Exception]

My application is connected to mysql successfully.But I am getting these console error at runtime.I have pointed out the error lines in below code:

(Edited)

Console Error: 

DB Manager 4....
DB Manager 5....
DB Manager 4....
DB Manager 5....
DB Manager 4....
DB Manager 5....
DB Manager 4....
DB Manager 5....
DB Manager 4....
DB Manager 5....
DB Manager 6....
DB Manager 7....
DB Manager 5....
DB Manager 4....

 java.sql.SQLException: Operation not allowed after ResultSet closed
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
        at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:794)
        at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:838)
        at com.mysql.jdbc.ResultSetImpl.getObject(ResultSetImpl.java:4847)
        at agriya.talkr.db.DBManager.getObject(DBManager.java:270)
        at agriya.talkr.models.Settings.getSettings(Settings.java:62)
        at agriya.talkr.app.WebServices.doGet(WebServices.java:153)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:624)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:956)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:423)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1079)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:625)
        at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:318)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.lang.Thread.run(Unknown Source)

DB Manager 1....
DB Manager 2....
DB Manager 3....
DB Manager 4....
DB Manager 5....
DB Manager 4....
DB Manager 5....

DBManger.java:

public static Connection _connect = null;  
public static Statement _statement = null;  
 .......
 ........

  public static Connection getConnect(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            _connect = (Connection) DriverManager.getConnection(CONNECTION_STR);
            _statement = (Statement) _connect.createStatement();
        } catch (ClassNotFoundException e) {
            System.out.println("EX: " + e.getLocalizedMessage());
            return null;
        } catch (SQLException e) {
            System.out.println("EX: " + e.getLocalizedMessage());
            return null;
        }
        return _connect;
    }

    private static void prepareStatement(String query){
        try {
            if(_connect != null && !_connect.isClosed())
                _connect.close();
            _connect = getConnect();
            _preparedStatement = (PreparedStatement) _connect.prepareStatement(query);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }



 public static JSONObject getObject(String query){
       System.out.println("QUERY: " + query);
       _connect = getConnect();
       JSONObject json = new JSONObject();

       System.out.println("DB Manager 1....");
       try {

           System.out.println("DB Manager 2....");

            ResultSet rs =_statement.executeQuery(query);
            ResultSetMetaData md = rs.getMetaData();
            int columns = md.getColumnCount();
            while (rs.next()){

                  System.out.println("DB Manager 3....");

                 for(int i=1; i<=columns; ++i){       

                      System.out.println("DB Manager 4....");

                         json.put(md.getColumnName(i).toString(), 
                                  rs.getObject(i).toString()); --->270th line

                          System.out.println("DB Manager 5....");
                 }
            }
              System.out.println("DB Manager 6....");

            rs.close();

              System.out.println("DB Manager 7....");
            _connect.close();
       } 


       catch (SQLException e) {
           e.printStackTrace();
       } catch (JSONException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } 
       return json;
   }

Settings.java:

public static JSONObject getSettings(String phone_number){
        Settings s = (Settings) Settings.toObject(DBManager.getObject
       ("Select * from settings where user_id =" + phone_number), Settings.class); --->62nd line
        try {
            return new JSONObject(new Gson().toJson(s, Settings.class));
        } catch (JSONException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return new JSONObject();
    }

WebServices.java:

public class WebServices extends HttpServlet {

protected void doGet(final HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

 case "getSettings":{
    out.print(Settings.getSettings(user_id));  --->153 rd line
    break;
   }

Anyone can help me with this.Thank you.

Upvotes: 0

Views: 665

Answers (2)

Jan
Jan

Reputation: 13858

Your call to _connect.close(); might be run while another instance of that loop is still active - this will most likely close the ResultSet as well causing that error.

Using static variables for things you assign per method is generally dangerous - as you never know when the values might be reassigned.

Fix

Using try-with-resource syntax:

   System.out.println("QUERY: " + query);
   try(Connection connect = getConnect();
          Statement statement = connect.createStatement();
          ResultSet rs = statement.executeQuery(query)) {

        ResultSetMetaData md = rs.getMetaData();
        int columns = md.getColumnCount();
        while (rs.next()){
              System.out.println("DB Manager 3....");
             for(int i=1; i<=columns; ++i){       

                  System.out.println("DB Manager 4....");

                     json.put(md.getColumnName(i).toString(), 
                              rs.getObject(i).toString()); --->270th line

                      System.out.println("DB Manager 5....");
             }
        }
          System.out.println("DB Manager 6....");


          System.out.println("DB Manager 7....");
   } catch (SQLException e) {
       e.printStackTrace();
   } catch (JSONException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
} 
   return json;
}

Upvotes: 1

wero
wero

Reputation: 33010

Your DBManager uses static variables for Connection and statement. When receiving concurrent requests one request may close the connection and statement which is active by another request.

You could either make DBManger.getObject synchronized (which will give you bad performance since it synchronizes requests) or convert _connection and _statement to local variables:

public static JSONObject getObject(String query){
   try (Connection con = getConnect()) {
       Statement stmt = con.createStatement();
       ResultSet rs = stmt.executeQuery(query);
       ...
   }
}

Upvotes: 1

Related Questions