hibernate 对 sql server 2005 分页改进

赵小黑

　　引用链接：http://www.uandroid.com/home/space.php?uid=4&do=blog&id=49
　　
　　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