How to get Java exception thrown when connection closed and using Tomcat?

Question

I'm trying to get an exception thrown when the DB connection is closed so that a POST request can immediately return if the connection is closed. Right now Tomcat just keeps it open and gathering log data for around 25 minutes.

The way I'm testing this is with the following SQL code from Java:

WAITFOR DELAY '00:00:20';SELECT TOP(1) * FROM USER;"

I'm using the following properties in the Tomcat config:

abandonWhenPercentageFull = 0 //Close connections as soon as timed out.
removeAbandonedTimeout = 10 //Abandon connections after 10s.
removeAbandoned = true

What I'm trying to achieve is to get my Java code to throw an exception after 10s so that a closed or reset connection doesn't tie up the connection too long.

I have tried using QueryTimeoutInterceptor and also it's equivalent from Java, but that didn't seem to help. I'm using the latest SQL Server driver.

Answer 1

well if you are using connection pool(which you should), it won't close for a while after it returns. My suggestion is to either beak apart the data return or process prior to insert into the database/cache