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!

naXa stands with Ukraine · Accepted Answer · 2018-12-13 13:34:04Z

5

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.

answered Dec 13, 2018 at 13:34

naXa stands with Ukraine

38.6k25 gold badges208 silver badges279 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Over a year ago

Thanks. I must have missed the appendix somehow.

Tim · Accepted Answer · 2018-07-10 15:35:13Z

0

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.

answered Jul 10, 2018 at 15:35

Tim

1,7057 gold badges32 silver badges47 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related