在长时间没有访问tomcat后,数据库连接已经失效,连接池因为没有检测,所以认为连接有效,导致连接出错。避免这种错误的方法是设置连接有效查询sql语句。设置后,连接池会使用该sql语句测试连接是否有效,如果无效,会重新建立连接。
设置方法为:在server.xml中的<Resource>节点中增加属性validationQuery="select sysdate from dual;"。例如:
<Resource name="system" auth="Container" type="javax.sql.DataSource" driverClassName="net.sourceforge.jtds.jdbc.Driver" url="jdbc:jtds:sqlserver://127.0.0.1:1433/x5_1900"
username="sa" password="sa" maxActive="10" maxIdle="5"
validationQuery="select sysdate from dual;"/>
修正后启动tomcat报错:
com.justep.exception.BusinessException: 编码: JUSTEP150009; 提示: 业务服务器初始化出错
at com.justep.exception.BusinessException.create(Unknown Source)
at com.justep.business.server.BusinessServerServlet.init(Unknown Source)
at com.justep.x.bs.BusinessServerServlet.init(Unknown Source)
at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1173)
at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:993)
at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:4350)
at org.apache.catalina.core.StandardContext.start(StandardContext.java:4659)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
at org.apache.catalina.core.StandardHost.start(StandardHost.java:785)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:445)
at org.apache.catalina.core.StandardService.start(StandardService.java:519)
at org.apache.catalina.core.StandardServer.start(StandardServer.java:710)
at org.apache.catalina.startup.Catalina.start(Catalina.java:581)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:592)
at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:289)
at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:414)
Caused by: com.justep.exception.BusinessException: 编码: JUSTEP150210; 提示: 执行KSQL出错, KSQL: SELECT p FROM SA_OPPerson p where p.sValidState=1
at com.justep.exception.BusinessException.create(Unknown Source)
at com.justep.system.data.impl.TableUtilsImplement.ksqlSelect(Unknown Source)
at com.justep.system.data.impl.TableUtilsImplement.ksqlSelect(Unknown Source)
at com.justep.system.data.impl.TableUtilsImplement.ksqlSelect(Unknown Source)
at com.justep.system.data.KSQL.select(Unknown Source)
at com.justep.business.server.BusinessServer.init1(Native Method)
at com.justep.business.server.BusinessServer.init(Unknown Source)
... 19 more
Caused by: com.justep.exception.BusinessException: 编码: JUSTEP150055; 提示: 在模块"/system/data"下获取数据库连接出错
at com.justep.exception.BusinessException.create(Unknown Source)
at com.justep.system.data.Transaction.getConnection(Unknown Source)
at com.justep.system.data.Transaction.getConnection(Unknown Source)
at com.justep.model.ModelUtils.getConnection(Unknown Source)
... 25 more
Caused by: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Unknown column 'sysdate' in 'field list')
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
at com.justep.system.data.Transaction.getConnectionWithJndi(Unknown Source)
... 28 more
Caused by: java.sql.SQLException: Unknown column 'sysdate' in 'field list'
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2928)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2988)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2917)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:824)
at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory.validateConnection(PoolableConnectionFactory.java:658)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1558)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
... 31 more