Close result set in loop using Java

Question

This question should be simple and it may annoy , but still I got this doubt about closing of Result Set using Java. It should be done for each statement or result set should be closed only in final?

       try {
        Class.forName(<driver class>);                
        con = DriverManager.getConnection(
            "IP", 
            "username",
            "password");
        for(String dealId : items) {
            String sql= "SQL Query";
            preparedStatement = con.prepareStatement(sql);
            rs = preparedStatement.executeQuery();
            while(rs.next()) {
                count += rs.getInt("total");                    
            }
            // Result should be closed here as the statement got executed?
        }
        System.out.println(count);
        if(items.size() == count) {
            dealsBelongToTheParty = true;
        }

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        rs.close(); // Or this is right?
        preparedStatement.close();
        if(!con.isClosed())
            con.close();
    }

You seem to be creating a new result set in each iteration of your `for` loop. In that case, the logical thing would be to close each one in the for loop, instead of just closing whichever one is left at the end. — khelwood, Sep 28 '17 at 11:23
Cramming multiple close statements in one finally block is a really bad idea. You'll cause a connection leak. — Nathan Hughes, Sep 28 '17 at 11:35
You are leaking both ResultSets and PreparedStatements. You should close both every time around the loop. Fortunately provided you are using a fully compliant JDBC driver they will all get closed when you close the statement. But that may not be soon enough for all purposes. — user207421, Sep 28 '17 at 11:35
1) First of all put preparedStatement = con.prepareStatement(sql); outside the loop. 2) close ResultSet each time: rs = preparedStatement.executeQuery(); creates a new ResultSet each time, the previous resutl set remains unclosed — Evgeniy Dorofeev, Sep 28 '17 at 11:36
@EvgeniyDorofeev it is assumed that the loop var `dealId` is used in the query... — Sharon Ben Asher, Sep 28 '17 at 11:37
@ Sharon Ben Asher yes, but this is supposed to be parameter of parameterized SQL — Evgeniy Dorofeev, Sep 28 '17 at 11:39
@EvgeniyDorofeev this is another issue of optimizing the sql query. it will work just fine if the variable is embedded in the sql string — Sharon Ben Asher, Sep 28 '17 at 11:41
@EJP, Can you provide the exact solution where there will be no leakages? — Aliy, Sep 28 '17 at 11:44

Sharon Ben Asher · Accepted Answer · 2017-09-28T11:38:28.930

2

I suggest to use Java 7 try-with-resources
(the code below assumes that the loop var dealId is used in the query)

Class.forName(<driver class>);                
try (Connection con = DriverManager.getConnection(
        "IP", "username", "password")) {
    for(String dealId : items) {
        String sql= "SQL Query with " + dealId;
        // resources are opened by order 
        try (PreparedStatement preparedStatement = con.prepareStatement(sql);
             ResultSet rs = preparedStatement.executeQuery()) {
            while(rs.next()) {
                count += rs.getInt("total");                    
            }
        } // resources are implicitly closed in reverse order of open
    }
} catch (Exception e) {
    e.printStackTrace();
}

System.out.println(count);
if(items.size() == count) {
    dealsBelongToTheParty = true;
}

edited Sep 28 '17 at 11:38

answered Sep 28 '17 at 11:31

Sharon Ben Asher

13,849
5
33
47

ResultSet is also AutoCloseable – Evgeniy Dorofeev Sep 28 '17 at 11:34
1

...and is also inside try-with-resources... – Sharon Ben Asher Sep 28 '17 at 11:35
There won't be any memory leakages in this case? and where did you close the statement, resultset & connection? I was asking about closing the result set – Aliy Sep 28 '17 at 11:46
It should be done in the finally or right after try block? Im worrying about memory leakage. – Aliy Sep 28 '17 at 11:47
@SharonBenAsher, please add the closing statements of resources. After which line you want to close. You cannot close it after try, because rs object is defined inside try block. Please let me know – Aliy Sep 28 '17 at 11:53
1

@Aliy, in try-with-resouces there is no explicit close. it is added by the compiler at the closing bracket of the try block – Sharon Ben Asher Sep 28 '17 at 12:05
So, there will not be any memory leakages in your code. Am I right? – Aliy Sep 28 '17 at 12:17
no leakage of DB resources – Sharon Ben Asher Sep 28 '17 at 12:33

score 0 · Answer 2 · answered Sep 28 '17 at 11:25

0

https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html

"A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results."

So, you shouldn't worry about closing ResultSet.

answered Sep 28 '17 at 11:25

DDovzhenko

1,295
1
15
34

then why is `ResultSet` implements `AutoCloseable`? – Sharon Ben Asher Sep 28 '17 at 11:26
@SharonBenAsher, because it can be closed exclicitly. – M. Prokhorov Sep 28 '17 at 11:28
3

The OP is creating a new statement in every iteration of the `for` loop. So the cited doc doesn't cover his case. – Ralf Kleberhoff Sep 28 '17 at 11:28

Close result set in loop using Java

2 Answers2

Linked