5 Replies Latest reply on Oct 14, 2008 5:06 PM by Bart DP

    multiple databases configuration

    Bart DP Newbie

      I searched the forum/google and didn't find a right solution yet.
      The user has to logon with his username/password and choose which db to use (radio button)
      Then the application has to authenticate the user in the choosen db and work further in that db.
      This is how I have configured my seam application:


      Components.xml


              <persistence:entity-manager-factory name="entityManagerFactory0"
                      persistence-unit-name="ddsPersistenceUnit0" />
      
              <persistence:entity-manager-factory name="entityManagerFactory1"
                      persistence-unit-name="ddsPersistenceUnit1" />
      
              <persistence:managed-persistence-context name="entityManager0"
                      auto-create="true" entity-manager-factory="#{entityManagerFactory0}" />
      
              <persistence:managed-persistence-context name="entityManager1"
                      auto-create="true" entity-manager-factory="#{entityManagerFactory1}" />
      


      context.xml


      <Context path="/test" docBase="test" debug="5" reloadable="true"
              crossContext="true">
      
              <Resource name="datasource0"
                      driverClassName="oracle.jdbc.driver.OracleDriver"
                      factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="2"
                      maxIdle="1" maxWait="10000" auth="Container"
                      password="user0" type="javax.sql.DataSource"
                      url="jdbc:oracle:thin:@..."
                      username="user0" />
      
              <Resource name="datasource1" 
                      driverClassName="com.mysql.jdbc.Driver" auth="Container"
                      factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="2"
                      maxIdle="1" maxWait="10000" type="javax.sql.DataSource"
                      url="jdbc:mysql://localhost:3306/testseam" username="root" />
      
      </Context>
      


      persistent.xml


      <?xml version="1.0" encoding="UTF-8"?>
      <persistence version="1.0"
              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">
              
              <persistence-unit name="persistence0" transaction-type="RESOURCE_LOCAL">
                      <provider>org.hibernate.ejb.HibernatePersistence</provider>
                      <jta-data-source>java:comp/env/datasource0</jta-data-source>
                      <properties>
                              <property name="hibernate.default_schema" value="test" />
                              <property name="hibernate.connection.autocommit" value="false" />
                              <property name="hibernate.show_sql" value="true" />
                              <property name="hibernate.format_sql" value="true" />
                              <property name="hibernate.cache.provider_class" value="org.hibernate.cache.EhCacheProvider" />
                              <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle9Dialect" />
                              <property name="hibernate.cache.provider_configuration_file_resource_path" value="ehcache.xml" />
                              <property name="hibernate.cache.use_second_level_cache" value="true" />
                              <property name="hibernate.cache.use_query_cache" value="true" />
                      </properties>
              </persistence-unit>
              
              <persistence-unit name="persistenceUnit1" transaction-type="RESOURCE_LOCAL">
                      <provider>org.hibernate.ejb.HibernatePersistence</provider>
                      <jta-data-source>java:comp/env/datasource1</jta-data-source>
                      <properties>
                              <property name="hibernate.connection.autocommit" value="false" />
                              <property name="hibernate.show_sql" value="true" />
                              <property name="hibernate.format_sql" value="true" />
                              <property name="hibernate.cache.provider_class" value="org.hibernate.cache.EhCacheProvider" />
                              <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" />
                              <property name="hibernate.cache.provider_configuration_file_resource_path" value="ehcache.xml" />
                              <property name="hibernate.cache.use_second_level_cache" value="true" />
                              <property name="hibernate.cache.use_query_cache" value="true" />
                      </properties>
              </persistence-unit>
      </persistence>
      
      



      My first db is an oracle db and the second a mysql.
      Now, I'm making a generic data service class where I define the transactional methods.
      Here I'm a little confused, not knowing how to solve the problem.
      GenericDataServiceImpl.java


      public abstract class GenericDataServiceImpl<T, ID extends Serializable>
                      implements GenericDataService<T, ID> {
      
              private Class<T> persistentClass;
      
              @In
              protected EntityManager entityManager0;
      
              @In
              protected EntityManager entityManager1;
      
              @SuppressWarnings("unchecked")
              public GenericDataServiceImpl() {
                      this.persistentClass = getPersistentClass();
              }
      
              protected abstract Class<T> getPersistentClass();
      
              @Transactional
              public void removeEntity(T entity) {
                      entityManager1.remove(entity);
              }
      
              @Transactional
              public void persistEntity(T entity) {
                      entityManager1.persist(entity);
              }
      
      


      Do I have to write an if-statement where I choose the right entityManager for each method or is there a better solution?

        • 1. Re: multiple databases configuration
          Bart DP Newbie

          I know how I can solve this with spring integrated, but I'm searching for a seam-only solution.

          • 2. Re: multiple databases configuration
            Guillaume Jeudy Master

            There is no seam out of the box solution for this afaik. I had to deal with a similar problem and instead of injecting all entitymanagers with annotations I injected my service locator object then based on arguments passed to the service locator I would lookup one entitymanager or another from JNDI.


            JBoss allows you to publish transactional entity manager in the global JNDI context by adding the following line in your persistence.xml


            <property name="jboss.entity.manager.jndi.name" value="java:/rdm-sdEntityManager" />



            The beauty of this is that no matter from where you do the JNDI lookup you will always get an entityManager that is guaranteed to participate in the current transaction.

            • 3. Re: multiple databases configuration
              Bart DP Newbie

              I think you mean:


              <property name="jboss.entity.manager.factory.jndi.name" value="java:/rdm-sdEntityManager"/>
              



              So you used a service locator pattern.
              Instead of:


                      @In
                      protected EntityManager entityManager0;
              
                      @In
                      protected EntityManager entityManager1;
              


              in my GenericDataServiceImpl, I have to lookup (using the service locator) the right entityManager?

              • 4. Re: multiple databases configuration
                Guillaume Jeudy Master

                Yes thats right, DI purists will think it wrong to do a JNDI lookup but I dont see a better solution especially if you have many datasources. I made it so that the JNDI name follows a certain pattern to facilitate the lookup.


                In any case your DAO is still isolated of the lookup conditionals hidden behind the service locator interface.

                • 5. Re: multiple databases configuration
                  Bart DP Newbie

                  Thanks for the response.


                  Is it maybe possible to copy/paste a little code here, that would be interesting not only for me, but for a lot of people who has the same problem.


                  Thx