How to run Custom query on audit table hibernate envers

user_3696393 0
Viewed 1274

Trying to retreive records through custom query on audit table. I have Hibernate entity structure like below..,

@Audited
@Entity
public class TableA {
    @EmbeddedId
    private PrimaryKeyID primaryKeyID;
    private Double price;
    private Date modifiedOn;
    private String status;
}
@Embeddable
public class PrimaryKeyID implements Serializable {
    private Integer Id;
}

The query I am trying to replicate through AuditFactory is 

SELECT * FROM table_A_aud where id = ? and status=? order by modifiedOn desc limit 3;

Tried Hibernate document on the Envers but I didnt find much info on that. Can someone kindly help me with this?

spring-data-jpa hibernate-envers
1 Answers

In order to issue a query against the audit tables, you would do the following:

List results = AuditReaderFactory.get( session )
  .createQuery()
  .forRevisionsOfEntity( TableA.class, true, false )
  .add( AuditEntity.id().eq( entityId ) )
  .add( AuditEntity.property( "status" ).eq( entityStatus ) )
  .addOrder( AuditEntity.property( "modifiedOn" ).desc() )
  .setMaxResults( 3 )
  .getResultList();

In this query, we instruct the reader to get only entity instances (excluding delete markers) where the entity id is equal to entityId and the status is equal to entityStatus and ordering the results based on the modifiedOn column in descending order.

You'll notice that a lot of this Query API mimics that of the legacy Hibernate Criteria API.

user_15722690
Related