10

I am trying to retrieve the latest revision of all entities, that have not been deleted. Doing this in SQL is very simple with an subselect:

select * from article_aud aud1
where rev in
 (select max(rev) from article_aud aud2
  where aud1.id = aud2.id)
and revtype < 2

But I do not have a clue, how to implement this via the envers API. I started with AuditReader but did not find a way, to select distinct objects

public List<Object[]> findLatestArticleRevisions(){
    List<Object[]> results = (List<Object[]>) getJpaTemplate().execute(new AuditReaderCallback() {
        @Override
        public Object doInAuditReader(AuditReader auditReader) {
            return auditReader.createQuery().forRevisionsOfEntity(Article.class, false, false)
                // TODO select distinct on entities
                .addOrder(new PropertyAuditOrder(new RevisionNumberPropertyName(), false))
                .getResultList();
        }
    });

    return results;
}

Important: I want to do this in one or at least two queries, because I have got many articles (entities) with many revisions.

Thanks a lot!

powerMicha
  • 2,753
  • 1
  • 24
  • 31

4 Answers4

10

We need to use fix of https://hibernate.atlassian.net/browse/HHH-7827 i.e. AuditEntity.revisionNumber().maximize().computeAggregationInInstanceContext().

    AuditQuery query = getAuditReader().createQuery().forRevisionsOfEntity(
            entityClass, false, false);
    query.add(AuditEntity.revisionNumber().le(revision));
    query.add(AuditEntity.revisionNumber().maximize()
            .computeAggregationInInstanceContext());
    query.addOrder(AuditEntity.revisionNumber().desc());
    return query.getResultList();

Also refer to:

Find max revision of each entity less than or equal to given revision with envers

Can hibernate envers return the latest revision of all entities of a specific type?

Community
  • 1
  • 1
charybr
  • 1,888
  • 24
  • 29
  • Is there way to get latest revision number of an entity? Say like an entity was inserted, updated twice and now it has 3 revision numbers. How can I get the last revision number? – AppSensei Jun 16 '15 at 16:31
  • Hmm this seems to work when I have multiple revisions returned in the query, the aggregate function gives me the latest (highest) revision. But if there is only one revision it returns no revision at all. Have you had this issue? – chrismacp Sep 27 '17 at 13:06
  • Actually I think misunderstood how it was working. If you filter out the latest revision of an entity, that entity is not returned. I wanted the latest version of the entity after I filtered revisions out. I.e. give me all revisions before Wednesday, and give me the latest version of the entity from that subset. Doesn't seem to work that way. – chrismacp Sep 27 '17 at 16:10
0

this will work:

    public <T extends AbstractPersistentEntity> Number latestRevision(final Class<T> entityClass,
        final long uniqueIdentifier) {
  return hibernateTemplate.execute(new HibernateCallback<Number>() {
     @Override
     public Number doInHibernate(Session session) throws HibernateException, SQLException {
        try {
           Number number = (Number) get(session).createQuery()
                    .forRevisionsOfEntity(entityClass, true, true)
                    .addProjection(AuditEntity.revisionNumber().max())
                    .add(AuditEntity.id().eq(uniqueIdentifier)).getSingleResult();
           if (number != null) {
              logger.debug("max_rev = %s for entity_class %s.%s", number,
                       entityClass.getSimpleName(), uniqueIdentifier);
           }
           return number;
        } catch (javax.persistence.NoResultException e) {
           logger.debug(
                    "unable to find revision number by[entity_class=%s,unique_identifier=%s]",
                    entityClass.getName(), uniqueIdentifier);
        }
        return null;
     }
  });

}

and then

    protected <T extends AbstractPersistentEntity> T loadForRevision(final Class<T> entityClass,
        final long uniqueIdentifier, final Number revisionNumber) {
  return (T) hibernateTemplate.execute(new HibernateCallback<Object>() {
     @Override
     public Object doInHibernate(Session session) throws HibernateException, SQLException {
        T result = get(session).find(entityClass, uniqueIdentifier, revisionNumber);
        return result;
     }
  });

}

Andrey Borisov
  • 3,160
  • 18
  • 18
  • Thanks for your answer - But I would like to find the latest revision for all entities and not for one specific. I do not have an id to query for – powerMicha Jul 31 '12 at 14:35
0

Try adding a maximized property:

[your query].add(AuditEntity.revisionNumber().maximize())
adamw
  • 8,038
  • 4
  • 28
  • 32
  • 1
    This will give me only the revision of most resent changed entity. I need to have the latest revision of each exiting entity – powerMicha Aug 01 '12 at 08:49
  • Hmm did you try? I think it should generate a query with a max() subquery as in the question. – adamw Aug 02 '12 at 08:56
  • Yes I tried. And yes, it generates a subquery with max(), but the sub query is not evaluated per id so the resulting query looks like this `select * from article_aud aud1 where rev in (select max(rev) from article_aud aud2)` The comparision by id is missing – powerMicha Aug 02 '12 at 11:30
0

The answer from @charybr works, IF you put the parathess the right way. Just add your filters that you need for the subset you want to select from directly to the maximize query part. In this way the select statements will also appear in the subquery.

    AuditQuery query = auditReader.createQuery()
            .forRevisionsOfEntity(Event.class, true, true);

    query.setFirstResult((pageable.getPageNumber()) * pageable.getPageSize());
    query.setMaxResults(pageable.getPageSize());
    query.add(
                AuditEntity.revisionNumber().maximize()
                .computeAggregationInInstanceContext()
                    .add(AuditEntity.property("critical").eq(true))
             );
    query.getResultList();

A bit late, but might help someone.

dan_23
  • 46
  • 4