How to Use ORDER BY and LIMIT/TOP to limit Records in HIBERNATE?

In SQL, we have been used to writing queries with ORDER BY eother DESC or ASC. And to limit the number of resocrd retrieved from teh database, we generally use LIMIT or TOP and mention the number of records that we intend to retrieve from the database.

Being in hibernate for long, I have had situations where I would require the first record of the list. At first, when I came across such a situation,, I went ahead and wrote a named query that was as follows:

select s from Student as s where s.id in (:sIdList) and s.age=:age ORDER BY s.age DESC LIMIT 1

When I executed this query, there was absolutely no errors that were visible. As such I thought that this would work. Since I was getting a getting a single record from DB, I used the following in my DAO

<br />Student student = (Student)session.getNamedQuery("findStudent")<br />   .setInteger("age", studAge)<br />   .setParameterList("sIdList", sIdList)<br />   .uniqueResult();<br />

Even this did not show up any error till there was only one record which matched the above criteria. But if you have multiple records in the DB that matches the criteria given, then you would have the following exception

You either get a NonUniqueResult exception or ClassCastException at the DAO when multiple records match the criteria. Though the ORDER BY works, the LIMIT does not limit the number of records retrieved and as such a list of obtained from the DB instead of a single record.

So, the best way to handle such cases would be the following. You can make use of ORDER BY in the query and setMaxResults(int) in the DAO to overcome this problem and to get the first record from the list. Here is how it is:

<br />Student student = (Student)session.getNamedQuery("findStudent")<br />   .setInteger("age", studAge)<br />   .setParamterList("sIdList", sIdList)<br />   .setMaxResults(1)<br />   .uniqueResult();<br />

In this case the query should have been as follows:

<br />select s from Student as s where s.id in (:sIdList) and s.age=:age ORDER BY s.age DESC <br />

I felt that this was pretty useful and hence thought of sharing with you all!! More tips ahead! Stay tuned!

If you find the information pretty helpful, I would really be happy if you would keep me posted via the comments form displayed under this article! If you had wanted some other information related to the same topic, I would suggest you to drop a note to me using the comments form for that would help me in getting back to you with the details you are in need of!