Hi all,

I would appreciate your PoV on the following issue.

We are about to start developing a web application for a large outsourcing company. The application will be used by users belonging to different companies. Each company has a dedicated schema on the database. The table structure is identical on each schema (so we have the same data model replicated n times on n schemas). The application must access data selecting at runtime the right schema based on the user's company. If plain jdbc was used, the solution would be straightforward: the SQL code in the statement would contain a parametrized schema based on the company name contained in the session context:





How would you approach this issue with Hibernate?
My solution is to have n different mapping files (one for each schema), and to istantiate n different net.sf.hibernate.cfg.Configuration objects, putting them in the application context. When a user logs in, so that can be identified as a company user, a pointer to the right Configuration object will be put into the session context. In this way I should be able to address at runtime a specific schema on a user basis.

Do you think that it would work? Are there better solutions?

Thanks for your help.

Regards.

Dario Di Bella

We have had the same problem and after asking several questions here the response I got was "You simply can't do this with Hibernate." Apparently performance cost is the reason why hibernate doesn't do this.

IMO this is the single biggest weakness of Hibernate. We pretty much dumped hibernate and went with iBATIS (http://www.ibatis.com/)

p.s. take a look at my previous posts here.

Thanks for your answer.
It is not exactly the same problem. In my case I don't need to bind an arbitrary table at runtime, simply to select at runtime one mapping file (provided that I already mapped all the possible cases at design time). With my approach I do not modify Hibernate (and I don't want to do that!). I was wondering if it is possible to have different Configuration object and select one of them at runtime. Do you think that the same performance/resources issues apply also in my case?

Regards.

Dario Di Bella.

Have a look at Spring. I think this will ease your problems with regards to multiple data source.

You can of course have multiple different configuration objects (and SessionFactorys) and at runtime choose which one to use. I suspect this has to be done only once when the user logs in, not dynamically during application runtime (eg. the configuration does not have to be changed, only multiple ones have to be present) this should be no problem.

Thank you all for your help.

Michael,
you have caught my idea: having multiple Configuration objects created once at startup (never changing) and selecting at runtime one of them when a user logs in. Do you think that there may be performance/resources issues if the number of Configuration file is about 20-30?

Regards

Dario Di Bella

As long as you don't keep building SessionFactorys every time (build them at startup and choose between them and not between the configuration objects) the cost might be bearable ... will get you a long startup time for the app though. You'll have to experiment.

Your best bet would be to preload all the config files and place them into a collection in application context or load a configuration file for the initial user then place it into the application context. You

p.s. You might take an initial hit as far as resource usage but I don't think it will be significant. You