Problems to close communication with the database

Question

I have some applications consuming data from the database, but for a while now the applications are not able to close the connection, when I give a conexao.close(); Java says it cannot close because the ResultSet that this logo up in the Try block has already been closed.

Look down my code

    public ResultSet getHist(int ID) {
    ResultSet rs = null;
    PreparedStatement state = null;
    Connection conexao = null;
    try{
        conexao = FactoryConnection_MySQL.getConnection("base");
        state = conexao.prepareStatement("SELECT * FROM table where id = ?");
        state.setInt(1, ID);
        rs = state.executeQuery();            
    } catch (Exception e) {
        e.getMessage();
    } finally {
        try {
            if(!rs.isClosed()){//aqui eu verifico se o rs ta fechado ou não
                System.out.println("Rs não fechado...");
                conexao.close();
            }else{
            System.out.println("Rs ja fechado!");
        }
        } catch (Exception err) {
            err.getMessage();
        }
    }
    return rs;
}

Follow the error :

Informações:   Rs não fechado...false
Advertência:   StandardWrapperValve[jsp]: Servlet.service() for servlet jsp threw exception
java.sql.SQLException: Operation not allowed after ResultSet closed
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
    at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:743)
    at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6289)
    at org.apache.jsp.historico_jsp._jspService(historico_jsp.java:985)
    at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:111)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
    at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:411)
    at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:473)
    at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:377)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
    at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1682)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:318)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:160)
    at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:734)
    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:673)
    at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:99)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:174)
    at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:416)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:283)
    at com.sun.enterprise.v3.services.impl.ContainerMapper$HttpHandlerCallable.call(ContainerMapper.java:459)
    at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:167)
    at org.glassfish.grizzly.http.server.HttpHandler.runService(HttpHandler.java:206)
    at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:180)
    at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:235)
    at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:283)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:200)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:132)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:111)
    at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77)
    at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:536)
    at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:112)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:117)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:56)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:137)
    at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:591)
    at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:571)
    at java.lang.Thread.run(Thread.java:748)

Answer 1

I suggest you change the code to use the Try-with-Resources block. Declare your resources (ResultSet, PreparedStatement and Connection) in this way, as they all currently implement the interface java.lang.AutoCloseable.

So you don’t have to worry about manually closing the resources.

To understand the concept and see examples, see: https://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html