You probably can benefit from a connection pool. The "Communications link failure" together with long-lived JDBC connections makes me suspect the connection is broken after some time of not being used (idle).
A database connection pool like HikariCP does 2 things for you that can help:
- check a connection is valid before handing it out. If it is not valid, it is discarded and another one or a new connection that is valid is handed out. This is all done by the pool, your application does not have to take care of this.
- keep connections healthy by closing idle connections ("idleTimeout") and cycling long-lived connections ("maxLifetime"). The latter is especially useful when bad network components (firewalls) drop any connection that is open for longer than, let's say, 30 minutes.(*)
If all connections from the pool are used, a thread might have to wait ("connectionTimeout"). But if your pool has a proper maximum size ("maximumPoolSize") this will rarely be a long time. It does require your application to minimize the time it uses a connection: between getting a connection and closing it (which returns the connection to the pool), your application should mostly/only perform database actions. A side effect will be that you will need far less connections: where you use 60 now, you might find that you only need 6 in the pool. Some performance testing is needed to determine the proper "maximumPoolSize" for your application.
I suggest you try an "unplug" test with and without a connection pool. Run your application and give it something to do, unplug the network cable, than plug the network cable back in and see how long it takes your application to recover. In the pool-case, you should see your application functioning normally again as soon as the pool is able to create a new connection to the database.
(*) There is another reason for cycling connections: some queries may produce temporary data on the database server side and the database server may keep this around for as long as the connection is alive. This could result in an ever increasing memory usage by the database server. I have not seen this happen, but I know others have. A "maxLifetime" option is very useful in such a case.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…