Hibernate、oracle分页、order by问题

最新推荐文章于 2020-11-27 19:34:43 发布
转载 最新推荐文章于 2020-11-27 19:34:43 发布 · 902 阅读 · 0
文章标签：

#hibernate #oracle #rownum #order #错乱

探讨了在Oracle数据库中使用ROWNUM进行分页时遇到的问题，特别是在SQL包含ORDER BY子句时出现的数据混乱现象，并提供了多种解决方案。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

引用Hibernate处理Oracle的分页，是用rownum。
代码如下：
Java代码 

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();;  
}

Java代码 

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代码 

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); );;  

}

Java代码 

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代码 
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" );;  
Java代码  
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代码 

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();;  
Java代码  
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代码 

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;  
  }  
}

Java代码 

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代码 

<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>  
Java代码  
 <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个前提条件，因此出现了同一记录在不同页中出现的现象。

Java 开发：利用 Hibernate 实现分页查询
AI开发架构师
04-30 985
本文旨在为Java开发者提供使用Hibernate实现高效分页查询的全面指南。Hibernate分页的核心API不同数据库的分页语法差异处理性能优化策略与Spring框架的集成大数据量场景下的特殊处理文章首先介绍Hibernate分页的基础知识，然后深入探讨实现细节和优化策略。随后提供实际代码示例和性能分析，最后讨论高级主题和未来发展趋势。: 对象关系映射，将数据库表与Java对象进行映射的技术分页查询(Pagination): 将大数据集分成多个小部分(页)进行查询和显示的技术。
Hibernate实战——Set集合属性
实践求真知
09-17 509
一配置文件 <?xml version="1.0" encoding="GBK"?> <!-- 指定Hibernate配置文件的DTD信息 --> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http:...
hibernate 带order by 的 分页
gaoshanwushui的专栏
07-22 2451
近日做网站，商户按照设定的优先级排序分页显示。翻页有记录重复或者遗失，很难找到规律，非常奇怪。最后发现原来是hibernate order by的分页在 Oracle 下因为 oder by 的字段重复，而导致的。。解决方案：order by的时候必须带上unique的字段，例如主键或者rowid等。
Hibernate排序
foart的专栏
08-14 2728
在查询对象的Set或Map成员时，您可以对其进行排序，排序可以在两个层次进行，一个是在Java执行环境中进行，一个是利用数据库本身的排序功能。 Java执行环境中进行排序：－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－如果要在Java执行环境中进行排序，可以映像文件中设定sort属性，例如若为Set，则如下设定： 藉由指定sort为natural，Hiberna
在用hibernate实现oracle 分页查询时报错ORA-00923: FROM keyword not found where expected
创业的程序员
10-13 3968
  Query q=super.getSession().createQuery("from Eventinfoall");             q.setFirstResult(1);             q.setMaxResults(3); 用hibernate实现mysql sqlserver 分页时使用这种方法都没有问题,但是用oracle时就出现问题,老
oracle null转0_使用Oracle和hibernate实现分类：Oracle分页原理
weixin_39781783的博客
11-27 80
首先建立个表：create table USERS( USERID VARCHAR2(20), USERNAME VARCHAR2(20), AGE NUMBER)一、分页原理 由于在Oracle中的sql没有limit ,所以不能使用limit对数据进行分割。那么oracle有个字段叫做rownumselect rownum,t.* from users t，我们利用rownum进行数据分割比如...
Oracle的SQL分页实践
07-26
使用JDBC时，可以结合LIMIT和OFFSET子句（Oracle不支持LIMIT，但可以通过ROWNUM模拟）来实现分页，而在Hibernate中，可以使用Criteria API或HQL的`setFirstResult`和`setMaxResults`方法来实现相同的功能。...
oracle分页查询
03-07
在 Oracle 中，使用 ORDER BY 语句对数据进行排序，然后使用 ROWNUM 函数来限制输出结果集的范围。但是，需要注意的是，Oracle 在执行查询时，会首先执行 ORDER BY 语句，然后执行 ROWNUM 函数。如果想取得排序后的...
oracle 分页完整代码
02-01
在Oracle数据库中，分页查询是一项非常常见的操作，特别是在处理大量数据时，为了提高用户体验，我们需要将结果集分块加载，这就是所谓的分页。在这个Java和Oracle结合的分页实现中，我们将探讨如何通过Java后端配合...
Hibernate+Struts2+Spring+Oracle分页亲测
01-27
标题中的“Hibernate+Struts2+Spring+Oracle分页亲测”揭示了这个项目或教程是关于使用四个关键的Java Web开发技术实现分页功能的。这四个技术分别是Hibernate（一个对象关系映射框架），Struts2（一个MVC框架），...
Oracle中慎用带有order by的分页
oldwolf1987的专栏
04-05 5853
Hibernate处理Oracle的分页，是用rownum。 代码如下：Java代码 public String getLimitString(String sql, boolean hasOffset);    {     StringBuffer pagingSelect = new StringBuffer(sql.length(); + 100);;   
Hibernate中的order by子句
07-30 3116
一、概述 二、order by子句使用 public void testOrderByClause(){            String hql= " from Country as country order by country.ccode";            Session session=CustomSessionFactory.
hibernate之order by子句
栖梧桐的博客
12-21 1799
hibernate之order by子句
hibernate注解 @OrderBy
fengxue_love的博客
04-13 3543
@OrderBy 用于实体中的集合的排序，指定排序字段，默认asc，可desc 例1：多对多映射 @ManyToMany(targetEntity = Menu.class,fetch = FetchType.LAZY) @JoinTable(name="music_role_menu",joinColumns={@JoinColumn(name="role_id")},inver
Hibernate映射集合属性4__排序用的order-by属性
辐_射的博客
05-06 1682
<!-- addressSet属性,Set集合 table属性：集合表的名称 key子元素：集合外键的列名 element子元素：存放集合元素列的信息 order-by属性：写的是order by 子句,是SQL语句,是操作的集合表 这是在查询数据时指定order by子句 -->
hibernate配置文件中order-by用法
missyz的专栏
04-22 1761
针对有一对多关系的关联表， 对主表下集合排序情况                                                                      注意： inqu_interTime 为数据库中字表列名 碰见几次才着手解决，给大家分享下。
Hibernate 一用order by 查询分页就会有重复记录
小虫一两只
08-15 408
估计很可能是你排序的时候没考虑全面。可能是你加的ORDER BY的排序字段的内容不是唯一的。 例如你按录入日期(lrrq)排序(即 order by lrrq)，而lrrq又只是“yyyy-MM-dd”的格式而不是时间戳的格式的话，你的排序字段不唯一(有可能两条以上的数据录入日期都是 2008-11-26 )，在分页的时候第一页的内容有可能还会在第二页中出现。 解决办法也...
hibernate注解排序@orderBy
Daily one note for Java
12-06 6354
[html] view plain copy @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER, mappedBy = "province")   @OrderBy(clause = "cityId asc")   public SetCity> getCities() {       r
Oracle分页实现源代码分享与教程
SELECT * FROM table_name ORDER BY some_column OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY; ``` 以上SQL将返回第二页的数据，其中OFFSET后的10表示跳过第一页的10条记录，FETCH NEXT的10表示获取接下来的10条记录。 ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值