Querydsl: NumberExpression.random() is not working

Created on 12 May 2016 · 5Comments · Source: querydsl/querydsl

I have same issue
http://stackoverflow.com/questions/15869279/does-querydsl-not-support-rand

I'm trying to run this query.
query.from(test) .orderBy(NumberExpression.random().asc()) .limit(20) .list(test);

It is shown query
select test0_.id as id1_46_ from test test0_ order by random() asc

And I get this error
FUNCTION random does not exist

Mysql version is

mysql Ver 14.14 Distrib 5.7.11, for Linux (x86_64) using EditLine wrapper

I'm using querydsl

com.mysema.querydsl
querydsl-apt
3.6.7

com.mysema.querydsl
querydsl-jpa
3.6.7

mysql supported RAND() function.
http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html#function_rand

So, for mysql, don't i have to use rand()?

resolved

Source

okihouse

Most helpful comment

Which JPA Provider do you use?
For Hibernate you can extend a Dialect and register custom functions.

I just remembered that for others you can also use the special function(name, [arg,[arg...]]_ function, to call arbitrary SQL functions.
However, that doesn't work in Hibernate.

Then you can use Querydsl Template Expressions to serialize them into the query.

Expressions.numberTemplate(Double.class, "function('rand')");

Shredder121 on 13 May 2016

👍3 😄2

All 5 comments

Hi,
It seems to me that you are using JPQL (indicated by the querydsl-jpa dependency).
JPQL doesn't have a random() function, but you can make your JPA provider swap out random() for the actual random function specific to the dialect.

Shredder121 on 12 May 2016

@Shredder121

Thank you for letting me know.

Please can you show me how to use JPA provider to swap out the random()?

okihouse on 13 May 2016

Which JPA Provider do you use?
For Hibernate you can extend a Dialect and register custom functions.

I just remembered that for others you can also use the special function(name, [arg,[arg...]]_ function, to call arbitrary SQL functions.
However, that doesn't work in Hibernate.

Then you can use Querydsl Template Expressions to serialize them into the query.

Expressions.numberTemplate(Double.class, "function('rand')");

Shredder121 on 13 May 2016

👍3 😄2

@Shredder121

thank you :D

below code is success result

.orderBy(Expressions.numberTemplate(Double.class, "function('rand')").asc())

jxin19 on 15 Oct 2019

if you have similar error like below, please use
Expressions.numberTemplate(Double.class, "RAND()"); instead.
I'm using java spring with mysql

PM org.hibernate.hql.internal.ast.ErrorCounter reportError
ERROR: line 8:25: expecting COMMA, found ')'
line 8:25: expecting COMMA, found ')'
    at antlr.Parser.match(Parser.java:211)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.jpaFunctionSyntax(HqlBaseParser.java:4107)