Managing Multiple Database Connections

Question

I've been struggling with this problem for days,

Here is the scenario: I have several databases, one for each of my customers, all of them with the same structure(same tables and columns), so my application needs to decide at runtime with which one it needs to connect. I'm using JPA2, EclipseLink and EJB3.

My first attempt was to implement a custom EntityManager with all the logic to performs the operations on the right database, then I configured this EntityManager as an Stateless EBJ in order to make it possible to inject it with the @EBJ annotation (as described at this link: http://www.hostettler.net/blog/2012/11/20/multi-tenancy/). I coundn't make it work because it was throwing an exception when trying to inject the EntityManager.

So I decided to try something else, I've created EntityManagerFactory and I passed the JTA_DATASOURCE to it(after decide at runtime which one to use), so it could connect to the right database.

Here is the code:

@Stateless
@TransactionManagement(TransactionManagementType.CONTAINER)
public class TestEntDAO {

    private EntityManager em;
    private EntityManagerFactory emf;

    @PostConstruct
    public void init() {
        em = getEntityManager();
    }

    public EntityManager getEntityManager() {
        Map props = new HashMap();
        props.put(PersistenceUnitProperties.TRANSACTION_TYPE, "JTA");
        props.put(PersistenceUnitProperties.JTA_DATASOURCE, dataSourceName());
        emf = Persistence.createEntityManagerFactory("testePU", props);
        em = emf.createEntityManager();
        return em;
    }

    public String dataSourceName(){
        if(someCondition){
            return "db1";
        }else{
            return "db2";
        }
    }
}

This worked perfectly, the only problem is that the transaction is not managed by the container, so I had to explicitly mark the transaction's boundaries(call begin() and commit()). I could just use the @PersistenceContext annotation to make it work, but then I wouldn't have the EntityManagerFactory to pass the datasource.

Does anyone know of a way to use the Container-Managed Transactions(CMT) and still be able to pass the datasource?

Why can't you inject the datasource name? Or read it from a properties file? — Elliott Frisch, Sep 19 '14 at 01:08
I just couldn't find a way to do it and use the @PersistenceContext at the same time — Leonardo Jines, Sep 19 '14 at 01:15

score 0 · Answer 1 · answered Sep 19 '14 at 12:29

0

Maybe try to define 3 Data sources and 3 Persistence units.

<persistence-unit name="PU1">
  <jta-data-source>jdbc/DS1</jta-data-source>
  ...
</persistence-unit>
<persistence-unit name="PU2">
  <jta-data-source>jdbc/DS2</jta-data-source>
  ...
</persistence-unit>
<persistence-unit name="PU3">
  <jta-data-source>jdbc/DS3</jta-data-source>
  ...
</persistence-unit>

And inject Entity manager from whatever Persistence unit you want.

@PersistenceContext(unitName = "PU2")
EntityManager em;

This should work, although I didn't test it.

answered Sep 19 '14 at 12:29

zbig

3,830
2
29
37

I've already tried this, it worked, but if one of the connections fails the application won't deploy. – Leonardo Jines Sep 19 '14 at 12:49
Maybe it can be overcome. What kind of error did you have? – zbig Sep 19 '14 at 12:52
Something like this --> Internal Exception: java.sql.SQLException: Error in allocating a connection. Cause: Connection could not be allocated because: The connection attempt failed. – Leonardo Jines Sep 19 '14 at 13:04
Consider asking a new question with the full stack trace as well as you info about app server and DB with Datasource config. – zbig Sep 19 '14 at 13:33
Here is the new quetion: http://stackoverflow.com/questions/25935889/application-does-not-deploy-when-the-database-is-not-available – Leonardo Jines Sep 19 '14 at 14:07

Managing Multiple Database Connections

1 Answers1