I have one webapp with 100 customers. One schema for each customer.
What can i do?
One jndi/dataSorce + sessionFactory for each customer.
Or
execute:

session.connection().prepareStatement("set search_path ="+ myschema).execute();

for each session
????


Hibernate version: 3

Name and version of the database you are using:PostGreSql 8.0

Somenone knows the best way for this problem?

Every schema is identical, I assume? We have done the set search_path thing you are mentioning, and it has worked very well for us. We wrapped the Hibernate Session and every call to beginTransaction explicity sets the search_path on the connection. (The first real problem we've hit with this is now that we are trying to cluster the db, the clustering technology we are using doesn't seem to handle the search_path correctly.)

Anyway, I'm telling you this so you know that this approach has been done, and can work. Having said that, I'll also say that this is a temporary solution in our case until we can get the schemas combined into a single schema. I'm not sure why you would ever actually want multiple identical schemas. We did it as a short-term solution to handle some legacy issues.

I personally didn't want to go the other route because of the extra memory use caused by having many session factories.

Good luck.

Yes. Every schema is identical.

How do you will combine multiple schemas into a simgle schema?

You will put a WHERE to every HQL? (or SQL)

Assuming that you are using surrogate keys throughout your DB.



You will need to write a utility to do the data migration so that no two records in the same table have the same primary key which is probably an issue because your main hibernate_sequence will currently exist in each of the schemas.

Once you have that you will need to examine your unique constraints.
Basically any unique constraint that encompasses a foreign key to another tables primary key shouldn't cause an issue because your utility has already fixed all the primary keys.

For the remaining unique constraints you will either need to decide if you can live without them or add a new "schema_id" field.




A significant number of HQL/SQL's wouldn't need to be changed.
The reason for this is that if you reference a record in one table (by its primary id) and then as part of that query you join to a bunch of other tables the joined records must have originally come from the same schema.

So the only time you would need to add extra where clause is in SQL's where you don't reference a primary (surrogate) key.

However I am guessing that the scope of this will be limited to a few top level tables (probably the same ones you needed to add the extra schema_id fields too).