I'm using MS SQL Server and I need to join tables belonging to different databases. Does Hibernate support it ?
If yes, how should I configure the associations in *.hbm.xml files ?

Thanks for your attention,
Marcello

You cannot do association between classes defined in different SessionFactory configs. If you somehow access both the databases through the same SessionFactory (i.e., by using database links or some other measure), then simply define the association as normal.

Otherwise, I think a custom UserType is the only way to implement this.

MS SQL Server *can* do a join between multiple databases, like this:

select a.field2, b.field3
from database_1.dbo.table_1 a
join database_2.dbo.table_2 b
on a.field1 = b.field2

I don't know how to map this in hibernate.cfg.xml and table_*.hbm.xml.

I thought I could define a connection to database_1 in hibernate.cfg.xml and then put in table_1.hbm.xml the following:

<class
name="mypackage.hibernate.table_1"
table="table_1"
<set name="children" inverse="true" lazy="true">
<key column="field1"/>
<one-to-many class="mypackage.hibernate.table_2"/>
</set>>

and in table_2.hbm.xml the following:

<class
name="mypackage.hibernate.table_2"
table="database_2.dbo.table_2"

>

and try a query like this:
SELECT t1
FROM table_1
LEFT JOIN table_2 t2

but it doesn't seem to work. Where could be the error ?

Thanks a lot for help.

I was thinking more of database links in Oracle. Is there a similair concept in SQLServer/Sybase? I mean how would the server running database_1 know how to resolve a reference to database_2?

How would you do the above in JDBC? When you try the mappings you posted with the query, you say "it doesn't seem to work". In what way, exactly, does it not work? Do you get an exception? What does the generated sql look like?

Have you looked at the "schema" attribute of the class element in the mapping document? Will that work?

Sorry, I seem to have more question for you... ;) But I don't know SQLServer very well.

Use, eg:


schema="database1.dbo" table="table1"

I'll follow your suggestions and let you know.
Thanks for your replies!

Marcello