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

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'
sb1977Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

objectsCommented:
depends on your mappings
0
mrigankCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sb1977Author Commented:
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)")
   .setParameter("user",userName)
   .setMaxResults(6).list();

Thanks for your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.