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);
q.setMaxResults(3);
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.
Years ago I tried getting a random three local (Independent Financial Advisers) from SQL Server 6.5. First attempt used an ORDER BY expression of RAND(). Kind of made me understand RDBMS better when I found it gave the same result for every column. Second attempt was something along the lines of COS(id*(1+RAND())), which should give the results a shuffle. Indeed I got a random order if I substituted the RAND() call for a random number. However, RAND() in these circumstances always appeared to give the same value.