Access multiple databases from a Java web application using JPA / EclipseLink / EJB

Question

I created a simple java SOAP application (server side) and I use Glassfish4,JPA/EclipseLink,EJB . I set up db connections (resources / pools) in Glassfish. Please offer several templates / knowledge for using several databases from one application. Is creating a multiple persistence unit a good idea for multiple access? Or is there another optimized solution? I have a common database access class.

 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") good idea? Please suggest me.

My 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> 

Please suggest some optimizations / changes in this file.

And I used EJB to use 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 application provides the database name for use, and each database has the same structure. I just need to access multiple databases as per client request.

Answer 1

We ran into the same use case and created several persistence blocks and created a factory entity manager that returns the correct object manager according to the parameter sent by the client (as an enumeration in our case, Environment ). Then, instead of introducing a persistence context in clients, we enter 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; } } } 

Listing example:

 public enum Environment{ DEV, PROD } 

In your case, GenericDAO will be reorganized as follows:

 public class GenericDAO<T> { @EJB private EntityManagerFactory entityManagerFactory; public void save(T entity, Environment env) { entityManagerFactory.getEntityManager(env).persist(entity); } } 

And then your client will call using dao.save(someEntity, Environment.DEV) .

Your persistence.xml will end as follows:

 <?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 working with one application and several databases, EclipseLink provides two solutions. Which one is best for you depends on your use case if

Users need to map the exposure of multiple storage units as a single persistence context in the application.

Take a look at Using Multiple Sustainability Databases

If your case is such that

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

How to Look at Tenant Isolation Using EclipseLink

Alternatively, this blog post describes how to design a multi-tenant lease without being tied to a vendor-specific feature.

UPDATE regarding comment

I do not think that the type of routing of the dynamic data source that you are using is a finished construction of glass fish. But this should not be too difficult to implement. You should take a look at the TomEE dynamic datasource api and the reference implementation that they provided. You should be able to write your own router based on this without much trouble.

Answer 3

My solution would be to add a second save block for the second database and then reorganize your GenericDAO so that the EntityManager is not a class attribute, but is passed to each method. Then I would create facade objects for each of your databases that would contribute GenericDAO and the corresponding EntityManager to them. If you really wanted you to have a common interface to support api the same way. 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> 

General 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 One Facade Database:

 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 Two Facade Database:

 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; } } 

Hope this makes sense, let me know if you need clarification!

Answer 4

Of course, this can be done in a more complicated way, but there is a direct solution that comes to my mind. What if you deploy as many applications as you have many databases, and develop a small request routing application that forwards all your client requests to the appropriate application using the "databaseId" provided in the request. This solution works great in a distributed environment.

Answer 5

Another solution is a programmatic approach to a constant context.

Define persistent.xml without a 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 a custom connection:

The method receives two parameters: the name of the device being configured 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 like:

  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(); } } }