CODEWITHSUNDEEP
javasqlhibernatehibernate-envers
powerMicha
powerMicha

Reputation: 2773

Listing latest revision of each entity with envers

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!

Upvotes: 10

Views: 13861

Answers (4)

dan_23
dan_23

Reputation: 56

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.

Upvotes: 1

charybr
charybr

Reputation: 1948

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?

Upvotes: 11

adamw
adamw

Reputation: 8606

Try adding a maximized property:

[your query].add(AuditEntity.revisionNumber().maximize())

Upvotes: 0

Andrey Borisov
Andrey Borisov

Reputation: 3170

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;
     }
  });

}

Upvotes: 0

Related Questions