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?