Any help greatfully received - I know this is a little open ended - but its an interesting problem, so here goes:

I'd like to able to determine the schema at runtime based on data in a bean persisted by Hibernate. Right now there are 2 occaisons where this would be beneficial -

1. when the data is spread over multiple databases for performance/redundcancy - i.e. names with A-C in schema A, D-J in schema B, H-L in schema C and M-Z in schema D. Clearly this can only be effective when I have a partial key lookup. Once I've chosen a schema, I don't want to have to do anything special at update/commit. Think of the phone company databases and you'll get the idea.

2. When I have common data stored - like a shared, common supplier list and a distinct and separate private local invoice list for 2 companies sharing a common platform. Invoices for Company A go in schema A, Invoices for Company B - go in schema B, the shared suppliers, diary etc go in Shared Schema C. It looks like I can't ask Hibernate to link the invoices to the supplier, as hibernate seems to expect linked items to be in the same schema - maybe someone knows better?

Does anyone have any experience of implementing something like this that they might be prepared to share? I've seen a few posts on dynamically specifying the schema at runtime for the whole system, but nothing that looks as if it might be able to deal with the above cases.

Tia.

Steve.

Use ORACLE, it has support for table partitioning at DB level
http://www.oracle-base.com/articles/8i/ ... ndexes.php



I would try not to split invoices by different schemas because it would complicate querying, for example to find all the invoices where stuff from common schema has referenced would require UNION query with number of subparts equal to the number of schema's(companies) that does not look like scalable solution to me.

If you are worried about performance then ORACLE's partitioned tables are your friend here.

Thanks Konstantin - I also found that I if keep the schema name in my EJB annotation for table name, I can share entries over any of the dynamic schema - although I'm not sure what the cache will do when it sees (possibly) the same entity and primary key - I'm guessing bad things.... I can still keep the key system unique!

I have something like:

@Entity
@Table(name="Common.Country")
class Country {
...
}

any attempt to load or store Product will be forced into the Common schema - even if I have a link from another schema - e.g.

@Entity
@Table(name="Invoice") {

@ManyToOne
Country getCountry() {
}

}

I can deploy the Country and Invoice beans, and can now have as many instances of Invoice as I like (one for each Accounting Company). I don't need to see a union of "Invoice" - If I need that information, I go to the general ledger - not the invoices.

I'm surprised that its so difficult to have any further input to the way links between tables are organised at runtime. The SQL for the above would still look like :

select * from Invoice, Common.Country where Invoice.company_id = Company.id etc....

This would work fine in any schema I wish to deploy....

I don't want to pollute your post, but I was about to post almost the same question, and I hoped I could chime in.

My situation is this: We have millions of people with their information in our database. The people are divided among the companies they belong to. The DBAs have designed and implemented this (in DB2) with each company having their own schema for their persons. There is a master "company schema lookup" table that identifies what schema to use for each company. When you want to perform a person lookup, it's a simple matter of querying that schema table, and then creating your next query to hit tables in the correct schema. Our "legacy" java code does this fine with our own homegrown dao framework, but over the last year we've migrated a lot of our projects to hibernate. However, it seems pretty much impossible to handle our company/person lookups using hibernate. We've looked into a few options, but everything we can think up is just a very ugly workaround. At this point, we are using hibernate extensively across many different projects and databases, but because of this one problem which keeps coming up and biting us, we are strongly considering whether or not hibernate is the right tool for our company.

I was surprised how hard this is - but when you unravel what's happening under the covers, its pretty clear that Hibernate seems to be slave to the Session. This is propagated when unwinding links to other objects.

The work around I came up will help, but is only one level deep - i.e. you can only have one master.

I'm pretty sure with judicious use of the Configuration object - I can get Hibernate to do exactly what I want - similar to yourselves - we've got some "global" objects (usually with many to one relations) and some specific to given companies (or accounting entities anyway). Since you can use the schema as part of SQL, I'm thinking the workaround of using the schema name as part of the table name "common.country, common.currency, common.timezone etc" and having no schema associated with distinct tables is OK.

I can't help thinking this will get ironed out "in the fullness of time" - I just hope not too many folks give up on Hibernate before then. I can't see why hibernate couldn't be extended to work with this really well - after all, its only need to hydrate and de-hydrate objects - and you're only forcing the schema.

Good to hear someone else has come accross it - I figure quite a few test systems will work this way too - with the data under test being out in one schema, and the main static data in another.

Wanted to conclude this - just in case anyone else has a need to share data over multiple schema's and have the hibernate linking "just work".

I ended up writing a new naming strategty that knows about our schemas. In essence I have some core data (country codes, profiles, language codes, products) and some per company data (invoices, areas etc) that use the common data. I wanted to get hibernate to still manage a manytoone on the shared data - so my naming strategy determines if the class is a "shared" one and inserts a fixed schema name into the table name.

The net effect is great - each "per company" data item goes into its correct schema (i.e. no override, determined by its EJB entity manager aka hibernate session), and the common data is forced into its common schema.

I also built a simple singleton to manage the "persistenceManager"s so that I can (at runtime) decide which schema the data belongs to. Thus I can use a deterministic algorith, based on the user, to decide which companies data the objects will be persisted in.

Its all there - you just have to find it. Would make a good section in the next version of Hibernate in Action.....