4 Replies Latest reply on Oct 16, 2013 5:28 PM by e3aeb444-ff39-44cc-b23c-747d1945ca99

    ORDER BY RAND() with EclipseLink ??

      how to implement ORDER BY RAND() with EclipseLink ?

      * I want to execute a MySql query that returns a collection sorted randomly ....
        • 1. Re: ORDER BY RAND() with EclipseLink ??
          You can do this using an Expression query.

          There is no direct support for rand() but you can access using getFunction() or using fromLiteral(). getFunction() is normally used for functions that take one argument, so fromLiteral() may work better.
          You could use getFunction() if you defined your own ExpressionOperator for rand().

          ReadAllQuery query = new ReadAllQuery(Employee.class);
          query.addOrdering(query.getExpressionBuilder().fromLiteral("RAND()", query.getExpressionBuilder()));

          Please also log a bug on EclipseLink to have rand() support added.

          James : http://www.eclipselink.org : http://en.wikibooks.org/wiki/Java_Persistence
          • 2. Re: ORDER BY RAND() with EclipseLink ??
            very nice, thank you!

            * I don't agree with a native support of the RAND() function since there is no standard way of doing that in SQL. So, for each DB you need a different function. To have a chance to call these functions with EclipseLink seems enough.
            • 3. Re: ORDER BY RAND() with EclipseLink ??
              The online sample:


              F5 to refresh and see the order of the advertisements changing :)

              and the same data loaded under a GUI:


              * the gui was not yet updated to support the dynamic order, but it is coming soon :)

              Edited by: Felipe Gaúcho on Nov 20, 2009 10:12 AM

              Edited by: Felipe Gaúcho on Nov 20, 2009 10:12 AM
              • 4. Re: ORDER BY RAND() with EclipseLink ??

                This is an old post but I was trying to achieve the same with Criteria API and couldn't find the "right" way.

                This was the only post that helped when googling for a solution, hope this can help to someone else too.


                Currently this works for me using Criteria API:

                cq.orderBy(criteriaBuilder.asc(criteriaBuilder.function("RANDOM", Void.class)));


                Using what James post suggests with Criteria API:


                will not work.


                Note: I'm using PostgreSQL so that's why I use RANDOM function instad of RAND.




                Marc Nuri