How do I create association between multiple databases or catalogs.
I have created two different session factories for two databases:



While creating association <many-to-one> I am getting error that

Please help as I need to use two different databases.

You want to map tables in two different databases with an association via mapping files? You know how Hibernate works? It generates SQL-statements and executes them via JDBC. What database can handle a SELECT that uses tables "living" in another DB? That cannot work.

You have to build the association inside your app. Read from the first DB. Get the values you need and go to the second DB. Put it in your model. But there cannot be an automagic solution.

Multiple db can be handled from single query. Have you ever tried this.
Please check this query in mysql. It runs smoothly. I want to execute this from Hibernate.
You can excute this query from any of the database.

The notation you posted gets data from different schema not databases! You cannot run a query in one database and get data from a completly different other db. What you can do is using database links e.g. in Oracle. But this leads to firing the statement against ONE database instance.

If you only want to use to different schema inside one database you can use the qualified tablename, but you don't need two SessionFactory then.

I have tried this query with two different databases and not schemas.Please check before posting again. I have executed it in mysql and looking for the same through Hibernate.

The same query is also possible through HQL but how to make it through mapping is a problem.

The above classes refer to two different databases i.e. different SessionFactory Objects.

You asked for help not me, so no reason to stamp on the ground and talk to people that try to help YOU like the way you do.

You could post your SessionFactory config.
How can a MySQL DB know another MySQL DB on another server? Maybe your statement works if you connect to MySQL and execute this statement and MySQL-engine can connect the resultsets, but Hibernate does not know anything about the physics of your DB instances and cannot know if this obviously MySQL specific feature can work with the configuration you use.

You need to use one SessionFactory only. Then, in the mapping files for your classes, use the schema or catalog attributes. For example:



This of course assumes that the databases lives in the same MySQL server.