org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object

exception jakarta-ee jsf-2 tomcat7 mysql-workbench

22,339

Solution 1

what are you connection pool settings can you post whats in DBController? Tip: db.terminate() should be in finally{} block, may be you are lossing connections on exceptions.

Update: Posting some of the modifications that might help you, but DO CLEAN UP THE CODE for maintenance sake. Look for comments where changes have been made.

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();}
}
}

Code cannot be improved beyond this by me, but I suggest have a look at some of the best practices over net and as suggested by @BalusC. Remember: To close the connection object when done.

Solution 2

As you hinted yourself, your code is horrible. You need to ensure that all JDBC resources are acquired and closed in the shortest possibe scope in a try-finally block. Rewrite your code so that it follows the following standard JDBC idiom:

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;
}

Mysophobe

Hello, I'm from Singapore!

Updated on August 28, 2020

Comments

Mysophobe almost 4 years

I'm using Tomcat 7, MySql Workbench 5.2.27, JSF 2.0 and this exception comes from the ManagedBean(TripTableBean.java) of my web page(Trip Record.xhtml). It comes up whenever I click to go to Trip Record.xhtml after navigating through my other web pages. Pardon my horrible codes...

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)

The method where the exception originates : (TripTableBean.java:389) points to 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();
}

It seems that I have exhausted my connection =\ May I know how do I reduce my usage of the connection/increase the capacity of the connection?

Update:

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();}
}

}

Update 2: This exception occurs when the scope of my ManagedBean(TripTableBean.java) is ViewScoped, it doesn't occur when I change it to SessionScoped. However if it is SessionScoped, I'll need to find a way to kill session and recreate a new session whenever I come to this web page if not my dataTable on this page won't load updated changes from the database.

Mysophobe over 12 years

Thanks for helping, I'll go try adding finally{} block. I've added my DBController in my post.
Mysophobe over 12 years

Update: The exception still occurs. I tried alternating opening this web page with another web page, the exception came when I clicked to go to this web page on the 6th time.
baba.kabira over 12 years

finally{} I meant through out app not just above code :). Also I notice your readRequest() method opens a new connection and its used inside a loop, you can use same connection to create statements, that way it might help in transactions too may you need some where in code.
Mysophobe over 12 years

Hmm use same connection? You mean using the same ResultSet for all the dbQuery? or creating a new DBController for each ResultSet? I'm lost =\
Mysophobe over 12 years

Thanks for your help too, I'm currently trying to follow the above format. Erm may I know what is in that Database class? (Line 7) It isn't available for me to import =\ And how do I go about executing a while-loop 2nd query while inside a still-executing while-loop 1st query?
baba.kabira over 12 years

see code above posted by @BalusC and even yours, I meant when you create statement object you can use same connection object like stm1 = con.createStatement(qry1) and again stm2 = con.createStatement(qry2), where con is same instance not new connection object taken from pool via call ds.getConnection()
BalusC over 12 years

As to the database class, check the "see also" links for examples. It should just basically create and return the connection (do not hold the connection as instance variable of that class!). As to the 2nd query, learn SQL JOIN clause so that you end up with only 1 query which returns exactly the data you need.
Mysophobe over 12 years

Omg I just saw the update in your post and went to make the changes. It worked!!! Thank you gbagga and BalusC for your help! Really appreciate it =D
BalusC over 12 years

This approach is still bad. You need to rewrite much more code.