Single web application in Single Database Shared Schema mode.

[arvindkumarc]

I am developing the single web application which needs to support multiple schema using the Spring-Hibernate framework.

In order to acheive the same, I used the code(ContextSwappableTargetSource- MultiSessionFactoryTargetSource.java, ContextHolder.java) uploaded in this forum.

I am declaring all the session factories in the config file and I make a intercept call on every request and put the particular schema to connect in the context. Then the DAO will take the corresponding session factory's session to do the operation.

I did this as a simple sample application and everything works fine.

Whether i can proceed with the same to develop the real application, will their be any hidden problem in this method?

By the way, the real application will be hosted in single application server and 15-20 concurrent users for the initial phase of deployment. 100-200 concurrent users to be added up, over a period of 6 months.

[Marten Deinum]

I suggest not using multiple SessionFactories but multiple datasources, it is much more lightweight. This exact method is used in an application which host 50 different clients on a single tomcat with a couple of 100 to 1000 concurrent users without any problems.

[arvindkumarc]

Are you maintaining separate database for each client?

In our case we have to maintain single database with multiple schema each for a particular client.

how to define the schema in the datasource such that we can have multiple datasources?

[Marten Deinum]

We had a mixture of single and multiple databases. In our case each client had its own username/password which was tied to a particulair schema., so basically defining multiple datasources was enough. I also assume you have something similair, if not I suggest you start doing it. It also adds an extra layer of security.

If it is only a single database you have you might instead want to take a look at the UserCredentialsDataSourceAdapter, that creates a connection for a certain user. Drawback of that approach is that it doesn't use a connection pool.

[vamsirkrishna]

Dear Aravind can you provide us sample code or project which you have developed?
(ContextSwappableTargetSource- MultiSessionFactoryTargetSource.java, ContextHolder.java)
It is very needful to us.


Thanks & Regards,

Vamsi R Krishna