JAVA线程循环取数据库数据假死
服务运行环境linux+tomcat+oracle数据库,oracle数据库与tomcat服务不在同一台机器上
1.查询数据线程代码,在创建数据线程时注入IJdbcDao对象拿到JdbcTemplate做查询操作
public class IdentifyAutoGrowth implements Runnable {
private static KedaLogger log = KedaLogger.getLogger(IdentifyAutoGrowth.class);
private IJdbcDao interfaceJdbcDao;
public IdentifyAutoGrowth(IJdbcDao interfaceJdbcDao) {
this.interfaceJdbcDao = interfaceJdbcDao;
}
public void run() {
while (true) {
String querySql="select * from biao a where a.pointId=001";
try {
log.error("【4组装查询SQL语句");
List throughSource=this.interfaceJdbcDao.getTemplate().queryForList(this.querySql, args);
log.error("【5查出数据大小:" + throughSource.size());
} catch (Exception ex) {
ex.printStackTrace();
}
try {
Thread.sleep(5000);
} catch (InterruptedException e) {
log.error("异常信息...", e);
}
}
}
}
2.spring框架数据源配置
<bean id="DataSourceAccessHeb"
class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
<property name="url" value="jdbc:oracle:thin:@172.16.239.138:1521:orcl" />
<property name="username" value="1.5.2" />
<property name="password" value="1.5.2" />
<property name="maxActive" value="100" />
<property name="maxIdle" value="30" />
<property name="maxWait" value="1000" />
<property name="defaultAutoCommit" value="true" />
<property name="removeAbandoned" value="true" />
<property name="removeAbandonedTimeout" value="60" />
<property name="testWhileIdle" value="true"/>
<property name="testOnBorrow" value="false"/>
<property name="testOnReturn" value="false"/>
<property name="validationQuery" value="select sysdate from dual"/>
<property name="timeBetweenEvictionRunsMillis" value="180000"/>
<property name="numTestsPerEvictionRun" value="20"/>
</bean>
3.线程在运行的过程中会出现有频率的(大概几分钟)出现异常,如下:
Io exception: Connection reset; nested exception is java.sql.SQLRecoverableException: Io exception: Connection reset
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:236)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:607)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:641)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:670)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:678)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:710)
at org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:776)
at com.kedacom.kdkk.service.autogrowth.IdentifyAutoGrowth.run(IdentifyAutoGrowth.java:255)
at java.lang.Thread.run(Thread.java:662)
Caused by: java.sql.SQLRecoverableException: Io exception: Connection reset
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:101)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:521)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:802)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:866)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3431)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:648)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:591)
... 7 more
Caused by: java.net.SocketException: Connection reset
at java.net.SocketInputStream.read(SocketInputStream.java:168)
at oracle.net.ns.Packet.receive(Packet.java:240)
at oracle.net.ns.DataPacket.receive(DataPacket.java:92)
at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:172)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:117)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:92)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:77)
at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1034)
at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1010)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:588)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:791)
... 15 more
但是在长时间运行后会一直阻塞在查询数据库这里如:
List throughSource=this.interfaceJdbcDao.getTemplate().queryForList(this.querySql, args);
导致线程不能继续执行取数据操作,但这时线程也没有抛出任何异常。求各位解释下...
疑问点:
(1)queryForList 是阻塞的? 出现异常
(2)DBCP数据源在出现网络不稳定情况时不能感知异常并抛出会一直卡着?
(3)在spring数据源配置时已经配置了超时时间,怎么没有体现出来?
(4)如果是拿不到数据库池的连接对象,但是用PL/SQL连接数据库确能正常查询