13 Replies Latest reply on Jun 2, 2009 8:05 AM by billy K

    Several Datasources??

    Guillermo Ortiz Newbie

      Hi,


      I want to have several Datasources.


      I've configured:


      components.xml


       
         <persistence:managed-persistence-context name="entityManager"
                                           auto-create="true"
                            persistence-unit-jndi-name="java:/PruebasSeamEntityManagerFactory"/>                          
      
      
         <persistence:managed-persistence-context name="entityManagerBPM"
                                           auto-create="true"
                            persistence-unit-jndi-name="java:/PruebasSeamJBPMEntityManagerFactory"/>



      persistence.xml


       <persistence-unit name="PruebasSeam">
            <provider>org.hibernate.ejb.HibernatePersistence</provider>
            <jta-data-source>java:/PruebasSeamDatasource</jta-data-source>
            <properties>
               <!-- <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/> -->
               <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle9iDialect"/>
               <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:/PruebasSeamEntityManagerFactory"/>
            </properties>
         </persistence-unit>
         
            <persistence-unit name="PruebasSeamJBPM">
            <provider>org.hibernate.ejb.HibernatePersistence</provider>
            <jta-data-source>java:/PruebasSeamJBPMDatasource</jta-data-source>
            <properties>
               <!-- <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>-->
               <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle9iDialect"/>
               <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:/PruebasSeamJBPMEntityManagerFactory"/>
            </properties>
         </persistence-unit>



      and MyProject-ds.xml


      How can I acceded several DataSources in a class??
      I've tring:



      @Stateless
      @Name("authenticator")
      public class Authenticator implements IAuthenticator
      {   
          @PersistenceContext(name="entityManager")
          EntityManager entityManager;
          
          @PersistenceContext(name="entityManagerBPM")
          EntityManager entityManagerBPM;




      The exception produced is:


      16:30:58,213 WARN  [ServiceController] Problem starting service jboss.j2ee:service=EJB3,module=PruebasSeam.jar
      java.lang.RuntimeException: Illegal @PersistenceUnit on javax.persistence.EntityManager com.prueba.sesion.Authenticator.entityManager :EMPTY STRING unitName and there is more than one scoped persistence unit
           at org.jboss.injection.PersistenceContextHandler.handleFieldAnnotations(PersistenceContextHandler.java:182)
           at org.jboss.injection.InjectionUtil.processFieldAnnotations(InjectionUtil.java:137)
           at org.jboss.injection.InjectionUtil.processAnnotations(InjectionUtil.java:174)




        • 1. Re: Several Datasources??
          Arbi Sookazian Master

          use @In rather than @PersistenceContext to inject the EntityManager instance:


               

          @In private EntityManager entityManagerDSS;
                    
               @In private EntityManager entityManager;



          The instance name must match the names from components.xml:


            

          <persistence:managed-persistence-context name="entityManager"
                                               auto-create="true"
                                persistence-unit-jndi-name="java:/boBETSEntityManagerFactory"/>     
                                
             <persistence:managed-persistence-context name="entityManagerDSS"
                                               auto-create="true"
                                persistence-unit-jndi-name="java:/coxDSSEntityManagerFactory"/>  

          • 2. Re: Several Datasources??
            Gregory Nikle Apprentice

            You need to use xa-datasource to use 2 em in one transaction.

            • 3. Re: Several Datasources??
              Dan Allen Master

              You are trying to inject a Seam component (the Seam-managed persistence context) using a Java EE annotation (@PersistenceContext). That isn't going to work. You either have to use @In in your example or you need to add unitName attributes that match the name of the persistence units in persistence.xml (because the Java EE container doesn't know anything about components.xml).


              Read more here: When do I use @In vs @Persistence Context to inject an EntityManager

              • 4. Re: Several Datasources??
                Guillermo Ortiz Newbie

                I've two schemas for one database. Have I use xa-transaction or only if it are different database?

                • 5. Re: Several Datasources??
                  Arbi Sookazian Master

                  Guillermo Perez wrote on Apr 07, 2009 09:01:


                  I've two schemas for one database. Have I use xa-transaction or only if it are different database?


                  XA/2PC is typically required when you are using JTA and have a Transaction manager managing more than one resource manager in one distributed transaction.  This scenario can be two db's on two different servers, one db and one JMS session, etc.


                  IIRC, Oracle is XA-enabled out of the box but MSSQL is not (requires DLL and sproc installation on MSSQL 2005).  Also understand that XA/2PC has some overhead and thus is a performance hit.




                  One of the key features of the Enterprise JavaBeans architecture is support for distributed transactions.
                  The Enterprise JavaBeans architecture allows an application developer to write an application that
                  atomically updates data in multiple databases which may be distributed across multiple sites. The sites
                  may use EJB servers from different vendors.

                  source: JSR220


                  refer to 13.2 Sample Scenarios of JSR220 for more info.


                  As far as two schemas for one db, most likely the JTA/tx mgr sees that as one resource, so perhaps 2PC/XA is not required.  However, if you want to use more than one EntityManager instance in one public (interface-exposed) EJB method with transaction enabled (e.g. REQUIRED, REQUIRES_NEW, etc.), then you will need to use XA/2PC most likely.  I've run into problems with this before and I had to use NOT_SUPPORTED tx attribute type for the EJB method...

                  • 7. Re: Several Datasources??
                    Pragun Mehta Newbie
                    I am passing persistence unit name to container managed EntityManager at runtime as follows

                    (1) Create multiple <xa-datasource> envelopes with different JNDI names, in the single mysql-ds.xml datasource file.
                    <datasources>
                             <xa-datasource>
                                        <jndi-name>1TestDS</jndi-name>
                                         ..
                             </xa-datasource>
                             <xa-datasource>
                                        <jndi-name>2TestDS</jndi-name>
                                         ..
                             </xa-datasource>
                    </datasources>

                    (2) Create multiple <persistence-unit> envelopes with different persistence unit name, in a single persistence.xml file of your application
                    <persistence>
                            <persistence-unit name="1TestPU" transaction-type="JTA">     
                            <jta-data-source>java:/1TestDS</jta-data-source>
                             ...
                            </persistence-unit>
                            <persistence-unit name="2TestPU" transaction-type="JTA">
                            <jta-data-source>java:/2TestDS</jta-data-source>
                             ...
                            </persistence-unit>
                    </persistence>

                    (3) In my application, the persistence unit name is the same as login User's branch office name. So it will be available in User entity which has Seam SESSION scope after user logged in.

                    (4) Here is the Stalteless bean which uses different persistence unit as follows
                      @PersistenceContext
                      private EntityManager em;

                      @PersistenceUnit
                      private EntityManagerFactory emf;
                     
                      {
                      // get the persistence unit name (which is same as USer Branch Office) from User Entity and invoke below method
                      }

                      public List <Om> findOms(String puName){
                         try{
                         emf = Persistence.createEntityManagerFactory(puName);
                         em = emf.createEntityManager();
                         List <Om> x = (List <Om>) em.createQuery("select t from Om t").getResultList();
                         }
                         catch(){
                         ..
                         }
                         finally{
                         em.close();
                         }
                         return x;
                      }

                    Is this a proper approch to create your own EntityManager and define persistence unit at runtime ?

                    or is there any better way ?

                    Thanks in advance.
                    • 8. Re: Several Datasources??
                      Dan Allen Master

                      First of all, your @PersistenceContext and @PersistenceUnit injections serve no purpose in this scenario, so you don't need them.


                      I strongly advise against using vertical partitioning in application code. If you need to break up your database by branch office, I would look into using Hibernate Shards or something equivalent.


                      Even if you disregard that advice, you should never be bootstrapping a persistence unit (EntityManagerFactory) in a business method. It is extremely expensive (in terms of time) to do...and in this case you are throwing it away immediately afterwords.


                      A better solution is to inject the EntityMangerFactory for each branch office and then select one of them based on the naming...but again, this is really not what I would do:


                      @PersistenceUnit(unitName = "branchAPU")
                      private EntityManagerFactory branchAEmf;
                      
                      @PersistenceUnit(unitName = "branchBPU")
                      private EntityManagerFactory branchBEmf;
                      
                      public List<Om> findOms(String branch) {
                          EntityManagerFactory emf = null;
                          if (branch.equals("branchA")) {
                              emf = branchAEmf;
                          }
                          else if (branch.equals("branchB")) {
                              emf = branchBEmf;
                          }
                          else {
                              throw new IllegalArgumentException("No persistence unit for branch: " + branch);
                          }
                      
                          try {
                              EntityManger em = emf.createEntityManager();
                              return (List<Om>) em.createQuery("select t from Om t").getResultList();
                          } finally {
                              em.close();
                          }
                          
                      }

                      • 9. Re: Several Datasources??
                        Pragun Mehta Newbie

                        Thank you so much Dan for this valuable advice. As you can see, my code is a seam newbie code and you have provided great suggetion. We would definatley look for Database partition and Hibernate Shards.


                        Is there any way to initialise the EntityMangerFactory with the Persistence unit name, as soon as User logs in and than inject the same EntityMangerFactory in everywhere ?


                        • 10. Re: Several Datasources??
                          Dan Allen Master

                          If you are going to be manipulating the EntityManagerFactory in that way, I strongly recommend that you use Seam managed persistence. You can create an application-scoped manager component that resolves (and instantiates if necessary) the proper EntityManagerFactory based on context. The injection point wouldn't give any indication of which one is being selected (meaning all logic is centralized).

                          • 11. Re: Several Datasources??
                            Pragun Mehta Newbie

                            Thanks Dan for providing your valuable inputs.


                            As you said, we looked at the Database partitioning which in terminology called as SaaS Data Architecture based on Multitenancy and foung it very usefull concept while designing the application and database.


                            For those who are interested can visit these site for more detail
                            http://www.oracle.com/technology/tech/saas/pdf/saas-data-architecture-whitepaper.pdf
                            http://msdn.microsoft.com/en-us/library/aa479086.aspx
                            http://dev.mysql.com/tech-resources/articles/performance-partitioning.html


                            We are also exploring Hibernate Shards or smething equivalent.


                            Thanks a Ton !!

                            • 12. Re: Several Datasources??
                              Clint Popetz Apprentice

                              Wilfred Rosdorff wrote on Apr 22, 2009 11:04:



                              Sherkan Sherkan wrote on Apr 06, 2009 19:33:


                              You need to use xa-datasource to use 2 em in one transaction.


                              About '2 em in one transaction': If I am using 2 em's in one component, or in one method, does that mean they are in the same transaction? In my case my em's use completely unassociated datasources, but they happen to be used in the same method.


                              It depends on (a) how your entity managers get their connection to the database, and (b) how those connections are configured.  Assuming, since you say em, that you are using JPA and not hibernate directly, then you are probably configuring your persistence-unit with either <jta-data-source> or <non-jta-data-source>.  The latter will never participate in a jta transaction.  The former will participate unless in your <datasource> definition you have <no-tx-datasource>


                              So, for example, in one application I use two jta datasources, one for app data and one for logging.  The former is configured as a local-tx-datasource and the latter as a no-tx-datasource.  When we use a jta transaction in code, both data sources are active, but only one is participating in the tx.  This is how we want it, because we want to be able to log in the logging entity manager even if an error marks the jta tx as rollbackOnly.


                              If you try to use two jta datasources, and both are configured as local-tx-datasource, you'll get an exception.  This is, IMHO, horrible as the default behavior, because I think you very rarely want 2PC.  But my vote doesn't count :)


                              • 13. Re: Several Datasources??
                                billy K Newbie

                                I am a student working on Jboss with very basic knowledge of java. I was doing a project on Jboss to manage XA transactions using two data sources  I am very new to Jboss. I would be greatly obliged if you could help me how to go about it . I have to use two databases( Oracle, MSSQL, or may be My SQL ) . I am able to connect to the database one at a time using seam . But I have to configure to use the two databases simultaneously . So that if i do a trasaction then the results in the two databases have to be reflected.Just like a Bank transction(transfer of money) between two accounts of diferent banks. Please help me in going about it.


                                Any help would be great ..