Hi

I have a Problem while doing pagination in Hibernate. we have more than 6000 operations, I had to implement Pagination in Hibernate Level.

The following is one of the method in settlementOperationDao class.

I have commented the lines required for Pagination
Here Order clause is mandatory
SetFirstResult and MaxResult are also mandatory.


/**
* @param lSpId
* @throws com.cavali.exception.BaseException
* @return
*/
public List findSettlementOperations(Long lSpId, int intPage ,
int intPageSize) throws BaseException { \* Here intPage and intPageSize
related to Pagenation */
Session session = null;
try {
session = HibernateUtil.currentSession();
Criteria objCr = session.createCriteria(SettlementOperations.class,
"so").createCriteria("so.settlementOperationHolderses",
"soh").add(
Expression
.eq("so.settlementProcesses.spIdProcessPk", lSpId))
.add(Expression.eq("so.soInWithdrawed", new Long(0))).add(
Expression.eq("soh.sohInWithdrawed", new Long(0)))
.addOrder(Order.asc("so.soIdSettlementPk")); // Pagination
objCr.setFirstResult(intPage * intPageSize); // Pagination
objCr.setMaxResults(intPageSize + 1); // Pagination
return objCr.list(); // Pagination
} catch (RuntimeException re) {
log.error("Exception occured in findSettlementOperations method",
re);
throw re;
} finally {
HibernateUtil.closeSession();
}
}


and In one of the method in BC Class I have called like the following.

/**
* registerParticipantPositions
*
* @param lngSPK
* Long
* @return String
* @throws BaseException
*/
private String registerParticipantPositions(Long lngSPK)
throws BaseException {

List lstSettlementOps = objSettlementOperationsDAO
.findSettlementOperations(lngSPK, 0, 500); // Call to Pagenation method
List lstSettlementOpsNew = new ArrayList();
int intPage = 0;
while(lstSettlementOps.size()>500){ // Checks whether nextPage exists or
not
lstSettlementOpsNew = lstSettlementOps.subList(0, 500-1); // Gets only
500 records
updateSettlementOpeAmount(lstSettlementOpsNew);
lstSettlementOps = null;
intPage++;
lstSettlementOps = objSettlementOperationsDAO
.findSettlementOperations(lngSPK, intPage, 500); // Call for nextPage
}
if(lstSettlementOps.size() != 0){
updateSettlementOpeAmount(lstSettlementOps);
}

int iStatus = calculateParticipantPosiCommon(lngSPK);
if (iStatus == 0) {
return "success";
} else {
return "fail";
}
}


I tested this code in my system. For 6000 operations it is working fine but I have 26000 operations for that I am getting exception.

I have already increased Transaction time and Memory Size for my OC4J application server.

Plz suggest me is there any better solution for pagination in hibernate.

Thanks in advance