SQLException throws after changing C3P0 configuration
Hi All,
We are developing a work flow based system using JBPM 3.1.4, Struts and Hibernate 3. All is working good until we changed some configuration to implement the Threading and C3P0 for concurrent users. The configuration for this in hibernate.cfg.xml is as below:
<property name="hibernate.connection.url">jdbc:oracle:thin:@<hostname>:1521:orcl</property> <property name="hibernate.connection.username">username</property> <property name="hibernate.connection.password">pass</property> <property name="hibernate.current_session_context_class">thread</property> <property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider </property> <property name="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</property> <property name="hibernate.cache.use_second_level_cache">true</property> <property name="show_sql">false</property> <property name="hibernate.c3p0.max_size">4</property> <property name="hibernate.c3p0.min_size">4</property> <property name="hibernate.c3p0.timeout">5000</property> <property name="hibernate.c3p0.max_statements">0</property> <property name="hibernate.c3p0.idle_test_period">300</property> <property name="hibernate.c3p0.acquire_increment">1</property>
There are two types of errors throwing after implementing the same,
1) Closed ResultSet : Error Console for the error is as below:
java.sql.SQLException: Closed Resultset: next at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:222) at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:916) at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:181) at com.mchange.v2.c3p0.impl.NewProxyResultSet.next(NewProxyResultSet.java:2859) at com.elitecore.workorder.subscriberapplication.dao.SubscriberApplicationDao.getSearchedGroupCusto merByCustomerId(SubscriberApplicationDao.java:739) at com.elitecore.workorder.subscriberapplication.manager.BulkNewProvisionManager.getSearchedGroupCu stomerByCustomerId(BulkNewProvisionManager.java:321) at com.elitecore.workorder.subscriberapplication.action.SearchCustomerAction.searchGroupCustomer(Se archCustomerAction.java:121) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:269) at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:170) at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:425) at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:228) at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913) at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:462) at javax.servlet.http.HttpServlet.service(HttpServlet.java:637) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290 ) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at com.elitecore.workorder.jbpm.session.JbpmSessionConversationFilter.doFilter(JbpmSessionConversat ionFilter.java:78) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235 ) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447) at java.lang.Thread.run(Thread.java:595)
The code snap for the same is :
JbpmContext jbpmContext = JbpmContextManager.getJbpmContext();
PreparedStatement pst = null;
StringBuffer sbQuery = new StringBuffer();
sbQuery.append("select DISTINCT c.id,c.clientName,c.clientTitle, c.PANGIRNO,c.headquarteraddressid, ");
sbQuery.append("c.customerid,c.PANGIRNO from tblmclient c ");
if(accessLevel !=null && accessLevel.equalsIgnoreCase("LDCA")){
sbQuery.append("join tbltassignedlocation al on al.locationtypeid=C.ldcabcaid ");
}else if(accessLevel !=null && accessLevel.equalsIgnoreCase("Region")){
sbQuery.append("join tbltassignedlocation al on al.locationtypeid=C.regionid ");
}else if(accessLevel !=null && accessLevel.equalsIgnoreCase("Circle")){
sbQuery.append("join tbltassignedlocation al on al.locationtypeid=c.circleid ");
}
sbQuery.append("where upper(c.customerid) like (?) ");
sbQuery.append("and c.isdeleted like 'No' ");
sbQuery.append("and al.staffid='"+staffId.trim()+"' order by c.CLIENTNAME ");
pst = jbpmContext.getConnection().prepareStatement(sbQuery.toString());
pst.setString(1,"%"+ customerId.toUpperCase()+"%");
rs = pst.executeQuery();
while(rs.next())// Error comes here.
{
searchGroupCustomer=new SearchGroupCustomer();
searchGroupCustomer.setClientId(rs.getString("id"));
searchGroupCustomer.setSubscriberName(rs.getString("CLIENTNAME"));
searchGroupCustomer.setSubscriberTitle(rs.getString("clientTitle"));
searchGroupCustomer.setCustomerId(rs.getString("customerId"));
searchGroupCustomer.setPanGIRNo(rs.getString("PANGIRNO"));
Address address1=(Address)abstractDao.initliaze(Address.class,rs.getString("headquarteraddressid"));
searchGroupCustomer.setAddress(address1);
lstCustomerName.add(searchGroupCustomer);
}
Error Comes at this line:
while(rs.next())// Error comes here.
2) Invalid Cursor :Error Console for the error is as below:
java.sql.SQLException: ORA-01001: invalid cursor at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180) at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208) at oracle.jdbc.ttc7.v8Odscrarr.receive(v8Odscrarr.java:192) at oracle.jdbc.ttc7.TTC7Protocol.describe(TTC7Protocol.java:592) at oracle.jdbc.driver.OracleStatement.describe(OracleStatement.java:5371) at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.java:5141) at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:698) at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:1505) at com.mchange.v2.c3p0.impl.NewProxyResultSet.getString(NewProxyResultSet.java:3342) at com.elitecore.workorder.subscriberapplication.dao.SearchSubscriberApplicationDao.getSAStatusReport(SearchSubscriberApplicationDao.java:445) at com.elitecore.workorder.subscriberapplication.manager.SubscriberApplicationManager.getSAStatusReport(SubscriberApplicationManager.java:114) at com.elitecore.workorder.subscriberapplication.action.UpgradePostConnectionAction.listApplications(UpgradePostConnectionAction.java:259) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:269) at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:170) at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:425) at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:228) at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913) at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:462) at javax.servlet.http.HttpServlet.service(HttpServlet.java:637) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at com.elitecore.workorder.jbpm.session.JbpmSessionConversationFilter.doFilter(JbpmSessionConversationFilter.java:78) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447) at java.lang.Thread.run(Thread.java:595)
The code snap for the same is :
PreparedStatement pst=null;
JbpmContext jbpmContext = JbpmContextManager.getJbpmContext();
pst = jbpmContext.getConnection().prepareStatement(getSAStatusReport);
pst.setString(1, saId);
rs = pst.executeQuery();
while (rs.next())
{
saStatusInfo = new SaStatusInfo();
AbstractDao abstractDao=new AbstractDao();
Address endAAddress=(Address)abstractDao.initliaze(Address.class,rs.getString("endAAddressId"));
Address endBAddress=(Address)abstractDao.initliaze(Address.class,rs.getString("endBAddressId"));//Error Comes here.
log.debug(" endAAddress.getAddressAsString() "+endAAddress.getAddressAsString());
log.debug(" endBAddress.getAddressAsString() "+endBAddress.getAddressAsString());
saStatusInfo.setEndAInstallAddress(endAAddress.getAddressAsString());
saStatusInfo.setEndBInstallAddress(endBAddress.getAddressAsString());
}
Error Comes at this line:
Address endBAddress=(Address)abstractDao.initliaze(Address.class,rs.getString("endBAddressId"));//Error Comes here.
As from basic debugging we identify that the problem arises when the abstractDao.initliaze() is used. This class basically fetch the data of a table and prepare an object for the same. The code snap for the same is as below:
public Object initliaze(Class clazz, String id) throws Exception
{
JbpmContext jbpmContext = JbpmContextManager.getJbpmContext();
return jbpmContext.getSession().get(clazz,id);
}
We are at a very critical stage in the project and this Errors are started.
Kindly suggest us a way by which we can be out with this problems.
Note: All this errors removed if we commented the hibernate.cfg.xml configuration for Thread and C3P0.