Execute native SQL query

[newlukai]

Hi there,

I don't know if it's the right place for this question, but the Hibernate forum didn't help so far.
My application uses Seam 2.2.0.GA and JBoss AS 5.1.0.GA.
In my application I have to insert a bunch of entities. The entities which are inserted within one transaction should be marked with a continuous number. So, this number is the same for all entities inserted at once but differs for entities which are inserted in different transactions. The oracle database which I'm using provides a sequence for this matter.
Now I try to get the next value from this sequence. But I don't know how. Using plain SQL I can query the database in this way:

select SEQ_PRC_CHANGE_ID.nextval from dual;

I tried this:

(Long) em.createNativeQuery("select SEQ_PRC_CHANGE_ID.nextval from dual").getSingleResult()

But it doesn't work since Hibernate translates this to:

select * from ( select SEQ_PRC_CHANGE_ID.nextval from dual ) where rownum <= ?

which results in an oracle error telling me that a sequence number isn't allowed at this place.

Would be really great if I could do this without using JDBC myself. Has somebody an idea how to solve this?

Thanks in advance
Jens

[mikkus70]

Jens,

Your code is not strictly hibernate, but JPA (yes, hibernate IS providing the JPA support, but APIs are not the same and don't have the same capabilities).

Metaphisical hair-splitting aside, getSingleResult()'s name is somewhat missguiding: you're not getting a single result (intended as a single scalar value), but rather a single row of data. So, the query made by hibernate is ok, it just returns the sequence value as the sole element of a list. You can retrieve it with:

Long seqNumber = (Long)((List)em.createNativeQuery("select SEQ_PRC_CHANGE_ID.nextval from dual").getSingleResult()).get(0);

[newlukai]

Thanks! It works now.
I just thought getSingleResult() would do the same as (em.createNativeQuery("xxx").getResult()).get(0) ...