4 Replies Latest reply on Jan 6, 2011 3:50 AM by lazo

    Dynamic Datasource invocation at runtime in JSF-EJB3-MySql a

      This issue is regarding Dynamic Datasource invocation at runtime in JSF-EJB3-MySql application on Eclipse3.4.1-JBoss5.0.0 plateform.

      I have created a sample JSF-EJB3-MySql application on JBoss5.0.0 AS. Application works fine which performs CRUD operation in database schema "schema1" on table "table1".

      my JBoss5.0.0 server datasource file C:\jboss5.0.0\server\default\deploy\mysql-ds.xml looks like

      <local-tx-datasource>
      <jndi-name>DefaultDS</jndi-name>
      <connection-url>jdbc:mysql://host1:3306/schema1</connection-url>
      <driver-class>com.mysql.jdbc.Driver</driver-class>
      <user-name>username</user-name>
      password
      <min-pool-size>1</min-pool-size>
      <max-pool-size>2</max-pool-size>
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
      <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker</valid-connection-checker-class-name>
      <type-mapping>mySQL</type-mapping>
      </local-tx-datasource>


      my sample application has persistence.xml which looks like
      <persistence-unit name="pmtest">
      org.hibernate.ejb.HibernatePersistence
      <jta-data-source>java:/DefaultDS</jta-data-source>








      </persistence-unit>

      Primary requirement for my new application is as below,
      (1) Use JBoss Seam Framework to develop JSF-EJB3-MySql application on Eclipse3.4.1-JBoss5.0.0
      (2) There are multiple databases (having exactly same table design structure)
      running on single database server "host1" placed in a central "Head Office" location i.e.
      jdbc:mysql://host1:3306/schema1
      jdbc:mysql://host1:3307/schema2
      jdbc:mysql://host1:3308/schema3

      All the schemas listed above have has different JNDI name defined in mysql-ds.xml (multiple datasource configuration in one mysql-ds.xm file). As "table1" design structure is same in all the schema, the table1.java entity class of the application can be common.

      (3) The application will be deployed on one application server machine placed at the same central "Head Office" location where the database server "host1" is placed. This single application will access multiple databases running on database server "host1". This application will be called by various "Branch Office" from any other location say "Branch1", "Branch2" etc.

      So, is there any way in JBoss Seam framework using which, the application can identify which datasource to be invoked on the basis of "Branch User" login information ? i.e.
      Branch1 user --> invoke Schema1 datasource
      Branch2 user --> invoke Schema2 datasource

      I came across an article by Spring that provides the features as explained in http://blog.springsource.com/2007/01/23/dynamic-datasource-routing/

      So, can we implement the Dynamic Datasource invocation logic in JBoss Seam using JSF-EJB3-MySql application on Eclipse3.4.1-JBoss5.0.0 plateform ?

      Thanks for your help in advance.

        • 1. Re: Dynamic Datasource invocation at runtime in JSF-EJB3-MyS
          lazo

          I'm also looking for solution, how to use multiple databases (having exactly same table design structure).

          Have you found any solution for this ?

          • 2. Re: Dynamic Datasource invocation at runtime in JSF-EJB3-MyS

            I manage to solve it but need to fine-tune as per the Seam/Hibernate-JPA standards before using it in the application.

            (1) Create multiple <xa-datasource> envelopes with different JNDI names, in the single mysql-ds.xml datasource file. e.g.

            <xa-datasource>
            <jndi-name>1TestDS</jndi-name>
            ..
            </xa-datasource>
            <xa-datasource>
            <jndi-name>2TestDS</jndi-name>
            ..
            </xa-datasource>


            (2) Create multiple <persistence-unit> envelopes with different persistence unit name, in a single persistence.xml file of your application e.g.

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


            (3) In my case, the persistence unit name is the same as User 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 from User Entity and invoke below method
            }

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

            I hope this helps you. LEt me also know if you have found some better approch.

            • 3. Re: Dynamic Datasource invocation at runtime in JSF-EJB3-MySql a
              ahsanfile

              I have same problem...

              but i have a quick win solution : BACK TO JDBC !...

              • 4. Re: Dynamic Datasource invocation at runtime in JSF-EJB3-MySql a
                lazo

                I solved the problem with seam. I created component which is returning different entity managers for different host names.


                @Name("entityManager")
                public class SchemaSelection {

                    @Unwrap
                    public EntityManager getEntityManager() {

                        String emName = getEmName(HttpUtil.getRequest().getServerName());    

                        return (EntityManager)Component.getInstance(emName);

                    }

                }

                 

                All different entity managers are defined in components.xml.