时间戳

首页 > 解决方案 > Hibernate-Envers 审计查询：选择最新的实体版本，其中字段等于所有实体的值

sql - Hibernate-Envers 审计查询：选择最新的实体版本，其中字段等于所有实体的值

问题描述

我正在尝试选择我的每个 trackedResource 实体的最新版本，其中 position_source_feed_id 具有特定值。

我有一个功能性 PostgreSQL 查询：

select tr0.*
from
    trackedresource_aud tr0
where
    tr0.position_source_feed_id='7870b8b9-4b98-4ea7-8fb8-99746d7f0e85'
and
    tr0.rev=(
        select
            max(tr1.rev)
        from
            trackedresource_aud tr1
        where
            tr1.id=tr0.id
        and
            tr1.position_source_feed_id=tr0.position_source_feed_id
    )
order by
    tr0.rev desc
;

但我似乎无法让 AuditQuery 匹配。

当我尝试上述 createNativeQuery 时，我得到：

javax.persistence.PersistenceException: org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111

可能是因为实体上有几个 UUID 字段，包括 position_source_feed_id

标签： sqlhibernatespring-data-jpahibernate-envers

解决方案

I would expect the following query to work:

AuditReader reader = AuditReaderFactory.get( session );
YourEntity lastRevision = reader.createQuery()
  .forRevisionsOfEntity( YourEntity.class, true, false )
  .add( AuditEntity.property( "position_source_feed_id" ).eq( feedId ) )
  .addOrder( AuditEntity.revisionNumber().desc() )
  .setMaxResults( 1 )
  .getSingleResult();

The way the query works you are asking Envers for all revisions of YourEntity, you're only interested in the query returning the actual entities themselves (excluding deletions) where the position_source_feed_id is equal to the provided value. The result-set is is then limited to the highest revision number and it gets returned.

推荐阅读