tomcat dbcp 基於jndi配置時出現java.sql.SQLException: Already closed

標籤：style   blog   http   color   java   os   io   資料   

最近觀察生產環境發現一個現象，一段時間不操作，再重新操作時，資料庫連接第一次會出現：java.sql.SQLException: Already closed.，如下：

資料來源是用tomcat dbcp 基於jndi組態管理的，查了下，如下：

原因：

You‘re probably running into the fact that MYSQL closes connections  which have been open "too long". 
Probably if you make the request to the server again, you‘ll find that  it works because the connection is reopened. 

解決方案：

One way to deal with this is to add the following to your context.xml  Resource: 

validationQuery="select 1"  (基於mysql)

validationQuery="select 1 from dual"  (基於oracle)

代價：

This causes a very cheap test query to always be run first; if the  connection has been closed, this gets the failure, and 
then a new connection is opened.

細想下原來用weblogic jdbc jndi的配置有一個專門的配置項來配置這個test query，配置tomcat時粗心大意了 ：(