Hi.
I need a nested query to lock some rows (oracle-db).
Native sql looks like this:
Code:
select * from (
select * from person p where p.name = :name order by p.name
) where rownum < 10 for update p.id
My experience with hibernate:
First try:
Code:
List personen = session.createQuery("from PERSON as p where p.name = ? order by p.name")
.setString(0, argName )
.setMaxResults(10)
.setLockMode("p", LockMode.UPGRADE)
.list();
The generated sql was wrong (a known hibernate bug:-( in my version). Hibernate mixes the aliases. Without alias, i can not set a LockMode()..
Second try:
I try to use a detached Subquery. But this generate sql looks like this:
Code:
SELECT * FROM person p
WHERE p.id = (SELECT p2.id FROM person as p2 WHERE .. ORDER BY p2.name)
AND rownum < 10
The order by Statement is not allowed inside a nested Query and i get an error (ORA-00907: missing right parenthesis).
Our person class has any subclasses with different discriminator values. Therefore i won't use a native sql with hard coded discriminator values inside.
Does anybody know a suggestion without native sql?
best regards
Login
FAQ
Search