Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp
Hibernate version: 3.2.2
[b]Name and version of the database you are using: Oracle 10g
Hi There,
I have a criteria I'm setting up which returns me the correct results set:
Code:
Criteria criteria = session.createCriteria(DispensingServiceAuditTrail.class)
.add(Example.create(example))
.createAlias("organisation.accreditedSystems", "as")
.add(Restrictions.in("as.partyKey", partyKeys))
.add(Restrictions.between("timestamp", fromCalendar, toCalendar)) .addOrder(Order.asc("organisationMessageIid")).addOrder(Order.asc("timestamp"));
criteria.list();
The SQL generated by this statement is:
Code:
select this_.AUDIT_TRAIL_IID as AUDIT1_0_3_,
...{Setting of lots of properties}...
from AUDIT_TRAIL
this_ left outer join SDS_ORGANISATION bosdsorgan3_ on this_.ORGANISATION_IID=bosdsorgan3_.ORGANISATION_IID
inner join SDS_ACCREDITED_SYSTEM as1_ on bosdsorgan3_.ORGANISATION_IID=as1_.SDS_ORGANISATION_IID
left outer join SDS_PERSON bosdsperso5_ on this_.PERSON_IID=bosdsperso5_.PERSON_IID
where this_.AUDIT_TRAIL_TYPE='DISPENSING_SERVICE_AUDIT_TRAIL'
and as1_.PARTY_KEY in (?, ?)
and this_.DOMAIN in (?)
and this_.MESSAGE_DIRECTION in (?, ?)
and this_.ORGANISATION_MESSAGE_IID in (?, ?, ?, ?, ?)
and this_.AUDIT_TRAIL_TIMESTAMP between ? and ?
order by this_.ORGANISATION_MESSAGE_IID asc,
this_.AUDIT_TRAIL_TIMESTAMP asc
However if I try to get do a row count query using Projections.rowCount() on exactly the same criteria:
Code:
Criteria criteria = session.createCriteria(DispensingServiceAuditTrail.class).add(Example.create(example))
.createAlias("organisation.accreditedSystems", "as")
.add(Restrictions.in("as.partyKey", partyKeys)).add(Restrictions.between("timestamp", fromCalendar, toCalendar))
.addOrder(Order.asc("organisationMessageIid")).addOrder(Order.asc("timestamp"));
criteria = criteria.setProjection(Projections.rowCount());
It generates the following SQL:
Code:
select count(*) as y0_
from AUDIT_TRAIL
this_ where this_.AUDIT_TRAIL_TYPE='DISPENSING_SERVICE_AUDIT_TRAIL'
and as1_.PARTY_KEY in (?, ?)
and this_.DOMAIN in (?)
and this_.MESSAGE_DIRECTION in (?, ?)
and this_.ORGANISATION_MESSAGE_IID in (?, ?, ?, ?, ?)
and this_.AUDIT_TRAIL_TIMESTAMP between ? and ?
order by this_.ORGANISATION_MESSAGE_IID asc,
this_.AUDIT_TRAIL_TIMESTAMP asc
As you can see, the inner join has disappeared and so I get this error:
Code:
ERROR JDBCExceptionReporter:72 - ORA-00904: "AS1_"."PARTY_KEY": invalid identifier
I have tried adding the projection in at different points but keep getting the same error.
Can you please tell me how I use the projection to maintain the same inner join as the criteria which doesn't include it?
many thanks,
Ben Temperton
Login
FAQ
Search