I am trying to write the following query outlined here on sqlfiddle in JPA. I first tried using the @query annotation with native = true and that does work, but my issue is that I want the query to be more dynamic, because it could be the case where I don't want to add the clause to filter by name or by account.
My entities look something like this:
@Entity
@Table(name = "INSTRUCTION")
public class Instruction {
@Id
@Column(name = "ID", nullable = false, unique = true)
public Long id;
@Column(name = "ACCOUNT", nullable = false)
public String account;
@Column(name = "NAME", nullable = false)
public String name;
@OneToMany(cascade = CascadeType.All, fetch = FetchType.EAGER)
@JoinColumn(name = "INSTRUCTION_ID", referenceColumnName = "ID")
@OrderBy("lastUpdated")
private List<Audit> auditItems = new ArrayList<>();
//Getters & Setters
}
.
@Entity
@Table(name = "AUDIT")
public class Audit {
@Id
@Column(name = "ID", nullable = false, unique = true)
public Long id;
@Column(name = "INSTRUCTION_STATUS", nullable = false)
public InstructionStatus status;
@Column(name = "LAST_UPDATED", nullable = false)
public LocalDateTime lastUpdated;
@Column(name = "LAST_UPDATED_BY", nullable = false)
public String lastUpdatedBy;
//Getters & Setters
}
I had looked into using specifications to do this, and I managed to break my query into different specifications like so:
private Specification<Instruction> hasAccount(String account) {
return (root, query, criteriaBuilder) -> criteriaBuilder.in(root.get("account")).value(account);
}
private Specification<Instruction> havingStatus(List<String> status) {
return (root, query, criteriaBuilder) -> {
List<Predicate> predicates = new ArrayList<>();
final Subquery<Audit> auditSubquery = query.subquery(Audit.class);
final Root<Audit> audit = auditSubquery.from(Audit.class);
//select instruction id from audit where status is not in {status}
auditSubquery.select(audit.get("instruction").get("id"));
auditSubquery.where(criteriaBuilder.trim(audit.get("status")).in(status).not());
//Select instruction from table where
predicates.add(root.get("id").in(auditSubquery).not());
return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
};
}
// Other specifications....
And these work fine when called like so:
final List<Instruction> instructions = this.instructionRepository.findAll(
where(havingStatus(statuses)
.and(hasAccount(account))));
But my goal is have it so that for example I could check if account == null then do not include the hasAccount specification, and so on for other fields that may be null. Is there a way I can do this?