File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes EJB and other Java EE Technologies and the fly likes Dynamic Datasource invocation at runtime in JSF-EJB3-MySql application on Eclipse3.4.1-JBoss5.0.0 Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "Dynamic Datasource invocation at runtime in JSF-EJB3-MySql application on Eclipse3.4.1-JBoss5.0.0" Watch "Dynamic Datasource invocation at runtime in JSF-EJB3-MySql application on Eclipse3.4.1-JBoss5.0.0" New topic
Author

Dynamic Datasource invocation at runtime in JSF-EJB3-MySql application on Eclipse3.4.1-JBoss5.0.0

Prabhu Mehta
Greenhorn

Joined: Feb 13, 2009
Posts: 9
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.
Prabhu Mehta
Greenhorn

Joined: Feb 13, 2009
Posts: 9
Related post:
http://www.coderanch.com/t/431146/EJB-Other-Java-EE-Technologies/java/change-database-Java-EE
http://www.coderanch.com/t/217545/Object-Relational-Mapping/java/create-EntityManagerFactory
Reza Rahman
author
Ranch Hand

Joined: Feb 01, 2005
Posts: 580
    
    5
Prabhu,

Other than what I said in your previous post, as far as I know, you'd have to write custom code to interact with the JPA SPI to do this (which is what the Spring code is doing). A better place for this question might be on the Seam forums for JBoss.

Regards,
Reza


Independent Consultant — Author, EJB 3 in Action — Expert Group Member, Java EE 6 and EJB 3.1
Prabhu Mehta
Greenhorn

Joined: Feb 13, 2009
Posts: 9
Hi Reza,

True, I need to interact with JPA. I am newbie in JPA, so searching over the net for the right solution before digging it more ...
I came across few article which uses "Persistence.createEntityManagerFactory" class to override Persistence Unit parameter at runtime. But I am not aware how much would it cost me to create EMF.

Application is going to use Hibernet as JPA provider and as you said, JBoss Seam also is the area in which I need to look for this.

BTW, I have read your article on TheServerSide.com about "New Features in EJB 3.1 - Part 1 to Part 5". It is really interesting.

Thanks,
Prabhu.
Reza Rahman
author
Ranch Hand

Joined: Feb 01, 2005
Posts: 580
    
    5
Prabhu,

Forgot to mention this, another avenue to pursue is talking to the persistence provider (Hibernate, TopLink) via their forums. They might be able to provide a vendor extension for you in the long term.

Regards,
Reza
Prabhu Mehta
Greenhorn

Joined: Feb 13, 2009
Posts: 9
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.
<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 e.g.
<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 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 <Om> findOms(String puName){
emf = Persistence.createEntityManagerFactory(puName);
em = emf.createEntityManager();
List <Om> x = (List <Om>) em.createQuery("select t from Om t").getResultList();
em.close();
return x;
}

I hope this turns out to be a right way of solving the issue.
Your opinions are always welcome

Thanks in advance.
 
jQuery in Action, 2nd edition
 
subject: Dynamic Datasource invocation at runtime in JSF-EJB3-MySql application on Eclipse3.4.1-JBoss5.0.0