hibernate 对 sql server 2005 分页改进
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
