title: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object
body: <p>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...</p>
  
  <p><strong>TripTableBean.java</strong></p>
  
  <pre><code>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.&lt;init&gt;(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)
  </code></pre>
  
  <p><strong>The method where the exception originates</strong> : (TripTableBean.java:389) points to rs2 = db.readRequest(dbQuery2);</p>
  
  <pre><code>public void retrieve() throws SQLException, NamingException {
      t = new ArrayList&lt;TripSearchy&gt;();
  
      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&lt;String&gt; dbQueryM = new ArrayList&lt;String&gt;();
  
              while (rs2.next()){
                  lec = rs2.getString("tripstaff.lecturer_id");
                  dbQueryM.add("Select * from lecturer where id = '" + lec + "'");
              }
  
              ArrayList&lt;NypStaff&gt; nsf = new ArrayList&lt;NypStaff&gt;();
  
              for (int i = 0; i &lt; 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();
  }
  </code></pre>
  
  <p>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?</p>
  
  <p><strong>Update:</strong></p>
  
  <p><strong>DBController.java</strong></p>
  
  <pre><code>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();}
  }
  </code></pre>
  
  <p>}</p>
  
  <p><strong>Update 2:</strong>
  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.</p>