Multiple DB in Hibernate in one schema using JBoss-Cache?

[etienno]

Hi,

this question concerns the JBoss Cache and Hibernate.

Hibernate have a "limitation" that I would like to overcome.

Hibernate cannot use the Same Schma for 2 different databases. Using C-JDBC is not the answer either.

See:
http://forum.hibernate.org/viewtopic.php?t=937847
http://forum.hibernate.org/viewtopic.php?t=938654&highlight=schema+multiple+database

I read in the JBoss faq http://docs.jboss.org/jbcache/FAQ.html and in the documentation that the JBoss cache can use a Cache Loader (since 1.1) to persist its cache objects (FileCacheLoader, BdcjeCacheLoader and JDBCCacheLoader).

JBossCache 1.2 released:
- JDBC CacheLoader: allows to load/store data from DB
- Common dta transfer format: this allows for replication between different
CacheLoaders, e.g. FileCacheLoader and JDBCCacheLoader. Also
enables replication e.g. between MySQL and Oracle
(still needs more testing though)

Would it be possible that 2 SessionFactory (each connect to a different physical DB, with a different schma: Schema 1 on DB1:Oracle, Schema 2 on DB2:Sybase) use the SAME cache loader? I means that if a Parent table is on Sybase, the Children data (on Oracle) could be retreived in memory because it could have been load in the same cache before?

But from this mail: http://forum.hibernate.org/viewtopic.php?t=938654&highlight=schema+multiple+database
it seems that it isn't the case.

First, no, Hibernate will not be implementing in-memory joins

But if the CacheLoader do the job, could it be possible? I think about 2 BdcjeCacheLoader for each SessionFactory loading in the same SleepyCat BD?

BdbjeCacheLoader: this implementation is based on the Sleepycat Java Edition database, a fast and efficient transactional database. It uses a single file for the entire store. http://www.sleepycat.com/jeforjbosscache for details.

Another options could be, using the JDBCCacheLoader, and cache the DB1:Oracle data inside the DB2:Sybase in a extended schema? So, the connection factory on DB2:Sybase might have all the data?

I know this sound tricky to say the less, but could it be feasible by writing a CustomClassLoader?

The goal of all this is to simulate a transparent SQL INNER JOIN between two physical databases and avoid tousand of "SQL SELECT" on a real DB.

Note that the DB1:Oracle maintain only read-only data (easily cachable) and only updated by a batch process each day at 12AM. The DB2:Sybase would be the transactinoal (r/w) database.

Thanks for replying

Etienne.
Montreal

[ben.wang]

I am not sure if CacheLoader is what you want. It is used for *state* persistence so it does not have the entity notion of schema and such.

-Ben

[etienno]

Thank Ben for your answer,

I understand. It is probably more on the Hibernate side that the issue of schema must be resolved.

But in the JBoss Cache side, would it be possible to do this next scenario.

In a way it doesn't matter that the data to cache comes from a database, I just want to know if it is possible to control the PersistenceStore at the "data level" : to add new "relationship" between object after putting it in the data store.

In the other side, the use of a dataBase (BdcjeCacheLoader) as the persistence store points out that it could be possible in a near future to add relationship between cached object.

Here's an example to illustrate the problem:

1- My application wants some data: ObjectTypeA and a Collection of ObjectTypeB linked each other to a Parent-Child relationship.

ObjectTypeA and ObjectTypeB come from Data_BaseA. The application check the JBossCache (via the CacheLoader) to see if the data is there, if not it loaded the data from Data_BaseA before and put the "Structure" (One ObjectTypeA and many ObjectTypeB) in the persistence store.

2- The application needs now to get the ObjectTypeB plus a collection of ObjectTypeC that is linked to ObjectTypeB in a Parent-Child relationship. But this data is in another database: Data_BaseB. I check if the ObjectTypeB is in the cache, lets say I found it (because it load it from Data_BaseA). It then check if the collection of ObjectTypeC is in the cache. It doesn't find it. So it load it from Data_BaseB. Then it update the ObjectTypeB in the cache to linked it to a Collection of added ObjectTypeC.

3- It means that future access of ObjectTypeA->ObjectTypeB->ObjectTypeC will be done in the persistence store without accessing any database, and without any probleme of multiple database inner join.

Using Hibernate, I cannot use the same cache for 2 database access. But if by some way we can add an object in the persitence store, and add new relationship between them, Hibernate could maybe use the same cache and the same schema to access 2 databases.

I know it sound strange...

Thanks,

Etienne
Montreal