Getting error when querying the audit table using haschanged

Question

I have Template entity which has:

@ManyToOne
    @JsonView(JsonDefinitionMapper.SecondLevel.class)   
    @Audited
    private TemplateType templateType;

instance.

When I am going to query the all the changes using loop through entity properties as below (properties are get by metadata of entity) :

for(String property:PropertiesList){
newValue = auditReader.createQuery()
                                            .forRevisionsOfEntity(Template.class, false, true)
                                            .addProjection(AuditEntity.property(property))
                                            .add(AuditEntity.property(property).hasChanged())
                                            .add(AuditEntity.id().eq(templateId))
                                            .add(AuditEntity.revisionNumber().eq(revisionNumber)).getSingleResult();
}

the generated Template_AUD table has template_type_mod,template_type_id columns(Auto generated)

I am getting this error when running above query:

org.hibernate.QueryException: could not resolve property: templateType of: com.templates.domain.Template_AUD [select e__.templateType from com.templates.domain.Template_AUD e__, com.template.domain.AuditedRevisionEntity r where e__.templateType_MOD = :_p0 and e__.originalId.id = :_p1 and e__.originalId.REV.id = :_p2 and e__.originalId.REV.id = r.id]
at org.hibernate.QueryException.generateQueryException(QueryException.java:120)
at org.hibernate.QueryException.wrapWithQueryString(QueryException.java:103)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:218)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:142) at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:115) at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:76) at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:150) at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:302) at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:240) at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1894) at org.hibernate.envers.internal.tools.query.QueryBuilder.toQuery(QueryBuilder.java:226) at org.hibernate.envers.query.internal.impl.AbstractAuditQuery.buildQuery(AbstractAuditQuery.java:79) at org.hibernate.envers.query.internal.impl.AbstractAuditQuery.buildAndExecuteQuery(AbstractAuditQuery.java:85) at org.hibernate.envers.query.internal.impl.RevisionsOfEntityQuery.list(RevisionsOfEntityQuery.java:108) at org.hibernate.envers.query.internal.impl.AbstractAuditQuery.getSingleResult(AbstractAuditQuery.java:97) at com.template.dataRepository.TemplateRevisionRepository.getAllChangedPropertiesWithREvisions(TemplateRevisionRepository.java:211) at com.template.dataRepository.TemplateRevisionRepository$$FastClassBySpringCGLIB$$5bf5efd1.invoke() at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:720) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655) at com.template.dataRepository.TemplateRevisionRepository$$EnhancerBySpringCGLIB$$952a9450.getAllChangedPropertiesWithREvisions() at com.template.EnversTest.getAllChangedPropertiesWithREvisions(EnversTest.java:151) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75) at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86) at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84) at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94) at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290) at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71) at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288) at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58) at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268) at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61) at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70) at org.junit.runners.ParentRunner.run(ParentRunner.java:363) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191) at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86) at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:678) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)

i am using postgres database

Answer 1

My initial idea was to leverage the new relation traversal API added in Envers 5.2 by which you would have written your query much like the following:

final List results = auditReader.createQuery()
  .forEntitiesModifiedAtRevision( Template.class, revNo )
  .traverseRelation( "templateType", JoinType.INNER )
    .addProjection( AuditEntity.selectEntity( false ) )
    .up()
  .add( AuditEntity.property( "templateType" ).hasChanged() )
  .getResultList();

The idea here is that we'd basically request that the TemplateType be returned in the resulting list but only if it was deemed modified on the root entity, Template; however, this query introduces what I believe is a bug:

org.hibernate.QueryException: Named parameter [revision] not set

This is because the subquery added to the root query specifies a named parameter revision; however, the query logic never binds the revision value; leading to this problem.

I've filed issue HHH-11981 to address this problem.

So for now, the only suggestion I have is if your entity class has anything beyond basic type properties, you'll likely need to resort to using reflection in your code to actually obtain the values from the returned bean

final List results = auditReader.createQuery()
  .forEntitiesModifiedAtRevision( Template.class, revNo )
  .add( AuditEntity.property( propertyName ).hasChanged() )
  .getResultList();

For the entry in the list, you'd need to use reflection to call the appropriate getter for the appropriate property identified by propertyName. It is less than ideal but at least circumvents the query problems you're facing.