今天有学员问我:“hibernate是真分页么?我今天看了一些帖子,好像是真分页。……”
这个问题不能简单地用“真”或“假”来回答。Hibernate分页的“真”与“假”,取决于所指定的方言(Dialect)。分析了一下Hibernate中相关的源代码,目前的结论是:OracleDialect、Oracle9Dialect、MySQLDialect、MySQL5Dialect是真分页,SQLServerDialect是假分页。
两段简单的测试代码:
1、假设我想查询第1到第10条记录
public static void main(String[] args) {
int offset = 0; //可以理解为起始行号
int limit = 10; //可以理解为每页的最大记录数
String sql = "select * from MyTable"; //原始SQL语句
Dialect dialect = null;
//测试Oracle9方言的分页功能
dialect = new Oracle9Dialect();
System.out.println( "Oracle9方言生成的SQL分页语句:" );
System.out.println( dialect.getLimitString( sql, offset, limit ) );
//测试MySQL的分页功能
dialect = new MySQLDialect();
System.out.println( "MySQL方言生成的SQL分页语句:" );
System.out.println( dialect.getLimitString( sql, offset, limit ) );
//测试SQLServer的分页功能
dialect = new SQLServerDialect();
System.out.println( "SQLServer方言生成的SQL分页语句:" );
System.out.println( dialect.getLimitString( sql, offset, limit ) );
}
以下是控制台输出:
Oracle9方言生成的SQL分页语句: select * from ( select * from MyTable ) where rownum <= ? MySQL方言生成的SQL分页语句: select * from MyTable limit ? SQLServer方言生成的SQL分页语句: select top 10 * from MyTable
2、现在我想改查第11到第20条记录
public static void main(String[] args) {
//现在我想查询第11到20条记录
int offset = 10; //可以理解为起始行号
int limit = 10; //可以理解为每页的最大记录数
String sql = "select * from MyTable"; //原始SQL语句
Dialect dialect = null;
//测试Oracle9方言的分页功能
dialect = new Oracle9Dialect();
System.out.println( "Oracle9方言生成的SQL分页语句:" );
System.out.println( dialect.getLimitString( sql, offset, limit ) );
//测试MySQL的分页功能
dialect = new MySQLDialect();
System.out.println( "MySQL方言生成的SQL分页语句:" );
System.out.println( dialect.getLimitString( sql, offset, limit ) );
//测试SQLServer的分页功能
dialect = new SQLServerDialect();
System.out.println( "SQLServer方言生成的SQL分页语句:" );
System.out.println( dialect.getLimitString( sql, offset, limit ) );
}
以下是控制台输出:
Oracle9方言生成的SQL分页语句: select * from ( select row_.*, rownum rownum_ from ( select * from MyTable ) row_ where rownum <= ?) where rownum_ > ? MySQL方言生成的SQL分页语句: select * from MyTable limit ?, ? SQLServer方言生成的SQL分页语句: Exception in thread "main" java.lang.UnsupportedOperationException: sql server has no offset at org.hibernate.dialect.SQLServerDialect.getLimitString(SQLServerDialect.java:53) at test.Test.main(Test.java:30)
上述结果是如何产生的呢?让我们来查看一下Hibernate中相关的源代码:
1、Oracle9Dialect.java(OracleDialect.java中的代码与此类似)
public String getLimitString(String sql, boolean hasOffset) {
sql = sql.trim();
boolean isForUpdate = false;
if ( sql.toLowerCase().endsWith(" for update") ) {
sql = sql.substring( 0, sql.length()-11 );
isForUpdate = true;
}
StringBuffer pagingSelect = new StringBuffer( sql.length()+100 );
if (hasOffset) {
pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
}
else {
pagingSelect.append("select * from ( ");
}
pagingSelect.append(sql);
if (hasOffset) {
pagingSelect.append(" ) row_ ) where rownum_ <= ? and rownum_ > ?");
}
else {
pagingSelect.append(" ) where rownum <= ?");
}
if ( isForUpdate ) {
pagingSelect.append( " for update" );
}
return pagingSelect.toString();
}
2、MySQLDialect.java(MySQL5Dialect.java)
public String getLimitString(String sql, boolean hasOffset) {
return new StringBuffer( sql.length()+20 )
.append(sql)
.append( hasOffset ? " limit ?, ?" : " limit ?")
.toString();
}
3、SQLServerDialect.java
public String getLimitString(String querySelect, int offset, int limit) {
//以下这句就是第2段测试代码出异常的原因了,呵呵。由此可知,对于SQLServer的分页,Hibernate中对于SQLServer的分页,只会使用一次top子句取出前面的部分记录,来通过操作游标来获取指定页的数据,是假分页。
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();
}
SQLServerDialect没有重写public String getLimitString(String sql, boolean hasOffset)方法,以下是该方法在其父类Dialect的实现。
public String getLimitString(String querySelect, boolean hasOffset) {
throw new UnsupportedOperationException( "paged queries not supported" );
}
public String getLimitString(String querySelect, int offset, int limit) {
return getLimitString( querySelect, offset>0 );
}