oracle 、MySql分页 举例

三重循环

select mbid00,mbmc00, llurl0,cfwz00,mbsltp,sycs00,lrsj00 from (
select mbid00,mbmc00, llurl0,cfwz00,mbsltp,sycs00,lrsj00,rownum as con from (
select * from JZ_MBXXB0 where 1=1 and lbid00=
'201'
order by lrsj00)
where rownum<=
10
) where con>=
1

在hibernate + Mysql 方式

/**
	 * 分页查询
	 */
	@SuppressWarnings("unchecked")
	public List doSearch(PageDao pageDAO, final int pageSize,
			final String name00) throws Exception {
		
		List list = new ArrayList();
		StringBuffer sql = new StringBuffer(
		"from Book b where b.name00 LIKE '%");
		sql.append(name00);
		sql.append("%'");
		
		final String typeSql = new String(sql);
		// 设置每页显示条数
		pageDAO.setPagesize(pageSize);
		int pagesize = pageDAO.getPagesize();
		// 获取总记录数
		List count = (List)hibernateTemplate.execute(  

			new HibernateCallback(){
				public Object doInHibernate(Session session)
				throws HibernateException, SQLException{
				Query q = session.createQuery(typeSql);
				//q.setFirstResult(0);
				//q.setMaxResults(2);
				List ss =q.list();
				return q.list(); //没查到数据也不会返回null, 只能用isEmpty
			}
		}
		);
		pageDAO.getRsCountForSQL(count.size());
		// 获取总页数
		pageDAO.GetPageCount();
		// 获取当前页
		final int currpage = pageDAO.GetCurrentPageForRequest();
		// 设置工具条类型
		String pool = pageDAO.pagetool(PageDao.Text);

		//System.out.println(pageDAO.getParamUrl());
		
		List<BookDTO> dt = new ArrayList<BookDTO>();
		List rs = (List)hibernateTemplate.execute(  

				new HibernateCallback(){
					public Object doInHibernate(Session session)
					throws HibernateException, SQLException{
					Query q = session.createQuery(typeSql);
					q.setFirstResult((currpage - 1) * pageSize);
					q.setMaxResults(pageSize);//currpage * 
					List ss =q.list();
					return q.list(); //没查到数据也不会返回null, 只能用isEmpty
				}
			}
			);
		for (Iterator it = rs.iterator(); it.hasNext();) {
			Object obj = it.next();
			BookDTO dto = new BookDTO();
			BeanUtils.copyProperties(obj,dto);
			dt.add(dto);
		}
		//session.setAttribute("pagedto", pageDAO);
		list.add(0,dt);
		list.add(1,pool);
		return list;
		
	}

hibernate + Oracle 方式 效率较差

public List queryMbList(final PageDao pageDAO, final int pageSize,
			String lbid) {
		
		List list = new ArrayList();
		StringBuffer sql = new StringBuffer();
		StringBuffer hql = new StringBuffer();
		
			sql = new StringBuffer(
			"select * from JZ_MBXXB0 where 1=1 and lbid00='");
			sql.append(lbid);
			sql.append("'");
		}
		
		// 设置每页显示条数
		pageDAO.setPagesize(pageSize);
		@SuppressWarnings("unused")
		int pagesize = pageDAO.getPagesize();
		// 获取总记录数
		List count = persistence.executeSQLQuery(sql.toString());
		pageDAO.getRsCountForSQL(count.size());
		// 获取总页数
		pageDAO.GetPageCount();
		// 获取当前页
		int currpage = pageDAO.GetCurrentPageForRequest();
		// 设置工具条类型
		String pool = pageDAO.pagetool(PageDao.Text);
		
		
		hql = new StringBuffer(
		"select mbid00,mbmc00, llurl0,cfwz00,mbsltp,sycs00,lrsj00 from ( ");
		hql.append("select mbid00,mbmc00, llurl0,cfwz00,mbsltp,sycs00,lrsj00,rownum as con from (");
		
		if(lbid.equals("200")){
			hql.append("select * from JZ_MBXXB0 where 1=1");
		}else{
			hql.append("select * from JZ_MBXXB0 where 1=1 and lbid00='");
			hql.append(lbid);
			hql.append("'");
		}
		
		
		hql.append("order by sycs00 desc,lrsj00 desc)"); //按条件排序 
		hql.append(" where rownum<=");
		hql.append(currpage * pageSize); //显示到第几条
		hql.append(") where con>="); //从第几行开始显示
		hql.append((currpage - 1) * pageSize + 1);
		List<Mbxxb0DTO> dt = new ArrayList<Mbxxb0DTO>();
		List rs = persistence.executeSQLQuery(hql.toString());
		for (Iterator it = rs.iterator(); it.hasNext();) {
			Object obj = it.next();
			Mbxxb0DTO dto = new Mbxxb0DTO();
			BeanHelper.copyProperties(obj,dto);
			dt.add(dto);
		}
		list.add(0,dt);
		list.add(1,pool);
		return list;
	}