Hi -
I haven't yet found this issue addressed in the FAQs or elsewhere. I'm using Hibernate 3.1, with MySQL 5.0.18 (or close) on various platforms.

My schema - for a variety of reasons - encompasses several databases. As usual, I have multiple SessionFactory objects, each referring to a different database. However, tables from databaseC act as lookup constraints to tables in databaseA and databaseB.

I am baffled how to refer to tables in one database from tables in another within Hibernate. ( i.e., from databaseC.foo ), if this indeed is possible.

Thus, within my databaseA.cfg.xml hibernate-configuration I have multiple mapping elements, each specifying a hbm.xml to entities in that database. However, some of these hibernate-mapping elements - let's refer to one called bar - have a configured many-to-one relationship to a table foo in databaseC:

many-to-one
name="foo"
column="type_id"
class="com.whatever.support.hibernate.C.Foo"
not-null="true"/>

But of course that table 'foo' is declared within databaseC.cfg.xml. During Hibernate instantiation, the HbmBinder succesfully maps databaseC.foo. But when it attempts to map databaseA.bar, I see the MappingException "An association from the table bar refers to an unmapped class: foo"

This makes sense - there is no reference in databaseA.cfg.xml to a foo table; it exists in databaseC. Obviously I can't place a mapping to 'foo' in databaseA.cfg.xml - it belongs to another database.

The simple solution ("just move table foo from C to A") doesn't address foo's use by tables in database B, nor does it address the fact that there are relationships in databaseC.foo to other tables in databaseC.

Are there any solutions, either through configuration or programmatically, to this problem? Solution X is to move all tables in all databases into one. I'd rather avoid that! Many thanks in advance, Stephen

Hi, I'm not very familiar with mysql, but how would you do this in SQL? Hibernate is only a ORM solution, it can do nothing with the database you cannot to with SQL.

In most DBMS you can configure serveral databases. If you want to access multiple databases with one statment you have to use one SessionFactury. You can user one SessionFactor and configure you're databases using the "catalog" attribute in the mapping files.

I don't know about mysql but could you use a "oracle" like database link if that is a MySQL feature?!

simon

jherbst and simon,
Thanks for your replies; I'm not sure yet whether jherbst's reply has anything I can use.

As for the jherbst's question "how would you do this is SQL?" I'm not sure off-hand how all implementations do this. However, with Oracle and MySQL it is very straightforward, and I can post some examples later. I should have been explicit in saying that my multiple-database use works fine within MySQL. I attempted to allude to the MySQL (and Oracle) syntax in my original post. You simply refer to the the other database, whose connection parameters have been somehow set, by using the dot-notation database.table.row.

So without at ORM, (some) SQL implementations can do this easily. It is of course true that if SQL can't do something, an ORM tool can't either. But my problem is that (at least) MySQL can do what I want (joining across databases very easily. It is possible that ORM tools can't - at least not easily.

If I understand jherbst's comments, it is possible within one SessionFactory to use the catalog attribute of the hibernate-mapping element (or other more specific elements such as class, joined-subclass...) to provide or override "the name of a database catalog" [from the Hibernate HTML, section 5.1.2 and following].

I'll be honest - I don't know what this means exactly, nor does it appear in the DTD that one can provide more than one such catalog value. Can anyone point me to any docs or code on what "catalog" does and how to use it? I see nothing else in the Hibernate HTML docs.

However, I'll speculatively answer my own question, and request from the community a "confirm or deny":
1. Just because SQL implementations can join across databases does not mean that an ORM tool can.
2. Looking at JDBC and connection pooling, I'm not sure possible it is to configure a Connection that uses more than one database.
3. It is likely that in most situations where access across multiple databases is required that simple matters such as latency would weigh against trying to directly enforce referential integrity across network connections. Generally, it would be wiser to use DTOs or other such strategies, or even manually copy the remote database tables locally. If necessary, a replication facility can be used.
4. In my case, I used multiple databases as a device to separate logical functionality. The databases were not separated because of business concerns, just for clarity of design. So now I just dumped all tables in one database. Ugly but problem solved.

Still, I believe that business-mandated or design-driven separations into multiple databases are common enough that an ORM solution would be desirable if not immediately possible.

Thanks, Stephen

Hi Stephen,

I fear you'll making you're live much more complicate than it is. You're definitly on the right pass. The catalog attribute is nothing more than the thing you call a database base.

So if you use one SessionFactory and must map you're table like the following:



This will create full qualified select statements like

By the way, if you want to separate you're data for design reasons only, I would prefer using different schema over different databases.

Greetings
Joerg

Joerg,
Many thanks! This made it much clearer for me - I will give it a try soon [I wasn't thinking clearly yesterday when I looked at the DTD). And good advice about schema vs. databases.