org.apache.tomcat.dbcp.dbcp.SQLNestedException：无法获取连接，池错误等待空闲对象超时

Question

我正在使用 Tomcat 7、MySql Workbench 5.2.27、JSF 2.0，此异常来自我的网页 (Trip Record.xhtml) 的 ManagedBean(TripTableBean.java)。每当我浏览其他网页后单击转到 Trip Record.xhtml 时，它就会出现。请原谅我可怕的代码...

TripTableBean.java

org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object
    at org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:114)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
    at Database.DBController.readRequest(DBController.java:21)
    at Database.TripTableBean.retrieve(TripTableBean.java:389)
    at Database.TripTableBean.<init>(TripTableBean.java:69)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at java.lang.Class.newInstance0(Unknown Source)
    at java.lang.Class.newInstance(Unknown Source)
    at com.sun.faces.mgbean.BeanBuilder.newBeanInstance(BeanBuilder.java:188)
    at com.sun.faces.mgbean.BeanBuilder.build(BeanBuilder.java:102)
    at com.sun.faces.mgbean.BeanManager.createAndPush(BeanManager.java:409)
    at com.sun.faces.mgbean.BeanManager.create(BeanManager.java:269)
    at com.sun.faces.el.ManagedBeanELResolver.resolveBean(ManagedBeanELResolver.java:244)
    at com.sun.faces.el.ManagedBeanELResolver.getValue(ManagedBeanELResolver.java:116)
    at com.sun.faces.el.DemuxCompositeELResolver._getValue(DemuxCompositeELResolver.java:176)
    at com.sun.faces.el.DemuxCompositeELResolver.getValue(DemuxCompositeELResolver.java:203)
    at org.apache.el.parser.AstIdentifier.getValue(AstIdentifier.java:71)
    at org.apache.el.parser.AstValue.getTarget(AstValue.java:94)
    at org.apache.el.parser.AstValue.getType(AstValue.java:82)
    at org.apache.el.ValueExpressionImpl.getType(ValueExpressionImpl.java:176)
    at com.sun.faces.facelets.el.TagValueExpression.getType(TagValueExpression.java:98)
    at org.primefaces.component.datatable.DataTable.isLazy(DataTable.java:904)
    at org.primefaces.component.datatable.DataTableRenderer.encodeMarkup(DataTableRenderer.java:177)
    at org.primefaces.component.datatable.DataTableRenderer.encodeEnd(DataTableRenderer.java:103)
    at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:875)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1763)
    at javax.faces.render.Renderer.encodeChildren(Renderer.java:168)
    at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:845)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1756)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1759)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1759)
    at com.sun.faces.application.view.FaceletViewHandlingStrategy.renderView(FaceletViewHandlingStrategy.java:401)
    at com.sun.faces.application.view.MultiViewHandler.renderView(MultiViewHandler.java:131)
    at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:121)
    at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
    at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:139)
    at javax.faces.webapp.FacesServlet.service(FacesServlet.java:594)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:929)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:405)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:964)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:515)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:304)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)

异常产生的方法：(TripTableBean.java:389) 指向 rs2 = db.readRequest(dbQuery2);

public void retrieve() throws SQLException, NamingException {
    t = new ArrayList<TripSearchy>();

    ResultSet rs = null;
    ResultSet rs2 = null;
    ResultSet rs3 = null;
    DBController db = new DBController(); 
    db.setUp();

    //SQL: change select statement here
    String dbQuery = "select * from (trip inner join agency on trip.id=agency.trip_id inner join tourguide on trip.id=tourguide.trip_id inner join accommodation on trip.id=accommodation.trip_id)";
    rs = db.readRequest(dbQuery);

    try{
        while(rs.next()){
            //add to list
            id = rs.getInt("trip.id");
            name = rs.getString("trip.name");
            startDate = rs.getString("trip.startDate");
            endDate = rs.getString("trip.endDate");
            costOfTrip = rs.getString("trip.costOfTrip");
            maxNoOfParticipants = rs.getString("trip.maxNoOfParticipants"); 
            closingDateOfApplication = rs.getString("trip.closingDateOfApplication");
            instructions = rs.getString("trip.instructions");
            psea = rs.getString("trip.psea");
            fasop = rs.getString("trip.fasop");
            ktpiop = rs.getString("trip.ktpiop");
            opId = rs.getInt("trip.overseasprogramme_id");
            overseasProgramme = rs.getString("trip.overseasProgrammeName");

            tourGuideName = rs.getString("tourguide.name");
            tourGuideContact = rs.getString("tourguide.contact");

            companyName = rs.getString("agency.companyName");
            agentName = rs.getString("agency.agentName");
            agentContact = rs.getString("agency.agentContact");
            airlineChoice = rs.getString("agency.airlineChoice");

            placeOfLodging = rs.getString("accommodation.placeOfLodging");
            startDateOfLodging = rs.getString("accommodation.startDate");
            endDateOfLodging = rs.getString("accommodation.endDate");

            String dbQuery2 = "Select * from tripstaff where trip_id = '" + id + "'";

            rs2 = db.readRequest(dbQuery2); 

            String lec;
            ArrayList<String> dbQueryM = new ArrayList<String>();

            while (rs2.next()){
                lec = rs2.getString("tripstaff.lecturer_id");
                dbQueryM.add("Select * from lecturer where id = '" + lec + "'");
            }

            ArrayList<NypStaff> nsf = new ArrayList<NypStaff>();

            for (int i = 0; i < dbQueryM.size(); i++){
                rs3 = db.readRequest(dbQueryM.get(i));

                if (rs3.next()){
                    NypStaff temp = new NypStaff();

                    //set values retrieved from database into attributes
                    temp.setName(rs3.getString("lecturer.name"));
                    temp.setEmail(rs3.getString("lecturer.email"));
                    temp.setContact(rs3.getString("lecturer.contact"));

                    nsf.add(temp);
                }
            }

            try {
                Calendar c = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Calendar c4 = Calendar.getInstance();
                Calendar c5 = Calendar.getInstance();

                try {
                    c.setTime(formatter.parse(startDate));
                    c2.setTime(formatter.parse(endDate));
                    c3.setTime(formatter.parse(startDateOfLodging));
                    c4.setTime(formatter.parse(endDateOfLodging));
                    c5.setTime(formatter.parse(closingDateOfApplication));
                } 
                catch (ParseException e1) {
                    e1.printStackTrace();
                }
                c.add(Calendar.DATE, 1);
                c2.add(Calendar.DATE, 1);
                c3.add(Calendar.DATE, 1);
                c4.add(Calendar.DATE, 1);
                c5.add(Calendar.DATE, 1);

                startDate = formatter.format(c.getTime());
                endDate = formatter.format(c2.getTime());
                startDateOfLodging = formatter.format(c3.getTime());
                endDateOfLodging = formatter.format(c4.getTime());
                closingDateOfApplication = formatter.format(c5.getTime());

                startDated = formatter.parse(startDate);
                endDated = formatter.parse(endDate);
                startDatedOfLodging = formatter.parse(startDateOfLodging);
                endDatedOfLodging = formatter.parse(endDateOfLodging);
                closingDatedOfApplication = formatter.parse(closingDateOfApplication);

                c.add(Calendar.DATE, -1);
                c2.add(Calendar.DATE, -1);
                c3.add(Calendar.DATE, -1);
                c4.add(Calendar.DATE, -1);
                c5.add(Calendar.DATE, -1);

                startDate = formatter.format(c.getTime());
                endDate = formatter.format(c2.getTime());
                startDateOfLodging = formatter.format(c3.getTime());
                endDateOfLodging = formatter.format(c4.getTime());
                closingDateOfApplication = formatter.format(c5.getTime());
            } 
            catch (ParseException e1) {
                e1.printStackTrace();
            }

            t.add(new TripSearchy (id, opId, overseasProgramme, name, startDated, startDate, endDated, endDate, costOfTrip, ns, nsf, staffName, tourGuideName, tourGuideContact, companyName, agentName, agentContact, airlineChoice, placeOfLodging, startDatedOfLodging, startDateOfLodging, endDatedOfLodging, endDateOfLodging, maxNoOfParticipants, closingDatedOfApplication, closingDateOfApplication, instructions, psea, fasop, ktpiop));
        }
    }catch (Exception e) {
        e.printStackTrace();
    }       
    db.terminate();
}

看来我已经耗尽了我的连接=\我可以知道如何减少连接的使用/增加连接的容量吗？

更新：

DBController.java

public class DBController {
private DataSource ds;
Connection con;

public void setUp() throws NamingException{
    //connect to database
    Context ctx = new InitialContext();
    ds = (DataSource)ctx.lookup("java:comp/env/jdbc/it2299");
}

public ResultSet readRequest(String dbQuery){
    ResultSet rs=null;
    try{
        con = ds.getConnection();
        Statement stmt = con.createStatement();
        rs = stmt.executeQuery(dbQuery);
    }
    catch(Exception e){e.printStackTrace();}
    return rs;
}

public int updateRequest(String dbQuery){
    int count=0;
    try{
        con = ds.getConnection();
        Statement stmt = con.createStatement();
        count=stmt.executeUpdate(dbQuery);
    }
    catch(Exception e){e.printStackTrace();}
    return count;
}

public void terminate(){
    try {con.close();}
    catch(Exception e){e.printStackTrace();}
}

}

更新2： 当我的 ManagedBean(TripTableBean.java) 的范围是 ViewScoped 时，会发生此异常，而当我将其更改为 SessionScoped 时，不会发生此异常。但是，如果它是 SessionScoped，我需要找到一种方法来终止会话并在每次访问此网页时重新创建新会话，否则此页面上的数据表不会从数据库加载更新的更改。

Answer 1

正如您暗示自己的那样，您的代码很糟糕。您需要确保在 try-finally 块中的最短可能范围内获取并关闭所有 JDBC 资源。重写您的代码，使其遵循以下标准 JDBC 习惯用法：

public List<Entity> list() throws SQLException {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    List<Entity> entities = new ArrayList<Entity>();

    try {
        connection = Database.getConnection();
        statement = connection.prepareStatement("SELECT id, foo, bar FROM entity");
        resultSet = statement.executeQuery();

        while (resultSet.next()) {
            Entity entity = new Entity();
            entity.setId(resultSet.getLong("id"));
            entity.setFoo(resultSet.getString("foo"));
            entity.setBar(resultSet.getString("bar"));
            entities.add(entity);
        }
    } finally {
        if (resultSet != null) try { resultSet.close(); } catch (SQLException ignore) {}
        if (statement != null) try { statement.close(); } catch (SQLException ignore) {}
        if (connection != null) try { connection.close(); } catch (SQLException ignore) {}
    }

    return entities;
}

另请参阅：

• 如果我使用单例类进行数据库连接，一个用户可以为所有人关闭连接吗？
• 避免连接池耗尽的 JDBC MySql 连接池实践

Answer 2

您的连接池设置是什么，您可以在 DBController 中发布什么内容吗？
提示：db.terminate() 应该位于finally{} 块中，可能是您在异常时丢失连接。

更新：
发布一些可能对您有帮助的修改，但为了维护目的请清理代码。查找已进行更改的评论。

public class DBController {
private DataSource ds;
private Connection con;// NEW CHANGE

public void setUp() throws NamingException{
    //connect to database
    Context ctx = new InitialContext();
    ds = (DataSource)ctx.lookup("java:comp/env/jdbc/it2299");
    con = ds.getConnection(); // NEW CHANGE
}

public ResultSet readRequest(String dbQuery){
    ResultSet rs=null;
    try{
        //REMOVED CODE FROM HERE
        Statement stmt = con.createStatement();
        rs = stmt.executeQuery(dbQuery);
    }
    catch(Exception e){e.printStackTrace();}
    return rs;
}

public int updateRequest(String dbQuery){
    int count=0;
    try{
        //REMOVED CODE FROM HERE
        Statement stmt = con.createStatement();
        count=stmt.executeUpdate(dbQuery);
    }
    catch(Exception e){e.printStackTrace();}
    return count;
}

public void terminate(){
    try {con.close();}
    catch(Exception e){e.printStackTrace();}
}
}

我无法对代码进行改进，但我建议查看一些网络上的最佳实践以及 @BalusC 的建议。
记住：完成后关闭连接对象。