Envers & Hibernate Criteria Query on Insert Date

[szvx4x]

We recently added Envers to our project and, as part of that, removed the insert date and userid from our entity since that is not business information and will be maintained in the RevInfo.  Now I am stuck trying to figure out how to create dynamic search functionality that includes the insert date.  Previously, we were using Hibernate Criteria queries to create these dynamic queries and that work great when all the data resides on the entity.  Since we have no entities for the aud tables I am stuck.  I have looked at the AuditReader but am thinking that only gives me access to the aud data and not the enitity itself.  Can someone point me in the right direction?

[sebp]

Hi Lisa,

for audit queries have a look at

http://docs.jboss.org/envers/docs/index.html#queries

You cannot use Hibernate Criteria with envers but you can create queries with the AuditReader and add criterions to the queries. For the criterions you can access the entities properties with AuditEntity.property("propertyName") and you can access the revision entity properties with AuditEntity.revisionProperty("propertyName").

[szvx4x]

Thanks!! That worked

[szvx4x]

Ok - I jumped the gun on thinking I had this working - turns out it isn't getting me quite what I need.  I have an entity that has a status along with some other properties.  Insert date is now derived from the revinfo.  I need to be able to generate dynamic queries, hopefully via the auditReader, where the insert date (from the revinfo) is between a provided start and end date and the current value of other attributes on the entity are equal to provided values.  What I am getting now is the first revision of all the entities that match the criteria instead of the current revision of the entities:  

AuditReader reader = AuditReaderFactory.get(entityManager);

AuditQuery query = reader.createQuery().forRevisionsOfEntity(Transmittal.class,true, true);

query.addOrder(AuditEntity.property("store").desc());

query.setFirstResult(firstResult);

query.setMaxResults(maxResults);

query.add(AuditEntity.property("status").eq(trnRecStatus));

query.add(AuditEntity.revisionType().eq(RevisionType.ADD));

query.add(AuditEntity.revisionProperty("revDate").between(startDate, endDate));

List<Transmittal> revisions = query.getResultList();

Is is possible to do this via the AuditReader?  I have tried adding the criteria for the insert date as follows as well but, in this case, I only get one enitity (the one with the minimum date) returned instead of all that meet the criteria:

AuditCriterion auditCriterion = AuditEntity.revisionProperty("revDate").minimize()

  .add(AuditEntity.revisionProperty("revDate").between(tranDate, endDate));

query.add(auditCriterion);

Thoughts?  Ideas for me?

[adamw]

And what if you try to maximize the revision number?

Adam

[szvx4x]

I added

query.add(AuditEntity.revisionNumber().maximize());

and now I get no entities at all.  query.add(AuditEntity.revisionType().eq(RevisionType.ADD)); is asking for the first revision.  There are multiple revisions so query.add(AuditEntity.revisionNumber().maximize()); results in no entities matching the criteria.

[adamw]

Or the simplest solution: query for the whole history, sort by revision number descending and limit the resulst to 1.

Adam

[szvx4x]

I went around and around with this and came to the conclusion that I needed two seperate queries to get what I need since I am looking for a set of rows where the insert date is in a particular range (thus I need the revdate from the first revision) and the current value of other attributes of the entity are specific values (thus I need the most current revision of each entity or the actual enity itself).  What I could not seem to get working was some kind of "group by" functionality where I could get the most recent revision of each entity in the set. 

Solution:  I ended up getting the set of entities where the first revision falls into the supplied date range and then using that set of entity ids as an additional parameter to a second regular Hibernate criteria query along with the other params specifying remaining current value criteria.  My other option was HQL but since the query parameters are variable and we wanted the flexibility to build the query dynamically we did not want to go that route.  In addition, the queries were initially structured as Hibernate citeria queries so it didn't seem to make sense to have to convert it all to HQL simply because the insert date was no longer on the entity.  Bottom line, since the insert date was moved off the entity to the revinfo, it seems like building a dynamic query that includes the insert date now requires a less than ideal combination of AuditReader querying and hibernate querying.  If I missed something in my research or experimentation, please enlighten me.

[adamw]

Hmm yes, I don't see a nice way to do it with one query. The main problem is that you want to use the first and last revisions in the "where" part, but select only the last revision. Which isn't something you can do with SQL. So two queries are probably optimal

Adam