Hibernate Envers: How to get all current enties with their creation timestamps

Question

I use Hibernate Envers to audit changes on an entity named Client. Now I want to read all current (not deleted) Client entities from the persistence layer including their creation timestamp. Hibernate Envers stores all necessary information in the auditing tables. For me it is unclear how to perform the required query. What I tried was the following:

//Get last Revision Number for Entity Client
private Number getLastRev() {
    AuditReader auditReader = AuditReaderFactory.get(getEm());
    AuditQuery query = auditReader.createQuery()
            .forRevisionsOfEntity(Client.class, true, true)
            .addProjection(AuditEntity.revisionNumber().max());
    Number n = (Number) query.getSingleResult();
    return n;
}
public List<Object[]> getAllClientsWithCreationTimestamp() {
    Number revision = getLastRev();
    AuditReader auditReader = AuditReaderFactory.get(getEm());
    AuditQuery query = auditReader.createQuery()
            .forEntitiesAtRevision(Client.class, revision)
            .addProjection(AuditEntity.property("id"))
            .addProjection(AuditEntity.property("firstName"))
            .addProjection(AuditEntity.property("lastName"))
            .addProjection(AuditEntity.property("email"))                
            .addProjection(AuditEntity.revisionProperty("timestamp"))
            .addOrder(AuditEntity.revisionProperty("timestamp").desc());
}

Unfortunately getAllClientsWithCreationTimestamp() lists all current entities with timestamp = "last modification" not "creation" timestamp.

I hope anyone can help me to create the correct query.

The java code for my entity looks like this:

@Entity
@Table(name = "CLIENT")
@Audited
public class Client implements Serializable {

private static final long serialVersionUID = 1L;

@Id
@Column(name = "id", nullable = false)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator="CLIENT_SEQ")
@SequenceGenerator(name="CLIENT_SEQ", sequenceName="CLIENT_SEQ", allocationSize=1)
private Long Id;

@Column(name = "FIRST_NAME", length = 100)
private String firstName;

@Column(name = "LAST_NAME", nullable = false, length = 100)
private String lastName;

@Column(name = "EMAIL", nullable = false)
private String email;

public Long getId() {
    return Id;
}

public void setId(Long id) {
    Id = id;
}

public String getFirstName() {
    return firstName;
}

public void setFirstName(String firstName) {
    this.firstName = firstName;
}

public String getLastName() {
    return lastName;
}

public void setLastName(String lastName) {
    this.lastName = lastName;
}

public String getEmail() {
    return email;
}

public void setEmail(String email) {
    this.email = email;
}
}

I have the following database tables:

• REVISION: Envers main Revision Entity table
• CLIENT: Table to store Client entity
• CLIENT_AUD: Table to store client revisions

database tables

Answer 1

Your code is close but I believe you have a few errors.

In order to obtain the creation-timestamp, you're going to need to use a different query for that use case and marry that with your other queries. The following query will return you a projection that contains the entity-id along with the timestamp when audit row was created. You simply need to take this and create a map of id/timestamp pairs.

AuditQuery query = auditReader.createQuery()
  .forRevisionsOfEntity( Client.class, true, false )
  .addProjection( AuditEntity.id() )
  .addProjection( AuditEntity.revisionProperty( "timestamp" ) )
  .add( AuditEntity.revisionType().eq( RevisionType.ADD ) );

Next, you'll want to fetch the latest revision number for each entity id and again transform the result into a map of id/revision-number pairs.

AuditQuery query = auditReader.createQuery()
  .forRevisionsOfEntity( Client.class, true, false )
  .addProjection( AuditEntity.id() )
  .addProjection( AuditEntity.revisionNumber().max() );

Now its simply a matter of asking Envers to give you back your data projection query based on the maximum revision number and marrying that with the id/timestamp map from above.

for ( Map.Entry<YourEntityIdType,Number> entry : idRevisionPairs.entrySet() ) {
  AuditQuery query = auditReader.createQuery()
    .forEntitiesAtRevision( Client.class, entry.getValue() )
    .add( AuditEntity.id().eq( entry.getKey() )
    .addProjection( ... );

  // here take the results from query and the id-timestamp pairs and
  // marry them into some DTO you return.
}