File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Servlets and the fly likes is it good to carry the jdbc Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Servlets
Bookmark "is it good to carry the jdbc "Connection" over servlet and Actions ?" Watch "is it good to carry the jdbc "Connection" over servlet and Actions ?" New topic
Author

is it good to carry the jdbc "Connection" over servlet and Actions ?

Artemesia Lakener
Ranch Hand

Joined: Jun 21, 2005
Posts: 162
I have few servlet and Action classes. In each servlet or action I need to access database so I need JDBC Connection. Instead of opening a new one everytime, I want to create in the first time and then store it in the "session" so it can be carried from one servlet to another, or another Action class, etc. Is this safe and is it a good way to save the time for opening a connection ? I mean, temporarily ignore other optimization methods like pooling, etc. I want to see if I think makes sense.

Thanks.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61772
    
  67

No, it is a very bad idea. By doing so you run the risk of having separate threads compete for the same connection.

You should be using container-managed connection pooling and obtain a connection from the pool for each request, releasing the connection when the request is done with it.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Artemesia Lakener
Ranch Hand

Joined: Jun 21, 2005
Posts: 162
Originally posted by Bear Bibeault:
No, it is a very bad idea. By doing so you run the risk of having separate threads compete for the same connection.

You should be using container-managed connection pooling and obtain a connection from the pool for each request, releasing the connection when the request is done with it.



Thannks. Could you help me clarify the following things --

1. Don't we have risk of having separate threads compete for any other objects stored in the "session" ? what if they compete to do "setAttribute" on those objects at the same time ? Why is only "Connection" object specially concerned ?

2. I use an application server and it seems I do NOT need to code anything for connection pooling. All I have to do is check a box "use connection pool". Then I simply write

javax.naming.InitialContext ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup(dbname);
con = ds.getConnection();

That's it. It seems it is very dummy code. Is that all I need to do ? Do you mean the container takes care of all of the stuff like building connection for me ?

Thanks.
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17260
    
    6

1. If you create a Connection just for that Session, then no, you don't have that threading issue, per say. But is bad design because you have no control over how many Connection objects end up getting created, and since you won't be using it 100% of the time, it is wasted resources that a Connection Pool handles.

2. Yes, it handles all that for you. And is that simple.

Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61772
    
  67

If you create a Connection just for that Session, then no, you don't have that threading issue


I disagree. It is quite possible for the same session to have multiple active requests competing for the same session data when multiple browser windows are opened using Cntl-N, or when using framesets, popup windows, or iframes.
[ July 05, 2005: Message edited by: Bear Bibeault ]
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61772
    
  67

Is that all I need to do ?


Yes, but be sure to get the connection within the scope of a request, and to release it within the scope of that same request. If you take the pooled connection and stuff it into the session, you introduce the same multi-threading problems I alluded to earlier.

Why is only "Connection" object specially concerned


It's not special. All session data is vulnerable to threading issues. For read-only attributes it's usually not an issue.
[ July 05, 2005: Message edited by: Bear Bibeault ]
Artemesia Lakener
Ranch Hand

Joined: Jun 21, 2005
Posts: 162
Originally posted by Bear Bibeault:
[QB]

It's not special. All session data is vulnerable to threading issues. For read-only attributes it's usually not an issue.

This is bit scary, especially for session beginner like me. My application includes multiple JSP pages that are connected by servlet and action classes(struts). Eventually it is the result display page using what user has selected in the previous pages. quite common application..
So, basically for each page I have an action class and in it I use "getParamter()" sort thing to grab what user selects, and set it into session using session.setAttribute(). I understand this object is not thread safe, but since everytime user clicks "submit" button my Action class "reset" its value, so it should be fine. Am I right ?

Secondly, bear, you said I should take care of the connection within the request scope. Do you think I should explicitly do "connection.close()" after I am done with the query ? I am not sure about that because it seems I should just "return" the open connection to the pool without closing it so it can be reused. right ? But how to "return" an open connection to pool ?

Thanks lot.
Vince Hon
Ranch Hand

Joined: Feb 11, 2003
Posts: 117

javax.naming.InitialContext ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup(dbname);
con = ds.getConnection();

That's it. It seems it is very dummy code.


You should use the J2EE Service Locator Pattern, to write a class that encapsulate JNDI calling,
check at --> http://java.sun.com/blueprints/patterns/ServiceLocator.html


Vince Hon<br /> <br />SCJP 1.4 | SCWCD | SCBCD <br /><a href="http://vincehon.homeip.net:8000/VJW" target="_blank" rel="nofollow">http://vincehon.homeip.net:8000/VJW</a>
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61772
    
  67

Do you think I should explicitly do "connection.close()" after I am done with the query ? I


That depends upon how your connection pool works, which I cannot tell you.

Some pool implementations, like Tomcat-managed pooling, override the close() method on the connection they give you to mean release back to the pool -- the connection is not actually closed.

You'll need to find out what your pooling mechanism expects.
Jeroen Wenting
Ranch Hand

Joined: Oct 12, 2000
Posts: 5093
A little story as to why keeping database resources reserved for specific sessions is a bad idea.
This happened a few years ago at a major customer in the financial sector and involved a very large database server.
The database was Oracle 8i with several thousand licenses running on the largest available HP6000 series hardware, so an order of magnitude larger than your typical webapplication.

A new application for users accessing the database had been introduced. From that moment on the database server would suddenly start refusing requests every day at around 10AM.
People were baffled, the only thing that would cure the problem was for the server to be rebooted, it seemed completely locked up every day at about the same time (mind the database itself hadn't changed, only the applications accessing it).

As I'd seen (on a smaller scale) behaviour like that before I suggested checking the new application for improper release of database resources and indeed it was found that the application opened a new database connection for every request and failed to release that connection again after use.
This way the few dozen users of the new application could completely lock up (inadvertently) the database server in under an hour of use every morning. The application was removed from service to be repaired and the problems were immediately gone.

Keeping your database connection in your session has a similar effect.
If there are only a few simultaneous sessions (such as during testing) you'll never notice anything. But when the application starts to get used for real with people coming in and leaving without properly logging out (we have this even during testing despite hammering the point home to our testers every day that they MUST log out properly) connections will accumulate quickly.
Now a web application on a successful site may have several thousand active users (as in, non-expired sessions) at any time.
Unless your database server can handle that many open connections at once you'd better use a system in which connections are open for the shortest possible time (which means for the duration of an HTTP request or better yet only for the duration of the SQL query that is to be performed).
By using a connection pool you can save the cost of creating and disposing of database connections constantly while retaining a system in which servlets don't hold onto open connections needlessly.


42
Daniel Prene
Ranch Hand

Joined: Jul 15, 2005
Posts: 241
I found this thread extreamly helpfull! It just stoped me from incorperating a huge design flaw into my webapp... thank you!


All praise JavaRanch!
-D.P
Eddy Lee Sin Ti
Ranch Hand

Joined: Oct 06, 2005
Posts: 135
Inspiring indeed.


SCJP, SCWCD, SCJWS, IBM 700,IBM 701, IBM 704, IBM 705, CA Clarity Technical<br /> <br /><a href="http://eddyleesinti.blogspot.com" target="_blank" rel="nofollow">http://eddyleesinti.blogspot.com</a>
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: is it good to carry the jdbc "Connection" over servlet and Actions ?