Hibernate version: 3.1.2

I'm not sure how achieve this but ....

I have two databases.

I have a list of objects I've obtained from a criteria.list() method call against the first database. I now want to load additional fields in these objects from a second database.

My original code simply looped through the objects and did a session.refresh() using the second databases sessionFactory. This works fine and loads the data nicely.

However, it does it using a single query per object which can results in a lot of queries. So what I was thinking of doing was generating a second criteria against the second database, and pass the list of objects from the first database as Restrictions.in(List) so that they are used to obtain records from the second database.

The question is now how to merge the objects from the first database with the objects from the second database ?

Of is there a better way of doing this ???? I could not see any form of session.refresh() function which accepts a list. I was thinking finding and extending the class which hibernate must use to load data from a result set into objects and adding a method to accept a list and populate it rather than creating new objects, but I'd rather not go that low unless I have too ;-)

Any thoughts ?????

ciao
Derek

Are the databases on the same server? Will they always be on the same server? You can access tables on other databases of the same server just by fully qualifying the table names, and using just the one sesion factory. So you'd use something like "otherdatabase.dbo.othertable", or however your DBMS handles it. The only things that don't work across databases like this are relational constraints (foreign keys).

If the databases are on separate servers, you could try link servers to do the same thing. I know that only a few DBMSs handle link servers, so this mightn't be an option for you.

I can't think of a purely hibernate solution to this. Possibly do something like what hibernate does when it proxies associations? Inside the get method of the objects from database1 is an "if null then fetch from database2" bit of code? Not exactly bean-like, but at least it's object oriented.

Hi, the databases are on different servers and whilst both are Postgres ATM, one will change to MS SQL server in a few months and with a completely different schema.

I ended up using two different queries as follows:

1. Access database A and get the first lot of records.

2. Copy the key property to a vector and use it as an in() clause on the query to the second data.

3. Bulk copy data from the second results to the first.

I wrote a static method which takes two collections of beans, the name of a key field and a list of properties names as parameters. it then uses reflection to copy the specified properties from one collection to the other using the specified key to find target records.

This results in two SQLs instead of many and a very fast operation. ;-) ;-) ;-)

ciao
Derek