Combining JPA and JDBC actions in one transaction

Question

So, I have an application with some legacy JDBC calls in it that I need to update with some additional JPA actions. I need to be able to make both JDBC calls and JPA calls as part of the same DB transaction. I'm using OpenJPA 2.1.1 and Postgres 9.1, if it matters. The following code appears to work correctly - I've run some basic tests, and both the JDBC and JPA statements execute; an error in either one results in the pair of statements not happening (e.g. they're part of the same DB transaction). Are there any problems with it that I'm not seeing - some best practice that I'm violating, or some other reason I can't re-use the Connection object in this manner?

EntityManager em = _em; //acquired from OpenJPA
em.getTransaction().begin();
Connection conn;
try {
  OpenJPAEntityManager oem = (OpenJPAEntityManager) em.getDelegate();
  conn = oem.getConnection();
  PreparedStatement ps = conn.prepareStatement(myStatement);
  //initialize parameters in ps
  ps.executeUpdate();
  em.merge(myJpaObject);
  em.getTransaction().commit();
} finally {
    if (ps != null && !ps.isClosed()) {
      ps.close();
    }
    if (em.getTransaction().isActive()) {
    em.getTransaction().rollback();
  }
}

Thanks!

Answer 1

With one tweak, i think that should be fine.

It's worth noting what the OpenJPA documentation has to say about it:

The returned connection is only guaranteed to be transactionally consistent with other EntityManager operations if the EntityManager is in a managed or non-optimistic transaction, if the EntityManager has flushed in the current transaction, or if you have used the OpenJPAEntityManager.beginStore method to ensure that a datastore transaction is in progress. Always close the returned connection before attempting any other EntityManager operations. OpenJPA will ensure that the underlying native connection is not released if a datastore transaction is in progress.

Your transaction is not managed, but it is, i think, non-optimistic. Also, you haven't done any JPA work between starting the transaction and doing the JDBC stuff, so i think you probably fall under the "if the EntityManager has flushed in the current transaction" case.

The one thing you're not doing is closing the connection before carrying on with JPA. I assume that OpenJPA doesn't return the actual connection it's using, but some wrapper round it which should be closed before OpenJPA resumes work.