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?
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.