Solved

DB Pool Connection Issue

Posted on 2006-11-09
8
209 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
  • 4
  • 2
  • 2
8 Comments
 
LVL 9

Accepted Solution

by:
owenli27 earned 75 total points
Comment Utility
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
Comment Utility
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
Comment Utility
> 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
 

Author Comment

by:princehyderabad
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 9

Expert Comment

by:owenli27
Comment Utility
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
Comment Utility
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
Comment Utility
> 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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
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 …

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now