Same data souce multiple database

[unnis]

How can I connect to multiple databases using same datasource.

My requirement is ,
Based on the user profile I need to connect multiple databases. All of my databases will reside in same mysql server and is having same structure.I can't use multiple datasource and multiple connection pool, since the number of databses is more. So my plan is to have a single datasource and swich the database using setCatalog() jdbc method.

My application does the data source lookup in the following ways

1) Direct JNDI lookup in EJB
2) Using Hibernate
3) Using KODO-JDO

In all the above cases I am using same JNDI name ( mySqlDS) .

How can I achive this ???

I feel that database switching has to be done before giving the connection to the client ( EJB/Hibernate/jdo) . So this has to be done at DataSource level so that ds.getConnection() should return the proper connection with proper catalog set.


If I am using CustomDataSource , Inside CustomDataSource is there a way to access values from EJbContext ???

Thanks & Regards

unnis

[peterj]

I haven't tried this, but here is what I would do.

First, set up only one *-ds.xml file connected to one of the MySQL databases. I would also make sure that the user-name and password has rights to all of the MySQL databases. Then have the apps initially connect to the database defined by the *-ds.xml file, and then issue the "use XXX" statement to switch databases.

[unnis]

I can also do the same thing if my application takes the database connection using ds.getConnection()

But in my case I am using Hibernate and kodo-jdo. Both cases i need a managed connection, so I am using the same -ds file for this. Finally all connections will come from same connection pool.

Since Hibernate and Kodo-jdo takes the connection using datasource look up in their own code, I won't be able to change the database there. So this has to managed in the datasource level.

[jaikiran]

"unnis" wrote:


Since Hibernate and Kodo-jdo takes the connection using datasource look up in their own code, I won't be able to change the database there. So this has to managed in the datasource level.

From what i know, Hibernate allows the schema to be set through one of its APIs.

Configuration cfg = new Configuration();
cfg.setProperty("hibernate.default_schema", "XXX");
SessionFactory sf = cfg.configure().buildSessionFactory();

[unnis]

From what i know, Hibernate allows the schema to be set through one of its APIs.

Yes hibernate allows it. But I need to create seperate sessionfactiories for each database. Since number of database is more , this may not be good solution . And if somebody gets the connection from hibernate session and use that connection for JDBC query , it will use the default catalog which is set in the ds.xml

[jaikiran]

"unnis" wrote:
But I need to create seperate sessionfactiories for each database. Since number of database is more , this may not be good solution .

In-fact that's a correct thing to do http://www.hibernate.org/117.html#A23