Hi there, Can this be done in Hibernate 3.0?

When a user queries or updates the data on our web based application, the first thing that happens is we determine which "catalog" they should affect. We create a new catalog for each user for data protection reasons. (each "user" catalog will typically have around 30 tables with between tens of thousands and millions of rows depending on their usage)

Previously, this would have meant creating XML mapping descriptors for each catalog which, when you have over 2000 is not practical, nor is it practical to write a single web application for such a solution.

We eventually created our own XML data access generator that allows us to include, as one of the arguments, the catalog name so for instance we may have a static method :

addressDAO.findAddress(String catalog, long addressID) ...

We also have classes with non-static methods where the finder class has a method "setCatalog" that must be called before any finder methods are called. The main reason for having both methods was experimentation..

So my question is... can this be automated effectively in Hibernate 3.0? I appreciate that the fact that our schema inherently has tables with identical primary keys across catalogs could cause issues if this catalog approach is not at the heart of much of Hibernate..

Any help would be appreciated!!

Why don't you just set hibernate.default_schema or hibernate.default_catalog before creating your SessionFactory?

We could do that, but we'd then need one session factory per user which means 2000 session factories.. The ideal situation seems to be to have a single session factory..

Wouldn't this solution, in a multi-threaded webapp environment, also mean I would really have to build a new session factory for each new request? (As the request could be coming from any of our users)

I'm sure I could cache them.. but again, having 2000 (and climbing, daily) session factories cached doesn't sound ideal...

We also need to be able to access data across certain user databases sometimes - this happens when two or more of our users are affiliated in some way...

I'm not sure your usage of catalogs is good.

The design of the database isn't really the issue at hand (and way beyond my current remit to change).. it turns out it's a little unusual although I do know several web based application service providers that use the same method...

Are you saying that it can't be done?

No, it's not really possible, because most of the SQL hibernate issues is generated up-front at SessionFactory build time.

I can imagine that some patch for this would be easy to do.

well - the pregenerated sql contains the hard core catalog name so not that easy.

But one thing that comes to mind is to use H3 entity names to map this - that would give you 1 session factory, but then x mappings per class...which also might not be optimal...I think i would use 2000 sessionfactories instead..

hmm well this time next year it'll be 20,000 SessionFactories so I don't really think that's a feasible option..

I guess we'll just have to keep developing our own DAO generator
:(

But the pregenerated sql contains the hard core catalog only when

"hibernate.default_catalog" property is set. If we are not setting the property while session factory build up, catalog won't be appended with the query. Am i right????


Thanks & regards

unnis