We help IT Professionals succeed at work.

Connection Pool vs localized Connection use...

bganoush
bganoush asked
on
403 Views
Last Modified: 2010-03-31

Hello World!

Recently I was asked to change my code from a single-use connection based model to a connection pool model for all database use.

The only reason for this is that my code would litter the netstat full of TIME_WAIT connections that seem to multiply like rabbits in spring...

So my solution was to implement a connection pool. That exercise took the better part of a morning and now the TIME_WAIT issue is gone although I may be faced with another issue. I was wondering what would happen once my connections started to get aborted from the default timeouts for connections... Well I think I fixed that by looking at the "isClosed()" method of the Connection class and then resetting the connection member of my connection pool set.

I'm sure there are other caveats that I'll find along the way but for now I just hope it will work...

The reason I posted here is to ask your opinion...  In the "Java World" is there a standard or what is your opinion as to what method is best to use?  I know so many people who say that you should create and close a connection right away but then in the case of my application, that leaves you with TIME_WAIT connections.  And then there is pooling... and  everything that goes with that.

By the way, the main reason that I was accumulating so many TIME_WAITs orginally was because I have a queue in one of my tables that I need to poll  every few milliseconds for incoming data so I open a connection, check the queue with a select statement and then close the connection... repeat...

-- Bubba
Comment
Watch Question

Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
why not use an existing pool implementation such as dbcp
Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
> so I open a connection, check the queue with a select statement and then close the connection... repeat...

if thats the case why do you close the connection at al;l and instead reuse ther same connectoion

Author

Commented:

>> why not use an existing pool implementation such as dbcp

That's not what this question is about...  I'm not asking to compare between a new implementation versus an existing version... I'm asking to compare using a connection pool versus not using a connection pool. In your case, just re-read the question and insert "dbcp" every place I wrote "implement my own connection pool"...

>> if thats the case why do you close the connection at al;l and instead reuse ther same connectoion

That's the whole issue...  much of the sample code I found on the web (codeproject, and E.E.) will create a "Database" object that the caller creates, gets data from and then destroys. So when you get the data, the database connections are created, used then then closed...  That's ok when you only access the database once for every user interaction but in the case of the example of a queue sniffer, that doesn't make much sense and keeping the connection open is the right approach but even then, is it best to use a Connection Pool class to manage the connections and if so, what are the drawbacks or should the class simply be implemented with a static variable that holds a connection open no matter how many instances of that class are created?

-- Bubba
Java Developer
CERTIFIED EXPERT
Top Expert 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Mayank SPrincipal Technologist
CERTIFIED EXPERT

Commented:
>> I'm asking to compare using a connection pool versus not using a connection pool

Of course, a connection-pool approach would be better. You should use one of the existing data-sources/ drivers which manage that for you instead of managing it in your own code (though that is possible too).

As an example, look at:

http://www.orafaq.com/articles/archives/000047.htm

http://cocoon.apache.org/1.x/connection-pool.html
Mayank SPrincipal Technologist
CERTIFIED EXPERT

Commented:
Somehow, that 'recommendation' was not followed ;-)

Author

Commented:
Oops
Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
looking at it, I'm not sure why my comments were not in the recomendation :)
Mayank SPrincipal Technologist
CERTIFIED EXPERT

Commented:
Yes, I would go for a split.
Mayank SPrincipal Technologist
CERTIFIED EXPERT

Commented:
>> Oops

:)
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.