Dear all,

We are about to build an application for our clients using Hibernate (we have already built our main enterprise level app using Hibernate 2.1.8). Even though the application itself is not mind blowingly complicated, our clients have thrown up an issue in that they each want their own database.

Is it possible to configure Hibernate with multiple datasources? What are the issues that I need to be aware of? What are the best practices? Initally I was thinking of specifying a parameter in the query string to identify the target datasource. For example:

http://someserver.org?ds=db_1&getClient ... Summary=39
http://someserver.org?ds=db_2&getClient ... Summary=40

the 'ds' parameter will indicate the underlying datasource.

Do you think this approach will work or have I really got things wrong? BTW, we wanted to use Hibernate 2.1.8 and not 3. We wanted to migrate to 3.x later in the year.

just use different sessionfactories for each customer or if the mappings are completely the same then use the same session factory, but provide your own connection (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.)

Ahh I see. But does this mean, as we have more clients, corresponding session factories will have to be written and deployed. So if we have 5 clients then we have 5 SessionFactories and with each request we need to pass in a parameter essentially telling the DAOs which session factory to use. Is that what you mean?

basically yes - just like if you used jdbc, where you need to use a different datasource per customer

Sweet! So I can do something like this in my BaseDAO:

protected void getSession(String clientIdentifier) {
try {
// call the corresponding session factory's getSession
// for this client
//
if (clientIdentifier.equalsIgnoreCase("Client A")) {

ClientA_HibernateSessionFactory.getSession();

} else if (clientIdentifier.equalsIgnoreCase("Client B")) {

ClientB_HibernateSessionFactory.getSession();

} else {

// throw some sort of an exception

}
} catch (Exception e) {

// there was a problem closing the DAO session.
//
logger.fatal(e.getMessage(), e);
}
}

I can write a similar method for closing the session as well... Then it will be a matter of modifying this BaseDAO and writing additional session facotries... right?

Thanks a bunch Max.
Arup

Sorry, screwed up the method signature... so here is the revised version :-)

public static Session getSession(String clientIdentifier) {
try {
// call the corresponding session factory's getSession
// for this client
//
if (clientIdentifier.equalsIgnoreCase("Client A")) {

ClientA_HibernateSessionFactory.getSession();

} else if (clientIdentifier.equalsIgnoreCase("Client B")) {

ClientB_HibernateSessionFactory.getSession();

} else {

// throw some sort of an exception

}
} catch (Exception e) {

// there was a problem closing the DAO session.
//
logger.fatal(e.getMessage(), e);
}
}