Hi,
I hope you can provide me some guidelines. My objective is to handle(display) large volume of data using Hibernate and Stored Procedure. To do this, I like to apply pagination. i.e. rather than loading the whole bunch of rows (from a select query) into memory, I would like to pass some paramers to hibernate Query (e.g. page number, #rows per page .. etc), and show results page by page. I'm using hibernate with Oracle SP, spring and extrememcomponents.
Here are my codes:
[in the action class]
Context context = new HttpServletRequestContext(getRequest());
LimitFactory limitFactory = new TableLimitFactory(context);
Limit limit = new TableLimit(limitFactory);
metadataList = metadataManager.getMetadatas(metadataWo, limit.getPage()); // The obejctive is to view this metadatalist into
JSP page
[in the service layer]
public List getMetadatas(final MetadataWo metadataWo, int pageNo) {
final int defaultRows = 50;
int startRow = (pageNo - 1) * defaultRows ;
return dao.getMetadatas(metadataWo, startRow, defaultRows);
}
[in the dao layer]
Query q = null;
q = session.getNamedQuery("getMetadatasSP");
q.setString(0, metadataWo.getPsId()).
setString(1,metadataWo.getMetadataId()).
setString(2,metadataWo.getDocumentName()).
setString(3,metadataWo.getLocationCode()).
setString(4,metaDataObjectType).
setString(5,metadataWo.getUpdatedBy()).
setString(6,metadataWo.getBuCode()).
setString(7,metadataWo.getGxpRelevance()).
setString(8,metadataWo.getDetailsGxpAssessment()).
setString(9,metadataWo.getBuRelevance()).
setString(10,metadataWo.getBuCriticalityAssessment()).
setString(11, metadataWo.getFailureConsequence()).
setString(12, metadataWo.getDetailsFailureConsequence()).
setString(13, metadataWo.getTestingRequirement()).
setString(14,metadataWo.getDetailsTestingRequirement()).
setString(15,metadataWo.getTestPerformedApproved()).
setString(16, metadataWo.getSystemType()).
setString(17, metadataWo.getRelatedCoreItem()).
setString(18, metadataWo.getStatusName()).
setString(19, metadataWo.getCrId()).
setString(20,metadataWo.getIsSearchForExport()).
setString(21, metadataWo.getFromDay()).
setString(22, metadataWo.getFromMonth()).
setString(23, metadataWo.getFromYear()).
setString(24,metadataWo.getToDay()).
setString(25,metadataWo.getToMonth()).
setString(26,metadataWo.getToYear());
q.setFirstResult(startRow); // line test#1
q.setMaxResults(endRow); // line test#2
result = q.list(); // AT THIS STAGE, THE APPLICATION GETS THE ERROR. CAUGHT BY A TRY-CATCH BLOCK
______________________________________________________________
Now, if I try to view the results in JSP page, error shows up. At the
tomcat console, the following error message is generated:
java.lang.NullPointerException
at oracle.jdbc.ttc7.TTCAdapter.newTTCType(TTCAdapter.java:300)
at oracle.jdbc.ttc7.TTCAdapter.createNonPlsqlTTCColumnArray(TTCAdapter.java:270)
at oracle.jdbc.ttc7.TTCAdapter.createNonPlsqlTTCDataSet(TTCAdapter.java:231)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1937)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:880)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2516)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2850)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:609)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:685)
at org.hibernate.dialect.Oracle9Dialect.getResultSet(Oracle9Dialect.java:278)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:146)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1666)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2145)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:111)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1655)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:164)
at com.mh.pep.dao.MetadataDaoHibernate$2.doInHibernate(MetadataDaoHibernate.java:1216)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:362)
at org.springframework.orm.hibernate3.HibernateTemplate.executeFind(HibernateTemplate.java:332)
at com.mh.pep.dao.MetadataDaoHibernate.getMetadatas(MetadataDaoHibernate.java:1136)
at com.mh.pep.service.MetadataManagerImpl.getMetadatas(MetadataManagerImpl.java:1003)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:280)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:187)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:154)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:210)
at $Proxy5.getMetadatas(Unknown Source)
at com.mh.pep.webapp.action.MetadataAction.searchList(MetadataAction.java:748)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:360)
at com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:228)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:202)
at com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:177)
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:86)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:200)
at com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:115)
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:86)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:200)
at com.opensymphony.xwork2.interceptor.ConversionErrorInterceptor.intercept(ConversionErrorInterceptor.java:123)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:200)
at com.opensymphony.xwork2.interceptor.ParametersInterceptor.intercept(ParametersInterceptor.java:147)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:200)
at com.opensymphony.xwork2.interceptor.StaticParametersInterceptor.intercept(StaticParametersInterceptor.java:105)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:200)
at org.apache.struts2.interceptor.FileUploadInterceptor.intercept(FileUploadInterceptor.java:204)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:200)
at com.opensymphony.xwork2.interceptor.ModelDrivenInterceptor.intercept(ModelDrivenInterceptor.java:74)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:200)
at com.opensymphony.xwork2.interceptor.ChainingInterceptor.intercept(ChainingInterceptor.java:115)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:200)
at com.opensymphony.xwork2.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:143)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:200)
at com.opensymphony.xwork2.interceptor.PrepareInterceptor.intercept(PrepareInterceptor.java:115)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:200)
at org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(ServletConfigInterceptor.java:156)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:200)
.....
Interestingly, if I omit the lines (marked as line test#1 and line test#2) specified in the DAO layer, the result shows fine. But this does not solve my problem. Because without setting the firstResult and maxResult, it will work fine upro 10,000 or, 20,000 rows. But if the returned rows are more than that, there will be memory leak.
Can you suggest something? How can I apply pagination with Hibernate and SP?
Thanks,
... Chisty
