Hopefully this post makes sense and someone can assist me. I have also posted this in the Spring User's forum because even though it is a Hibernate question, I'm attempting to use Spring's support functionality.


My organization partitions its data into multiple schema's. In my example we would have a single table, defined as follows:




This table would actually exist in 5 schemas. Depending on some property of the object, for this example we'll use birth dates, the object will be persisted to a specific schema.

ie:
if the person's brithday is before 1950, it goes in "SCHEMA_A"
if the person's birthday is after 1950 but before 1970, it goes in "SCHEMA_B",
etc.

Now, since we're using Spring & Hibernate, we want to have the following spring bean definitions:



The Crux of the problem:

All of the Schemas exist in the same Database, so the same SessionFactory should suffice. Except that when defined, the SessionFactory's schema is set and cannot be changed.

Some gotchas: The DAO's all extend the HibernateDaoSupport class of Spring, which allows us to properly manage transactions and isolation levels, in a declarative manner. Therefore we can't go arbitrarily creating new SessionFactories (via programmatic interface), or we'll break the Spring support for this.

We can't use multiple SessionFactory instances, as this would require we define multiple DAO bean instances, which in turn would require that anything using it know which bean-id to look for (Kinda defeats the purpose, no?)

What I'm really looking for is some way to...

intercept the session after the call to a "getHibernateTemplate()" method (eg: save) is made,
change the setting of the schema on it,
allow the original requested functionality to perform,
return the schema value to the previous setting,
return to the DAO that made the call to "getHibernateTemplate()" method.

OR - Change the stored Schema value on the Session before it builds the SQL, and change it back after


But all of that without breaking the transaction/isolation support and keeping it platform independent. I know that Hibernate has a project in the works for supporting something like this (Hibernate Shards) but I can't use a beta codebase for anything I'm doing.

Does this make sense? Is it possible with the current Spring/Hibernate frameworks, or am I out of luck til Hibernate Shards is complete?

Thanks for any help...

-B

I'm posting this response to provide more information about my original problem, hopefully someone can assist me.

The real core problem I'm running into is the SQL generation.

Lets start by providing a better example than my previous one:




Our Member data is partitioned across multiple schemas. The schema in which a member resides is determined by using the Customer ID on the Member instance. Customer A's data cannot co-exist in the same table as Customer B's data.

When SessionFactory instances are created, you define a default schema for all of the Sessions that will be created. You don't necessarily have to define the default schema for the SessionFactory, however if you don't, it will use whatever the default schema is for the connection.

When you go to use the Session to create a Criteria, or run a HQL query, at some point, it converts all of this into a native SQL string to be executed. If a default schema is defined, this information is used to qualify the table names for the mapped entities when generating the SQL string.

What I would like to do is provide a means to plug-in a class that knows how to differentiate which schema to use based on the instance of the mapped entity to be found. In essence: change the schema that will be used when the SQL generation is done, for each Entity in the query. Meaning: if it's a multi-tiered mapped structure, each mapped entity is used to resolve the schema for that entities SQL string.

This functionality would need to work for all of the operations supported for an Entity.

Obviously I would define the class that would make the determination. But from what I can see in the API, theres no real way to change the schema value once/if its defined.

I could use multiple connections/SessionFactory instances, but this would require that I create 100+ connection pools. Not something I want to do. (And we use JNDI configured connection pools so c3p0 is out...)

Does anyone know if this is possible? Does it seem like its something that would be useful?

I tried to find out where exactly in the framework the SQL is generated, so i could find out if it'd be possible to wrap an interceptor around that part and update that value. Unfortunately I haven't been able to dig that deep into the framework (as it is way, way, way down there, necessarily so). As such I'm still at a loss for what to do.

Any help would really be great. Thanks in advance.

-B