wood burning stoves 2.0*
The moose likes JDBC and the fly likes JDBC DBCP connection time out Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC DBCP connection time out" Watch "JDBC DBCP connection time out" New topic
Author

JDBC DBCP connection time out

Nemo Cavianini
Greenhorn

Joined: Sep 17, 2009
Posts: 6
Hello all,

I am having problems when using DBCP to connect to my database. The problem is that the connections in the pool get expired after my program stays stale for about 9 or 10 hours. The next time I try to connect I received the error on connection timeout.

I know, in case of DBCP, it is possible to validate the connections taken out of the pool on the time of borrowing from or storing to the pool. I have set testOnBorrow equal to true for my connection pool and also the validationQuery is set to "SELECT 1 FROM mytable" where mytable is the name of a valid table in my DB. Nonetheless, still the problem seems to be there and the connections get time out after a certain period of time.

I have also read about autoReconnectForPools which is supposed to do a reconnection to the DB when the connections in the pool are timed out. yet, setting it to true had no effect either.

It is worth mentioning that, usually after two or three trials to connect to the server a fresh copy of a connection object gets returned from the pool and I am able to query the database from then on. Leaving the system stale for a while would however make the problem occur again.

I have read that C3P0 is probably a better way of implementing connection pools, nonetheless I would like to get this problem solved with DBCP first before deciding on whether or not to switch to C3P0.

Could anybody suggest any method to test whether the changes that I am making to the pool really have some effects on it or not? Basically, how can I check if the validation really happens? also, does anyone have any suggestion on a potential solution to this?

thanks,
Nemo
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

also the validationQuery is set to "SELECT 1 FROM mytable"
This should do the trick.

Yes, it is possible that your connections become invalid when they are not active, but it is the purpose of validationQuery to detect and correct this.
Have you checked that you return all connections to the pool as soon as you have finished using them? That is a prerequisite for this mechanism.
If you keep some connections local in stead of returning them, the validationQuery mechanism will not help you.



OCUP UML fundamental and ITIL foundation
youtube channel
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC DBCP connection time out
 
Similar Threads
Standalone Connection pooling with DBCP
Connection pool - Own implementation / DBCP / c3p0
commons DBCP problem
Very slow to hook up to MySQL db
~~ Newbie question - database ~~