Hibernate: Returning N records in a random order
I was pleasantly surprised by Hibernate yesterday when I tried need to have a web page show a listing of 3 randomly chosen articles. Using SQL Server, you’d write a query like this:
SELECT top 3 * FROM mytable ORDER BY newid()
The ‘top n’ syntax is specific to SQL Server, which makes it non portable. I saw that the Query class has a method that allows you to restrict the number of rows that are returned from a query:
String query = "SELECT * FROM mytable ORDER BY newid()";
Query q = session.createQuery(query);
but I thought that would be inefficient because Hibernate would have to retrieve a large resultset and then only return the first n rows. Turns out that Hibernate actually figures out that SQL Server is the underlying platform and uses the ‘TOP’ keyword [look at the getLimitString() method]. Hibernate has made what used to be proprietary SQL Server sql, portable across any database platform that Hibernate supports. Case in point — if you use the same method
q.setMaxResults(3) when using MySQL, Hibernate will automatically use the MySQLDialect and substitute the limit keyword that MySQL uses.
Of course, Hibernate can’t do everything… yet. The second part of my query ordered the results in a random fashion. You can do that in SQL Server using the newid() function; MySQL accomplishes the same thing with the rand() function.