一开始以为是数据库连接池配置的问题。一开始配置
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="jdbcUrl">jdbc:oracle:thin:@wiseuat.wisers.com:1521:uatdb</property>
<property name="driverClass">oracle.jdbc.driver.OracleDriver</property>
<property name="user">wisers</property>
<property name="password">uatuat</property>
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">20</property>
<property name="minPoolSize">10</property>
<property name="maxPoolSize">30</property>
</default-config>
</c3p0-config>
后来添加了
<!--最大空闲时间,1800秒内未使用则连接被丢弃。若为0则永不丢弃。Default: 0 -->
<property name="maxIdleTime">1800</property>
<!--每60秒检查所有连接池中的空闲连接。Default: 0 -->
<property name="idleConnectionTestPeriod">60</property>
<!--定义在从数据库获取新连接失败后重复尝试的次数。Default: 30 -->
<property name="acquireRetryAttempts">20</property>
但是
最后
发现
并不是这个问题。
错误是:
public QueryRunner qr = new QueryRunner(getDataSource());
写在了getUserPref()和getKeyList()方法里面了。每次都会获得新的连接,连接资源不够了
真正的原因是:要把QueryRunner写在数据库方法外面。下面这种方式就对了。
@Repository
public class UserPrefDaoImpl implements UserPrefDao {
public static ComboPooledDataSource getDataSource() {
ComboPooledDataSource ds = new ComboPooledDataSource("c3p0-config.xml");
return ds;
}
public QueryRunner qr = new QueryRunner(getDataSource());
@Override
public UserPref getUserPref(String uid,String key) {
UserPref user=null;
try {
String sql = "select * from USER_PREF u where u.USERID=? AND u.KEY= ? ";
user=qr.query(sql,new BeanHandler<UserPref>(UserPref.class),uid,key);
} catch (Exception e) {
e.printStackTrace();
}
return user;
}
@Override
public List<String> getKeyList(String uid) {
List<String> keyList=null;
try {
String sql = "select u.key from USER_PREF u where u.USERID=? ";
keyList= (List<String>) qr.query(sql,new ColumnListHandler("KEY"),uid);
} catch (Exception e) {
e.printStackTrace();
}
return keyList;
}
}