java - ResultSet 关闭后不允许执行操作[SQL 异常]

Question

我的应用程序已成功连接到 mysql。但是我在运行时收到这些控制台错误。我已在下面的代码中指出了错误行:

(已编辑)

控制台错误:

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)类 WebServices 扩展 HttpServlet {

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

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

任何人都可以帮助我。谢谢。

Answer 1

您的 DBManager 对连接和语句使用静态变量。当接收并发请求时，一个请求可能会关闭另一个请求处于 Activity 状态的连接和语句。

您可以使 DBManger.getObject 同步(这会导致性能下降，因为它会同步请求)或将 _connection 和 _statement 转换为局部变量:

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