oracle 、MySql分页举例

最新推荐文章于 2025-12-04 15:25:15 发布

最新推荐文章于 2025-12-04 15:25:15 发布 · 91 阅读

文章标签：

#数据库 #java

本文介绍了一种使用Hibernate结合MySQL和Oracle进行高效分页查询的方法，包括SQL查询语句的构造和执行过程，以及如何通过调整参数提高查询效率。

三重循环

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; }