how to return rownum column value with HQL? (using oracle db)

Question

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.

Answer 1

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

Answer 2

If you really want to do it in the mapping, you might try to define it as a formula instead of a column. Hibernate may not pre-pend the table name if it doesn't recognize it as a column.