Efficient way to close multiple statements and resultsets?

I tried closing them all in one finally block but it caused an ORA-01000 and ORA-00604 error. So now, what I did is, each statements and resultsets has their own try-catch-finally block. It works pretty great but I’m still wondering if there is a more efficient way to close all the statements and resultsets

try{
    Connection conn = HikariCp.getConnection();

    try{
        String sql1 = "SELECT * FROM TABLE1";
        PreparedStatement pst1 = conn.prepareStatement(sql1);
        ResultSet rs1 = pst1.executeQuery();

    }catch(SQLException e){
        JOptionPane.showMessageDialog(this,e.getMessage());
    }finally{
        try { if (rs1 != null) rs1.close(); } catch (Exception e) {};
        try { if (pst1 != null) pst1.close(); } catch (Exception e) {};
    }

    try{
        String sql2 = "SELECT * FROM TABLE2";
        PreparedStatement pst2 = conn.prepareStatement(sql2);
        ResultSet rs2 = pst2.executeQuery();

    }catch(SQLException e){
        JOptionPane.showMessageDialog(this,e.getMessage());
        
    }finally{
        try { if (rs2 != null) rs2.close(); } catch (Exception e) {};
        try { if (pst2 != null) pst2.close(); } catch (Exception e) {};
    }

    try{
        String sql2 = "SELECT * FROM TABLE3";
        PreparedStatement pst3 = conn.prepareStatement(sql3);
        ResultSet rs3 = pst3.executeQuery();

    }catch(SQLException e){
        JOptionPane.showMessageDialog(this,e.getMessage());
    }finally{
        try { if (rs3 != null) rs3.close(); } catch (Exception e) {};
        try { if (pst3 != null) pst3.close(); } catch (Exception e) {};
    }

}catch(Exception e){
    JOptionPane.showMessageDialog(this,e.getMessage());
}finally{
    try { if (conn != null) conn.close(); } catch (Exception e) {};
}

Answer

what luk2302 said – use try-with-resources. this closes the connection automatically after executing that block.

your initial idea of closing all connections in a single finally-block is not really desireable, as you leave connections open that are not being used. depending on your Database, you can only have X connections open, so that might be the cause of the Errors.

Leave a Reply

Your email address will not be published. Required fields are marked *