0

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!

Aron Kim
  • 3
  • 4

2 Answers2

0

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.

Alan Hay
  • 22,665
  • 4
  • 56
  • 110
  • I have a couple of additional questions to follow, if it would be okay. How good are views performance-wise? In the case that I would like to do scale this up, is it still a good choice, or would a rework be better? (not using specifications and making a native query instead). Also, when is the view queried? Is it when I query the Person objects from the persons table (for example with a findByIds(ids) method-query), or when I use a getter on the attribute like getCreatedBy()? I'll try it out nonetheless. I didn't know either views nor the @SecondaryTable annotation, so many thanks! – Aron Kim Feb 19 '20 at 18:13
  • Sorry, I have one additional question. I see that normally a @SecondaryTable is connected to the first table via a foreign key. Since this is a view, how could I specify that the person's id and the view row's id are the same? Or does it do it automatically? And as a curiosity, what would happen if I didn't filter by revtype (and brought all the audit values for that person); would Spring map it to multiple results? – Aron Kim Feb 19 '20 at 18:38
0

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.

Aron Kim
  • 3
  • 4