Hibernate version: 3.0.5

In our system we currently have 1975 tables for our ERP product. We we deploy the session factory we have to allocate around 128 meg. of PermSize space for the single session factory to be deployed. More if we want to be able to redeploy.

My question is as follows... In order to allow our program(s) to connect to multple databases what is the correct or advised way to do this?

I have read that we should just create another session factory. But the session factories are so expensive and involve a lot of setup overhead for each database even though the schema is likely the same. Steps are:
1) create a copy of the datasource and modify.
2) copy hibernate-service.xml and modify to connect to new datasource name
a. change mbean service name
b. change SessionFactoryName attribute
c. modify ant build file to create 2 Har files and deploy them to Jboss
3) Change code to know which SessionFactory to connect to.
Rinse and repeat for every new database option. More if the tables/schema is different.

Another option is the ability to pass a Connection object to the SessionFactory.openSession(Connection) API. But if you do this do you lose the C3PO connection pooling? I also read that by doing this "but be warned that the 2nd level cache is global for one session factory thus you will not have complete isolation ... so multiple session factories are to be preffered" -- [urlh]ttp://forum.hibernate.org/viewtopic.php?t=942051&highlight=multiple+connection[/url]

Right now we are not explecitly doing anything with the 2nd level cache. Ie. we have not turned it on.
So by my understanding we should us the option of passing the Connection, unless we loose C3PO pooling, instead of creating new sessionFactories.

Thank you for any input,
David

I doubt you'll get an answer to this question.. I think Hibernate still does not have a suitable solution for this scenario.

With such a large number of tables, it would seem unlikely that a new Session Factory object per database would be appropriate. However, if you pass a connection object or parameter, as you point out, you will lose isolation in the 2nd level cache.

In our situation, one session factory is equally inappropriate. We have a smaller number of tables (only 400) but a large number of databases (2000 and climbing).

I believe we could use hibernate but in its current form, I think we would suffer on flexibility, scalability and performance by doing so.

M.

You should use one SF instance per database, of course.

Even with 2000+ catalogs / databases? I'm wondering how much memory I would require for this...

In the first post you say you have 2000 tables. Now it is 2000 database instances. Which?

How much memory does 2000 database instances consume? Much more than 2000 SessionFactory instances, I would have assumed.