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?

Answer 1

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'
}

Answer 2

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 :)