MySQL JDBC closeOnCompletion has no effect on PreparedStatement

Question

JDBC introduced a method called closeOnAutoCompletion, which states that it closes the statement, when all dependent resultSets are close.

I have a method to create prepared statements

public final PreparedStatement statement(Connection connection) throws SQLException {
    PreparedStatement stmt = connection.prepareStatement(query, ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CONCUR_READ_ONLY);
    stmt.closeOnCompletion();
    return stmt;
}

No I'm calling this method as follows

@Test
public void testCloseOnCompletionSemiManually() throws SQLException {
    PreparedStatement stmt = shards.statement(db);
    assertTrue("Statement must be closeOnAutoCompletion", stmt.isCloseOnCompletion());

    try (ResultSet rs = stmt.executeQuery()) {
        while (rs.next()) {
            //System.out.println("Shard id: " + rs.getInt("SHARD_ID"));
        }
    }
    assertTrue("Statement must be closed after all results sets have been processed", stmt.isClosed());
}

The last check fails as the statement is not closed.

Is this a problem due to the mysql implementation? Or did I missunderstand the JavaDoc.

Update: I'm using version 5.1.24

thanks, Muki

Maybe the close is asynchronous? Try sleeping for some time after exiting try clause. If it does not help you found yourself a bug :) — Jarosław Jaryszew, Apr 10 '13 at 10:36
It is entirely possible to the MySQL driver hasn't implemented this yet. — Mark Rotteveel, Apr 10 '13 at 10:44
It's true the implementation is very new. However I want to check if I'm doing something wrong, before opening a bug — Muki, Apr 10 '13 at 10:54
I openend a bug http://bugs.mysql.com/bug.php?id=68916 The implementation looks more like a placeholder (StatementImpl Line 3041) — Muki, Apr 10 '13 at 11:05

score 3 · Accepted Answer · answered Apr 10 '13 at 14:06

3

The driver doesn't support it yet, however the test is not correct either. Note that the API docs for Statement say that closeOnCompletion() closes the statement when all dependent result sets are closed, not scrolled past the end, so I'm not sure what behavior is actually assumed to be happening in your case.

answered Apr 10 '13 at 14:06

Mark Matthews

46
1

1

It uses try-with-resources. Java will automatically call the `ResultSet.close()` method at the end of the try block. – Mark Rotteveel Apr 10 '13 at 14:42
BTW: How do you know it doesn't support it, the [5.1.21 release announcement](http://forums.mysql.com/read.php?3,559877,559877) seems to indicate it is supported – Mark Rotteveel Apr 10 '13 at 14:44
I looked at the implementation (5.1.24) and the implementation seems more like a placeholder. A flag plus the API methods. My IDE wasn't able to find any usages of the methods/flags internally in the mysql driver. Regarding the test. The resultSet is closed. I also checked if there are other open resultSets. Nope. So this should work, as it is the simplest use case. – Muki Apr 11 '13 at 11:16

mthmulders · Answer 2 · 2013-04-10T10:59:22.487

-1

According to this announcement, version 5.1.21 of the MySQL Connector/J (the official name of the JDBC driver for MySQL) should support closeOnAutoCompletion. Can you check you're using that version?

edited Apr 10 '13 at 10:59

answered Apr 10 '13 at 10:27

mthmulders

9,483
4
37
54

He is using new feature of java 7 - http://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html – Jarosław Jaryszew Apr 10 '13 at 10:33
2

The `ResultSet` is being closed by the try-with-resources block; if `closeOnCompletion` is correctly implemented, this should result in a close of the `Statement` as well. – Mark Rotteveel Apr 10 '13 at 10:45

MySQL JDBC closeOnCompletion has no effect on PreparedStatement

2 Answers2