我有一个生产中的webapp,它可以正常运行7-8天,然后突然我的网站宕机了,日志复制了这个错误:
java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object
所有对数据库的操作都开始失败。我已经阅读了其他几个问题和博客,但无法找到任何明确的解决方案。我正在使用连接池,我真的不确定问题出在哪里。
是因为我写的代码,还是因为池配置?我在这里提供的代码,其中一个方法,从数据库中获得数据,也池配置。请看一看,如果我做错了什么,请告诉我。
public CartItem getCustomerCartItem(int customerId, int productId, int productOfCityId) {
Connection con = ConnectionPool.getInstance().getConnection();
PreparedStatement st = null;
ResultSet rs = null;
CartItem ci = null;
CityProduct cityProduct = productService.getCityProduct(productId, productOfCityId);
if (cityProduct == null) {
return null;
}
String query = "SELECT ci.* FROM customer_cart_item_mapping cim INNER JOIN cart_item ci ON ci.Id = cim.CartItemId WHERE "
+ "cim.CustomerId = ? AND ci.ProductOfCityId = ?";
try {
st = con.prepareCall(query);
st.setInt(1, customerId);
st.setInt(2, productOfCityId);
rs = st.executeQuery();
if (rs.first()) {
ci = new CartItem();
ci.setId(rs.getInt("Id"));
ci.setCityProduct(cityProduct);
ci.setQuantity(rs.getInt("Quantity"));
ci.setCreatedDate(rs.getDate("CreatedDate"));
ci.setUpdatedDate(rs.getDate("UpdatedDate"));
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(CartItemDaoImpl.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
} finally {
DBUtil.close(con, st, rs);
}
return ci;
}
下面是用于关闭连接的DBUtil
类:
公共类DBUtil {
public static void close(Connection c, Statement s, ResultSet r) {
try {
if (r != null) {
r.close();
}
if (s != null) {
s.close();
}
if (c != null) {
ConnectionPool.getInstance().freeConnection(c);
}
} catch (SQLException ex) {
Logger.getLogger(DBUtil.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
下面是ConnectionPool
类
public class ConnectionPool {
private static ConnectionPool pool=null;
private static DataSource dataSource = null;
public synchronized static ConnectionPool getInstance(){
if (pool == null){
pool = new ConnectionPool();
}
return pool;
}
private ConnectionPool(){
try{
InitialContext ic = new InitialContext();
dataSource = (DataSource) ic.lookup("java:/comp/env/jdbc/prod_db");
}
catch(NamingException e){
System.out.println(e);
}
}
public Connection getConnection(){
try{
return dataSource.getConnection();
}
catch (SQLException sqle){
System.err.println(sqle);
return null;
}
}
public void freeConnection(Connection c){
try{
c.close();
}
catch (SQLException sqle){
System.err.println(sqle);
}
}
}
下面是连接池配置
<Context antiJARLocking="true" path="/">
<Resource auth="Container" connectionProperties="useEncoding=true;" driverClassName="com.mysql.jdbc.Driver" initialSize="2"
logAbandoned="true" maxIdle="40" maxTotal="70" maxWaitMillis="1000" minEvictableIdleTimeMillis="600000" minIdle="2" name="jdbc/winni_prime_db"
password="password" removeAbandoned="true" removeAbandonedTimeout="90" testWhileIdle="true" timeBetweenEvictionRunsMillis="90000"
type="javax.sql.DataSource" url="jdbc:mysql://localhost:3306/prod_db"
username="sqladmin" validationQuery="SELECT 1"/>
这个问题在过去的几个月里一直存在。唯一的解决方案就是每晚重新启动tomcat。请看一下这段代码,并给我一个专家的意见,我做错了。我正在使用MySQL
发布于 2015-05-12 18:36:20
您可以在此处进行lick:
if (cityProduct == null) {
return null;
}
您在此代码之前已获得连接,但您可以在不关闭连接的情况下退出函数。
https://stackoverflow.com/questions/30185444
复制相似问题