Solved

DB Pool Connection Issue

Posted on 2006-11-09
8
214 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Suggested Solutions

This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
Suggested Courses

739 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