java.sql.SQLRecoverableException-从jdbc重新连接 [英] java.sql.SQLRecoverableException - reconnect from jdbc

问题描述

在使用Apache JDBC连接性连接到数据库的应用程序中，我偶尔会遇到"java.sql.SQLRecoverableException".搜索根本原因后，我能够了解到它可能是由于从oracle服务器端终止连接而引起的.我正在使用oracle 11G版本.

In my application which uses Apache JDBC connectivity to connect to a database, I am occasionally getting "java.sql.SQLRecoverableException". After searching on the root cause, I was able to learn it might be caused due the termination of connection from the oracle server end. I am using oracle 11G version.

现在，我的要求是，在执行任何查询之前，我必须确保连接仍然有效并且没有终止.如果连接从oracle end终止，我需要建立一个新的连接.

Now my requirement is that I have to make sure the connection is still valid and not terminated before executing any query. I need to establish a new connection if the connection was terminated from oracle end.

我不确定如何实现或测试它.我尝试从以下查询获取会话:

I am not sure how I can achieve this, or to test it. I have tried getting the session from below query:

select * from v$session where username is not null;

当我打开SQL Developer并且同时打开了应用程序(使用相同用户凭据的2个应用程序)时，它显示一个结果.我想知道如何终止与SQL的连接并重现"SQLRecoverableException"以进行测试，并在出现问题后也重新连接到数据库.

It displays a single result when I have SQL Developer open and the applications (2 applications using same user credentials) are open too. I want to know how to terminate the connection from SQL and reproduce the "SQLRecoverableException" for testing and also reconnect to the database after the issue occurs.

由于我是数据库连接的新手，所以我不确定该怎么做或要研究什么才能实现此目的.请帮我解决这个问题.

Since I am new to database connections, I am not sure what to do or what to look into in order to achieve this. Kindly help me out with this.

spring-servlet.xml中的JDBC连接如下:

My JDBC connectivity in spring-servlet.xml is given below:

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource" />
</bean>

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close">
    <property name="driverClassName" value="${myjdbc.driverClassName}" />
    <property name="url" value="${myjdbc.url}" />
    <property name="username" value="${myjdbc.username}" />
    <property name="password" value="${myjdbc.password}" />
</bean>
<context:property-placeholder location="classpath:myjdbc.properties" />

推荐答案

似乎某些防火墙或其他活动正在断开连接. 我们遇到了类似的问题，即数据库终止了空闲30分钟的连接.

It seems the connection is being dropped by some firewall or other activity. We have faced similar issue where the database was terminating the connections which were idle for 30 mins.

要解决此问题，我们通过指定以下属性来调整数据库池

To Overcome the issue we have tuned database pool by specifying the following properties

testOnBorrow:-Setting it true will force the pooling provider to run the validation query while handing out the connection to the application.
testWhileIdle:-Setting it true will enable the validation when the connection is sitting idle in the pool.
timeBetweenEvictionRunsMillis:- Setting this property to non-zero will allow the evictor thread to run,which will test the idle connections.

重现该问题将需要终止数据库侧的连接.我们已经使用mssql执行了一个小型测试，其中我们可以使用服务器工具终止连接，并且池重新建立了连接.

Reproducing the issue will require to kill the connection on the database side.We have performed a small test using mssql wherein we can terminate the connection using the Server Tools and the pool was establishing the connection again.

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close">
    <property name="driverClassName" value="${myjdbc.driverClassName}" />
    <property name="url" value="${myjdbc.url}" />
    <property name="username" value="${myjdbc.username}" />
    <property name="password" value="${myjdbc.password}" />
    <property name="testOnBorrow" value="true" />
    <property name="testWhileIdle" value="true" />
    <property name="timeBetweenEvictionRunsMillis" value="3000" />
</bean>

请注意timeBetweenEvictionRunsMillis的单位为Millisonds.

Note the timeBetweenEvictionRunsMillis is in millisonds.

以上配置将检查无效连接，如果数据库或防火墙突然将其关闭，则会将其从池中删除.

The above configuration will checks the invalid connection and drops them from the pool if they are abruptly closed by the database or the firewall.

这篇关于java.sql.SQLRecoverableException-从jdbc重新连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！