Hibernate实现分页查询的原理

在前面几个帖子的讨论过程中,我为了搞清楚问题,查了一下Hibernate源代码,搞清楚了Hibernate分页查询的原理,介绍一下:

Hibernate List可以实现分页查询:
从第2万条开始取出100条记录


Query q = session.createQuery("from Cat as c");
q.setFirstResult(20000);
q.setMaxResults(100);
List l = q.list();

那么Hibernate底层如何实现分页的呢?实际上Hibernate的查询定义在net.sf.hibernate.loader.Loader这个类里面,仔细阅读该类代码,就可以把问题彻底搞清楚。

Hibernate2.0.3的Loader源代码第480行以下:


if (useLimit) sql = dialect.getLimitString(sql);
PreparedStatement st = session.getBatcher().prepareQueryStatement(sql, scrollable);

如果相应的数据库定义了限定查询记录的sql语句,那么直接使用特定数据库的sql语句。

然后来看net.sf.hibernate.dialect.MySQLDialect:


public boolean supportsLimit() {
return true;
}
public String getLimitString(String sql) {
StringBuffer pagingSelect = new StringBuffer(100);
pagingSelect.append(sql);
pagingSelect.append(" limit ?, ?");
return pagingSelect.toString();
}

这是MySQL的专用分页语句,再来看net.sf.hibernate.dialect.Oracle9Dialect:

public boolean supportsLimit() {
return true;
}

public String getLimitString(String sql) {
StringBuffer pagingSelect = new StringBuffer(100);
pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
pagingSelect.append(sql);
pagingSelect.append(
" ) row_ where rownum <= ?) where rownum_ > ?");
return pagingSelect.toString();
}

Oracle采用嵌套3层的查询语句结合rownum来实现分页,这在Oracle上是最快的方式,如果只是一层或者两层的查询语句的rownum不能支持order by。

除此之外,Interbase,PostgreSQL,HSQL也支持分页的sql语句,在相应的Dialect里面,大家自行参考。

如果数据库不支持分页的SQL语句,如果在配置文件里面
hibernate.jdbc.use_scrollable_resultset true
默认是true,如果你不指定为false,那么Hibernate会使用JDBC2.0的scrollable result来实现分页,看Loader第430行以下:


if ( session.getFactory().useScrollableResultSets() ) {
// we can go straight to the first required row
rs.absolute(firstRow);
}
else {
// we need to step through the rows one row at a time (slow)
for ( int m=0; m<firstRow; m++ ) rs.next();
}

如果支持scrollable result,使用ResultSet的absolute方法直接移到查询起点,如果不支持的话,使用循环语句,rs.next一点点的移过去。

值得一提的是,Oracle的JDBC驱动的Scrollable ResultSet的实现方法实际上也是用循环语句rs.next一点点的移过去。

不过Oracle的absolute还不算如何丑陋,最丑陋的是MySQL。 MySQL是把所有的记录一股脑取到内存,你要其中几条,它就返回给你几条。

Hibernate的Dialect还是做的比较好,
以前我们在oracle都是用嵌套3层的sql来做分页的, 不用遍历ResultSet
oracle嵌套2层也是可以的, 但开销大一点
SELECT * from (
select rownum , rsisql_alias.*, rank() over (order by rownum) rsisql_rownum from (

select * from reporter.queue
) rsisql_alias ) where rsisql_rownum > 10
and rsisql_rownum <= 20

看来使用Hibernate的HQL有一个好处,增强了平台的兼容性,不必象直接使用JDBC一样,为了数据库平台通用性丧失一些性能了。

我一直在找一段语法:就是只返回ID或一个字段,不返回一个Java Object的Query做法?


Query q = session.createQuery("select c.id, c.name from Cat as c");
List l = q.list();
Object[] row = new Object[2];
for (i=0; i< l.size(); i++) {
row = l.get(i);
Integer id = (Integer) row[0];
String name = (String) row[1];
System.out.println(id+
" "+name);
}


上面写的有点错误。

取单个字段,会返回字段List:


Query q = s.createQuery("select c.id from Cat as c");
List l = q.list();
for (i=0; i< l.size(); i++) {
System.out.println(
"id = " + l.get(i));
}

取多个字段,会返回一个对象数组List,数组每个元素就是每个字段:


Query q = s.createQuery("select c.id, c.name,c from Cat as c");
List l = q.list();
for (int i=0; i< 100; i++) {
Object[] row = (Object[]) l.get(i);
Integer id = (Integer) row[0];
String name = (String) row[1];
Cat c = (Cat) row[3];
}

>>>>如果支持scrollable result,使用ResultSet的absolute方法直接移到查询起点,如果不支持的话,使用循环语句,rs.next一点点的移过去。

>>>>值得一提的是,Oracle的JDBC驱动的Scrollable ResultSet的实现方法实际上也是用循环语句rs.next一点点的移过去。


现在假设有如下场景:

表中有30万条用户,我现在做了一个分页浏览所有用户的功能,现在不考虑使用hibernate,用直接使用JDBC的话,那我是不是就要

ResultSet rs = stmt.executeQuery("SELECT * FROM user");

rs.absolute(290000);

然后再取20条?

我用Sqlserver+MS JDBC Driver测试的时候,当执行
ResultSet rs = stmt.executeQuery("SELECT * FROM user");
的时候CPU保持N秒占用率100%,同时java.exe和sqlserver.exe两个进程占用的内存暴涨,难道oracle或其它的JDBC Driver这时候就会这样吗?

关于JDBC的Scrollable我不是很清楚它们的实现机制。

大家说说?

>>>>同时java.exe和sqlserver.exe两个进程占用的内存暴涨,难道oracle或其它的JDBC Driver这时候就会这样吗?

这个我记忆非常清楚,sqlserver.exe占用多于150M

不知大家遇到这种情况是如何做的?

to robbin
多谢,你的HQL语句还是先构造了Java object,然后从c这个对象取出字段。不过这样可以用,我只要将ID单独设计成一个表,这样一个ID字段和一个含ID字段的对象,两者之间提取速度差异应该可以忽视。

到底Hibernate相当于一个数据库驱动代理模式一样,在具体应用与不同JDBC之间加入一层,实现了两者解耦,而且会根据不同数据库实现最优的查询性能,单这点,看来使用HQL是有其必要性的。

不对不对hibernate的作者佩服得五体投地
他对数据库实在是太精通了

My God,你误解了。我特意做了一个查询多个字段,同时包括对象本身的例子,是为了演示HQL的灵活,你看这个就明白了:


Query q = s.createQuery("select Cat.id, Cat.name, from Cat ");
List l = q.list();
for (int i=0; i< 100; i++) {
Object[] row = (Object[]) l.get(i);
Integer id = (Integer) row[0];
String name = (String) row[1];
}

Hibernate把每个id和name做成一个2个元素的单维数组,List实际上是一个单维数组的集合。在这种情况下,HQL是不会去构造PO的,这一点很容易验证。JCS是对象Cache,如果写了这样的HQL,JCS里面是空的,说明Hibernate没有构造PO,如果再加上一个对象c本身,JCS里面就有数据了。

如果你提取单个id,那更加简单,直接得到一个id的集合,根本就不会构造PO,同样用JCS可以验证。

To: hailwind

从你的描述来看,你使用SQL Server的Scrollable和我使用MySQL的Scrollable的情形是完全一样的,这说明SQL Server的JDBC驱动和MySQL一样烂。

>>>>>从你的描述来看,你使用SQL Server的Scrollable和我使用MySQL的Scrollable的情形是完全一样的,这说明SQL Server的JDBC驱动和MySQL一样烂。

其它的JDBC Driver没有这个问题吗?

>>>>>值得一提的是,Oracle的JDBC驱动的Scrollable ResultSet的实现方法实际上也是用循环语句rs.next一点点的移过去。

这句话是什么意思呢?

问一个关于hibernate的OracleDialect问题, 对于id的自动生成,
OracleDialect提供了sequence的方法, 它是直接调sequence来插入ID的.
但一般做法都是用trigger来调sequence插入ID, 这种方法即安全又灵活,
不知OracleDialect怎样处理这种情况

你真的要把SQL Server JDBC Driver的Scrollable搞清楚的话,非的去把JDBC驱动类用jad反编译,把源代码读一遍才能搞的清楚。分页算法其实就是这么几种办法而已。

我看你能用SQL Server的嵌套sql语句写分页语句,不如写一个SQLServerDialect类吧,提交给Gavin King,现在Hibernate还没有SQL Server的Dialect,很多用SQL Server的人都用Sybase的Dialect,想必郁闷很久了 :)