Hibernate 可以實現(xiàn)分頁查詢 如下
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 參數(shù)(均為0)！！
然而在sql server2005過后支持ROW_NUMBER() 函數(shù),可用該函數(shù)大大提高分頁效率。但現(xiàn)有的Hibernate api不支持該函數(shù)。我們可以重寫public String getLimitString(String querySelect, int offset, int limit)方法來實現(xiàn) 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 前的內(nèi)容
int indexOfFrom = querySelect.toLowerCase().indexOf("from");
String selectFld = querySelect.substring(0,indexOfFrom);
//取出 from 語句后的內(nèi)容
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參數(shù)有效還應重寫 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 前的內(nèi)容
int indexOfFrom = querySelect.toLowerCase().indexOf("from");
String selectFld = querySelect.substring(0,indexOfFrom);
//取出 from 語句后的內(nèi)容
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 參數(shù)生效
public boolean supportsLimitOffset(){
return true;
}
最后再 hibernate.cfg.xml 配置 dialect
<property name="dialect">
org.jac.common.JacSQLServerDialect
</property>
測試結(jié)果如下：
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
