Is there a way to reference an audit table for Specifications (Spring)?

Question

I've been looking for a similar question to this, without success.

My goal: I need to obtain the username of whoever created a certain entity, which is stored in the audit table of said entity referenced via Spring's @AuditTable.

A bit (hopefully) explained: In this particular situation I'm adding to a previously existing query built with the org.springframework.data.jpa.repository.JpaSpecificationExecutor class, along with the Predicate class (javax.persistence.criteria.Predicate). Joins tend to be made with the name of the class (example Join), from which then Spring gets the table name. My question is, is there a way to make Spring look in the audit table name, instead of the Entity's table name?

An example would be:

@Entity @Table(name = "persons")
@Audited @AuditTable(name = "audit_persons")
public class Person {
        private String id;
        private String name;
        private Job job;
        /*getters and setters*/
}

@Entity @Table(name = "jobs")
@Audited @AuditTable(name = "audit_jobs")
public class Job {
        private String id;
        private String name;
        /*getters and setters*/
}

public final class PersonSpecification {
    private PersonSpecification(){}

    public static Specification<Person> findBy(final String attributeName, final Object filterValue) {
        return new Specification<Person>() {
            @Override
            public Predicate toPredicate(Root<Person> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                return cb.equal(root.get(attributeName), filterValue);
            }
        };
    }

    public static Specification<Person> findByJob(final Job job) {
        return new Specification<Person>() {

            @Override
            public Predicate toPredicate(Root<Person> root, CriteriaQuery<?> query, CriteriaBuilder cb) {

                Join<Person, Job> joinJob = root.join("job", JoinType.LEFT);
                return cb.equal(joinJob.<Job>get("job"), job);
            }
        };
    }

/*
    public static Specification<Person> findByUsername(final String username) {
        return new Specification<Person>() {
            @Override
            public Predicate toPredicate(Root<Person> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                Join<Person, audit person?> join? = root.join(?, JoinType.LEFT);
                return I don't know how to access;
            }
        };
    }
*/
}

import static ().PersonSpecification.findBy;
import static ().PersonSpecification.findByJob;
@Service(value="personService")
public class PersonService {

    @Autowired
    private PersonDao personDao;

    public List<Person> find(String id, String name, Job job /*, String username*/){

        Specifications<Person> specifications = null;

        if (!StringUtils.isBlank(id) && StringUtils.isNumeric(id)) {
            specifications = loadFilter(specifications, findBy("id", id));
        }else{
            if (!StringUtils.isBlank(name)) {
                specifications = loadFilter(specifications, findBy("name", name));
            }
            if (job != null) {
                specifications = loadFilter(specifications, findByJob("job", job));
            }
            /*
            if (!StringUtils.isBlank(username)) {
                specifications = I don't know what to do here;
            }
            */
        }
        return personDao.findAll(specifications);
    }
}

A possible solution: I was thinking about adding a new entity which would reference the audited entity, like this:

@Entity @Table(name = "audit_persons")
public class AuditedPerson {
       private String id;
       private Integer rev;
       private Integer revtype;
       private String username;
       /*getters and setters*/
}

And then my method would be

public static Specification<Person> findByUsername(final String username) {
       return new Specification<Person>() {
           @Override
           public Predicate toPredicate(Root<Person> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
               Join<Person, AuditedPerson> joinAudit = root.join(id, JoinType.LEFT);
               return cb.and(cb.equal(joinAudit.get("username"), username),
                               cb.equal(joinAudit.get("revtype"), 0)); //this is so I get the creation instance's username
           }
       };
   }

I'll be trying to make this work. Is there already a way to do this? Should I just add a native query that does this? Thanks!

Answer 1

In the end, I couldn't find a way to properly reference an audit table, so had to use hibernate enver's to access it. I won't mark this as an answer, since it doesn't comply with what I was looking for, but I'll still add this so I know what I did. What I did (for future reference) is the following:

@Transactional(isolation = Isolation.READ_UNCOMMITTED, propagation = Propagation.REQUIRED, readOnly = true)
public Map<Long,String> getCreatorUsernameByPersonId(List<Long> personIds) {

    Map<Long,String> result = new HashMap<Long,String>();
    EntityManager em = entityManagerFactory.createEntityManager();
    List<?> auditedPeople = null;
    try {
        AuditReader reader = AuditReaderFactory.get(em);
        AuditQuery query = reader.createQuery().forRevisionsOfEntity(Person.class, false, false);
        if(personIds != null && !personIds.isEmpty()) query.add(AuditEntity.id().in(personIds));
        query.add(AuditEntity.revisionType().eq(RevisionType.ADD));
        auditedPeople = query.getResultList();
        if(auditedPeople != null && !auditedPeople.isEmpty()){
            for(Object o : auditedPeople) {
                result.put(((Person)((Object[])o)[0]).getId(), ((AuditRevEntity)((Object[])o)[1]).getUsername());
            }
        }
    } finally {
        em.close();
    }
    return result;
}

Then I used that hashmap when creating the dto for what I needed.

Answer 2

One solution would be to use JPA @SecondaryTable with a database view.

Firstly, create a view (say vw_person_additional_data) from the audit table that selects only the initial 'created' record for each Person.

Update your person Entity as below. The @SecondaryTable annotation lets us map an Entity to one or more tables or views.

@Entity 
@Table(name = "persons")
@Audited 
@AuditTable(name = "audit_persons")
//secondary table referencing our new view
@SecondaryTable(name = "vw_person_additional_data", 
                   pkJoinColumns = {@PrimaryKeyJoinColumn(name = "person_id", 
                        referencedColumnName = "id")}) 
public class Person {
        private String id;
        private String name;
        private Job job;

        @Column(name = "username", table="vw_person_additional_data") //column in view
        private String createdBy;
}

The createdBy property is now just like any other property of Person when it comes to writing specifications.