How to add JPA specifications conditionally to query?

Viewed 2302

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?

1 Answers

This should do the trick.

Specification spec = where(null);
if (statuses != null) {
    spec = spec.and(havingStatus(statuses))
}
if (account != null) {
    spec = spec.and(hasAccount(account))
}

final List<Instruction> instructions = this.instructionRepository.findAll(spec);
Related