File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Connection Problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Connection Problem" Watch "Connection Problem" New topic

Connection Problem

Mark Henryson
Ranch Hand

Joined: Jul 11, 2005
Posts: 200

I am developing an application in JSP/Servlet with Oracle as database. I used a connecion pool for getting the connections. But I frequently encountered the problem "The maximum no of connections exceeds" from the oracle server. Though I took great care in closing the connection using the try-catch-finally block, still I encounter the connection problem.

How to avoid this problem. I also want to look into module by module to see whether the connections are closing properly for each. Any idea/suggestions in this regard will be useful. I heard one sql query is there for finding out how many connections are there, whether it is closed properly or not. If someone knows that sql ,pls tell me.
Joel McNary

Joined: Aug 20, 2001
Posts: 1824


is a place to start.

Piscis Babelis est parvus, flavus, et hiridicus, et est probabiliter insolitissima raritas in toto mundo.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3753

What server are you using? It may be a middle-layer configuration problem. Off hand always closing the connection in a finally block is the best advice for resolving loose connections.

[OCA 8 Book] [Blog]
Mark Henryson
Ranch Hand

Joined: Jul 11, 2005
Posts: 200
I am using Jrun 4 Server. For database Oracle 9i Server. I am frequently encountering the connection provlem stating that "Exceeds max no of connections - 150". I have to restart the oracle server now then, to resolve the problem .I am closing the connections in the finally loop, however I am encountering the same problem.

I want to see more clearly in which part(since I am using Add, Delete, View and delete part for each module) connections are not closed properly.
I am opening the connection only once and taking the connection from the connection pool. And close it in the finally block.

I am using the same resultset and statment. Whenever I am executing the query using executeUpdate (or) executeQuery, I am put it inside the try and catch block to narrow down the error more specifically. I am using lot of queries. Whether it is adviseable to create the connection, resultset and statment newly or each query and close the connection, resultset and statement for that query then and there.

I don't know the performance tuning in this regard. I am also used like above I mentioned, but what happend If I am fncing more than 70,000 - 1,00,000 records, it is very slow and some time gives eror.

I want to know which is the best way to avoid the connection problems. Any sample coding / site which mention more about the perfomrance tuning for the beginners will be useful.
[ December 11, 2005: Message edited by: Mark Henryson ]
I agree. Here's the link:
subject: Connection Problem
It's not a secret anymore!