Can hibernate connect to multiple databases ?

suman · **Joined:** Sun Mar 28, 2004 7:18 pm **Posts:** 26

I searched for "multiple databases" and "multiple datasources", but I dont think I came accross anything that gave me a definite answer.

I am developing a system whereby the same information can reside in multiple databases. For example, when a user wants to search for a policy, the policy# can reside in db1, 2 or 3. Depending on which database it's found in, I need to pull more info about that policy from that database.

I have used hibernate to connect to "a" database using the hibernate.properties in the past. Is it possible for hibernate to connect to different/multiple databases and if so, how does one do it ?

If someone could point me to a URL that solves this problem or someone can enumerate the steps that I need to take, I'd be really greatful.

I would be connecting to Oracle databases (9i).

Thanks in advance.
S

christian · **Posted:** Wed Apr 28, 2004 1:41 am

You can use two Configurations and two SessionFactories, but in your case, it makes much more sense to link the databases at the DBMS level. See your vendors documentation for that.

suman · **Joined:** Sun Mar 28, 2004 7:18 pm **Posts:** 26

Christian,

Can you please tell me briefly what you mean by connecting the databases as the DBMS level ? What does that mean and how would that solve my problem ? Is that akin to some global view that I would create ?

Pls let me know.
Thanks.

christian · **Posted:** Wed Apr 28, 2004 1:58 am

Exactly, see the Oracle documentation.

suman · **Joined:** Sun Mar 28, 2004 7:18 pm **Posts:** 26

Can I create mappings for tables AND views in hibernate ? Or just tables ?

michael · **Posted:** Wed Apr 28, 2004 9:20 am

timlucia · **Joined:** Wed Apr 28, 2004 2:12 pm **Posts:** 6

How about using SQL Server? For example, the current JDBC mechanisms look like:

SELECT a,b,c FROM <db>.dbo.<table>

where <db> is one of many possible databases, all structurally identical, but containing data for different parties. A set of credentials is available in the Http session from which I can obtain which <db> to connect to.

Thanks,
Tim Lucia
Nashua, NH

timlucia · **Joined:** Wed Apr 28, 2004 2:12 pm **Posts:** 6

timlucia wrote:

How about using SQL Server? For example, the current JDBC mechanisms look like:

SELECT a,b,c FROM <db>.dbo.<table>

where <db> is one of many possible databases, all structurally identical, but containing data for different parties. A set of credentials is available in the Http session from which I can obtain which <db> to connect to.

Thanks,
Tim Lucia
Nashua, NH

Answer to my own question -- From the session, you get connection, and then call setCatalog on it, i.e.,

sessionFactory.openSession().connection().setCatalog("dbname");