@Resource UserTransaction utx1 and @PersistenceContex programmatically in java application server

Question

I am using glassfish server with multiple jdbc connection pools. I want to have EntityManagerFactories for every jdbc connection.

Using the code

Map props = new HashMap();
props.put(PersistenceUnitProperties.JTA_DATASOURCE, <my jdbc datasource>);
Persistence.createEntityManagerFactory(<persistence unit name>, props);

Works fine but I cannot combine it with the UserTransation to have transaction begin and commit, rollback. Furthermore entityManager.getTrasnaction().begin, commit, rollback don’t work (I get records persisted in the database when an error occurs).

Things work great when I use UserTrasnaction and EntityManager injected with

@Resource
  UserTransaction utx1;
  @PersistenceContext(unitName = <persistence unit name>)
  private EntityManager em1;

With the injection the utx1.begin(), utx1.commit() controls the entityManager perfectly! But the problem is that I cannot use my multiple jdbc connection pools that i have set up on my server.

So my question is: Can I do programmatically what this injection does?

 @Resource
  UserTransaction utx1;
  @PersistenceContext(unitName = <persistence unit name>)
  private EntityManager em1;

Thanx!

I have tried this, but it doesnt work

  @Resource
  UserTransaction utx1;
  
  @PersistenceContext(
          properties = {@PersistenceProperty(name=PersistenceUnitProperties.JTA_DATASOURCE, value="ORCLH_MARMA")}, 
          unitName = "MINLO")
  private EntityManager em1; 

Answer 1

You should use CDI @Qualifiers and EntityProducers to do that, and use the persistence.xml file to define your multiple persistence-units (with the same entities), provided they are prepared to do that.

First things first: define as many datasource qualifiers as needed

@Qualifier
@Retention(RUNTIME)
@Target({METHOD, FIELD, PARAMETER, TYPE})
public @interface MainDatabase {
}

and a second one

@Qualifier
@Retention(RUNTIME)
@Target({METHOD, FIELD, PARAMETER, TYPE})
public @interface SecondaryDatabase {
}

Then create a EntityManager producer using proper datasources:

@ApplicationScoped
public EntityManagerProducer {
    
    @Produces @RequestScoped
    @MainDatabase
    @PersistenceContext(unitName = "main-pu-01")
    private EntityManager mainEntityManager;

    @Produces @RequestScoped
    @SecondaryDatabase
    @PersistenceContext(unitName = "secondary-pu-01")
    private EntityManager secondaryEntityManager

}

In your persistence.xml, define both persistence units for the same entities, but with different names and data-sources:

<persistence version="2.1"...>
    <persistence-unit name="main-pu-01" transaction-type="JTA">
        <jta-data-source>jdbc/maindatasource</jta-data-source>
        ...
    </persistence-unit>
    <persistence-unit name="secondary-pu-01" transaction-type="JTA">
        <jta-data-source>jdbc/secondarydatasource</jta-data-source>
        ...
    </persistence-unit>
</persistence>

Then, in your EJBs or CDI beans, inject the desired entity manager to perform your ops:

@RequestScoped
public abstract class EntityController<E> {

    @Inject @MainDatabase
    private EntityManager mainEM;

    @Inject @SecondaryDatabase
    private EntityManager secondaryEM;

    public E doSomethingInMain(E e) {
        return mainEM.something...
    }

    public E doSomethingInSecondary(E e) {
        return secondaryEM.something...
    }

}

Be aware that @Transactional operations may not propagate instantly between EntityManagers if they share the same database, except if you tune caches properly.

I don't know why you need to use different users for each datasource, so I suggest you check if you aren't incurring in a xyproblem.