|
Description:
|
I have a DAO method that does the following:
{code:borderStyle=solid}
return this.createNamedQuery( "systemTasks(machineId,status,maxExecuteTimestamp)" )
.setParameter( "machineId", machineId )
.setParameter( "status", status )
.setParameter( "maxExecuteTimestamp", maxExecuteTimestamp )
.setLockMode( LockModeType.PESSIMISTIC_WRITE )
.setMaxResults( 1 )
.getSingleResult();
{code}
HQL
The JPQL
is:
{code:borderStyle=solid}
select st from SystemTask st
where st.machineId = :machineId and st.status = :status
and st.executeTimestamp <= :maxExecuteTimestamp
order by st.executeTimestamp
{code}
Hibernate generates the following SQL:
{code:borderStyle=solid}
select * from (
select [column list]
from jaseadm.system_task systemtask0_
where systemtask0_.machine_id=?
and systemtask0_.task_status_id=?
and systemtask0_.execute_timestamp<=?
order by systemtask0_.execute_timestamp )
where rownum <= ? for update
{code}
Which fails with:
{noformat}
ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
{noformat}
This means that:
- setLockMode (pessimistic) can't be used with setMaxResults
The problem is automatic setting of max results
when
there's an order specified, because the oracle dialect only accommodates the "for update" clause and doesn't do anything with an "order by" clause.
- setLockMode (pessimistic) can't be used with
{{
getSingleResult
when there's an order by specified, because Hibernate indiscriminately calls setMaxResults in org.hibernate.ejb.getSingleResult()
}} is called
:
{code:borderStyle=solid}
boolean mucked = false;
// IMPL NOTE : the mucking with max results here is attempting to help the user from shooting themselves
// in the foot in the case where they have a large query by limiting the query results to 2 max
// SQLQuery cannot be safely paginated, leaving the user's choice here.
if ( getSpecifiedMaxResults() != 1 &&
! ( SQLQuery.class.isAssignableFrom( query.getClass() ) ) ) {
mucked = true;
query.setMaxResults( 2 ); //avoid OOME if the list is huge
}
List<X> result = query.list();
if ( mucked ) {
query.setMaxResults( getSpecifiedMaxResults() );
}
{code}
Honestly I would prefer the OOM prevention stuff be removed, or configurable through the session factory, as I end up with a lot of natural key queries that look like:
select * from ( ... ) where rownum <= 1
When I haven't called setMaxResults myself.
Related to HHH-3298 and HHH-1168 (7 years unresolved). Note that unlike HH-1168, this breaks getSingleResult as well due to the whole "mucked" code.
|
