Resultset object is null after Try-catch. Java

Question

I did a query in my database and it worked, but I’m not able to use the Resultset of this query. I debugged and found that it has a correct content, but it gets null after Try catch where it is filled:

Look at the two print outs I made from rs (Resultset):

public ContaCorrente getConta(int agencia, int numConta) {

        String query = "SELECT * FROM CONTACORRENTE" + " WHERE AGENCIA = ? AND NUMERO = ?";
        ResultSet rs = null;
        try (PreparedStatement st = ConexaoComBd.getConectora().getConexao().prepareStatement(query);) {
            st.setInt(COLUNA_AGENCIA, agencia);
            st.setInt(COLUNA_NUMCONTA, numConta);
            rs = st.executeQuery();
            System.out.println("Result set dentro do try catch: "+rs);//AQUI TUDO CERTO

        } catch (SQLException e) {
            System.err.println("Erro no banco de dados ao consultar conta corrente. Tente novamente"
                    + "ContaCorrenteDAO Linha67");
            System.err.println(e);
        } catch (Exception e2) {
            System.err.println("Erro geral ao consultar conta corrente. Tente novamente ");
        }                                                                                     
         System.out.println("ResultSet após o try catch:" +rs); //AQUI ELE ESTÁ  null
        ContaCorrente c = montarConta(rs);
        return c;
    }

Why does that happen? Where am I going wrong?

Answer 1

You are using the standard AutoClosable to create your PreparedStatement, in this pattern you create an instance of an obejto that implements the interface Closable on Try, whenever Try finishes it automatically closes the created objects.

The code below will work:

public ContaCorrente getConta(int agencia, int numConta) {

        String query = "SELECT * FROM CONTACORRENTE" + " WHERE AGENCIA = ? AND NUMERO = ?";
        ResultSet rs = null;
        try (PreparedStatement st = ConexaoComBd.getConectora().getConexao().prepareStatement(query);) {
            st.setInt(COLUNA_AGENCIA, agencia);
            st.setInt(COLUNA_NUMCONTA, numConta);
            rs = st.executeQuery();
            System.out.println("Result set dentro do try catch: "+rs);//AQUI TUDO CERTO
            ContaCorrente c = montarConta(rs);
            return c;

        } catch (SQLException e) {
            System.err.println("Erro no banco de dados ao consultar conta corrente. Tente novamente"
                    + "ContaCorrenteDAO Linha67");
            System.err.println(e);
        } catch (Exception e2) {
            System.err.println("Erro geral ao consultar conta corrente. Tente novamente ");
        }                                                                                     
         System.out.println("ResultSet após o try catch:" +rs); //AQUI ELE ESTÁ  null
         return null;
    }