Given that different users of the same servlets may be selecting different databases I have been reading up on the subject and wondered if anyone could comment on the praticality of storing either a jdbc connection of the information required to establish one in a session variable.
session.setAttribute( "jdbc_connection", conn )
I would need to be able to access this connection data in a number of servlets I guess. However I had also considered having a single multi function servelet that takes a parameter as a indicator of what page the user is on, that way I could re-use much of the code.
Thoughts and suggestions most welcome. This is my first foray into servlets so be kind ! :-)
[ March 17, 2007: Message edited by: Bear Bibeault ]
storing either a jdbc connection of the information required to establish one in a session variable.
You definately should NOT store a jdbc connection in a session - think of all the things that could go wrong, leaving you with an open connection that could not be closed.
Remember that all references stored in a session should be to a Serializable object.
Storing the information that would let you establish one - sure.
Connection pools are the way to go here, let the pool manage connections.
I believe part of your question was about session management across multiple servlets. As long as you pass the session id around with all redirects, that would not be a problem. I don't have the syntax in front of me, but you need to use redirect and encode redirect URL to ensure the integrity of the session ID if cookies are turned off.
if anyone could comment on the praticality of storing either a jdbc connection of the information required to establish one in a session variable.
While storing a connection itself in the session is not a great idea, storing the info to establish one is just fine. The session is a perfect place to put user-specific info.
In the case of container-managed pooling such as Tomcat provides, you'd store the JNDI string for the pool that's appopriate to the DB that user chooses upon entry to the site. This would be used to grab a connection from the pool whenever needed -- being sure to release it when done.
P,S, I also updated the topic title to better reflect the content.
Originally posted by David Heffington:
[QBthat there would be a significant overhead savings to use an existing connection as opposed to establishing a new connection every time db access is required.[/QB]
Exactly why a well-written and proven connection pooling mechanism is the best apparoach.
I've cobbled together a diagram which hopefully illustrates how my application will work. (see above url) Basically depending on the options the user selects when he/she signs on the servlet will connect to a different database.
User "a" can connect to database "1" whilst user "b" may connect to database "2".
So when the user points the browser at the application the "HostSelectServlet" will obtain and store the Session ID, then when the user clicks the submit button a servlet will link and store the Session ID against the relevent jdbc connection string.
This url does not display well in Firefox.
Originally posted by David Garratt:
So if I understand it you can either write your own connection pooling library or use the Tomcat built in facility.
You can also use a third party connection pool.
For instance, the one used by Tomcat is available as its own product, here:
(All) java servlet containers allow you to define connection pools. Their default implementation will do for most situations.
The tomcat manual JNDI Datasource HOW-TO explains how to set up the pools, and even provides code examples that show you how to look up a connection in your code.
[ March 18, 2007: Message edited by: Jan Cumps ]