org.apache.tomcat.dbcp.dbcp.SQLNestedException: Impossible d'obtenir une connexion, piscine erreur de Délai d'attente pour objet inactif

Je suis en utilisant Tomcat 7, MySql Workbench 5.2.27, JSF 2.0 et cette exception vient du ManagedBean(TripTableBean.java) de ma page web(registre de Voyage.xhtml). Il vient à chaque fois que je clique pour aller dans un Voyage d'Enregistrement.xhtml après avoir navigué à travers mes autres pages web. Pardon pour mon 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)

La méthode où l'exception est originaire : (TripTableBean.java:389) points de 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();
}

Il semble que j'ai épuisé ma connexion =\ Mai je sais comment puis-je réduire mon utilisation de la connexion/augmenter la capacité de la connexion?

Mise à jour:

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

}

Mise à jour 2:
Cette exception se produit lorsque le champ d'application de mon ManagedBean(TripTableBean.java) est ViewScoped, il ne se produit pas lorsque je l'ai changer pour SessionScoped. Toutefois, s'il est SessionScoped, je vais avoir besoin de trouver un moyen de tuer la session et de recréer une nouvelle session chaque fois que je viens à cette page web, si pas de ma dataTable sur cette page ne charge pas les modifications mises à jour de la base de données.

InformationsquelleAutor Zany | 2012-01-28