JPA Query By Example Sets Where Clause On Null'd Value

Question

For my F/OSS project I'm trying to add a way of loading a single table page worth of data at a time to avoid spending a lot of time loading records. So I settled on JPA Query by Example. And it works for the project.

That is, until I added checking for month and year values.

My data type is (abbreviated):

public class ComicDetail {
  @Column(name = "CoverDate", nullable = true)
  private Date coverDate;

  @Formula("(YEAR(CoverDate))")
  private Integer yearPublisheds;

  @Formula("(MONTH(CoverDate))"
  private Integer monthPublished;
}

I then setup my Example object by setting the coverDate, yearPublished, and monthPublished fields to null and only adding them to the matcher when a value is provided:

final ComicDetail detail = new ComicDetail();
detail.setCoverDate(null);
detail.setYearPublished(null);
detail.setMonthPublished(null);

ExampleMatcher matcher = ExampleMatch.matching();

if (coverYear != null) {
  log.debug("Setting cover year filter: {}", coverYear);
  detail.setYearPublished(coverYear);
  matcher = matcher.withMatcher("coverYear", ExampleMatcher.GenericPropertyMatchers.exact());
}

if (coverMonth != null) {
  log.debug("Setting cover month filter: {}", coverMonth);
  detail.setMonthPublished(coverMonth);
  matcher = matcher.withMatcher("coverMonth", ExampleMatcher.GenericPropertyMatchers.exact());
}


this.comicDetailRepository.findAll(Example.of(detail, matcher));

Even when no coverYear or coverMonth value is provided when setting up the example, though, every query generated has the following condition in it:

SELECT comicdetai0_.id                   AS id1_2_,
 ...
FROM   comicdetails comicdetai0_
WHERE  ( Year(comicdetai0_.coverdate) ) = 0

The only way I can get it to work is to comment out the yearPublished field from the ComicDetail class. But I don't have to do the same thing with the monthPublished field; i.e., that one only shows up when it's explicitly provided and a matcher is defined. And then it's always the value specified, i.e.:

WHERE  ( Year(comicdetai0_.coverdate) ) = 0
       AND ( Month(comicdetai0_.coverdate) ) = 3

Any idea on why the one field is always being included in the where clause?

Answer 1

It turns out the issue was that there was a method that was returning a default coverYear value of 0 when no coverDate was provided.

Removing that method (it was missed since we're using Lombok annotations on fields, so that getter method wasn't overlooked) fixed the problem.