Hibernate: Returning N records in a random order<\/p>\n
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: 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.<\/p>\n","protected":false},"excerpt":{"rendered":" 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 … Continue reading Hibernate: Returning N records in a random order<\/span>
\nSELECT top 3 * FROM mytable ORDER BY newid()<\/code>
\nThe ‘top n’<\/a> 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:
\n
\nString query = \"SELECT * FROM mytable ORDER BY newid()\";
\nQuery q = session.createQuery(query);
\nq.setMaxResults(3);
\n<\/code>
\nbut 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<\/a> [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)<\/code> when using MySQL, Hibernate will automatically use the MySQLDialect and substitute the limit<\/a> keyword that MySQL uses<\/a>. <\/p>\n