Hibernate处理Oracle的分页，是用rownum。

　　
代码如下：

Java代码 http://www.javaeye.com/images/icon_copy.gif

• public String getLimitString(String sql, boolean hasOffset);    
  
• {   
  
• 
    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 <= ?); where rownum_ > ?");;   
  
•   }   
  
• 
    else {   
  
• 
      pagingSelect.append(" ); where rownum <= ?");;   
  
•   }   
  
• 
    return pagingSelect.toString();;   
  
• }  
  

[java] view plaincopyprint?

• public String getLimitString(String sql, boolean hasOffset);   
  
• {  
  
• 
    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 <= ?); where rownum_ > ?");;  
  
•   }  
  
• 
    else {  
  
• 
      pagingSelect.append(" ); where rownum <= ?");;  
  
•   }  
  
• 
    return pagingSelect.toString();;  
  
• }  
  

  public String getLimitString(String sql, boolean hasOffset);
{
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 <= ?); where rownum_ > ?");;
}
else {
pagingSelect.append(" ); where rownum <= ?");;
}
return pagingSelect.toString();;
}

　　

出错前提

如果这里的sql是不带order by的sql，则查询结果没有任何问题。

但是，如果sql中带有order by，则会引起混乱，即相同记录会出现在不同页中。但是，这种混乱的出现通常是在下面的情况下：

1、纪录数足够多（如果表中有lob字段更好：P）

2、插入记录数大于3页，每页最好10＋条记录

3、order by字段至少需要有2个值

4、具备相同order by字段的记录数大于3页

5、插入记录后，最好做删除、修改操作，然后再插入记录。保证记录在磁盘环境中的顺序是无序的。

6、如果满足上述条件，但还没有出现混乱现象，则适当的加大纪录数。

大家测试这种现象，不需要通过hibernate，直接通过sql就可查到。下面有由hibernate生成的sql，以供大家试验：

select * from
  ( select row_.*, rownum rownum_ from
     ( select * from T_TABLE tTable where tTable.field1 order by  tTable.field2 desc ) row_ where rownum <= ?) where rownum_ > ?;

错误原因

之所以出现这样的问题，是和oracle处理ROWNUM的原理相关的。

以下是Oracle参考手册上的一段话：

引用


ROWNUM返回第一次从表中选择时返回的行的序列号。第一行的ROWNUM为1，第二行的为2，依此类推。但要注意，即使select语句中一条简单的order by都可能会搞乱ROWNUM（因为ROWNUM是排序前分配给各行的）。
　　

解决办法（来自使用手册）：

9.3.3. Scrollable iteration
If your JDBC driver supports scrollable ResultSets, the Query interface may be used to obtain a ScrollableResults which allows more flexible navigation of the query results. (Oracle 8.1.6+)

Java代码 http://www.javaeye.com/images/icon_copy.gif

• Query q = sess.createQuery("select cat.name, cat from DomesticCat cat " +   
  
• 
                              "order by cat.name");;   
  
• ScrollableResults cats = q.scroll();;   
  
• 
  if ( cats.first(); ); {   
  
•   
  
• 
      // find the first name on each page of an alphabetical list of cats by name   
  
• 
      firstNamesOfPages = new ArrayList();;   
  
• 
      do {   
  
• 
          String name = cats.getString(0);;   
  
•         firstNamesOfPages.add(name);;   
  
•     }   
  
• 
      while ( cats.scroll(PAGE_SIZE); );;   
  
•   
  
• 
      // Now get the first page of cats   
  
• 
      pageOfCats = new ArrayList();;   
  
•     cats.beforeFirst();;   
  
• 
      int i=0;   
  
• 
      while( ( PAGE_SIZE > i++ ); && cats.next(); ); pageOfCats.add( cats.get(1); );;   
  
•   
  
• }  
  

[java] view plaincopyprint?

• Query q = sess.createQuery("select cat.name, cat from DomesticCat cat " +  
  
• 
                              "order by cat.name");;  
  
• ScrollableResults cats = q.scroll();;  
  
• 
  if ( cats.first(); ); {  
  
•   
  
• 
      // find the first name on each page of an alphabetical list of cats by name   
  
• 
      firstNamesOfPages = new ArrayList();;  
  
• 
      do {  
  
• 
          String name = cats.getString(0);;  
  
•         firstNamesOfPages.add(name);;  
  
•     }  
  
• 
      while ( cats.scroll(PAGE_SIZE); );;  
  
•   
  
• 
      // Now get the first page of cats   
  
• 
      pageOfCats = new ArrayList();;  
  
•     cats.beforeFirst();;  
  
• 
      int i=0;  
  
• 
      while( ( PAGE_SIZE > i++ ); && cats.next(); ); pageOfCats.add( cats.get(1); );;  
  
•   
  
• }  
  

Query q = sess.createQuery("select cat.name, cat from DomesticCat cat " +
"order by cat.name");;
ScrollableResults cats = q.scroll();;
if ( cats.first(); ); {
// find the first name on each page of an alphabetical list of cats by name
firstNamesOfPages = new ArrayList();;
do {
String name = cats.getString(0);;
firstNamesOfPages.add(name);;
}
while ( cats.scroll(PAGE_SIZE); );;
// Now get the first page of cats
pageOfCats = new ArrayList();;
cats.beforeFirst();;
int i=0;
while( ( PAGE_SIZE > i++ ); && cats.next(); ); pageOfCats.add( cats.get(1); );;
}

　　

我觉得，如果处理数据库类是一个统一的基类，这种方法不适合用。

11.13 Tips & Tricks

1、Collection elements may be ordered or grouped using a query filter:

Java代码 http://www.javaeye.com/images/icon_copy.gif

• Collection orderedCollection = s.filter( collection, "order by this.amount" );;   
  
• 
  Collection counts = s.filter( collection, "select this.type, count(this); group by this.type" );;  
  

[java] view plaincopyprint?

• Collection orderedCollection = s.filter( collection, "order by this.amount" );;  
  
• 
  Collection counts = s.filter( collection, "select this.type, count(this); group by this.type" );;  
  

Collection orderedCollection = s.filter( collection, "order by this.amount" );;
Collection counts = s.filter( collection, "select this.type, count(this); group by this.type" );;

　　

2、Collections are pageable by using the Query interface with a filter:

Java代码 http://www.javaeye.com/images/icon_copy.gif

• Query q = s.createFilter( collection, "" );; // the trivial filter   
  
• q.setMaxResults(PAGE_SIZE);;   
  
• q.setFirstResult(PAGE_SIZE * pageNumber);;   
  
• List page = q.list();;  
  

[java] view plaincopyprint?

• Query q = s.createFilter( collection, "" );; // the trivial filter   
  
• q.setMaxResults(PAGE_SIZE);;  
  
• q.setFirstResult(PAGE_SIZE * pageNumber);;  
  
• List page = q.list();;  
  

Query q = s.createFilter( collection, "" );; // the trivial filter
q.setMaxResults(PAGE_SIZE);;
q.setFirstResult(PAGE_SIZE * pageNumber);;
List page = q.list();;

　　

这种方法的效率有待考察。

所有sql不用ROWNUM的解决办法

如果执行所有SQL都不用ROWNUM，那么最简单的办法如下：

1、派生Dialect类

Java代码 http://www.javaeye.com/images/icon_copy.gif

• package com.xxx.data.db.hibernate;   
  
•   
  
• 
  import  net.sf.hibernate.dialect.Oracle9Dialect;   
  
•   
  
• 
  public class Oracle9ThunisoftDialect extends Oracle9Dialect   
  
• {   
  
• 
    public Oracle9ThunisoftDialect();   
  
•   {   
  
• 
      super();;   
  
•   }   
  
•   
  
• 
    public boolean supportsLimit();   
  
•   {   
  
• 
      return false;   
  
•   }   
  
• }  
  

[java] view plaincopyprint?

• package com.xxx.data.db.hibernate;  
  
•   
  
• 
  import  net.sf.hibernate.dialect.Oracle9Dialect;  
  
•   
  
• 
  public class Oracle9ThunisoftDialect extends Oracle9Dialect  
  
• {  
  
• 
    public Oracle9ThunisoftDialect();  
  
•   {  
  
• 
      super();;  
  
•   }  
  
•   
  
• 
    public boolean supportsLimit();  
  
•   {  
  
• 
      return false;  
  
•   }  
  
• }  
  

package com.xxx.data.db.hibernate;
import  net.sf.hibernate.dialect.Oracle9Dialect;
public class Oracle9ThunisoftDialect extends Oracle9Dialect
{
public Oracle9ThunisoftDialect();
{
super();;
}
public boolean supportsLimit();
{
return false;
}
}

　　

2、修改hibernate.cfg.xml配置文件

Java代码 http://www.javaeye.com/images/icon_copy.gif

•  <property name="hibernate.dialect">com.xxx.data.db.hibernate.Oracle9ThunisoftDialect</property>   
  
•   
  
• 
  <!-- oracle 8.1.6+ -->   
  
• 
  <property name="hibernate.jdbc.use_scrollable_resultset">true</property>  
  

[java] view plaincopyprint?

•  <property name="hibernate.dialect">com.xxx.data.db.hibernate.Oracle9ThunisoftDialect</property>  
  
•   
  
• 
  <!-- oracle 8.1.6+ -->  
  
• 
  <property name="hibernate.jdbc.use_scrollable_resultset">true</property>  
  

<property name="hibernate.dialect">com.xxx.data.db.hibernate.Oracle9ThunisoftDialect</property>
<!-- oracle 8.1.6+ -->
<property name="hibernate.jdbc.use_scrollable_resultset">true</property>

　　
后记

通常情况下，这种现象很少出现，因为我们程序中缺省的order by字段的“选择性”都很大，比如缺省以日期时间排序，order by字段很少出现重复。上面的现象基本上不会出现。不过，我们公司在做压力测试的时候，同一日期时间的记录插入了N多条，从而满足了上面提到的5个前提条件，因此出现了同一记录在不同页中出现的现象。