We are on design phase of our project. The project needs to access multiple datasources, such as central database, project_1_db, project_2_db...

We are using Jboss as the ejb container and deploying hibernate as a jboss service. We use DAO factory pattern (called from Stateless session bean) and use Jboss's connection pooling.
We only deploy one SessionFactory with a predefined jndi name.
I am thinking to use ThreadLocal pattern, because this way, we can make use of session cached data within one thread. But since the same thread needs access more than one datasources, I am not sure I should create a session for each datasource or all datasources share the same session.

I am thinking to use Session s = sessionFactory.openSession(connection);
where connection is provided by jboss connection pool. So, in HibernateUtil.java, the code looks like:

public static Session getSessionForUserProvidedConnection(java.sql.Connection)

Session s = sessionFactory.openSession(connection);

return s;
}

public static Session currentSession() throws HibernateException {
if (sessionFactory == null)
return null;
Session s = (Session) session.get();
// Open a new Session, if this Thread has none yet
if (s == null) {
s = sessionFactory.openSession();
session.set(s);
}
return s;
}

As you can see, the first method getSessionForUserProvidedConnection() is not put into ThreadLocal, therefore the session cached data cannot be shared with the session saved in ThreadLocal.

My questions are as follows:
1) Every time I call Session s = sessionFactory.openSession(connection);
do I get a new session?

2)What is the life cycle of the session level cache? If I close the session, the cache will be purged?

3)In my particular situation, what is the besy way so that I can use the cache from two different datasources? Note that the tables in different datasources are different. So we could use the same session to cache data.

Thanks.

--Richard

yes for the first level cache (session)

maybe you should consider using second level cache but i'm not using JBoss so i may be wrong