SQl Query to Hibernate Query

Question

I have a MYSQL query that I use to retrieve random rows from a table. The query is :

SELECT * FROM QUESTION WHERE TESTID=1 ORDER BY RAND() LIMIT 10;

Now I need to change this query to Hibernate. Did a bit of Googling but couldn't find the answer. Can someone provide help on this. Thanks

Answer 1

According to this post, you can do that :

String query = "from QUESTION order by newid()";
Query q = session.createQuery(query);
q.setMaxResults(10);

Not sure if it will work (especially for the random part), but you can try it :)

Answer 2

The random function is different between each underlying DB and is not a standard part of SQL92.

Given that you will need to implement a SQLDialect for the given database type you are using.

eg:

class PostgresSQLDialect extends org.hibernate.dialect.PostgreSQLDialect {
    PostgresSQLDialect() {
        super()
        registerFunction( "rand", new NoArgSQLFunction("random", Hibernate.DOUBLE) );
    }
}

Then you will need to define that dialect in the config

hibernate {
    dialect='com.mycompany.sql.PostgresSQLDialect'
}