DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. JPQL - Pagination on Oracle Database with Hibernate

JPQL - Pagination on Oracle Database with Hibernate

Michal Jastak user avatar by
Michal Jastak
·
Aug. 28, 12 · Interview
Like (0)
Save
Tweet
Share
25.38K Views

Join the DZone community and get the full member experience.

Join For Free

In your daily work, you rely on many different libraries, trusting they will serve you well, being perfect piece of code ... do you? ... really?! Then it's time to realize that you are perfectly wrong :) Increasing complexity of code leads to new possibilities of making errors :) Many of them are lurking in the libraries used by you, even if they are used for years by thousands of developers.

Let's find some example. Suppose that we are using JPA, and have an entity named Employee, which contains at least two properties: name and id. Suppose that we want to display all employees ordered by name, and paginated. To fetch them from the underlying database we will need JPQL query like this:

 select e from Employee e order by e.name

We will paginate it using setFirstResult and setMaxResults methods of javax.persistence.Query interface. Now we need a JPA provider and database to make it work, let's choose Hibernate, and Oracle (10+).

At first try everything works perfectly :) - but let's assume that we have employees sharing the same name, for ex. 20 of them having name 'Smith' (identifiers between 1 and 20), 10 having name 'Donovan' (identifiers between 21 and 30) and 10 having name 'Johnson' (identifiers between 31 and 40) - total 40 employees. Let's try to display 5 employees on single page, and see what will happen:

Page 1 - employees having ID: 21, 25, 24, 23, 22 - all having name 'Donovan' - good :)
Page 2 - employees having ID: 26, 25, 24, 23, 22 - all having name 'Donovan' - but 4 of them were already displayed on first page (!)
Page 3 - employees having ID: 31, 35, 34, 33, 32 - here comes the 'Johnson' name - good again :)
Page 4 - employees having ID: 36, 35, 34, 33, 32 - 'Johnson' again, and again 4 of them were already displayed on third page (!!)
Page 5 - employees having ID: 1, 17, 18, 19, 20 - here comes the 'Smith' name - good again :)
and finally the real surprise - Pages 6, 7 and 8 contains same employees - having ID: 16, 17, 18, 19, 20

Don't you think something is wrong here ?! ;) Well, the reason of this strange error is visible when you check SQL queries generated by Hibernate:

select * from (
    select
        employee0_.EMPLOYEE_ID as EMPLOYEE1_0_, employee0_.DATE_OF_BIRTH as DATE2_0_,
        employee0_.EMPLOYEE_NAME as EMPLOYEE3_0_
    from EMPLOYEE employee0_
    order by employee0_.EMPLOYEE_NAME
    ) where rownum <= 5

for the first 5 rows and:

select * from (
    select
        row_.*, rownum rownum_
    from (
        select
            employee0_.EMPLOYEE_ID as EMPLOYEE1_0_, employee0_.DATE_OF_BIRTH as DATE2_0_,
            employee0_.EMPLOYEE_NAME as EMPLOYEE3_0_
        from EMPLOYEE employee0_
        order by employee0_.EMPLOYEE_NAME
    ) row_
    where rownum <= 10)
where rownum_ > 5

for the rows 6 - 10 (and similar for next pages).

What the heck?! - you may say - These beautiful SQL queries are suggested for pagination on Oracle's website - see Tom Kyte's article: On ROWNUM and Limiting Results - sure :) - but the one who implemented it in Hibernate didn't read this article too deeply, skipping this important part:

One important thing about using this pagination query is that the ORDER BY statement should order by something unique. If what you are ordering by is not unique, you should add something to the end of the ORDER BY to make it so.

As you see, correct JPQL query leads to invalid SQL query for Hibernate / Oracle combination, and the error shows himself only for some combinations of data in the database - it is very difficult to spot because of that.

Do you still think that libraries used by you are perfect? :)

PS: This article has been created thanks to one of my Colleagues - Joanna Głowińska - her awesome work on SQL queries in some of our projects lead me to the above thoughts.
 

Database Hibernate Oracle Database sql Library Data (computing) IT Interface (computing)

Published at DZone with permission of Michal Jastak, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Cloud-Native Application Networking
  • Top 5 PHP REST API Frameworks
  • Remote Debugging Dangers and Pitfalls
  • Three SQL Keywords in QuestDB for Finding Missing Data

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: