How to connect multiple databases at same time?

[hanyou]

I want to create multiple data center that same structure, when user login my application, he can choose one to work, EJB3 how to deal with?

[ejb3workshop]

You need to configure multiple persistent units withing the persistence.xml file and annotate your entities appropriately. There is an example in the Jboss TrailBlazer.

[kmagnant]

This is a question frequently asked, but not answered. I am having a similar issue: I need to determine the schema (PersistenceUnit) to use at runtime - actually on a per request basis.

Has this nut been cracked? Ideally I would like to still be able to use JTA and let the container take care of the transaction details. Below I try to summarize the approach I have been trying, but it doesn't feel right. If you know of any examples or references that address this issue I would be grateful.

I am using jBoss 4.2.1 as it was downloaded with the exception of defining the data sources, which are all defined in one file, all are local-tx-datasources.

Based on user entered criteria, I need to determine which schema to use. The structure of each schema is identical so the Entity beans are properly defined for the each possible schema.

I have been able to get things working for read requests by using a static EntityFactoryLocator that returns the proper EntityManager given some decision criteria. Apparently when you use a helper class to get the EntityManager, your transaction type must be set to RESOURCE-LOCAL. So, the session facade that wraps the entity calls EntityFactoryLocator(decisionCriteria).getEntityManager().

When a session facade method needs to do an update, I run into an issue where the update works in the correct schema, but I am unable to close the entityManager so the corresponding connection is never released back to the pool. This is likely related to the fact that I have an EXTENDED persistence context - that I don't know how to change. My code, in the session facade, looks like:

// update one entity
EntityTransaction transaction = entityManager.getTransaction();
transaction.begin();
if (query.executeUpdate() == 1) {
entityManager.commit(); // Blows up if not commented out. java.sql.SQLException: You cannot commit during a managed transaction!
entityManager.close();
}
else rollback, clean up, and throw some exception

I know I have issues with the above. When in debug mode I see that the persistenceContext is EXTENDED.

Thanks for looking.

[oskar.carlstedt]

Hello!

AFAIK the whole idea with JPA is to have one data source per ejb-jar-file (but I might have misunderstood everything). If you need to persist data into several schemas you have to create a "super/delegating" ejb that will lookup the correct ejb to use for persistence.

So, you have to make your business logic in one ejb, then a lookup on a (local SLSB) ejb to actually use when persisting your data.


Kind regards
/Oskar

[rasisuku]

Hi,

I am facing a problem with entitymanager injection.

http://www.jboss.com/index.html?module=bb&op=viewtopic&t=121113

can you please post a sample persistence xml which has the needed properties.

Thanks,
Sukumar

[waynebaylor]

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"
 version="1.0">

 <persistence-unit name="example" transaction-type="JTA">

 <provider>org.hibernate.ejb.HibernatePersistence</provider>
 <jta-data-source>java:/OraDS</jta-data-source>

 <class>com.example.....</class>

 <properties>
 <property name="hibernate.hbm2ddl.auto" value="create-drop"/>
 <property name="hibernate.cache.provider_class" value="org.hibernate.cache.NoCacheProvider"/>
 <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle9Dialect"/>

 </properties>

 </persistence-unit>

</persistence>

[kmagnant]

I have something that seems to work, although I don't know if it the best solution - feel free to comment/improve (or trash, if appropriate) this approach.

The problem space dictates that the application operate on a schema that is determined by user input, So the schema must be derived at runtime. The application id deployed in several environments as it evolves from current branch development to production - this is still a challenge as I am required to change code, the list of persistence units; I am looking at ant hacks to accommodate this.

The basic approach is to define a Stateless Session Bean base class that defines the PersistenceUnits that the application has available to it. My entities are all wrapped by generated session facades. Each method in the session facade must call super.getEntitymanager(persistenceUnitName) to ensure that an EntityManager is available. The PersistenceUnitName is determined by the client of the session facade.

One cool aspect of this design is that the table centric set of entities and session facades contain absolutely no business logic. Business logic goes in subclasses of the session facades to accomplish application/webService specific tasks. The result is a set of beans that expose the database and a common definition of property names. The brings naming consistence to all web services that are based on these tables. Not terribly slick, but it removes naming challenges/convention/religion from other development organizations that use them.

The Session Bean base class looks like:

package com.czncorp.base.session;

import javax.ejb.Stateless;
import javax.naming.*;
import javax.persistence.*;

@Stateless
@PersistenceUnits({
 @PersistenceUnit(name="persistence/CM5AZ", unitName="CM5AZ"),
 @PersistenceUnit(name="persistence/CM5CA", unitName="CM5CA"),
 @PersistenceUnit(name="persistence/CM5EA", unitName="CM5EA"),
 @PersistenceUnit(name="persistence/CM5US", unitName="CM5US")
})
public class BaseStatelessSession implements BaseStatelessSessionLocal {

 protected EntityManager entityManager;

 public void getEntityManager(String persistenceUnit) {

 try {
 if (entityManager == null) {
 InitialContext jndiContext = new InitialContext();
 EntityManagerFactory emf = (EntityManagerFactory)jndiContext.lookup("java:comp/env/persistence/" + persistenceUnit);
 entityManager = emf.createEntityManager();
 }
 }
 catch (NamingException ne) {
 ;; // Handle this based on your needs
 }
 }
}

Session facades (TableBeans) wrap entities with CRUD capabilities, nothing more. These look like:

package com.czncorp.as400.tables.blsbl;

import com.czncorp.base.session.BaseStatelessSession;
import com.czncorp.as400.tables.svord.LogUtil;

import java.util.List;
import java.util.logging.Level;
import javax.ejb.Stateless;


/**
 * Facade for entity Blsbl.
 *
 * @see com.czncorp.as400.tables.blsbl.Blsbl
 * @author MyEclipse Persistence Tools
 */
@ Stateless

public class BlsblFacade extends BaseStatelessSession implements BlsblFacadeLocal, BlsblFacadeRemote {

 public void save(String persistenceUnit, Blsbl transientInstance) {
 LogUtil.log("saving Blsbl instance", Level.INFO, null);
 try {
 super.getEntityManager(persistenceUnit); // <<---- make sure there is an Entity Manager
 entityManager.persist(transientInstance);
 LogUtil.log("save successful", Level.INFO, null);
 }
 catch (RuntimeException re) {
 LogUtil.log("save failed", Level.SEVERE, re);
 throw re;
 }
 }

 public void delete(String persistenceUnit, Blsbl persistentInstance) {
 LogUtil.log("deleting Blsbl instance", Level.INFO, null);
 try {
 super.getEntityManager(persistenceUnit);
 entityManager.remove(persistentInstance);
 LogUtil.log("delete successful", Level.INFO, null);
 }
 catch (RuntimeException re) {
 LogUtil.log("delete failed", Level.SEVERE, re);
 throw re;
 }
 }

 public Blsbl update(String persistenceUnit, Blsbl detachedInstance) {
 LogUtil.log("updating Blsbl instance", Level.INFO, null);
 try {
 super.getEntityManager(persistenceUnit);
 Blsbl result = entityManager.merge(detachedInstance);
 LogUtil.log("update successful", Level.INFO, null);
 return result;
 }
 catch (RuntimeException re) {
 LogUtil.log("update failed", Level.SEVERE, re);
 throw re;
 }
 }
...
...
...

Current challenge, and I think it is just a deployment issue I need to work through, is to deploy the TableBeans (stuff above) in it's own EAR. Other deployable units that implement business logic will subclass the session facades of TableBeans. Currently I am deploying two separate jars, problem is that the TableBean super class of my application specific session bean, a subclass of BlsblFacade in this case, can't find the Blsbl entity when the subclass calls methods in BlsblFacade. Of course, everything works fine if the subclasses are deployed in the same jar as TableBeans, so I know I am close. If that makes any sense, I am not beyond taking your suggestions.

Hope this helps others trying to determine the schema to use at runtime. Be aware that because you are using an EntityManagerFactory, the PersistenceContext will be "EXTENDED". I thought I could make it "TRANSACTION" since the container was doing everything, but that did not work for me.

[kmagnant]

OOPS, I forgot to insert the entityManager.joinTransaction() statement after super.getEntityManager(persistenceUnit). You need to do this otherwise your database changes will not get committed until the next operation involving that entityManager - at least that is the observed behavior.

You also need to do the joinTransaction in subclasses of TableBeans or you will observe the delayed commit behavior described above.