Can I close statements in a transaction before committing it in Derby (JDBC)?

Question

Basically I have (ignoring exception handling, etc.):

connection.setAutoCommit(false);  Statement statement1 = connection.createStatement(); statement1.executeUpdate("..."); statement1.close();  Statement statement2 = connection.createStatement(); statement2.executeUpdate("..."); statement2.close();  connection.commit(); 

If I understand correctly it shouldn't have any impact because all it really does is free the resources for the GC. Especially with Derby: You should explicitly close Statements, ResultSets, and Connections when you no longer need them. Connections to Derby are resources external to an application, and the garbage collector will not close them automatically.

However will it cause any issues with the transaction? I don't believe the transaction relies on the Statement. Can anyone please confirm this?

Answer 1

Absolutely, you can close them, and you should.

Answer 2

Generally speaking, once a Statement is executed, the underlying datasource/database is responsible for ensuring successful execution. Any failures are expected to result in SQLExceptions being thrown in the Statement.executeXXX invocations. And any successful execution would result in the database tracking these updates in a temporary working area. Committing the transaction merely ensures that the updates caused by the statements are written to a durable store, from the temporary working area. This is often the case in most/all databases.

It is therefore safe to close a Statement object once you no longer need it, without encountering any side effects in the transaction.