jdbc transaction management : when releasing resources and distributed transactions

Question

hello i'm a beginner java developer and this is one question in a list i'm posting since i've started porting a very old web service . I'm trying to improve the access to db and i'm finding a lot of code i believe migth be dangerous, but being not so experienced with java i cannot be sure . Actually i've a class which manages the db connection and holds static references to a connection and a statement objects : it exposes an "openDb" method which initializes the sql connection class member variable.

Problem number 1 : the class member variables are static, what is going to happen if the openDb is called multiple time and the first (for example) instance of the class is still executing a query?

the above class expose a method name "closeDb" which releases all resources (connection and statement both static members! ) and a "closeStatement" method which release only the statement member, and is used externally.

Problem number 2 : stements and resultset must be closed after a transaction committ/rollback or can be closed immediatly?

Still the same class handles the commit/rollback exposing some methods (to set autocommit to false, to commit or rollback ) . An instance of this class is passed to other classes instance (for example the usual employee department classes ) which execute their own queries on the db and finally the commit/rollback is called by the connection manager class . Is this "architecture" correct or do you see any danger in it ?

thank you in advance

Answer 1

This is a very bad design and the observations you make concerning the static variables are correct. If one thread calls openDb and another thread does the same, things will go wrong. Maybe this happens not all the time which makes this hard to debug.

Concerning problem nr. 2: you should close things once you're done with them. This way you don't keep resources occupied unnecesary. So when you have read all (necessary) data from a result set, close it. It's also not a good idea to have statements and result sets escape the transaction boundary. So instead of:

begin transaction
  open result set
commit transaction
read from result set

you should have:

begin transaction
  open result set
  read from result set
commit transaction

To make the picture complete, you should always have the following pattern when using transactions:

begin transaction
  open connection
    read/modify data
  close connection
commit transaction

The open connection/close connection combination may occur multiple times within one transaction.

EDIT: To complete the picture even more: in the usual scenario, your application has several layers (e.g. UI, business, data). Transactions are usually started and committed inside the business layer. From the business layer you call one or more data layer methods that become a part of the transaction:

// Inside business layer:
public void businessMethod(...) {
    try {
        // Begin transaction.
        dataMethod1(...);
        dataMethod2(...);
        ...
        // Commit transaction.
    }
    catch (...) {
    }
}

// Inside data layer.
public void dataMethod1(...) {
    // Open connection.
    try {
        // Get data from database.
        // Manipulate data.
    }
    finally {
        // Close connection.
    }
}

Some additional notes:

• The transaction commit is the final statement inside the try/catch (if you want to return data from your method, it can be followed by a return statement). This ensures that the transaction only commits when everything is ok (i.e. no exception thrown).
• The connection close is inside the finally part of the try statement. This ensures that it is always closed, no matter what happens inside your data method.