Why different persistence units with separated data sources query the same data source?

Question

I'm developing a webapp which needs access to two different database servers (H2 and Oracle). The container is an Apache Tomee 1.5.1 and I'm using the Java EE stack with libraries provided in it (JSF, JPA, CDI, EJB, etc.).

I'm trying to use two entity managers inside an XA transaction to extract data from the Oracle database and persist it in the H2 after transforming it, BUT all the queries are executed against the H2 database no matter the entity manager I use. Any help?

EDIT: I found that if I try to access the entity managers in inverse order, they behavior is the same but accessing to Oracle. I.e.: the entity managers stay with the first database accessed.

The EJB where this happens (calling service.getFoo() from JSF):

@Named
@Stateless
public class Service {
    @Inject
    @OracleDatabase
    private EntityManager emOracle;

    @Inject
    @H2Database
    private EntityManager emH2;

    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public List<Foo> getFoo() {
        TypedQuery<Foo> q = emH2.createQuery(
                "SELECT x FROM Foo f", Foo.class);
        List<Foo> l = q.getResultList();
        if (l == null || l.isEmpty()) {
            update();
        }

        return q.getResultList();
    }

    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public void update() {
        // FAIL: This query executes against H2 with Oracle entity manager!
        List<Object[]> l = emOracle.createNativeQuery("SELECT * FROM bar ").getResultList(); 

        //more stuff...
    }
}

The resource producer (CDI) for the entity managers (where @H2Database and @OracleDatabase are qualifiers):

public class Resources {
    @Produces
    @PersistenceContext(unitName = "OraclePU")
    @OracleDatabase
    private EntityManager emOracle;

    @Produces
    @PersistenceContext(unitName = "H2PU")
    @H2Database
    private EntityManager emH2;
}

My peristence.xml looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0"
    xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
    <persistence-unit name="H2PU"
        transaction-type="JTA">
        <provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>
        <jta-data-source>H2DS</jta-data-source>
        <class>my.app.h2.Foo</class>
        <exclude-unlisted-classes>true</exclude-unlisted-classes>
    </persistence-unit>

    <persistence-unit name="OraclePU" transaction-type="JTA">
        <provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>
        <jta-data-source>OracleDS</jta-data-source>
        <class>my.app.oracle.Bar</class>
        <exclude-unlisted-classes>true</exclude-unlisted-classes>
    </persistence-unit>
</persistence>

And finally, the data sources inside tomee.xml (there aren't any other data sources configured inside this file):

<Resource id="OracleDS" type="javax.sql.DataSource">
    jdbcDriver = oracle.jdbc.xa.client.OracleXADataSource
    jdbcUrl = jdbc:oracle:thin:@server:port:instance
    jtaManaged = true
    password = abcde
    userName = user
</Resource>

<Resource id="H2DS" type="javax.sql.DataSource">
    jdbcDriver=org.h2.jdbcx.JdbcDataSource
    jdbcUrl=jdbc:h2:h2/db;AUTO_SERVER=TRUE
    jtaManaged = true
    password = edcba
    userName = user
</Resource>

Solution

Container Managed Persistence Contexts

When using container-managed persistence contexts (as you are via @PersistenceContext annotations), the JPA spec specifies that only ONE persistence context may be associated with a JTA transaction.

The persistence context is created by the Java EE container. Despite appearances of the code (@PersistenceContext annotations seem to suggest that PC is injected directly into your EntityManager instance variables), the persistence context is actually stored as a reference WITHIN THE JTA TRANSACTION. Each time an EntityManager operation occurs it does not refer to it's own internal persistence context. Instead it does a special operation because it is container managed - it always looks up the persistence context within the JTA Transaction and uses that. This is called JTA persistence context propagation.

Some Quotes from the JPA spec:

When a container-managed entity manager is used, the lifecycle of the persistence context is always managed automatically, transparently to the application, and the persistence context is propagated with the JTA transaction.

Container-managed Transaction-scoped Persistence Context

... A new persistence context begins when the container-managed entity manager is invoked[76] in the scope of an active JTA transaction, and there is no current persistence context already associated with the JTA transaction. The persistence context is created and then associated with the JTA transaction.

Container-managed Extended Persistence Context

... A container-managed extended persistence context can only be initiated within the scope of a stateful session bean. It exists from the point at which the stateful session bean that declares a dependency on an entity manager of type PersistenceContextType.EXTENDED is created, and is said to be bound to the stateful session bean. The dependency on the extended persistence context is declared by means of the PersistenceContext annotation or persistence-context-ref deployment descriptor element. The persistence context is closed by the container when the @Remove method of the stateful session bean completes (or the stateful session bean instance is otherwise destroyed).

Requirements for Persistence Context Propagation

... If a component is called and there is no JTA transaction ..., the persistence context is not propagated. • Invocation of an entity manager defined with PersistenceContext- Type.TRANSACTION will result in use of a new persistence context. • Invocation of an entity manager defined with PersistenceContext- Type.EXTENDED will result in the use of the existing extended persistence context bound to that component.

... If a component is called and the JTA transaction is propagated into that component: • If the component is a stateful session bean to which an extended persistence context has been bound and there is a different persistence context bound to the JTA transaction, an EJBException is thrown by the container. • Otherwise, if there is a persistence context bound to the JTA transaction, that persistence context is propagated and used.

So that's your problem. The obvious $64 question: WHY does the spec ask for this???

Well, it's because it's a deliberate trade-off that brings powerful EntityManager magic to EJBs.

Using JTA transactions to propagate a single persistence context has a limitation: transactions cannot straddle multiple persistence contexts, so can't straddle multiple databases.

However, it also has a tremendous advantage: any entityManager declared in EJBs can automatically share the same persistence context and hence can operate on the same set of JPA entities and partake in the same transaction. You can have a chain of EJBs calling other EJBs of any complexity and they all behave sensibly and consistently against the JPA entity data. And they also do not need the complexity of consistently intializing/sharing entity manager references across method invocations - the EntityManagers can be declared privately in each method. The implementation logic can be very simple.

The Answer To Your Problem: Use Application-Managed Persistence Contexts (via application-managed EntityManagers)

Declare your entityManager via one of these approaches:

// "Java EE style" declaration of EM
@PersistenceUnit(unitName="H2PU")
EntityManagerFactory emfH2;
EntityManager emH2 = emfH2.createEntityManager();

OR

// "JSE style" declaration of EM
EntityManagerFactory emfH2 = javax.persistence.Persistence.createEntityManagerFactory("H2PU");
EntityManager emH2 = emfH2.createEntityManager();

and the same for emfOracle & emOracle.    

You must call em.close() when you are finished with each EM - prefereably via a final { } clause or via a Java 7 try-with-resources statement.

Application-managed EMs still partake in (i.e. synchronise with) JTA transactions. Any number of application-managed EMs can partake in a single JTA transaction - but none of these will ever have their persistence context associated with or propagated to any container managed EM.

If the EntityManager is created outside the context of a JTA transaction (before the transaction commenced), then you must ask it explicity to join the JTA transaction:

// must be run from within Java EE code scope that already has a JTA 
// transaction active:
em.joinTransaction();  

Or even simpler, if the EntityManager is created inside the context of a JTA transaction, then the application-managed EntityManager automatically joins the JTA transaction implicity - no joinTransaction() needed.

So Application-managed EMs can have a JTA transaction that straddles multiple databases. Of course, you can alway run a local resource JDBC transaction independent of JTA:

EntityTransaction tx = em.getTransaction();  
tx.begin();

// ....

tx.commit();

EDIT: EXTRA DETAILS for Transaction Management with Application-Managed Entity Managers

WARNING: the code samples below are for educational use - I've typed them off the top of my head to help explain my points & haven't had time to compile/debug/test.

The default @TransactionManagement parameter for EJBs is TransactionManagement.CONTAINER and the default @TransactionAttribute parameter for EJB methods is TransactionAttribute.REQUIRED.

There are four permutations for transaction management:

• A) EJB with CONTAINER managed JTA transactions

  This is the preferred Java EE approach.
  EJB class @TransactionManagement annotation:
  must set to TransactionManagement.CONTAINER explicitly or omit it to implicitly use the default value.
  EJB method @TransactionAttribute annotation: must set to TransactionAttribute.REQUIRED explicitly or omit it to implicity use the default value. (Note: if you had a different business scenario, you could use TransactionAttribute.MANDATORY or TransactionAttribute.REQUIRES_NEW if their semantics matched your needs.)
  Application-managed entity managers:
  they must be created via Persistence.createEntityManagerFactory("unitName") and emf.createEntityManager(), as described above.
  Join the EntityManagers with the JTA transaction:
  Create the EntityManagers WITHIN a transactional EJB method and they will automatically join the JTA transaction. OR if EntityManagers are created beforehand, call em.joinTransaction() within a transaction EJB method.
  Call EntityManager.close() when you are finished using them. That should be all that's required.

  Basic Examples - just use more EntityManagers for transaction across multiple DBs:

  @Stateless  
  public class EmployeeServiceBean implements EmployeeService {
  
      // Transactional method
      public void createEmployee() {
          EntityManagerFactory emf = Persistence.createEntityManagerFactory("EmployeeService");
          EntityManager em = emf.createEntityManager();
          Employee emp = ...; // set some data
          // No need for manual join - em created in active tx context, automatic join:
          // em.joinTransaction();         
          em.persist(emp);
          // other data & em operations ...
          // call other EJBs to partake in same transaction ...
          em.close();    // Note: em can be closed before JTA tx committed. 
                     // Persistence Context will still exist & be propagated 
                     // within JTA tx.  Another EM instance could be declared and it 
                     // would propagate & associate the persistence context to it.
                     // Some time later when tx is committed [at end of this 
                     // method], Data will still be flushed and committed and 
                     // Persistence Context removed .
      emf.close();
      }
  
  }
  
  
  
  @Stateful  
  public class EmployeeServiceBean implements EmployeeService {  
  
      // Because bean is stateful, can store as instance vars and use in multiple methods  
      private EntityManagerFactory emf;
      private EntityManager em;
  
      @PostConstruct      // automatically called when EJB constructed and session starts
      public void init() {
          emf = Persistence.createEntityManagerFactory("EmployeeService");
          em = emf.createEntityManager();
      }
  
      // Transactional method
      public void createEmployee() {
          Employee emp = ...; // set some data
          em.joinTransaction();         // em created before JTA tx - manual join
          em.persist(emp);
      }
  
      // Transactional method
      public void updateEmployee() {
          Employee emp = em.find(...);  // load the employee
          // don't do join if both methods called in same session - can only call once: 
          // em.joinTransaction();         // em created before JTA tx - manual join
          emp.set(...);                 // change some data
                               // no persist call - automatically flushed with commit
      }
  
      @Remove                           // automatically called when EJB session ends
      public void cleanup() {
          em.close();
          emf.close();
      }
  // ...
  }
  

• B) EJB with BEAN managed JTA transactions

  Use @TransactionManagement.BEAN.
  Inject the JTA UserTransaction interface, so the bean can directly mark JTA transactions.
  Manually mark/synchronize the transaction via UserTransaction.begin()/commit()/rollback().
  Ensure the EntityManager joins the JTA transaction - either create the EM in an active JTA transaction context OR call em.joinTransaction().

  Examples:

  @TransactionManagement(TransactionManagement.BEAN)  
  @Stateless  
  public class EmployeeServiceBean implements EmployeeService {
  
      // inject the JTA transaction interface
      @Resource UserTransaction jtaTx;
  
      public void createEmployee() {
          EntityManagerFactory emf = Persistence.createEntityManagerFactory("EmployeeService");
          EntityManager em = emf.createEntityManager();
          try {
              jtaTx.begin();
              try {
                 em.joinTransaction();         
                 Employee emp = ...; // set some data
                 em.persist(emp);
                 // other data & em operations ...
                 // call other EJBs to partake in same transaction ...
              } finally {
                  jtaTx.commit();
              }
          } catch (Exception e) {
             // handle exceptions from UserTransaction methods
             // ...
          }
  
          Employee emp = ...; // set some data
          // No need for manual join - em created in active tx context, automatic join:
          // em.joinTransaction();         
          em.persist(emp);
          em.close();    // Note: em can be closed before JTA tx committed. 
                     // Persistence Context will still exist inside JTA tx.
                     // Data will still be flushed and committed and Persistence 
                     // Context removed some time later when tx is committed.
          emf.close();
      }
  
  }
  

• C) POJO/Non-EJB with hand-coded (bean managed) resource local transactions (not JTA)

  Simply use the JPA EntityTransaction interface for tx demarcation (obtained via em.getTransaction()).

  Example:

  public class ProjectServlet extends HttpServlet {
  
      @EJB ProjectService bean;
  
      protected void doPost(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {
          // ...
          try {
              EntityManagerFactory emf = Persistence.createEntityManagerFactory("myPU");
              EntityManager em = emf.createEntityManager();
              EntityTransaction tx = em.getTransaction();
              tx.begin();
              try {
                  bean.assignEmployeeToProject(projectId, empId);
                  bean.updateProjectStatistics();
              } finally {
                  tx.commit();
              }
          } catch (Exception e) {
             // handle exceptions from EntityTransaction methods
             // ...
          }
      // ...
      }
  }
  

• D) POJO/Non-EJB with hand-coded (POJO-managed) JTA transactions

  This assumes the POJO/component is running in some container that has JTA support.
  If in a Java EE container, can use Java EE resource injection of JTA UserTransaction interface.
  (Alternatively, can explicitly lookup a handle to the JTA interface and do demarcation on it, then call em.getTransaction().joinTransaction() - see JTA spec.)

  Example:

  public class ProjectServlet extends HttpServlet {
  
      @Resource UserTransaction tx;
      @EJB ProjectService bean;
  
      protected void doPost(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {
          // ...
          try {
              tx.begin();
              try {
                  bean.assignEmployeeToProject(projectId, empId);
                  bean.updateProjectStatistics();
                  EntityManagerFactory emf = Persistence.createEntityManagerFactory("myPU");
                  EntityManager em = emf.createEntityManager();
                  // Should be able to avoid explicit call to join transaction.
                  // Should automatically join because EM created in active tx context.
                  // em.joinTransaction();
                  // em operations on data here
                  em.close();
                  emf.close();
              } finally {
                  tx.commit();
              }
          } catch (Exception e) {
             // handle exceptions from UserTransaction methods
             // ...
          }
      // ...
      }
  }