Pagination with Hibernate and SP

[M M Islam Chisty]

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

[Mark Spritzler]

I suggest you move the start and end record number algorithm to the Store Procedure. It is the fact that you are using a Stored procedure that you cannot set the first result and maxresults without getting an Exception.

So make the stored procedure take the start and max results and write it in the SP coded to limit the records to that matching.

Mark

[Iftikhar Arain]

try this way FOR ORACLE 9i database , tomcat 5.0.28 , hibernate 2.x , struts 1.2 , spring don't know the version.

/**
*
* @param userId
* @param startdate
* @param endDate
* @param criteria
* @return
*/
public ListHelper getCaseCategoryDetail(final String userId,
final String category_id,
final String startDate ,
final String endDate ,
final ListCriteria criteria,
final String groupCode)
{
List list = new ArrayList();
Connection conn = null;
int fullSize = 0;
Session session = getHibernateTemplate().getSessionFactory().openSession();
try
{
conn = session.connection();
CallableStatement call = conn.prepareCall("{ call ? := human_report_pkg.GetCases(?,?,?,?,?,?,?) }");
call.setString(2 , userId);
call.setString(3 , startDate);
call.setString(4 , endDate);
call.setString(5 , groupCode);
call.setInt(6 , Integer.parseInt(category_id));
call.setString(7 , String.valueOf( (criteria.getSortColumn()+1) ));
call.setString(8 , String.valueOf(criteria.getSQLSortOrder()));
call.registerOutParameter(1 , OracleTypes.CURSOR);
call.execute();
ResultSet rset = (ResultSet) call.getObject(1);

int nextRowNum = (criteria.getPageIndex()-1) * criteria.getMaxPageSize() + 1;
int rowCount = 0;
while( rset.next() )
{
fullSize = rset.getRow();
if( fullSize < nextRowNum ) continue;
if(++rowCount> criteria.getMaxPageSize()) continue;

CaseDetailReportBean caseBean = new CaseDetailReportBean();
caseBean.setCaseId(rset.getString(1));
caseBean.setIncidentDate(rset.getString(2));
caseBean.setCaseCategoryTypeName(rset.getString(3));
caseBean.setCaseTheaterName(rset.getString(4));
caseBean.setStatus(rset.getString(5));
caseBean.setCaseManager(rset.getString(6));
caseBean.setCaseCategoryName(rset.getString(7));
caseBean.setOldCaseId(rset.getString(8));
list.add(caseBean);
}

}
catch(Exception exp)
{
exp.printStackTrace();
}
finally
{
try{
session.close();
conn.close();}catch(Exception exp){}
}
return new ListHelper( list , fullSize , criteria);
}

================================================================
ONE MORE WAY FOR PAGINATION WITH MYSQL 5.0.

/**
* This method finds all Items that contain given product name.
* @param name String Product Name
* @return List of all Items
* @throws HibernateException
*/
public static List findItemsByProduct(String name, int start, int noOfRecords) throws HibernateException {
Session session = null;
List list = null;
List itemList = new ArrayList();
SearchItemVO sIVO = null;

try {
log.debug(">>>>>>>> Finding Items By Product Name >>>>>> ");
session = DBHelper.getInstance().getSession();

/* Getting No. of Records */
String sql = "SELECT COUNT(*) FROM ITEM I WHERE UPPER(I.NAME) LIKE UPPER('%" + name + "%') AND I.ID <> 0";
System.out.println("****** sql = " + sql + " ********");
list = session.createSQLQuery(sql).list();
int count = Integer.parseInt(list.get(0).toString());
itemList.add(0, count);

sql = "SELECT I.ID, I.NAME, U.CBUSERID, L.NAME LOC FROM ITEM I, USER U, LOCATION L " +
"WHERE UPPER(I.NAME) LIKE UPPER('%" + name + "%') AND I.USERID = U.ID AND I.LOCATIONID = L.ID AND I.ID <> 0" +
" LIMIT " + start + ", " + noOfRecords;

list = session.createSQLQuery(sql).list();

for(int i=0; i<list.size(); i++) {

Object obj[] = null;
sIVO = new SearchItemVO();
try {
obj = (Object[])list.get(i);

sIVO.setItemId(new Long(obj[0].toString()));
sIVO.setItemName(obj[1].toString());
sIVO.setCbUserId(obj[2].toString());
sIVO.setLocationName(obj[3].toString());

itemList.add(sIVO);
}
catch (Exception ex) {
System.out.println("ex = " + ex);
}
}

log.debug("List Size >>>>>>>> : " + itemList.size());

}
catch (HibernateException e) {
e.printStackTrace();
}
finally {
if(session != null)
session.close();
}
return itemList;

}

====================================================================

YOU CAN ALSO CHECK THIS.

1 - Is the stored procedure name used correct?

2 - if your using parameters are they in the correct order and the correct type as expected by the procedure?