7 Replies Latest reply on Sep 20, 2012 4:19 AM by Srinivasan Thoyyeti

    Multiple datasources and persistence units

    Harry Holt Newbie

      Okay, I'm trying to use a couple of datasources (and persistence units) in my seam application.  I don't understand why this doesn't work - it seems to want to bind every entity to every datasource.


      I'm using JBoss 4.2.2 and SEAM 2.0.2.SP1.  I'm using the latest stable version of JBoss Tools (for Europa) to create the project and entities.


      Start a new seam project, set up the jboss server and the first datasource using the tools wizard.  Update the -ds.xml file with the second datasource, create the second persistence unit in persistence.xml, and add the context references to components.xml:


      myProject-ds.xml:


         <local-tx-datasource>
            <jndi-name>myProjectDatasource</jndi-name>
            <connection-url>jdbc:hsqldb:MyDB</connection-url>
            <driver-class>org.hsqldb.jdbcDriver</driver-class>
            <user-name>sa</user-name>
            <password></password>
         </local-tx-datasource>
          
          <local-tx-datasource>
              <jndi-name>otherDatasource</jndi-name>
              <connection-url>jdbc:jtds:sqlserver://sandbox0:1433/MyTestDB</connection-url>
              <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
              <user-name>jondoe</user-name>
              <password>password1</password>
          </local-tx-datasource>
      



      persistence.xml:


         <persistence-unit name="myProject">
            <provider>org.hibernate.ejb.HibernatePersistence</provider>
            <jta-data-source>java:/myProjectDatasource</jta-data-source>
            <properties>
               <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/>
               <property name="hibernate.hbm2ddl.auto" value="update"/>
               <property name="hibernate.show_sql" value="true"/>
               <property name="hibernate.format_sql" value="true"/>
               <property name="jboss.entity.manager.factory.jndi.name" value="java:/myProjectEntityManagerFactory"/>
            </properties>
         </persistence-unit>
      
         <persistence-unit name="otherPU">
            <provider>org.hibernate.ejb.HibernatePersistence</provider>
            <jta-data-source>java:/otherDatasource</jta-data-source>
            <properties>
               <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect"/>
               <property name="hibernate.hbm2ddl.auto" value="validate"/>
               <property name="hibernate.show_sql" value="true"/>
               <property name="hibernate.format_sql" value="true"/>
               <property name="jboss.entity.manager.factory.jndi.name" value="java:/otherDatasourceEntityManagerFactory"/>
            </properties>
         </persistence-unit>
      



      components.xml:


         <persistence:managed-persistence-context name="entityManager"
                                           auto-create="true"
                            persistence-unit-jndi-name="java:/myProjectEntityManagerFactory"/>                          
      
         <persistence:managed-persistence-context name="otherEntityManager"
                                           auto-create="true"
                            persistence-unit-jndi-name="java:/otherDatasourceEntityManagerFactory"/>                          
      



      Next, create 2 entities.  Dog will be in the secondary datasource, so it's annotated with


      @PersistenceContext(name="otherPU")
      



      The Person entity is in the main datasource, and annotated thusly:


      @PersistenceContext(name="myProject")
      



      I've tried a number of variations of this configuration.  The secondary datasource will not be used extensively.  I tried using a non-jta-data-source and adding RESOURCELOCAL as the transaction type (as documented here), etc.

      But just deploying the application with these settings fails due to this error:


      09:29:00,349 WARN  [ServiceController] Problem starting service persistence.units:ear=myProject-ear.ear,unitName=otherPU
      javax.persistence.PersistenceException: org.hibernate.HibernateException: Missing table: Person
      



      As you can see, it is attempting to bind the Person table to the otherPU persistence unit, even though it's annotated to specify the main myProject unit.


      So what am I missing?  Is there a way to do this?  Any suggestions?


      Thx... squirrel


        • 1. Re: Multiple datasources and persistence units
          Guillaume Jeudy Master

          How are your entities packaged ? I assume that both PUs are declared in the same persistence.xml  ?


          By default all entities contained in the same jar as the persistence.xml will be part of the PU.


          If you want to change this behavior you will have to explicitely declare your entities using different methods:


          <mapping-file>, <jar-file>, <classes>

          among these I suggest jar-file as it is easier to maintain.


          In order to achieve this you will have to split your entities in 2 separate jars matching your PUs, put your persistence.xml in a different application jar and then refer to your jars using

          <jar-file>

          in each PU.


          This is just one way to solve this. You can also create a 2nd persistence.xml and have one PU in it, split entities and package in 2 separate jars. This way you have 2 autonomous persistence units each in their own jar.


          Let me know how it goes.

          • 2. Re: Multiple datasources and persistence units
            Carsten Hoehne Master

            We have an app with two datasources.


            But first, i do not know ig (and how)


            @PersistenceContext(name="otherPU")



            works.


            1. You do need separate persistence contexts for each datasource.
            This can be accomplished by putting each PC (and the corresponding entitys) in a jar, or by configuration (see below)


            2. JBoss AS 4.2.2 forces XA transactions. So you have to use XA-datasources. (search the jboss forums for my questions about XA).


            We have decided to use configuration for separating the PC.



             <persistence-unit name="pc1">
                  <provider>org.hibernate.ejb.HibernatePersistence</provider>
                  <jta-data-source>java:/pc1DatasourceXA</jta-data-source>
                       <class>de.bafz.aveq.address.model.User</class>
            [...]
                      <exclude-unlisted-classes />
            
                  <properties>
            [...]
                     <property name="jboss.entity.manager.factory.jndi.name" 
                                value="java:/mySQLEntityManagerFactory"/>
            
                        <property name="jboss.entity.manager.jndi.name" 
                                        value="java:/mySQLEntityManager" />
            
                 </properties>
               
               </persistence-unit>
            
               <persistence-unit name="pc2">
                      <provider>org.hibernate.ejb.HibernatePersistence</provider>
                      <jta-data-source>java:/Oracleprod</jta-data-source>
                      <class>de.bafz.aveq.address.model.Address</class>
            [...]          
                      <exclude-unlisted-classes />
                      <properties>
            [...]
                           <property name="jboss.entity.manager.jndi.name"
                                value="java:/oracleEntityManager" />
                           <property name="jboss.entity.manager.factory.jndi.name"
                                value="java:/oracleEntityManagerFactory" />
                      </properties>
                 </persistence-unit>
            



            Thats all. Beside the configuration of the entitymanager in component.xml



                 <persistence:managed-persistence-context name="entityManager"
                      auto-create="true"
                      persistence-unit-jndi-name="java:/mySQLEntityManagerFactory" />
            
                 <persistence:managed-persistence-context name="oracleEntityManager"
                      auto-create="true"
                      persistence-unit-jndi-name="java:/oracleEntityManagerFactory" />
            
                 <component name="oracleEntityConverter"
                      class="org.jboss.seam.ui.converter.EntityConverter" auto-create="true">
                      <property name="entityManager">#{oracleEntityManager}</property>
                 </component>
            



            Please refer to the JPA docs, why you have to do it in one of this 2 ways.
            Hope i have helped.

            • 3. Re: Multiple datasources and persistence units
              Dustin Norlander Apprentice

              if you want to use jta data sources you have to use XA.  otherwise you need to use non-jta-data-source in your persistence xml.


              I have never used @PersistenceContext to annotate any models, and I have gotten both XA and non-xa multiple datasources to work.  Have you tried removing that annotation? 


              -Dustin

              • 4. Re: Multiple datasources and persistence units
                Harry Holt Newbie

                Yes, I've tried it both with and without the PersistenceContext annotation.  It doesn't really seem to make a difference either way.


                Carsten's configuration suggestions get me very close (Thanks!!).  I'm still trying to figure out how to actually use the second entity manager (I don't really understand the EntityConverter at this point).


                With just that configuration, the application works, and I can create and read the Person entity fine.  But when I try to access Dog, I just get Not an entity:class org.domain.myProject.entity.Dog.  I assume it's because I am not correctly pointing to the correct entityManager somewhere.


                I've specified my secondary datasource as non-jta, but I don't think that's where the problem is right now. 


                - Squirrel


                • 5. Re: Multiple datasources and persistence units
                  Carsten Hoehne Master

                  Some more info
                  How to access the right entityManager:


                  If your entityManager is called entityManager all is automatic.


                  if it has an other name (e.g. oracleEntityManager) you have to do aditional config.


                  For the seam framework we have this classes:


                  public class OracleEntityHome <T> extends EntityHome <T>
                  {
                       
                       @Override
                       protected String getPersistenceContextName() {
                            return "oracleEntityManager";
                       }
                  }
                  
                  public class OracleEntityQuery<E>  extends EntityQuery<E> 
                  {
                  
                       @Override
                       protected String getPersistenceContextName() {
                            return "oracleEntityManager";
                       }     
                       
                  }
                       
                  



                  Now your Home and Query Objects have to extend OracleEntityQuery/Home when the entity belongs to the oracleEntityManager.


                  If you do not use seam framework it has to be done in a similar way.




                  (I don't really understand the EntityConverter at this point)

                  This sets the right entity converter. It has to be used in conjunction with s:selectItems:



                       <h:selectOneMenu value="#{bean.method}" id="selectMethod">
                            <s:selectItems value="#{MethodQuery.resultList}"
                                           var="method" label="#{method.code}"
                                           noSelectionLabel="Please select..." />
                            <f:converter converterId="oracleEntityConverter" />
                       </h:selectOneMenu>
                  
                  


                  • 6. Re: Multiple datasources and persistence units
                    Harry Holt Newbie

                    Thanks, that's just the hints I needed.


                    BTW, it seems that using the override for getPersistenceContextName works fine in the Home and Query objects without the extra layer of extends.  So I'll probably do that since I won't be using the secondary datasource much.



                    • 7. Re: Multiple datasources and persistence units
                      Srinivasan Thoyyeti Newbie

                      Thank you so much for the response. you saved my day!