Potential Issues with creating EntityManager per HttpSession in web application

Question

In one of the application ,I saw Entity Manager is created 'per user HttpSession' and EntityManagerFactory is created only once.

Spring or EJB is NOT used in application. Entity manager is cached in http session and closed when session invalidated.

public EntityManager getEntityManager() { 
    //Logic to get entity manger from session
    //If its present , then return 
    //Else create new one , entityManagerFactory.createEntityManager();
    //Put it into session and then return.
    //Returned entity manager is not closed by dao methods , 
    //but clear() is invoked
}

1. What are potential problems with this design.
2. What if 100K Users logged in into application simultaneously, will we run out of jdbc connections ?
3. Does every entity manager has a separate JDBC connection associated with it?

Answer 1

The answer to 2 and 3 is yes. As for Q1:

One problem you will have is that sessions typically last anywhere from 2 to 24 hours (or more) after they were last accessed. This means that your session object will try to sustain the EntityManager open and the EntityManager will try to keep the JDBC connection alive and exclusive to itself. Even with 50 users per hour, you'll already have loads of exceptions and Error 500 pages because of this.

I believe Serge Ballesta listed the other major problems this approach will cause.

A much safer solution is to have a static ThreadLocal<EntityManager> singleton access and a javax.servlet.Filter on all URLs with a try-finally statement that makes sure the EntityManager is properly closed on each request. Otherwise any exception that might occur will leave the connection dangling and cause additional problems.