0 Replies Latest reply on Sep 14, 2018 11:03 AM by Sarath K S

    Envers AuditQuery is adding _id to mappedBy column and is failing with exception "can not resolve property"

    Sarath K S Newbie

      Hi,

       

      I am using Envers to audit my entities. But when I use the AuditQuery to fetch the revisions of my entity along with the associated entity, it fails with the following exception:

       

      "could not resolve property: userid_id of: com.envers.domain.vo.UserOrgVO_AUD [select e__ from com.envers.domain.vo.UserOrgVO_AUD e__ where e__.userid_id = :userid_id and e__.originalId.REVISION_ID.id = (select max(e2__.originalId.REVISION_ID.id) from com.envers.domain.vo.UserOrgVO_AUD e2__ where e2__.originalId.REVISION_ID.id <= :revision and e__.originalId.id = e2__.originalId.id) and REVISION_TYPE != :delrevisiontype]",

       

      My entities are as follows:

      User(id: Long, name: String, organisations: Set<UserOrganisation>)

      UserOrganisation(id: Long, userid: Long, organisation: Organisation)

      Organisation(id:Long, name: String)

       

      The VO classes are defined as follows

       

      @Entity(name = "USERS")

      @Audited
      public class UserVO {

        @Id
        @Column(name = "id")

         private Long id;

         @Column(nullable = false)

         private String name;

         @OneToMany (fetch = FetchType.EAGER, mappedBy = "userid", cascade = CascadeType.ALL, orphanRemoval = true)

         @Audited
         private List<UserOrgVO> organisations;


         ............................................................

         .. constructors, getters and setters ....

         ............................................................

      }

       

      @Entity(name = "USER_ORGANISATIONS")

      @Audited
      public class UserOrgVO {

        @Id
        @Column(name = "ID")

         private Long id;

         @Column (name = "USER_ID")

         private Long userid;

        @ManyToOne
        @JoinColumn (name = "ORGANISATION_ID", nullable = false)

         private OrganisationVO organisation;

         @Column (name = "PREFERRED", nullable = false)

         private boolean preferred = false;


         ............................................................

         .. constructors, getters and setters ....

         ............................................................

      }

       

      @Entity(name = "ORGANISATIONS")

      @Audited
      public class OrganisationVO {

         @Id
         private Long id;

         @Column(nullable = false, name = "NAME", length = 70)

         private String name;


         ............................................................

         .. constructors, getters and setters ....

         ............................................................

      }

       

      I have created a sample application to show this (see attached zip file). It uses an in memory H2 database. When you try to get all the revisions of a particular user by GET /users/{id}/revisions endpoint, it fails with the exception

       

      ERROR o.a.c.c.C.[.[.[.[dispatcherServlet] - Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.hibernate.QueryException: could not resolve property: userid_id of: com.envers.domain.vo.UserOrgVO_AUD [select e__ from com.envers.domain.vo.UserOrgVO_AUD e__ where e__.userid_id = :userid_id and e__.originalId.REVISION_ID.id = (select max(e2__.originalId.REVISION_ID.id) from com.envers.domain.vo.UserOrgVO_AUD e2__ where e2__.originalId.REVISION_ID.id <= :revision and e__.originalId.id = e2__.originalId.id) and REVISION_TYPE != :delrevisiontype]] with root cause

      org.hibernate.QueryException: could not resolve property: userid_id of: com.envers.domain.vo.UserOrgVO_AUD

      at org.hibernate.persister.entity.AbstractPropertyMapping.propertyException(AbstractPropertyMapping.java:62)

      at org.hibernate.persister.entity.AbstractPropertyMapping.toType(AbstractPropertyMapping.java:56)

      at org.hibernate.persister.entity.AbstractEntityPersister.toType(AbstractEntityPersister.java:1764)

      at org.hibernate.hql.internal.ast.tree.FromElementType.getPropertyType(FromElementType.java:393)

      at org.hibernate.hql.internal.ast.tree.FromElement.getPropertyType(FromElement.java:505)

      at org.hibernate.hql.internal.ast.tree.DotNode.getDataType(DotNode.java:660)

      at org.hibernate.hql.internal.ast.tree.DotNode.prepareLhs(DotNode.java:264)

      at org.hibernate.hql.internal.ast.tree.DotNode.resolve(DotNode.java:204)

      at org.hibernate.hql.internal.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:109)

      at org.hibernate.hql.internal.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:104)

      at org.hibernate.hql.internal.ast.HqlSqlWalker.resolve(HqlSqlWalker.java:942)

      at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.expr(HqlSqlBaseWalker.java:1283)

      at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.exprOrSubquery(HqlSqlBaseWalker.java:4696)

      at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.comparisonExpr(HqlSqlBaseWalker.java:4166)

      at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2131)

      at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2056)

      at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2056)

      at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:810)

      at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:605)

      at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:309)

      at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:257)

      at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:262)

      at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:190)

      at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:142)

      at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115)

      at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(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.entities.mapper.relation.query.AbstractRelationQueryGenerator.getQuery(AbstractRelationQueryGenerator.java:55)

      at org.hibernate.envers.internal.entities.mapper.relation.lazy.initializor.AbstractCollectionInitializor.initialize(AbstractCollectionInitializor.java:49)

      at org.hibernate.envers.internal.entities.mapper.relation.lazy.proxy.CollectionProxy.checkInit(CollectionProxy.java:33)

      at org.hibernate.envers.internal.entities.mapper.relation.lazy.proxy.CollectionProxy.iterator(CollectionProxy.java:57)

      at com.envers.service.impl.UserServiceImpl.findAllRevisions(UserServiceImpl.java:79)

      at com.envers.service.impl.UserServiceImpl$$FastClassBySpringCGLIB$$a394cddd.invoke(<generated>)

       

      The code that tries to fetch the revisions of a user is as follows

      @Override
      public List<UserVO> findAllRevisions(Long id) {

        AuditReader reader = AuditReaderFactory.get(entityManager);
        AuditQuery query = reader.createQuery().forRevisionsOfEntity(UserVO.class, true, true);
        query.add(AuditEntity.id().eq(id));
        List revisions = query.getResultList();

        List<UserVO> users = new ArrayList<>();
        for(Object rev : revisions) {

          UserVO userRev = (UserVO) rev;

          for(UserOrgVO userOrgRev : userRev.getOrganisations()) {

            System.out.println("user org id: " + userOrgRev.getId());

          }

          users.add(userRev);
        }

        return users;

      }

       

      Can you tell why envers is adding "_id" to the column "userid" and how to correct it?

       

      Thanks & Regards,

      Sarath