aspose file tools*
The moose likes Servlets and the fly likes java.sql.SQLException: Closed Connection Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Servlets
Bookmark "java.sql.SQLException: Closed Connection" Watch "java.sql.SQLException: Closed Connection" New topic
Author

java.sql.SQLException: Closed Connection

Ben Souther
Sheriff

Joined: Dec 11, 2004
Posts: 13410

Originally posted by saikrishna cinux:
ya i am using class variable for creating the connection and i am creating the connection in service method

It appears that this is not the case:


Class variables or instance variables are declared outside of any method block. Your resultSets and statements are instance variables.

As David said, and as a rule of thumb, until you have a real firm grip on servlet threading, don't use any instance variables (don't declare variables outside of method blocks).

I would read Charles Lyon's (as well as all the other's) post very carefully and use those tips as a starting point toward cleaning up this code.
Once you've done that, your business and database logic should be a lot easier to debug.


Java API J2EE API Servlet Spec JSP Spec How to ask a question... Simple Servlet Examples jsonf
Charles Lyons
Author
Ranch Hand

Joined: Mar 27, 2003
Posts: 836
I would read Charles Lyon's (as well as all the other's) post very carefully and use those tips as a starting point toward cleaning up this code.
Once you've done that, your business and database logic should be a lot easier to debug.

Well, if it were me, I'd keep the old servlet source code and just start over again... put back only things you need to one at a time and make sure each step works completely (i.e. is completely thread-safe, doesn't deadlock etc.). A well-designed servlet (or class in general) will have very little implementation code to show - in fact, it'll be really simple to read through. All code should be broken down into blocks which are then commented so you (and everyone else) can see what's going on... Use methods sensibly to ensure you exploit code-reuse fully. In the event that you need to change that code, alterations to one method are much easier than having to change vast chunks of line-by-line code (and almost invariably missing some).

Think about the things your class is going to need to do before you start on its implementation - and then decide whether methods or other classes would best implement those goals. For example, you should only require (at most) one database connection per client, and you should make proper use of PreparedStatements, if you need them at all. A good design will save you hours (if not weeks) of debugging cluttered code, and by breaking down the build process, you'll be able to test the ideas one step at a time... you would then be able to come back to us with a minimal class and say "hey, why is my database connection failing here?" We'd then be able to look at 15 or so lines and say "well, on line ... you've done ..., change it to ... and that should work!". Rushing into a job code-first rarely pays off. Very few people here, who are all volunteers, are going to have the time to look through hundreds of lines of code; we're happy to help you understand small things, one step at a time, but I personally (and I suspect others) am not going to debug an entire build - especially as you appear to be working for a corporate body and I'm not being paid on contract .

We are trying to help, but you haven't exactly made it easy so far.

And as a general point to pick up from earlier, synchronising any service method (or any other lifecycle method handled by the container) is an extremely bad idea. Since Servlet 2.4 containers don't maintain pools of servlets, synchronisation prevents the container from executing multiple requests through the same service method simultaneously. This means clients will end up hanging waiting for a chance to have their request serviced... really bad idea, even in a small app. with low-volume traffic. Synchronisation on small blocks within the method would be okay (sometimes this is necessary when synchronising access to ServletContext or HttpSession for instance), but never on the entire method itself.

Oh, and if you don't believe me about the sychronisation effects, see the third paragraph of Servlet 2.4 spec., section SRV.2.3.3.1 "Multithreading Issues".
[ May 20, 2006: Message edited by: Charles Lyons ]

Charles Lyons (SCJP 1.4, April 2003; SCJP 5, Dec 2006; SCWCD 1.4b, April 2004)
Author of OCEJWCD Study Companion for Oracle Exam 1Z0-899 (ISBN 0955160340 / Amazon Amazon UK )
Vini Rai
Greenhorn

Joined: May 21, 2006
Posts: 2
Hi,

Why don't you create your connection in init() method;
saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
i am not sure abt creating connection in init method()


A = HARDWORK B = LUCK/FATE If C=(A+B) then C=SUCCESSFUL IN LIFE else C=FAILURE IN LIFE
SCJP 1.4
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

Originally posted by vini guru:

Why don't you create your connection in init() method;


Because it is a very bad idea, for a number of reasons. If you search this forum you'll find a number of threads saying the same thing: please don't create connections in the init() method.
Martin Simons
Ranch Hand

Joined: Mar 02, 2006
Posts: 196
Originally posted by dema rogatkin:
Dave,
I do not know who you are by occupation, but believe me I worked as a universtity professor for long time. So teaching is something different than you meet in your profession as a software engineer. You need to concentrate attention a student on something relevant to current topic and keep in shade other details which will be learned after. So, here is a perfect example, if he/she learns servlet, then thread safety can be other topic. On this stage of education adding synchronized is just perfect solution. Later in other courses students can learn how to get thread safety without possible compromisimg performance. Again, without sufficient information about requirements we can't recommend any thread safety solution. BTW If you are work near Oracle we can get lunch together and do more discussion.


I am sorry, but I have to chime in about this one, even if it late. I agree
that in a classroom setting you do things simply and then, in later lessons,
perform things in a more advanced method. But in this case, the simple
method should not be, sync doGet/post, but should rather be simply saying,
use no instance variables whatsoever, but rather create the items locally
and pass them from routine to routine where they are needed. This repairs
the problem simply (albeit with a lot more footwork, but not any more
complexity) and he still does not, at this point, truely need to understand
how the threaded server model works. This way, he has a solution which will
work in most situations (an only single connection db not included) rather
than one that will only work efficiently in very narrow circumstances. This
is, in this forum, very important, because there is no guarantee that he
will continue on to the "next lesson". Then, in the future, he is posting
a question as to why his new project is performing so poorly since he is
syncing doGet/Post "as he is suppossed to", since that will be the way he
was taught.
saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
Yes ,Sir u are perfectly right here i guess my problem is solved now.90%
now i 've completely eliminated instance variables i've made it local to the do get method and also making use of sessions for passing on the value from servlet - servlet
so the good thing is now i am getting the correct order of questions for multiple users (tested it with 5 cuncurrent users)

But, the only problem is i am unable to run the application with out synchronizing doget method :-(
do i need change anything in my MainServlet???

:roll:

anyway i am thankful to ur suggestion
Martin Simons
Ranch Hand

Joined: Mar 02, 2006
Posts: 196
The first thing I see is that session itself is also an instance variable.
If this is still the case, this should also be locally declared and passed.
It, unfortunately, does not help much to create the connection locally and
store it in the session, when the session itself is "global". Global is a
bad word, but when speaking of servlets it is a good analogy, as all
instance variables are essentially "global", at least from the perspective
of the servlet itself, meaning all users see the same one. So the problem
I see here, is that the session variable is getting redefined each time a
user accesses the page affecting all users. Don't worry that this means
that the user will get a new session each time the page is accessed. This
is not the case. The server (Tomcat) stores the session for that user, and
your variable is only using it (the getSession call retrieves the session
from the server).
saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
Originally posted by Martin Simons:
The first thing I see is that session itself is also an instance variable.
If this is still the case, this should also be locally declared and passed.
It, unfortunately, does not help much to create the connection locally and
store it in the session, when the session itself is "global". Global is a
bad word, but when speaking of servlets it is a good analogy, as all
instance variables are essentially "global", at least from the perspective
of the servlet itself, meaning all users see the same one. So the problem
I see here, is that the session variable is getting redefined each time a
user accesses the page affecting all users. Don't worry that this means
that the user will get a new session each time the page is accessed. This
is not the case. The server (Tomcat) stores the session for that user, and
your variable is only using it (the getSession call retrieves the session
from the server).


Mr martin i dont agree that we need to use class variables in webapplications, it creates problem to the user if the value is not made it to null
so i prefer to use local variables only

Martin Simons
Ranch Hand

Joined: Mar 02, 2006
Posts: 196
That is what I just said. Don't use class/instance variables.
Unfortunately, from the earlier posted code, an instance variable is
exactly what your session variable is.
saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
Yes, now i changed my entire application by replacing the instance variables with local and now i am free from all problems excpt only one exception
i.e unable to conevert internal representation
this problem is occuring when 2 or more users run the application at same point of time
i've followed all the ways' which u all told me to do but no chance there might something wrong with my code or webserver
Martin Simons
Ranch Hand

Joined: Mar 02, 2006
Posts: 196
Can we see the actual exception and stacktrace as well as the relevant
parts of the code?
nandkishor rao
Ranch Hand

Joined: May 24, 2006
Posts: 53
Well, I am not a very experienced guy to provide some good solutions.
But I think it's not good to make a connection inside doGet() or doPost().
Instead you make a helper class and put the database connection codes there
and make a helper class object in your servlet and call the methods.
saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
yes i am doing the same thing i am creating a connection in mainservlet which extends httpservlet and my servlet extends mainservlet and inherits all the methods from tht calss
nandkishor rao
Ranch Hand

Joined: May 24, 2006
Posts: 53
No, I am not saying that. I know your MainServlet extends HttpServlet and all the methods are inherited from that Servlet.
What i want to explain you is "Make a helper class like MyJdbc.java and write a method inside it and put all your database connection code there.Now inside doGeT() or doPost() method of your MainServlet,create
a object of MyJdbc say jdbc and you can now call the method of MyJdbc.java which is having that database connection code."
Maybe your problem gets solved because you can create as many objects as you need and everyone will have their own connections.
And when your are done with those objects make them as null so that they can be removed from memory.
saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
boss i am doing the same thing
just see my mainservlet class which i've posted earlier
i am extending the main servlet and creating an object for each connection
Nikhil Goel
Ranch Hand

Joined: Jul 11, 2003
Posts: 32
My 2 cents .
1)Making doGet()/doPost() methods synchronized is a bad idea and practice . As inherently all the servlets are multithreaded and there lifecycle is controlled by container . I fully agree with those members that have defended the container indicating that problem lies with the code .
But on the contrary you need synchronization please then go for SingleThreaded Model i.e you need to implement SingleThread Interface(pls check the name form Servlet API) . This will make all the call to your servlet as synchronized .
2)After looking into the code it looks like you need to be very clear about Servlet life cycle methods . What it means is that put all the getConnection() logic in init() method and put the reference of this Connection object in a static variable so that it can be reused again on other Servlet calls . Put the connection destruction logic in destroy() method . Also individual JDBC DML Statement/PreparedStatements can be closed and resultsets closed in individual methods finally block .

3) What DB are u using in ur app . Did u googled the error . Reason why if you are using Oracle then thay have indicated the BUG in there implementation of JDBC ... Here is the link http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/readme_10201.html . Go checkout yourself . After opening the link search for "java.sql.SQLException: Closed Connection java.sql.SQLException: Fail to convert to internal representation" you will find the information ..


Hope it helps .
saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
Originally posted by Nikhil Goel:
My 2 cents .
1)Making doGet()/doPost() methods synchronized is a bad idea and practice . As inherently all the servlets are multithreaded and there lifecycle is controlled by container . I fully agree with those members that have defended the container indicating that problem lies with the code .
But on the contrary you need synchronization please then go for SingleThreaded Model i.e you need to implement SingleThread Interface(pls check the name form Servlet API) . This will make all the call to your servlet as synchronized .
2)After looking into the code it looks like you need to be very clear about Servlet life cycle methods . What it means is that put all the getConnection() logic in init() method and put the reference of this Connection object in a static variable so that it can be reused again on other Servlet calls . Put the connection destruction logic in destroy() method . Also individual JDBC DML Statement/PreparedStatements can be closed and resultsets closed in individual methods finally block .

3) What DB are u using in ur app . Did u googled the error . Reason why if you are using Oracle then thay have indicated the BUG in there implementation of JDBC ... Here is the link http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/readme_10201.html . Go checkout yourself . After opening the link search for "java.sql.SQLException: Closed Connection java.sql.SQLException: Fail to convert to internal representation" you will find the information ..


Hope it helps .

Mr nikhil there is no difference between implementing hte SingleThreadModel interface and synchronizing the doget method
ya u r right that there is a problem in my code not with webserver but i am unable to find out the actual problem

s people say my application is not thread safe and some people suggest me to create the connection in init method and some suggest to create the connection the service method itself and some people say to destroy ,close the connection in finally and some people told me to do it destroy method
and i am sure that the problem is not with my oracle9i server
so sombody please give the best suggestion for my problems

Ben Souther
Sheriff

Joined: Dec 11, 2004
Posts: 13410

Originally posted by saikrishna cinux:

Mr nikhil there is no difference between implementing hte SingleThreadModel interface and synchronizing the doget method


Not true.

With SingleThreadModel containers often create pooled instances of a servlet.
This means multiple concurrent requests can be serviced without one needing to wait for the other to finish.

If you synchronize the doGet method of a servlet that doesn't implement SingleThreadModel, No requests can be handled by a servlet until the last one has finished.

The reason not to use SingleThreadModel is that is is deprecated.
nandkishor rao
Ranch Hand

Joined: May 24, 2006
Posts: 53
the problem is your are using servlets for databse access i know that you have writen a separate java file for db access but the problem is your java class extends HttpServlet.you know that container instantiates a single instance of servlet and creates threads of that by which user can access.
What you can do is don't extend that class from HttpServlet and now you create objects of that class which contains the variables used to access db.
Otherwise the another way is you can create another instance of that servlet which you are using for db access by add one more time the servlet name in web.xml.Try this...or i'll try to put the code.
saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
no the problem is not with databse connection..
the actual problem is with variable declaration..
this is maintenance project..
i have enhanced some funtionalities of this applcaition
so previous programmers declared all the variables as instance variables

so THIS IS THE ACTUAL PROBLEM>..
i have learnt a good lession..

so ALWASY DECLARE THE VARAIBLES AS LOCAL


so i just changed my code by declaring the variables as local
and i ran this application with 10 members ata the same point of time
and it ran perfectly..

so i have eliminated synchronized modifier from do get and dopost methods...
now my application is purely thread safe application..

so i am really thankful to all the members of javaranch and specially who have helped me suggested me to do this in a tough of war .

i am veryvery happy now...
THANX A LOT



regards
cinux
 
jQuery in Action, 2nd edition
 
subject: java.sql.SQLException: Closed Connection