How to avoid accidentally closing an SQL Connection in Java?

Question

Currently what i am doing for transaction management is:

Connection connection = getConnection();
connection.setAutoCommit(false);
updateTableX ( connection, ... );
updateTableY ( connection, ... );
connection.commit();
closeConnection();

I would like to know, if it is possible to avoid closing the connection in my 'updateTableX' method. Because if someone accidentally closes the connection then my updateTableY will not be having the connection and it will throw the exception.

Answer 1

(I am unfamiliar with Java specifically)

Assuming you have some sort of database managing object, you could have it make sure it is connected before it attempts any operations.

You could try to restrict access to closing the connection but how would you decide if it should be closed, or if it's "accidental" (however you define that)?

Answer 2

Just discipline. In general, methods shouldn't try to take responsibility for closing things passed into them as parameters - with the exception of situations where you create a new object to wrap an existing one.

The way to avoid closing the connection in updateTableX is just to make sure you don't put a call to close() into the code. This is no different than any other bug really. How do you stop updateTableX from arbitrarily updating a different table, or throwing an exception, or doing anything else it's not meant to? Code reviews, unit tests, integration tests, manual testing etc...

I mean you could write a Connection implementation which wraps another connection and proxies all the methods through except close() but it sounds like a waste of time - if you don't trust the developers involved not to close the connection, do you trust them to get the rest of the code right?

Answer 3

I don't think what you are asking is possible.

You can technically make a copy of your connection object, but then what happens if the client programmer doesn't close the connection?

Answer 4

Like Jon said, if you really want to forbit to call close() you could write a decorator implementation that forwards to your "real" Connection object. I don't post a code example because the Connection interface is too big. With modern IDEs owever it is no problem to generate the code.

Recipe (presuming you're using Eclipse):

1. Create a class that implements collection, but do not implement the methods
2. Create a field private Connection delegate
3. Select the field name -> Source (Menu) -> "Generate Constructor using fields" -> make sure the field is selected and press ok
4. Select the field name -> Source (Menu) -> "Generate Delegate Methods..." -> check every method on you field
5. Change the implementation of the close() method to throw an UnsupportedOperationException

However like Jon said, I would really think about doing something like that. And maybe you just use a Object-Relational-Mapper (e.g. Hiberate) to encapsulate all of your Database access logic. An additional very helpful framework in this area is Spring, especially if you do not want to care about Connection and DataSource handling...