Hibernate Envers: Delete Data from _AUD table using native query

user_352673 0
Viewed 1532

I am using hibernate 4 and our project is using Hibernate Envers. This tables have accumulated lot of data in this years. Recently business has made a policy of keeping only 6 month audit data.

Now my question is can we delete data from this _AUD table using native query. I want to delete data that is older than 6 month.

hibernate-envers sql-delete hibernate
1 Answers

Each audit table has an entry on a revision entity table. This revision entity table has a revision date information. You can use that date to delete the rows from this audit table. But you need to do a join between the revision entity table and the sometable_aud (that has the entity versions), using the id from revision entity table and the rev field from sometable_aud.

If you have a revision entity like that: 

@Entity
@RevisionEntity(RevisionEntityListener.class)
public class RevEntity {

    @Id
    @GeneratedValue
    @RevisionNumber
    private int id;

    @Column(name = "revision_date", nullable = false)
    @RevisionTimestamp
    @Temporal(TemporalType.TIMESTAMP)
    private Date revisionDate;

}

You can use this SQL on a native query: 

String sql = "DELETE sometable_aud 
FROM   sometable_aud sometable_aud 
       JOIN sometable sometable 
         ON sometable.id = sometable_aud.id 
       JOIN rev_entity rev_entity 
         ON sometable_aud.rev = rev_entity.id 
WHERE  rev_entity.revision_date > '2018-06-18 00:00:00.0000000'";
entityManager.executeUpdate(sql);
user_75120720
Related