My application starts with a servlet that displays the user with a list of databases that they can connect to. They will click on a link and the next step will be to construct a jdbc connect string from their selection and establish a connection.
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 ! :-)
Dave [ March 17, 2007: Message edited by: Bear Bibeault ]
I would suggest that you write a class that obtains the connection for you, instead of storing it in a scope. Something like JDBCServices.getConnection("DB_Oracle") and you could use the factory pattern to return the right connection based on the string passed to you. Consume the connection and then close it when you are done.
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.
David- I would like a little elaboration by William regarding the 'pooling' technique. I would agree with you that 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.
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.
The William/Bear proposal seems to be the most appropriate:
set up the different possible databases as database connection pools in your web server. You will define a jndi lookup name for each pool.
(All) java servlet containers allow you to define connection pools. Their default implementation will do for most situations.
store the jndi lookup name of the database that the user is consulting into the session. It's the appropriate place to store the info.
whenever a connection is required, get a connection from the web server's connection pool mechanism using the jndi lookup name, use the connection, and than close it. Closing the connection will return the connection back to the pool.
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.
Regards, Jan [ March 18, 2007: Message edited by: Jan Cumps ]