Spring Data JPA Query method to find all objects with empty list property

Question

I have an entity which contains a property that is a list:

@Entity
@Table(name="sales.jobs")
public class Job {
    ...
    private List<Shipment> jobShipments;
    ...
    @OneToMany(cascade=CascadeType.ALL,orphanRemoval=true, fetch=FetchType.LAZY)
    @JoinTable(name="sales.jobs_shipments",
           joinColumns = @JoinColumn(name="fk_jobid", referencedColumnName="pk_jobid"),
           inverseJoinColumns = @JoinColumn(name="fk_shipmentid", referencedColumnName="pk_shipmentid"))
    public List<Shipment> getJobShipments() {
        return jobShipments;
    }

    public void setJobShipments(List<Shipment> jobShipments) {
        this.jobShipments = jobShipments;
    }
    ...
}

In the repository, I would like to have a query method which returns all jobs where jobShipments is an empty list. I had first tried the IsNull keyword as shown below, but that doesn't work because if there are no shipments it is an empty list rather than null.

@Repository
public interface JobRepository extends CrudRepository<Job, Integer> {
    Page<Job> findByJobShipmentsIsNull(Pageable pageable);
}

I looked through the keywords in the documentation hoping to find something like IsEmpty, but I couldn't find anything that looked right.

I am looking for an answer that uses keywords rather than writing out a query, but I suppose that would be the fallback. Thanks!

score 4 · Accepted Answer · answered Dec 13 '18 at 13:34

Actually, there're keywords IsEmpty and Empty for collections:

@Repository
public interface JobRepository extends CrudRepository<Job, Integer> {
    Page<Job> findByJobShipmentsIsEmpty(Pageable pageable);
}

@Entity
@Table(name="sales.jobs")
public class Job {
    ...
    @OneToMany(cascade=CascadeType.ALL,orphanRemoval=true, fetch=FetchType.LAZY)
    @JoinTable(name="sales.jobs_shipments",
           joinColumns = @JoinColumn(name="fk_jobid", referencedColumnName="pk_jobid"),
           inverseJoinColumns = @JoinColumn(name="fk_shipmentid", referencedColumnName="pk_shipmentid"))
    private List<Shipment> jobShipments = new ArrayList<Shipment>();
}

See Appendix C: Repository Query Keywords.

Thanks. I must have missed the appendix somehow. – Tim Dec 13 '18 at 18:14 — Tim, Dec 13 '18 at 18:14

score 0 · Answer 2 · answered Jul 10 '18 at 15:35

As it turns out, IsNull is actually working. I checked IsNotNull for a point of reference, and it gave me all the jobs that did have shipments. I then ran this query

SELECT COUNT(*) FROM sales.jobs AS jobs
    LEFT JOIN sales.jobs_shipments AS ship ON jobs.PK_JobID = ship.FK_JobID
WHERE ship.FK_ShipmentID IS NULL;

The count from that query matched the result from the IsNull repository query. So even though it is returning an empty array, it is still finding the null entries just like my query.

Spring Data JPA Query method to find all objects with empty list property

2 Answers2