Accessing multiple database from a Java web application using JPA/EclipseLink/EJB

Question

I have built a simple SOAP java application(server side) and I am using Glassfish4,JPA/EclipseLink,EJB. I have set the db connections(resources/pools) in Glassfish. Please suggest some design pattern/knowledge to utilize multiple databases from a single application. Is creating multiple persistence-unit a good idea for multiple access?? Or is there any other optimized solution ? I have a generic class of database access.

public class GenericDAO<T> {

/*
* private static final EntityManagerFactory emf =
* Persistence.createEntityManagerFactory("icanPU"); private EntityManager
* em;
*/
/*
* Persistence context is injected with following @PersistenceContext
* annotation. This uses all persistence configurations as specified in the
* persistence.xml.
* 
* Note this kind of injection can only be done for JTA data sources.
*/
@PersistenceContext(unitName = "SavingBalanceDemoServer_PU")
private EntityManager em;
private Class<T> entityClass;

public EntityManager getEntityManager() {
return this.em;
}

public void joinTransaction() {
/* em = emf.createEntityManager(); */
em.joinTransaction();
}

public GenericDAO(Class<T> entityClass) {
this.entityClass = entityClass;
}

public void save(T entity) {
em.persist(entity);
}

// Added by Sudeep for bulk Insert of List object.
public void saveList(List<T> objList) {
for (Iterator<T> iterator = objList.iterator(); iterator.hasNext();) {
T t = (T) iterator.next();
em.persist(t);
}
}

public void delete(Object id, Class<T> classe) {
T entityToBeRemoved = em.getReference(classe, id);

em.remove(entityToBeRemoved);
}

public T update(T entity) {
return em.merge(entity);
}

public int truncateUsingNative(String tableName) {
Query qry = em.createNativeQuery("TRUNCATE TABLE " + tableName);

return qry.executeUpdate();
}

// Added by Sudeep for bulk Update of List object.
public void updateList(List<T> entity) {
for (Iterator<T> iterator = entity.iterator(); iterator.hasNext();) {
T t = (T) iterator.next();
em.merge(t);
}
}

public T find(int entityID) {
// em.getEntityManagerFactory().getCache().evict(entityClass, entityID);
return em.find(entityClass, entityID);
}

public T find(long entityID) {
// em.getEntityManagerFactory().getCache().evict(entityClass, entityID);
return em.find(entityClass, entityID);
}

public T find(Object compositePkObject) {
// em.getEntityManagerFactory().getCache().evict(entityClass, entityID);
return em.find(entityClass, compositePkObject);
}

public T findReferenceOnly(int entityID) {
return em.getReference(entityClass, entityID);
}

// Using the unchecked because JPA does not have a
// em.getCriteriaBuilder().createQuery()<T> method
@SuppressWarnings({ "unchecked", "rawtypes" })
public List<T> findAll() {
CriteriaQuery cq = null;
if (isDbAccessible()) {
try {
cq = em.getCriteriaBuilder().createQuery();
cq.select(cq.from(entityClass));
return em.createQuery(cq).getResultList();
} catch (org.eclipse.persistence.exceptions.DatabaseException ex) {
System.out.println("The zzz error is :" + ex.toString());
/*JSFMessageUtil jsfMessageUtil = new JSFMessageUtil();
jsfMessageUtil
.sendErrorMessageToUser("Database Server is unavailable or not accessible! Please, contact your system admin!");*/
return null;
}
}
return null;
}

private boolean isDbAccessible() {
return em.isOpen();
}

@SuppressWarnings("unchecked")
public List<T> findAllWithGivenCondition(String namedQuery,
Map<String, Object> parameters) {
List<T> result = null;
Query query = em.createNamedQuery(namedQuery);

if (parameters != null && !parameters.isEmpty()) {
populateQueryParameters(query, parameters);
}

result = (List<T>) query.getResultList();

return result;
}

@SuppressWarnings("unchecked")
public List<T> findAllWithGivenConditionLazyLoading(String namedQuery,
Map<String, Object> parameters,int startingAt, int maxPerPage) {
List<T> result = null;
Query query = em.createNamedQuery(namedQuery);

if (parameters != null && !parameters.isEmpty()) {
populateQueryParameters(query, parameters);
}
query.setFirstResult(startingAt);
query.setMaxResults(maxPerPage);

result = (List<T>) query.getResultList();

return result;

}

@SuppressWarnings("unchecked")
public List<T> findAllWithGivenConditionJpql(String jpql,
Map<String, Object> parameters) {
List<T> result = null;
Query query = em.createQuery(jpql);

if (parameters != null && !parameters.isEmpty()) {
populateQueryParameters(query, parameters);
}

result = (List<T>) query.getResultList();

return result;
}

@SuppressWarnings("unchecked")
public T findOneWithGivenConditionJpql(String jpql,
Map<String, Object> parameters) {
Query query = em.createQuery(jpql);

if (parameters != null && !parameters.isEmpty()) {
populateQueryParameters(query, parameters);
}
return (T) query.getSingleResult();
}

// Using the unchecked because JPA does not have a
// query.getSingleResult()<T> method
@SuppressWarnings("unchecked")
protected T findOneResult(String namedQuery, Map<String, Object> parameters) {
T result = null;

try {
if (!em.isOpen()) {
/*JSFMessageUtil jsfMessageUtil = new JSFMessageUtil();
jsfMessageUtil
.sendErrorMessageToUser("Database Server is unavailable or not accessible! Please, contact your system admin!");*/
} else {
Query query = em.createNamedQuery(namedQuery);

// Method that will populate parameters if they are passed not
// null and empty
if (parameters != null && !parameters.isEmpty()) {
populateQueryParameters(query, parameters);
}

result = (T) query.getSingleResult();
}

} catch (NoResultException e) {
// JSFMessageUtil jsfMessageUtil = new JSFMessageUtil();
// jsfMessageUtil.sendErrorMessageToUser("No Information Found...!");

// e.printStackTrace();
return null;
} catch (org.eclipse.persistence.exceptions.DatabaseException e) {
/*JSFMessageUtil jsfMessageUtil = new JSFMessageUtil();
jsfMessageUtil
.sendErrorMessageToUser("Database Server is unavailable or not accessible!");*/
e.printStackTrace();
}

return result;
}

private void populateQueryParameters(Query query,
Map<String, Object> parameters) {
for (Entry<String, Object> entry : parameters.entrySet()) {
query.setParameter(entry.getKey(), entry.getValue());
}
}

/**
* @param startingAt
* @param maxPerPage
* @param t
* @return list of persisted entities which belong to this class t
*/
@SuppressWarnings("unchecked")
public List<T> getAllLazyEntities(int startingAt, int maxPerPage, Class<T> t) {
// regular query that will search for players in the db
Query query = getEntityManager().createQuery(
"select p from " + t.getName() + " p");
query.setFirstResult(startingAt);
query.setMaxResults(maxPerPage);

return query.getResultList();
}

/**
* @param clazz
* @return count of existing entity rows from backend
*/
public int countTotalRows(Class<T> clazz) {
Query query = getEntityManager().createQuery(
"select COUNT(p) from " + clazz.getName() + " p");

Number result = (Number) query.getSingleResult();

return result.intValue();
}

/**
* @return count of existing entity rows from backend acccording to given
*         condition
*/
public int countTotalRowsWithCond(Class<T> clazz, String Cond) {
Query query = getEntityManager()
.createQuery(
"select COUNT(p) from " + clazz.getName() + " p "
        + Cond + "  ");

Number result = (Number) query.getSingleResult();

return result.intValue();
}
}

Is dynamically modifying unitName in @PersistenceContext(unitName = "SavingBalanceDemoServer_PU") a good idea ? Please suggest me.

My persistence.xml is :

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1"
xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
<persistence-unit name="SavingBalanceDemoServer_PU"
transaction-type="JTA">
<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
<jta-data-source>jdbc/simfin</jta-data-source>
<class>org.demo.model.MemRegMcgEntity</class>
<class>org.demo.model.SavAccHolderMcgEntity</class>
<class>org.demo.model.SavAccMcgEntity</class>
<class>org.demo.model.SavTransactionEntity</class>
</persistence-unit>
</persistence>

Please suggest some optimization/changes in this file.

And I have been using EJB to utilize the Generic class. eg:

@Stateless
public class MemberEJB extends GenericDAO<MemRegMcgEntity> {
/**
* @see GenericDAO#GenericDAO(Class<T>)
*/
public MemberEJB() {
super(MemRegMcgEntity.class);
// TODO Auto-generated constructor stub
}

public List<MemRegMcgEntity> getListOfMemberByName(String name){
Map<String, Object> parameters = new HashMap<String, Object>();
parameters.put("memName", name+'%');

return super.findAllWithGivenCondition("Mem.getMemberByName", parameters);
}

}

The client app provides the database name to use and every database has same structure. I just need to access multiple database according to client's request.

Answer 1

We faced the same use case and ended up creating multiple persistence-unit and building an entity manager factory which returns the correct entity manager according to an parameter sent by the client (as an enum in our case, Environment). Then, instead of injecting the persistence context in the clients, we inject this factory and call getEntityManager(environment).

@Stateless
public class EntityManagerFactory {

    @PersistenceContext(unitName = "first_PU")
    EntityManager firstEm;

    @PersistenceContext(unitName = "second_PU")
    EntityManager secondEm;

    public EntityManager getEntityManager(Environment env) {
        switch (env) {
        case THIS:
            return firstEm;
        case THAT:
            return secondEm;
        default:
            return null;
        }
    }
}

Example enum:

public enum Environment{
    DEV, PROD
}

In your case, the GenericDAO would be refactored this way:

public class GenericDAO<T> {

    @EJB
    private EntityManagerFactory entityManagerFactory;

    public void save(T entity, Environment env) {
        entityManagerFactory.getEntityManager(env).persist(entity);
    }

}

And then your client would call with dao.save(someEntity, Environment.DEV).

Your persistence.xml would end up like this:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1"
xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">

    <persistence-unit name="first_PU" transaction-type="JTA">
        <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
        <jta-data-source>jdbc/simfin_1</jta-data-source>
        <class>org.demo.model.MemRegMcgEntity</class>
        <class>org.demo.model.SavAccHolderMcgEntity</class>
        <class>org.demo.model.SavAccMcgEntity</class>
        <class>org.demo.model.SavTransactionEntity</class>
    </persistence-unit>

    <persistence-unit name="second_PU" transaction-type="JTA">
        <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
        <jta-data-source>jdbc/simfin_2</jta-data-source>
        <class>org.demo.model.MemRegMcgEntity</class>
        <class>org.demo.model.SavAccHolderMcgEntity</class>
        <class>org.demo.model.SavAccMcgEntity</class>
        <class>org.demo.model.SavTransactionEntity</class>
    </persistence-unit>

</persistence>

Answer 2

When dealing with one app and multiple DBs EclipseLink provides two solutions. Which one is better suited for you depends on your use-case, if

Users need to map expose multiple persistence units as a single persistence context within an application.

Take a look at Using Multiple Databases with a Composite Persistence Unit

If your case is that

Multiple application clients must share data sources, with private access to their data environment.

than take a look at Tenant Isolation Using EclipseLink

Alternatively, this blog post describes a way of designing a multi-tenancy, without binding to vendor specific functionality

UPDATE with respect to the comment

I don't think that the type of dynamic data source routing that you're after exists as a ready made construct of glassfish. But it should not be too hard to implement it either. You should take a look at the TomEE's dynamic datasource api and the reference implementation they provided. You should be able to write your own router based on it without too much issues

Answer 3

My solution would be to add a second persistence unit for the second database, then refactor your GenericDAO so that the EntityManager is not an attribute of the class, but passed into each method. I would then create facade objects for each of your databases which get the GenericDAO and the relevent EntityManager injected into them. If you really wanted you could have a common interface to keep the api the same. It might look like this:

persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1"
xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">

    <persistence-unit name="SavingBalanceDemoServer_PU" transaction-type="JTA">
        <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
        <jta-data-source>jdbc/simfin</jta-data-source>
        <class>org.demo.model.MemRegMcgEntity</class>
        <class>org.demo.model.SavAccHolderMcgEntity</class>
        <class>org.demo.model.SavAccMcgEntity</class>
        <class>org.demo.model.SavTransactionEntity</class>
    </persistence-unit>

    <persistence-unit name="MySecondPersistenceUnit_PU" transaction-type="JTA">
        <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
        <jta-data-source>jdbc/other-jta-datasource</jta-data-source>
        <class>org.demo.model.OtherEntityOne</class>
        <class>org.demo.model.OtherEntityTwo</class>
        <class>org.demo.model.OtherEntityThree</class>
        <class>org.demo.model.OtherEntityFour</class>
    </persistence-unit>

</persistence>

Generic DAO:

public class GenericDAO<T> {

public void <T extends IEntity> save(EntityManager em, T entity) {
    em.persist(entity);
}

Entity Interface:

public Interface IEntity {
    ....
}

Entity Class:

public class SomeEntity implements IEntity {
    ....
}

DAO Facade Database One:

public class GenericFacadeOne {

@PersistenceContext(unitName = "SavingBalanceDemoServer_PU")
private EntityManager em; 
@Autowired
private GenericDao dao;

@Transactional(propogation=Propogation.REQUIRED)
public void saveSomeEntity(SomeEntity entity) {
    getDao().save(getEm(), entity);
}

public void setEm(EntityManager em) {
    this.em = em;
}   

public EntityManager getEntityManager() {
    return this.em;
}

public void setDao(GenericDao dao) {
    this.em = em;
}   

public GenericDao getDao() {
    return this.dao;
}
}

DAO Facade Database Two:

public class GenericFacadeTwo {

@PersistenceContext(unitName = "MySecondPersistenceUnit_PU")
private EntityManager em; 
@Autowired
private GenericDao dao;

@Transactional(propogation=Propogation.REQUIRED)
public void saveSomeEntity(SomeEntity entity) {
    getDao().save(getEm(), entity);
}

public void setEm(EntityManager em) {
    this.em = em;
}   

public EntityManager getEntityManager() {
    return this.em;
}

public void setDao(GenericDao dao) {
    this.em = em;
}   

public GenericDao getDao() {
    return this.dao;
}
}

Hopefully that makes sense, let me know if you need any clarification!

Answer 4

For sure it can be done more sophisticated way, but there is also a straight forward solution which comes into my mind. What if you deploy as many applications as many databases you have and design a small request routing application which will forward all your clientrequests to the corresponding app by 'databaseId' provided in the request. This solution will work great in a distributed environment.

Answer 5

Another solution is creating the persistent context programmatically.

Define an persistent.xml without the connection. Similar to:

persistent.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" ... >
    <persistence-unit name="UserInfo" transaction-type="JTA">
        <class>mx.saaskun.model.UserInfo</class>
    </persistence-unit>
</persistence>

Create a factory for the custom connection:

The method receives two parameters, the custom unit name and the JNDI for the connection.

DynamicResource.java

@Stateless
@LocalBean
public class DynamicResource implements Serializable{
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public EntityManagerFactory getEntityManager(unitName, jndiConnection){
        Map properties = new HashMap();
        properties.put("javax.persistence.jtaDataSource", jndiConnection);
        return Persistence.createEntityManagerFactory(unitName, properties);
    }
}

then you use as:

 public class UserService{
     @EJB
     DynamicResource radResources;

     public List<UserInfo> listAll(){
          List<UserInfo allUsers = new ArrayList<>();
          String[] databases = new String[]{"jndi/simfin","jndi/simfin2"};
          for(String db:databases){
               List results = listServerUsers("simfin", db);
               allUsers.addAll(results);
          }
          return allUsers;
     }

     protected List<UserInfo> listServerUsers(String unitName, String jndi){
         EntityManager em= radResources.getEntityManager(unitName,jndi);
         try {
             Query q = em.createNamedQuery("UserInfo.findAll");
             return (List<UserInfo>) q.getResultList();
         } finally {
             em.close();
         }
     }
 }