Spring data query where column is null

Suppose I have entities (getters/setters and various details omitted for brevity):

@Entity
class Customer{
    ...
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "customer")
    Collection<Coupon> coupons;
}

@Entity
class Coupon{
    ...
    @Temporal(value = TemporalType.TIMESTAMP)
    private Date usedOn;

    @ManyToOne(fetch = FetchType.LAZY)
    @NotNull
    Customer customer;
}

I wish retrieve all Coupons for a given Customer having null usedOn. I,'ve unsuccessfully defined a method in the CouponRepository as described in docs

@Repository
public interface CouponRepository extends CrudRepository<Coupon, Long> {
     Collection<Coupon> findByCustomerAndUsedOnIsNull(Customer);
}

but this leads on a compiler error Syntax error, insert "... VariableDeclaratorId" to complete FormalParameterList.

Solution 1:

My fault, the correct definition is

@Repository
public interface CouponRepository extends CrudRepository<Coupon, Long> {
     Collection<Coupon> findByCustomerAndUsedOnIsNull(Customer customer);
}

I simply missed the parameter name :-(

Solution 2:

You can use IsNull to check null columns in JPA query.

For example for any columnA you can write query like query like

findByColumnAIsNull

In this case you can write queries like

@Repository
public interface CouponRepository extends CrudRepository<Coupon, Long> {
 Collection<Coupon> findByCustomerAndUsedOnIsNull(Customer customer);
 List<Coupon> findByUsedOnIsNull();
}

Also you can check how this queries will be Refer this Spring Data JPA Query creation this will help you lot to understand and create different type of JPA query variation.

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.query-creation

Solution 3:

Try changing your method to this (assuming Customer.id is a long):

Collection<Coupon> findByCustomer_IdAndUsedOnIsNull(Long customerId);

then use like this:

repo.findByCustomer_IdAndUsedOnIsNull(customer.getId());