Database connection exception handling with Spring

Question

I have a Spring application where the database connection is secondary to the operation of the application. I want the application to continue to operate normally regardless of being able to connect to the database.

I'm using Spring framework (jdbcTemplate) and HikariCP. When the application starts up with a functional database all is fine. If I stop the database after the application has already started I get uncaught connection exceptions (see stack trace below) that because they are unchecked, cause additional problems in the application. I want to catch those connection exceptions and handle them differently.

I've already tried using Spring's custom sql error code translator (SQLErrorCodeSQLExceptionTranslator) injected into the jdbcTemplate, but that class is not being called, I'm thinking because this is a socket/network exception not a sql exception.

Anyone have any ideas how I can easily catch the connection exception? I have many calls to jdbcTemplate across multiple DAO's, so putting a try/catch around each one is not the ideal answer. Is there some other way?

Jun 02, 2016 5:06:31 PM org.glassfish.grizzly.http.server.HttpHandler doHandle
WARNING: service exception
org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-0 - Connection is not available, request timed out after 1030ms.
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:390)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:470)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:480)
    at com.pevco.persist.xaction.XactionDaoImpl.getAllXactsBetweenDateTimes(XactionDaoImpl.java:1372)
    at com.pevco.nexgen.service.TransactionActivityService.getXactionContextJSONDataByDate(TransactionActivityService.java:206)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60)
    at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$TypeOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:185)
    at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75)
    at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302)
    at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
    at com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108)
    at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
    at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84)
    at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1511)
    at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1442)
    at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1391)
    at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1381)
    at com.sun.jersey.server.impl.container.grizzly2.GrizzlyContainer._service(GrizzlyContainer.java:215)
    at com.sun.jersey.server.impl.container.grizzly2.GrizzlyContainer.service(GrizzlyContainer.java:185)
    at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:164)
    at org.glassfish.grizzly.http.server.HttpHandlerChain.service(HttpHandlerChain.java:196)
    at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:164)
    at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:175)
    at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:265)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:200)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:134)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:112)
    at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:78)
    at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:815)
    at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:112)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:115)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:55)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:135)
    at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:567)
    at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:547)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLTransientConnectionException: HikariPool-0 - Connection is not available, request timed out after 1030ms.
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:195)
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:147)
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:83)
    at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)
    ... 41 more
Caused by: java.sql.SQLException: Login timed out.
    at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:433)
    at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:95)
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:314)
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:171)
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:436)
    at com.zaxxer.hikari.pool.HikariPool.access$500(HikariPool.java:65)
    at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:567)
    at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:560)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    ... 1 more
Caused by: java.net.SocketTimeoutException: connect timed out
    at java.net.DualStackPlainSocketImpl.waitForConnect(Native Method)
    at java.net.DualStackPlainSocketImpl.socketConnect(DualStackPlainSocketImpl.java:85)
    at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:345)
    at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
    at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
    at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:172)
    at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
    at java.net.Socket.connect(Socket.java:589)
    at net.sourceforge.jtds.jdbc.SharedSocket.createSocketForJDBC3(SharedSocket.java:288)
    at net.sourceforge.jtds.jdbc.SharedSocket.<init>(SharedSocket.java:251)
    at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:331)
    ... 12 more

Answer 1

Are you just looking to suppress the warnings getting logged or you want something else to be done ?

If its the first case just set the logging level to ERROR or FATAL ;) The exception has already occurred and the only thing that you can do at this point is to gracefully recover. Say if you want to shutdown your connection pool and reconnect after X minutes then you can try using the after throwing AOP approach - keep in mind that you will have to ensure thread safety while shutting down and restarting.

You can implement an "after throwing advice" around all methods that fall in com.pevco.persist package using something like this

@Pointcut("within(com.pevco.persist.Foo.*) || within(com.pevco.persist.Bar.*)")

Answer 2

One solution is to extend JdbcTemplate and handle the exceptions by overloading the methods and catching your exceptions.

Another solution is to use AOP, but I am not the expert; someone else could probably elaborate more.