org.apache.tomcat.dbcp.dbcp.SQLNestedException：无法获取连接，池错误超时等待空闲对象 [英] org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object
本文介绍了org.apache.tomcat.dbcp.dbcp.SQLNestedException：无法获取连接，池错误超时等待空闲对象的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在使用Tomcat 7，MySql Workbench 5.2.27，JSF 2.0，这个异常来自我的网页（Trip Record.xhtml）的ManagedBean（TripTableBean.java）。每当我在浏览其他网页后点击进入Trip Record.xhtml，它就会出现。原谅我可怕的代码...
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)
发生异常的方法 :( TripTableBean.java ：389）指向rs2 = db.readRequest（dbQuery2）;
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?
更新：
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，我将需要找到一种方法来杀死会话并在我来到这个网页时重新创建一个新的会话，如果不是这个页面上的dataTable将不会从数据库加载更新的更改。
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.
推荐答案
你有什么连接池设置可以在DBController中发布什么内容？
提示：db.terminate（）应该在finally {}块中，可能是异常连接丢失。
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();}
}
}
我无法改进代码，但我建议看一下网上的一些最佳实践，以及@BalusC的建议。
请记住：完成后关闭连接对象。
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.
这篇关于org.apache.tomcat.dbcp.dbcp.SQLNestedException：无法获取连接，池错误超时等待空闲对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
