• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

connection session variable

 
Ranch Hand
Posts: 333
1
Mac Eclipse IDE Safari
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Bartender
Posts: 6663
5
MyEclipse IDE Firefox Browser Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
David Garratt
Ranch Hand
Posts: 333
1
Mac Eclipse IDE Safari
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think I understand, what about the overhead of opening and closing database connections for each servlet. Will that be a problem ?

Dave
 
Ranch Hand
Posts: 1090
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Getting connections takes time. Opening and closing connection just to make one more connection is not a good idea. Search for Connection pooling and you'll get an idea what should be done.
 
Author and all-around good cowpoke
Posts: 13078
6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.

Bill
 
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I cannot stress enough how much you should take William's advice to heart. Not using connection pooling will cause problems. No two ways around it.
 
David Garratt
Ranch Hand
Posts: 333
1
Mac Eclipse IDE Safari
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
OK I will read up on that. If I get stuck I'll be back. :-)

Thanks again

Dave
 
Bear Bibeault
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If you are using Tomcat, it provides a builtin pooling mechanism.
 
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Bear Bibeault
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I just re-read your original post and saw:

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.
 
Ranch Hand
Posts: 1325
Android Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Bear Bibeault:
If you are using Tomcat, it provides a builtin pooling mechanism.



[Edited]
Hi Bear,

You mean pooling mechanism for connection??

Thanks,
[ March 17, 2007: Message edited by: Saif uddin ]
 
Bear Bibeault
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
David Garratt
Ranch Hand
Posts: 333
1
Mac Eclipse IDE Safari
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So if I understand it you can either write your own connection pooling library or use the Tomcat built in facility.

http://elliotgarratt.me.uk/commander4j/ConnectionPooling/ConnectionPooling.htm

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.

Dave
 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
http://elliotgarratt.me.uk/commander4j/ConnectionPooling/ConnectionPooling.htm
This url does not display well in Firefox.

Regards, Jan
 
Sheriff
Posts: 13411
Firefox Browser VI Editor Redhat
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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:
http://jakarta.apache.org/commons/dbcp
 
David Garratt
Ranch Hand
Posts: 333
1
Mac Eclipse IDE Safari
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Sorry about the problems viewing the link in FireFox, it was a simple "save as" from a powerpoint slide and I only checked it out with Internet Explorer 7.

Dave
 
Jan Cumps
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
     
    David Garratt
    Ranch Hand
    Posts: 333
    1
    Mac Eclipse IDE Safari
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Thanks everyone for your help. I will get reading.

    Best Wishes

    Dave
     
    Bear Bibeault
    Sheriff
    Posts: 67746
    173
    Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Glad to have been of help.

    One point for future posts. If you want stuff that you post elsewhere to be viewed, do not assume use of IE. I was unable to view your diagram with Safari.
     
    Consider Paul's rocket mass heater.
    reply
      Bookmark Topic Watch Topic
    • New Topic