users@glassfish.java.net

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

From: <glassfish_at_javadesktop.org>
Date: Fri, 13 Feb 2009 04:55:40 PST

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
        <datasources>
                <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>password</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>
                        <metadata> <type-mapping>mySQL</type-mapping> </metadata>
                </local-tx-datasource>
        </datasources>

my sample application has persistence.xml which looks like
        <persistence-unit name="pmtest">
                <provider>org.hibernate.ejb.HibernatePersistence</provider>
                <jta-data-source>java:/DefaultDS</jta-data-source>
                <properties>
                <property name="hibernate.archive.autodetection" value="class"/>
                <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
                <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
                <property name="hibernate.connection.username" value="username"/>
                <property name="hibernate.connection.password" value="password"/>
                <property name="hibernate.connection.url" value="jdbc:mysql://host1:3306/schema1"/>
                </properties>
        </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.
[Message sent by forum member 'pragun' (pragun)]

http://forums.java.net/jive/thread.jspa?messageID=331771