Using Hibernate Query Language to get the top rows of query results

Posted on 2006-04-04
Last Modified: 2008-02-07
Hello - I need to use Hibernate to query my SQL Server database and determine if a new password equals any of the user's past 6 passwords.  Below is a query that will search the PASSWORD_HISTORY table and if no records are returned, it is ok to use the new one.  Can you please let me know how I would do this using HQL?

Thank you

SELECT password FROM
(SELECT TOP 6 password FROM password_history WHERE user_id = userId01 ORDER BY pwd_stamp) AS PWD
WHERE password = 'password01'
Question by:sb1977
    LVL 92

    Expert Comment

    depends on your mappings
    LVL 5

    Accepted Solution

    Write an HQL to return all the previous passwords for that User as a List.
    Order by the timestamp.

    Then while iterating through the List, go only  through the first 6 entries in the list.

    Author Comment

    I was trying to speed up the time it takes to execute the query, so I was thinking about using "count" and using native sql inside hibernate.  Unfortunately, I didn't have much luck with that, but did find a way to specify only returning the top six rows, but I still have to iterate through the list.  Here is what I came up with:

    results = session.createQuery("FROM PasswordHistory AS pwdHis WHERE pwdHis.username = :user)")

    Thanks for your help

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
    Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
    Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
    Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now