I am currently working on a project where we will be using one database, but with multiple schemas, one per user. The schemas will be identical except for the schema name. In essence, we're keeping our users seperated by using multiple scemas. For example, userA will belong to userA_schema and user B will belong to userB_schema. All schemas will be identical internally (i.e. same table names) so the hibernate classes will not change.

We're currently using Spring with Hibernate as our DAL. I'm just trying to figure out how to allow a user to connect at login to their specific schema. I would not think that it would be possible to get this information from a configuration file, since we won't know which user is connecting until they actually log in. We're also using connection pooling, so that adds a bit of complexity to the problem as well.

My original thought was to find a way to dynamically update the database connection string to allow us to connect to the correct schema, but I haven't seen any documentation that would lead me believe this is feasable. I was also thinking that perhaps I could pass an updated Configuration to the updateSchema method, but this seems to be more related to an update in the table structure rather then a change in the actual schema.

Has anyone else out there setup hibernate in a similar manner? Could you please let me know approximately how you accomplished this? I'm currently at an impasse and I just need a nudge to get me going in the right direction. Any help would be greatly appreciated, thank you!

Michael J

first off i have no idea why you're doing that, it sounds like a pretty weird thing to do, but whatever.

i think you can do it if instead of writing a hibernate.cfg.xml you do the configuration in java code.

for example in my SessionFilter class i have something like
SessionFactory sessionFactory = new Configuation().buildSessionFactory();

i think you can set parameters on that new configuration (i.e. the connection url) and have a different session factory for each user. hope that helps.

That helps a bit. I want to clarify a little bit since I've noticed that database and schema seem to be used interchangebly on the Internet. In essence, what we're trying to do is similar to what ISP's do when they host databases. I want to have one MySql server instance running. However, I want to have seperate user space for each user. So when user A logs in, they only see user A data. Likewise, when user B logs in, you only see user B data. I thought seperate schemas/databases would be a good idea since that would keep the user data seperate (think domains) and if the data server was generating too heavy of a load, I could simply dump some schemas into a new database server machine and continue on. If this seems very weird, I apologize, but I really don't know how else to make this work. If you have any ideas of a better way to implement this I'd love to hear it. Thank you for all your help so far!

Michael J

Hi Michael,

The question is - do you create the new shema in the DB for the new registered user?

I have been accessing the two schemas in the same DB this way.
Assuming that we have the same tables in the schemas, i.e. we can use the same hibernate mapping configuration file for all of them, I did following:

1. Configure data sources for each schema.

2. Implement the method setSession(String dataSourceJndiName), which creates the session factory for the desired data source. It loads the hibernate configuration file, changes the value of "hibernate.connection.datasource" property and creates the session factory.