2 nicholaskg NicholasKg 于 2016.01.24 10:46 提问

在界面上连续按F5刷新Tomcat报错,显示连接池已满

错误信息:org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object
Tomcat与数据库使用连接池,封装在一个类里面:
public class BaseDAO {

private Connection conn = null;
private ResultSet rs = null;
private Statement stmt = null;
private PreparedStatement pstmt = null;


//1)、获取数据库连接对象
private Connection getConnection(){
        try {
            Context context = new InitialContext();
            DataSource dataSource = (DataSource)context.lookup("java:comp/env/jdbc/sms");
            conn = dataSource.getConnection();
        } catch (NamingException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return this.conn;
}


//2)、获得结果集
public ResultSet executeQuery(String sql){
    try {
        conn = getConnection();
        stmt = conn.createStatement();
        rs = stmt.executeQuery(sql);
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return rs;
}

/*
 * 执行一个查询操作,在这个查询中,对象数组中的每个元素值被用来作为查询语句的置换参数
 */
public ResultSet executeQuery(String sql,Object ... paramValues){
    try {
        conn = getConnection();
        pstmt = conn.prepareStatement(sql);
        if(paramValues != null){
            for (int i = 0; i < paramValues.length; i++) {
                 pstmt.setObject((i+1), paramValues[i]);
            }
        }
        rs = pstmt.executeQuery();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return rs;
}


//3)、用来执行一个不需要置换参数的更新(插入、更新或删除)操作。
public int executeUpdate(String sql){
    int affectedRows = 0;
    try {
        conn = getConnection();
        stmt  = conn.createStatement();
        affectedRows = stmt.executeUpdate(sql);
    } catch (Exception e) {
        e.printStackTrace();
    }finally{
        closeAll(null,stmt,null,conn);
    }
    return affectedRows;
}

//用来执行一个需要置换参数的更新(插入、更新或删除)操作。
public int executeUpdate(String sql,Object ... paramValues){
    int affectedRows = 0;
    try {
        conn = getConnection();
        pstmt = conn.prepareStatement(sql);
        if(paramValues != null){
            for (int i = 0; i < paramValues.length; i++) {
                 pstmt.setObject((i+1), paramValues[i]);
            }
        }
        affectedRows = pstmt.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    }finally{
        closeAll(null,null,pstmt,conn);
    }
    return affectedRows;

}

//(5)通用查询工具方法
public List<Map<Object,Object>> query(String sql,Object ...paramValues){
    List<Map<Object,Object>> list = new ArrayList<Map<Object,Object>>();
    try {
        conn = getConnection();
        pstmt = conn.prepareStatement(sql);
        if(paramValues != null){
            for (int i = 0; i < paramValues.length; i++) {
                 pstmt.setObject((i+1), paramValues[i]);
            }
        }
        rs = pstmt.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData(); //获取元数据

        while(rs.next()){
            Map<Object,Object> map = new HashMap<Object,Object>();
            for (int j = 1; j <= rsmd.getColumnCount(); j++) {
                //获取列名
                String columnLabel = rsmd.getColumnLabel(j);
                //根据列名从结果集中获取对应的列值
                Object columnValue = rs.getObject(columnLabel);
                map.put(columnLabel, columnValue);
            } //end for
            list.add(map);
        }//end while
    } catch (SQLException e) {
        e.printStackTrace();
    }finally{
        closeAll(rs,null,pstmt,conn);
    }
    return list;
}

//4)、释放资源
public void closeAll(ResultSet rs,Statement stmt,PreparedStatement pstmt,Connection conn){
    if(rs!=null){
        try{
            //关闭存储查询结果的ResultSet对象
            rs.close();
        }catch (Exception e) {
            e.printStackTrace();
        }
        rs = null;
    }
    if(stmt!=null){
        try{
            //关闭负责执行SQL命令的Statement对象
            stmt.close();
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    if(pstmt!=null){
        try{
            //关闭负责执行SQL命令的PreparedStatement对象
            pstmt.close();
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    if(conn!=null){
        try{
            //将Connection连接对象还给数据库连接池
            conn.close();
        }catch (Exception e) {
            e.printStackTrace();
        }
    }
}

}

执行查询时ResultSet的实例rs在调用后就关闭了,但是无法关闭Connection的实例conn 和Statement的实例stmt

1个回答

rui888
rui888   Ds   Rxr 2016.01.24 15:10
Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!