Hibernate 可以实现分页查询 如下 Query q = session.createQuery("from Cat as c"); q.setFirstResult(10000); q.setMaxResults(20); List l = q.list(); 生成的sql 语句为 select top 10000 .... from Cat as c 分析:Hibernate 获取到 10000条记录过后再筛选出符合条件的20条记录。 再来看看Hibernate 的 dialect 对 sql server 怎样分页的 public String getLimitString(String querySelect, int offset, int limit) { if ( offset > 0 ) { throw new UnsupportedOperationException( "sql server has no offset" ); } return new StringBuffer( querySelect.length() + 8 ) .append( querySelect ) .insert( getAfterSelectInsertPoint( querySelect ), " top " + limit ) .toString(); } 由此可以看出Hibernate对 sql server 分页全是采取 top 方式来处理。如记录有10W条以上的话到后面几页效率会非常低。从getLimitString(String querySelect, int offset, int limit)方法可以看出 sql server 不支持 offset 参数(均为0)!! 然而在sql server2005过后支持ROW_NUMBER() 函数,可用该函数大大提高分页效率。但现有的Hibernate api不支持该函数。我们可以重写public String getLimitString(String querySelect, int offset, int limit)方法来实现 ROW_NUMBER 分页,重写的方法如下: public String getLimitString(String querySelect, int offset, int limit ){ int lastIndexOfOrderBy = getLastIndexOfOrderBy(querySelect); // 没有 order by 或第一页的情况下 if(lastIndexOfOrderBy<0 || querySelect.endsWith(")") || offset==0) return super.getLimitString(querySelect, 0, limit); else { //取出 order by 语句 String orderby = querySelect.substring(lastIndexOfOrderBy, querySelect.length()); //取出 from 前的内容 int indexOfFrom = querySelect.toLowerCase().indexOf("from"); String selectFld = querySelect.substring(0,indexOfFrom); //取出 from 语句后的内容 String selectFromTableAndWhere = querySelect.substring(indexOfFrom, lastIndexOfOrderBy); StringBuffer sql = new StringBuffer(querySelect.length()+100); sql.append("select * from (") .append(selectFld) .append(",ROW_NUMBER() OVER(").append(orderby).append(") as _page_row_num_hb ") .append(selectFromTableAndWhere).append(" ) temp ") .append(" where _page_row_num_hb BETWEEN ") .append(offset+1).append(" and ").append(limit); return sql.toString(); } } 让offset参数有效还应重写 public boolean supportsLimitOffset() 方法 public boolean supportsLimitOffset(){ return true; } 完整的代码如下: package org.jac.common; import org.hibernate.dialect.SQLServerDialect; public class JacSQLServerDialect extends SQLServerDialect { static int getLastIndexOfOrderBy(String sql){ return sql.toLowerCase().lastIndexOf("order by "); } public String getLimitString(String querySelect, int offset, int limit ){ int lastIndexOfOrderBy = getLastIndexOfOrderBy(querySelect); // 没有 order by 或第一页的情况下 if(lastIndexOfOrderBy<0 || querySelect.endsWith(")") || offset==0) return super.getLimitString(querySelect, 0, limit); else { //取出 order by 语句 String orderby = querySelect.substring(lastIndexOfOrderBy, querySelect.length()); //取出 from 前的内容 int indexOfFrom = querySelect.toLowerCase().indexOf("from"); String selectFld = querySelect.substring(0,indexOfFrom); //取出 from 语句后的内容 String selectFromTableAndWhere = querySelect.substring(indexOfFrom, lastIndexOfOrderBy); StringBuffer sql = new StringBuffer(querySelect.length()+100); sql.append("select * from (") .append(selectFld) .append(",ROW_NUMBER() OVER(").append(orderby).append(") as _page_row_num_hb ") .append(selectFromTableAndWhere).append(" ) temp ") .append(" where _page_row_num_hb BETWEEN ") .append(offset+1).append(" and ").append(limit); return sql.toString(); } } //使offset 参数生效 public boolean supportsLimitOffset(){ return true; } 最后再 hibernate.cfg.xml 配置 dialect <property name="dialect"> org.jac.common.JacSQLServerDialect </property> 测试结果如下: Query q = session.createQuery("from Cat as c order by c.id asc"); //注意要加 order by 才能用到 ROW_NUMBER分页 q.setFirstResult(10000); q.setMaxResults(20); List l = q.list(); 生成的sql 语句为 select * from (select ....,ROW_NUMBER() OVER(order by cat0_.id asc) as _page_row_num_hb from cat as cat0_) temp where _page_row_num_hb BETWEEN 10001 and 10020 |
|
来自: KILLKISS > 《Hibernate》