0

I would like to achieve same result as the below query using Hibernate SQL, i.e., I would like to get two random records from the table whose ID is not equal to 300. I am using Hibernate 4.1 and Oracle 11g. I ran the below query on Toad and it gives 2 random records. But, when I try to run the HQL, there is error to do with the usage of "DBMS_RANDOM.value".

SELECT * FROM
( SELECT *
FROM   table
where ID != '300'
AND q_ID=125
ORDER BY DBMS_RANDOM.value
)WHERE rownum < 3

;

I tried creating criteria and query, but both give Hibernate errors:

Hibernate Message: Invalid path: 'DBMS_RANDOM.RANDOM' [from com.model.table tab where tab.ID != '33092' ORDER BY DBMS_RANDOM.RANDOM]

and my actual hibernate query is:

Query query = session.createQuery("from table tab where tab.ID != '" +agrmId+"' ORDER BY DBMS_RANDOM.RANDOM").setMaxResults(2);

I also tried ORDER BY rand() and that gives an Oracle error.

Thank you for any help.

user3386101
  • 21
  • 3

1 Answers1

2

I solved the problem by adding a property tag in the hibernate mapping file:

<property name="constVal" formula="DBMS_RANDOM.RANDOM" type="long"/>

and then, in the POJO class, I added a variable with getter and setter methods:

private long constVal;

then, in the DAO class, I added the following query:

Criteria crit1 = session.createCriteria(table.class);
crit1.add(Restrictions.ne("id",300));
crit1.add(Restrictions.eq("quesId",125));
crit1.addOrder(Order.asc("constVal"));
crit1.setMaxResults(2);

and that solved it.

user3386101
  • 21
  • 3
  • To get this working on grails, you have to create a new property in the domain class `long constVal` then add this `constVal formula: 'dbms_random.value'` in the mapping section of the same domain class. – rodvlopes Jul 10 '17 at 18:05