JDBC distributed transaction and explicit rollback/commit

Question

I'm working on an existing project that's configured for distributed transaction.

The project is using Hibernate but for some historical reasons, the part on which I'm working on uses JDBC and needs to stay that way.

To get a connection I have to call an API which gives me back the JDBC connection of the hibernate session.

I need to wrap up some updates/inserts in one transaction so here's what I'm doing :

1. Set autoCommit to false on my connection.
2. Do my inserts (prepare statements, execute query)
3. Call commit.

At commit, I get an SQLException because apparently it's not allowed to call commit/rollback explicitely with distributed transactions. I should state that changing the datasource configuration to non XA is not an option.

Any ideas how I might get around this ?

        connexionDiff.setAutoCommit(false); 
        psInsertLiv = connexionDiff.prepareStatement(reqInsertLivraison);
        psInsertLivHisto = connexionDiff.prepareStatement(reqInsertLivraisonHisto);
        psSequence = connexionDiff.prepareStatement(reqCleLivraison);

        ps = connexionDiff.prepareStatement(requeteRelivraison);
        rs = ps.executeQuery();

        while(rs.next()) {

            rsSequence = psSequence.executeQuery();
            while ( rsSequence.next() ) {
                sequenceLivraison = rsSequence.getInt(1);
            }

            psInsertLiv.setInt(1, sequenceLivraison);
            psInsertLiv.setInt(2, rs.getInt(1));
            psInsertLiv.executeUpdate();

            psInsertLivHisto.setInt(1, sequenceLivraison);
            psInsertLivHisto.setInt(2, rs.getInt(1));
            psInsertLivHisto.executeUpdate();

            connexionDiff.commit();
        }

    } catch (SQLException ex) {
       try{
        connexionDiff.rollback();
      }catch {
//......
}
    } finally {
        //.....
    }

Thx

Answer 1

As you are using a XA connection, you surely are managing your transactions using JTA.

If it is a standalone JTA, get the UserTransaction and call begin and commit there. If it is inside an Application Server, use the transactional annotation or whatever the app server gives you to manage transactions. Look at this page to get an idea of how it is done in JavaEE 6.

If you are using Spring, you can also use the transactional annotation to wrap your code inside a transaction. Here is the relevant documentation.

The transaction management should be the same no matter if you are using Hibernate or plain JDBC. So check how it is done when it is using Hibernate in your project, and follow the same steps.