扩展hibernate的方言。使hibernate支持row_number

hibernate对于sqlserver2005真分页

Hibernate分页的“真”与“假”，取决于所指定的方言（Dialect）。分析了一下Hibernate中相关的源代码，目前的结论是：OracleDialect、Oracle9Dialect、MySQLDialect、MySQL5Dialect是真分页，SQLServerDialect是假分页。
Hibernate 可以实现分页查询 如下

view source

print?

1	Query q = session.createQuery("from Cat as c");

2	q.setFirstResult(10000);

3	q.setMaxResults(20);

4	List l = q.list();

生成的sql 语句为
select top 10000 .... from Cat as c
分析：Hibernate 获取到 10000条记录过后再筛选出符合条件的20条记录。

再来看看Hibernate 的 dialect 对 sql server 怎样分页的

view source

print?

1	public String getLimitString(String querySelect, int offset, int limit) {

2	if ( offset > 0 ) {

3	throw new UnsupportedOperationException( "sql server has no offset" );

4

}

5	return new StringBuffer( querySelect.length() + 8 )

6	.append( querySelect )

7	.insert( getAfterSelectInsertPoint( querySelect ), " top " + limit )

8	.toString();

9

}

由此可以看出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　分页,重写的方法如下：

view source

print?

01	public String getLimitString(String querySelect, int offset, int limit ){

02	int lastIndexOfOrderBy = getLastIndexOfOrderBy(querySelect);

03	//　没有 order by 或第一页的情况下

04	if(lastIndexOfOrderBy<0 || querySelect.endsWith(")") || offset==0)

05	return super.getLimitString(querySelect, 0, limit);

06

else {

07	//取出 order by 语句

08	String orderby = querySelect.substring(lastIndexOfOrderBy, querySelect.length());

09	//取出 from 前的内容

10	int indexOfFrom = querySelect.toLowerCase().indexOf("from");

11	String selectFld = querySelect.substring(0,indexOfFrom);

12	//取出 from 语句后的内容

13	String selectFromTableAndWhere = querySelect.substring(indexOfFrom, lastIndexOfOrderBy);

14	StringBuffer sql = new StringBuffer(querySelect.length()+100);

15	sql.append("select * from (")

16	.append(selectFld)

17	.append(",ROW_NUMBER() OVER(").append(orderby).append(") as _page_row_num_hb ")

18	.append(selectFromTableAndWhere).append(" ) temp ")

19	.append(" where  _page_row_num_hb BETWEEN  ")

20	.append(offset+1).append(" and ").append(limit);

21	return sql.toString();

22

}

23

}

让offset参数有效还应重写 public boolean supportsLimitOffset() 方法
    public boolean supportsLimitOffset(){
        return true;
    }

完整的代码如下：

view source

print?

01	package com.java1995.dialect;

02	import org.hibernate.dialect.SQLServerDialect;

03	public class Java1995SQLServerDialect extends SQLServerDialect {

04	static int getLastIndexOfOrderBy(String sql){

05	return sql.toLowerCase().lastIndexOf("order by ");

06

}

07

08	public String getLimitString(String querySelect, int offset, int limit ){

09	int lastIndexOfOrderBy = getLastIndexOfOrderBy(querySelect);

10	//　没有 order by 或第一页的情况下

11	if(lastIndexOfOrderBy<0 || querySelect.endsWith(")") || offset==0)

12	return super.getLimitString(querySelect, 0, limit);

13

else {

14	//取出 order by 语句

15	String orderby = querySelect.substring(lastIndexOfOrderBy, querySelect.length());

16	//取出 from 前的内容

17	int indexOfFrom = querySelect.toLowerCase().indexOf("from");

18	String selectFld = querySelect.substring(0,indexOfFrom);

19	//取出 from 语句后的内容

20	String selectFromTableAndWhere = querySelect.substring(indexOfFrom, lastIndexOfOrderBy);

21	StringBuffer sql = new StringBuffer(querySelect.length()+100);

22	sql.append("select * from (")

23	.append(selectFld)

24	.append(",ROW_NUMBER() OVER(").append(orderby).append(") as _page_row_num_hb ")

25	.append(selectFromTableAndWhere).append(" ) temp ")

26	.append(" where  _page_row_num_hb BETWEEN  ")

27	.append(offset+1).append(" and ").append(limit);

28	return sql.toString();

29

}

30

}

31	//使offset 参数生效

32	public boolean supportsLimitOffset(){

33	return true;

34

}

最后再 hibernate.cfg.xml 配置 dialect
<property name="dialect">
      com.java1995.dialect.Java1995SQLServerDialect
</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