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