How can I write:
SELECT TOP 1 x
FROM y
ORDER BY NEWID()
using querydsl-sql?
(See https://stackoverflow.com/a/4980733/14731 for an explanation of what the query does)
I ended up doing the following:
import com.mysema.query.types.expr.StringExpression;
import com.mysema.query.types.template.StringTemplate;
/**
* SQL Server specific expressions.
*
* @author Gili Tzabari
*/
public final class CustomExpressions {
private static final StringExpression newId = StringTemplate.create("NEWID()");
/**
* Prevent construction.
*/
private CustomExpressions() {
}
/**
* @return NEWID()
*/
public static StringExpression newId() {
return newId;
}
}
[...]
CustomExpressions expressions = new CustomExpressions();
new SQLQuery(connection, configuration).query().from(y).
orderBy(expressions.newId().asc()).
limit(1).uniqueResult(x);