Solved

DB Pool Connection Issue

Posted on 2006-11-09
8
215 Views
Last Modified: 2010-03-31
hi experts,

I'm using
 
DB Class
..
static public Connection getConnection() ..{
        try {
            Context env = (Context) new InitialContext().lookup("java:comp/env");
            DataSource ds = (DataSource) env.lookup("aacc");
            return  ds.getConnection();
        }
catch {}
}

In my JSPs:
...
try {
Connection conn = DBClass.getConnection();
...
}
catch { ..}
finally {
                if (conn != null) try { conn.close(); }
            }

But still I see soo many connections opened in DB tool. Technically all connection will be closed. I was told still they r dozens of connections opened ? Any Idea why ? I double checked all JSPs all has closed connection in finally block.

thx,
PH
0
Comment
Question by:princehyderabad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 9

Accepted Solution

by:
owenli27 earned 75 total points
ID: 17909287
when using dataSource, getConnection() gets an already open connection from the pool, and close() simply releases the connection object back to the pool.
0
 
LVL 9

Expert Comment

by:owenli27
ID: 17909450
This page explains the connection difference between DataSource (JDBC 2.0) and DriverManager (JDBC 1.0) using figures in detail.

http://www.javaworld.com/javaworld/jw-10-2000/jw-1027-pool.html
0
 
LVL 23

Expert Comment

by:Ajay-Singh
ID: 17909461
> But still I see soo many connections opened in DB tool. Technically
> all connection will be closed. I was told still they r dozens of
> connections opened ? Any Idea why ? I double checked all JSPs all has
> closed connection in finally block.

because connection pool is used. The connection you get from datasource is wrapper over actual connection. When this connection is closed - the connection is returned back to pool.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:princehyderabad
ID: 17909713
So does it means its okay to have dozens of connection opened. It will not caused any DB delay or so..... as I hv used connection close in all of my JSPs.
0
 
LVL 9

Expert Comment

by:owenli27
ID: 17909970
Yes,  at startup, A pool manager creates the connection objects upfront and optimizes resource usage by dynamically altering the pool size. The benefit to use pool connection is connect to DB without causing overheads on the database resource manager. The connections aren't closed until your application is closed.
0
 

Author Comment

by:princehyderabad
ID: 17910236
thx Owenli27 !

But still why is that even after all my JSPs have closed connection stmt, still it shows dozens of connection opened ?
0
 
LVL 23

Assisted Solution

by:Ajay-Singh
Ajay-Singh earned 50 total points
ID: 17911174
> But still why is that even after all my JSPs have closed connection
> stmt, still it shows dozens of connection opened ?

because pool never closes the connection. Is no of open connections growing beyound the configured limit of pool
0
 
LVL 9

Expert Comment

by:owenli27
ID: 17911375
When you are directly creating a connection by calling Drivermanager.getConnection(..) , you are creating a connection by yourself and when you call close() on it, the link to database is lost.

But when you get a connection from a Datasource, when you call the close() on it, it will NOT close the link to database, it only return to a connection pool where it can be reused by some other classes.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question