how to return rownum column value with HQL? (using oracle db)
17,450
ROWNUM doesn't belong to any table, so your query should be:
"select rownum from Dog as dog where ..."
For example:
SQL> select emp.ename, rownum from emp;
ENAME ROWNUM
---------- ----------
SMITH 1
ALLEN 2
WARD 3
JONES 4
MARTIN 5
BLAKE 6
CLARK 7
SCOTT 8
KING 9
TURNER 10
Author by
jedierikb
OB1 Kenobi was the result of an Off-By-One [OB1] error in the clone wars.
Updated on June 04, 2022Comments
-
jedierikb almost 2 years
I have a complicated HQL query.
I would like to access the Oracle specific rownum column value as part of my returned results. How do I write my query (and/or change my hbm.xml) to support this?
What I have tried so far does not work:
modifying my hbm.xml
<property name="rownum" type="int" update="false" insert="false" generated="never"/>
and a query such as:
"select dog.rownum from Dog as dog where ..."
But I get java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification
I suspect I might need to mix some sql with my complex hql query... suggestions welcome. Thanks.
Some background:
- http://ronfrancis.wordpress.com/2007/10/16/dont-forget-an-order-by-for-pagination-queries/
- hibernate uses oracle's rownum 'under the hood' to paginate results.
-
riroo about 7 yearsfor such a statement what would hibernate return in java ? a List<Map<String,Long>> ?
-
Tony Andrews about 7 years@miss_R Sorry I don't know