I use connection pooling provided by JBoss 7 and I do a connection.close() after every action.
But when I create a temporary table, it does not remain for the current user because he is using a new session from the database (due to connection.close() and pooling).
For example, I create a temp table in an action. The user changes page. The new action has to do queries in temp table but this does not exist anymore.
So, I really don't know how to provide temporary tables with this kind of architecture.
Temporary tables in combination with connection pooling means request scope for the temporary tables. You'd like to have session scope temporary tables.
I am not aware of an out-of-the box solution. So options are
- Create normal tables with a prefix/suffix to associate these with a user
- Use the same connection for a user throughout the whole session
- Rewrite the application: Instead of temporary tables, read the data completely, and store it in the HTTP session. Possibly the database can paginate so there is no need to cache intermediate results etc.
Options 1 and 2 require cleanup, especially if the session is not shut down properly. Option 2 possibly requires many resources. So I would investigate the 3rd option, even if it seems to be the most cumbersone one.
