分页查询

 从后台取得结果集，在前台分页显示，一直很模糊，没清楚的去了解这个技术的实现，现在明白了。

我的理解：实际上就是从前台动态的获得pageIndex（索引页），总页面数（totalpage），总记录数（totalCount），将想要得到的信息量传给后台的SQL语句，在租赁项目中，代码如下：

/** * 按资产包编号，年份月份查询月租金 * * @param pactId,year,month * @param pageSize * 页容量 * @param pageIndex * 索引页 * @return list */public List findAll(String pactId, String year, String month, int pageSize,int pageIndex) {// TODO Auto-generated method stubList list = new ArrayList();int beginIndex = (pageIndex - 1) * pageSize;int endIndex = beginIndex + pageSize;// ----------------------------查询语句-----------------------------------------------------//StringBuffer sql = new StringBuffer("SELECT * from ( select K.RENTER,T.CONTRACT_CODE,T.SHOULD_REPAY_DATE,T.SHOULD_REPAY_AMT,rownum rn "+ " from clc_repay_plan t, clc_asset_pact j, clc_contract_info k where "+ "t.calc_type = '4' "+ "and t.inner_account_id = k.inner_account_id ");if (!"".equals(year) && year != null && !"".equals(month)&& month != null) {sql.append(" and t.SHOULD_REPAY_DATE LIKE '%" + year + "-" + month+ "%' ");}if (!"".equals(pactId) && pactId != null) {sql.append(" and j.pact_Id = '" + pactId + "')");}sql.append(" where rn >" + beginIndex + " and rn<=" + endIndex);try {session = HibernateSessionFactory.getSession();Query query = session.createSQLQuery(sql.toString());list = query.list();} catch (Exception e) {e.printStackTrace();} finally {if (session != null) {session.close();}}return list;}public int getResultCount(String pactId, String year, String month) {int count = 0;// -----------------记录条数------------------------------------------------------//String countSql = "SELECT count(*) "+ " from clc_repay_plan t, clc_asset_pact j, clc_contract_info k where "+ "t.calc_type = '4' "+ "and t.inner_account_id = k.inner_account_id ";if (!"".equals(year) && year != null && !"".equals(month)&& month != null) {countSql += " and t.SHOULD_REPAY_DATE LIKE '%" + year + "-"+ month + "%' ";}if (!"".equals(pactId) && pactId != null) {countSql += " and j.pact_Id = '" + pactId + "'";}try {session = HibernateSessionFactory.getSession();count = Integer.valueOf(session.createSQLQuery(countSql).list().get(0).toString()).intValue();} catch (Exception e) {e.printStackTrace();} finally {if (session != null) {session.close();}}return count;}

 

其中分页查询的SQL语句如下：

SELECT *FROM (SELECT K.RENTER, T.CONTRACT_CODE, T.SHOULD_REPAY_DATE ,1,4, T.SHOULD_REPAY_AMT, ROWNUM RNFROM CLC_REPAY_PLAN T, CLC_ASSET_PACT J, CLC_CONTRACT_INFO K WHERE T.CALC_TYPE = '4' AND T.INNER_ACCOUNT_ID = K.INNER_ACCOUNT_ID AND T.SHOULD_REPAY_DATE LIKE '%2011-02%' AND J.PACT_ID = '10000180') WHERE rn > 10 AND rn <= 20

它先执行最里层的sql，此时就多了一个字段（RN），在对RN进行限制，从而实现分页。RN的值为：

int beginIndex = (pageIndex - 1) * pageSize;int endIndex = beginIndex + pageSize;

 

还可以利用Query query = session.createQuery(sql);query.setFirstResult((Integer.valueOf(m_WebFYbean.getPageno()).intValue()-1)*m_WebFYbean.getPagesize());query.setMaxResults(m_WebFYbean.getPagesize()); 来分页，其中m_WebFYbean是封装分页信息的实例。