Spring data query where column is null

Question

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.

score 50 · Accepted Answer · answered Apr 04 '15 at 17:50

50

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 :-(

answered Apr 04 '15 at 17:50

lrkwz

6,105
3
36
59

18

Wow lol At least this post helped me find how to use "IsNull" – GabrielBB Sep 21 '18 at 13:58

Nikunj Undhad · Answer 2 · 2018-09-24T10:56:57.207

33

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

edited Sep 24 '18 at 10:56

answered Sep 24 '18 at 07:01

Nikunj Undhad

487
5
8

Nikunj Undhad, while this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. [Answers that are little more than a link may be deleted.](//stackoverflow.com/help/deleted-answers) – 4b0 Sep 24 '18 at 08:21
@Shree, I updated answer with full description do you think now its understandable by everyone who face this problem. – Nikunj Undhad Sep 24 '18 at 10:58

score 14 · Answer 3 · answered Apr 04 '15 at 17:33

14

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());

answered Apr 04 '15 at 17:33

Xorty

18,367
27
104
155

Spring data query where column is null

3 Answers3

Linked