Error "The resultSet is closed" with PreparedStatement

4

I'm getting this when I'm traversing a ResultSet and get other ResultSet s:

public class BpaDetDao extends DaoAbstract<BpaDetBean> {

    public static final String // TODO Colocar campos no select
            SQL_SELECT = " SELECT * FROM bpadet b",
            SQL_SELECT_WHERE_COD = " WHERE b.cod = ? ",
            SQL_SELECT_WHERE_STRING = " WHERE b.data LIKE ?",
            SQL_INSERT = "",
            SQL_ATUALIZAR = "",
            SQL_DELETE = "",
            SQL_GENERATOR = "SELECT GEN_ID(gen_bpadet, 1) FROM RDB$DATABASE",
            SQL_SELECT_WHERE_COMP_PROF = " WHERE b.comp = ? and b.func = ? and b.unid = ?";

    public BpaDetBean buscaPorCodigo(long codigo, int codigoUps)
    { 
      BpaDetBean bpaDet = null;
      String sql = "SELECT cod, data, unid, func, comp, proced, cbo, idade, qtd "
                     + "FROM bpadet "
                     + "WHERE cod = ? ";
      try
      {
          Connection conn = Base.Conexao.getDBConnection();
          PreparedStatement ps = conn.prepareStatement(sql);
          ps.setLong(1, codigo);
          ResultSet rs = ps.executeQuery();
          bpaDet = resultSetParaEntity(rs);
          rs.close();
          ps.close();
      } catch (SQLException sqle)
      { Logger.getLogger(BpaDetDao.class.getName()).log(Level.SEVERE, null, sqle);
      } catch (Exception e)
      { Logger.getLogger(BpaDetDao.class.getName()).log(Level.SEVERE, null, e); }

      return bpaDet;
    }

    public List<BpaDetBean> buscaPorWhere(String where, Object[] parametros)
    {
        List<BpaDetBean> listaProced = null;
        ResultSet rs;
        if (parametros != null)
        {
            try
            {
                Connection conn = Base.Conexao.getDBConnection();
                PreparedStatement ps = conn.prepareStatement(
                        /*SQL_SELECT + " " + where*/
                        /*está fixo para testes*/
                         "SELECT * FROM bpadet b  WHERE b.comp = 201505 and b.func = 212 and b.unid = 8",
                         ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY,ResultSet.HOLD_CURSORS_OVER_COMMIT);

    //            for (int i = 0; i < parametros.length; i++)
    //                {
    //                    if (parametros[i] instanceof Integer)
    //                    {
    //                        Utils.setParamsInStatem(ps, i+1,(Integer)  parametros[i]);
    //                    } else if (parametros[i] instanceof Long)
    //                    {
    //                        Utils.setParamsInStatem(ps, i + 1, (Long) parametros[i]);
    //                    } else if (parametros[i] instanceof Double)
    //                    {
    //                        Utils.setParamsInStatem(ps, i + 1, (Double) parametros[i]);
    //                    } else if (parametros[i] instanceof Boolean)
    //                    {
    //                        Utils.setParamsInStatem(ps, i + 1, (Boolean) parametros[i]);
    //                    } else if (parametros[i] instanceof String)
    //                    {
    //                        Utils.setParamsInStatem(ps, i + 1, (String) parametros[i]);
    //                    }
    //                }
                rs = ps.executeQuery();
                listaProced = resultSetParaListaEntity(rs);
                ps.close();
            } catch (Exception e)
            {
                Logger.getLogger(BpaDetDao.class.getName()).log(Level.SEVERE, null, e);
            }
            return listaProced;
        } else
        {
            return null;
        }
    }

    @Override
    public List<BpaDetBean> resultSetParaListaEntity(ResultSet rs)
    {
        ArrayList<BpaDetBean> lista = null;
        BpaDetBean bpaDet;
        MedicoBean medico;
        UpsBean unidade;
        try
        {
            if (!rs.isAfterLast())
            {
                if (rs.getType() != ResultSet.TYPE_FORWARD_ONLY)
                {
                    rs.last();
                    //cria uma lista do tamanho da quantidade de registros para maior desemprenho
                    lista = new ArrayList<BpaDetBean>(rs.getRow());
                    rs.beforeFirst();
                } else
                {
                    //cria uma lista com o tamanho inicial de 30 posiçoes
                    lista = new ArrayList<BpaDetBean>(30);
                }

                while (rs.next())
                {
                    bpaDet = new BpaDetBean();
                    // Adiciona o M�dico relacionado
                    MedicoDao medicoDao = new MedicoDao(configFileXML);
                    medico = medicoDao.buscaPorCodigo(rs.getInt("func"), rs.getInt("unid"));
                    //^^^^^Neste metodo ele acaba fechando o resultset ^^^^ 
                    // Adiciona a Unidade relacionado
                    UpsDao upsDao = new UpsDao(configFileXML);
                    unidade = upsDao.buscaPorCodigo(rs.getInt("unid"));
                    //^^^^^esta linha a cima é a linha que lança o erro, linha 468 ^^^^
                    // Seta os valores da Distribui��o de medicamentos (Classe Principal)
                    bpaDet.setCodigo(rs.getInt("cod"));
                    bpaDet.setData(rs.getDate("data"));
                    bpaDet.setCompetencia(rs.getInt("comp"));
                    bpaDet.setCbo(rs.getString("cbo"));
                    bpaDet.setProcedimento(rs.getString("proced"));
                    bpaDet.setIdade(rs.getInt("idade"));
                    bpaDet.setQuantidade(rs.getInt("qtd"));

                    // Adiciona classes filhas
                    bpaDet.setUnidade(unidade); // UPS
                    bpaDet.setMedico(medico);   // M�dico

                    lista.add(bpaDet);
                }
            }

        } catch (SQLException ex)
        {
            Logger.getLogger(BpaDetDao.class.getName()).log(Level.SEVERE, ex.getMessage(), ex);
        } catch (ParseException ex)
        {
            Logger.getLogger(BpaDetDao.class.getName()).log(Level.SEVERE, ex.getMessage(), ex);
        }
        return lista;
    }

    @Override
    public BpaDetBean resultSetParaEntity(ResultSet rs)
    {
        BpaDetBean bpaDet = null;
        try
        {
            if (rs.next())
            {
                bpaDet = new BpaDetBean();
                // Adiciona o M�dico relacionado
                MedicoBean medico;
                MedicoDao medicoDao = new MedicoDao(configFileXML);
                medico = medicoDao.buscaPorCodigo(rs.getInt("func"), rs.getInt("unid"));

                // Adiciona a Unidade relacionado
                UpsBean unidade;
                UpsDao upsDao = new UpsDao(configFileXML);
                unidade = upsDao.buscaPorCodigo(rs.getInt("unid"));

                // Seta os valores da Distribui��o de medicamentos (Classe Principal)
                bpaDet.setCodigo(rs.getInt("cod"));
                bpaDet.setData(rs.getDate("data"));
                bpaDet.setCompetencia(rs.getInt("comp"));
                bpaDet.setCbo(rs.getString("cbo"));
                bpaDet.setProcedimento(rs.getString("proced"));
                bpaDet.setIdade(rs.getInt("idade"));
                bpaDet.setQuantidade(rs.getInt("qtd"));

                // Adiciona classes filhas
                bpaDet.setUnidade(unidade); // UPS
                bpaDet.setMedico(medico);   // M�dico

            }
        } catch (ParseException ex)
        {
            Logger.getLogger(BpaDetDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex)
        {
            Logger.getLogger(BpaDetDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return bpaDet;
    }

    @Override
    public BpaDetBean getNewEntityBeanAbstract()
    {
        return new BpaDetBean();
    }

    @Override
    public PreparedStatement setParmetrosPreparedStatement(PreparedStatement p) throws SQLException
    {
        throw new UnsupportedOperationException("Not supported yet.");
    }

}

And this is the method MedicoDao . buscaPorCodigo :

    public MedicoBean buscaPorCodigo(Integer codigo, Integer codigoUps) throws ParseException {     
    MedicoBean medico = new MedicoBean();
    String sql = "SELECT DISTINCT f.cod, f.nome, f.ativo FROM funcionarios f INNER JOIN funcesp fe ON fe.codfunc = f.cod WHERE f.cod = ? AND fe.unid = ? AND fe.cbo IS NOT NULL ORDER BY f.nome";

    try {
        PreparedStatement ps = Base.Conexao.getDBConnection().prepareStatement(sql);

        ps.setInt(1, codigo);
        ps.setInt(2, codigoUps);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            medico.setCodigo(rs.getInt("cod"));
            medico.setNome(rs.getString("nome"));
            medico.setAtivo(rs.getString("ativo"));             
        }           
    } catch (SQLException e) {
        e.printStackTrace();
    }       
    return medico;      
}

I've read the same Statment for two ResultSet s, close the ResultSet previous, but in my case I'm using two different statment. For each query in java do I have to create a new connection?

The error is this:

org.firebirdsql.jdbc.FBSQLException: The resultSet is closed
  at org.firebirdsql.jdbc.AbstractResultSet.getField(AbstractResultSet.java:642)
  at org.firebirdsql.jdbc.AbstractResultSet.getInt(AbstractResultSet.java:753)
  at br.com.teisistemas.teisaudeweb.dao.BpaDetDao.resultSetParaListaEntity(BpaDetDao.java:468)
  at br.com.teisistemas.teisaudeweb.dao.BpaDetDao.buscaPorWhere(BpaDetDao.java:422)
  at Base.DigProcedimentos.consultaProced4Profissional(DigProcedimentos.java:179)
  at Base.DigProcedimentos.consultaProced4Profissional(DigProcedimentos.java:218)
  at Base.DigProcedimentos.consultaProced4Profissional(DigProcedimentos.java:240)
  at org.apache.jsp.Enc.ProcedimentosProfissional_jsp._jspService(ProcedimentosProfissional_jsp.java:137)
  at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
  at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:388)
  at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
  at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
  at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
  at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
  at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:646)
  at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:436)
  at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:374)
  at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:302)
  at com.sun.faces.context.ExternalContextImpl.dispatch(ExternalContextImpl.java:542)
  at com.sun.faces.application.view.JspViewHandlingStrategy.executePageToBuildView(JspViewHandlingStrategy.java:359)
  at com.sun.faces.application.view.JspViewHandlingStrategy.buildView(JspViewHandlingStrategy.java:150)
  at com.sun.faces.application.view.JspViewHandlingStrategy.renderView(JspViewHandlingStrategy.java:190)
  at com.sun.faces.application.view.MultiViewHandler.renderView(MultiViewHandler.java:127)
  at org.ajax4jsf.application.ViewHandlerWrapper.renderView(ViewHandlerWrapper.java:100)
  at org.ajax4jsf.application.AjaxViewHandler.renderView(AjaxViewHandler.java:176)
  at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:117)
  at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:97)
  at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:135)
  at javax.faces.webapp.FacesServlet.service(FacesServlet.java:309)
  at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
  at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
  at org.ajax4jsf.webapp.BaseXMLFilter.doXmlFilter(BaseXMLFilter.java:206)
  at org.ajax4jsf.webapp.BaseFilter.handleRequest(BaseFilter.java:290)
  at org.ajax4jsf.webapp.BaseFilter.processUploadsAndHandleRequest(BaseFilter.java:388)
  at org.ajax4jsf.webapp.BaseFilter.doFilter(BaseFilter.java:515)
  at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
  at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
  at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
  at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
  at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
  at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
  at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
  at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
  at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
  at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
  at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
  at java.lang.Thread.run(Thread.java:745)
    
asked by anonymous 29.06.2015 / 22:20

1 answer

3

Part of the problem was in the fact that the outdated jaybird driver. this was in version 2.1.6 and she had this error link

Another detail is that I need to call createStatment and preparedStatment by setting the " resultSetHoldability " parameter to the value ResultSet.HOLD_CURSORS_OVER_COMMIT

With this, the ResultSet starts working as I wanted.

    
30.06.2015 / 13:30