Load newest version of an entity with JPA

Question

I have the following entities:

@Entity
public class Policy {
  @ID
  private String uuid;

  private String policyId;

  private Long version;

  private Long auditVersion;
}

@Entity
public class PolicySearch {
  @ID
  private String uuid;

  @ManyToOne(optional = false)
  @JoinColumn(name = "policy_id", referencedColumnName = "policy_id")
  private Policy policy;
}

Basically, I've got an insurance policy where all changes are tracked in the DB (auditVersion). After some smaller changes a version can be released, that's when version increments and auditVersion starts at 0 again. Each DB entry has a different UUID, but the insuranceId stays the same for all versions of one policy.

The problem: I've got an entity for searches, a search always searches all versions of a policy - that's why I reference the policyId and not the uuid. When JPA loads the entity I end up with any policy. I would like a way to always get the highest version of a policy given the referenced policyId (and the highest auditVersion of that version).

I've thought of the following ways, but I'm not happy with either of those:

1. Change the type of the referenced Policy from Policy to String and only save the policyId, this would work but I would still want the foreign key constraint and I can't seem to find a way to create it with a JPA annotation (JPA creates my DB schema).
2. Keep the entities as is but discard the loaded Policy in favor of the newest one after loading the PolicySearch. This could be done in the DAO but if any entities in the future have PolicySearch as a member this seems like a really bad idea.

Any help would be greatly appreciated. I use EclipseLink.

Answer 1

I tried to add constraints to the DB but Postgres won't let you add foreign key constraints for columns which are not unique. The solution (which a coworker of mine came up with) for us was to change the database design and create a new entity which holds the PolicyId. So our Entities now look like this:

@Entity
public class Policy {
  @ID
  private String policyId;
}

@Entity
public class PolicyVersion {
  @ID
  private String uuid;

  private Policy policy;

  private Long version;

  private Long auditVersion;
}

@Entity
public class PolicySearch {
  @ID
  private String uuid;

  @ManyToOne(optional = false)
  @JoinColumn(name = "policy_id", referencedColumnName = "policy_id")
  private Policy policy;
}

This basically solves all the problems and has some other benefits too (like easy queries).