JPA Criteria API not working in JPARepository queries

Question

I'm trying to perform query using JPARepository with Criteria API, but it seems like method in repository is just ignoring specification passed to it.

Here's my repository interface:

public interface MovieRepository extends JpaRepository<Movie, String>, JpaSpecificationExecutor<Movie> {}

Specification that I want to use:

    public static Specification<Movie> hasTitleLike(String title) {
        return (root, query, criteriaBuilder) ->
                criteriaBuilder.like(criteriaBuilder.lower(root.get("title")), title.toLowerCase());
    }

JpaRepository query I'm executing:

movieRepository.findAll(MovieSpecifications.hasTitleLike(title), pageable);

Repository returns proper sorted page, so pageable parameter works, but It's not filtered any way by specifications.

I'm using spring-boot-starter-parent and spring-boot-starter-jpa version 3.1.5 and openJDK 21.

Movie class:

@Getter
@Setter
@Entity
@NoArgsConstructor
public class Movie {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private UUID id;

    @NonNull
    @NotBlank
    private String title;
    private Float rating;

    @NonNull
    @NotBlank
    @Column(length = 1000)
    private String plot;

    @NonNull
    @NotBlank
    private String releaseYear;

    @DecimalMin(value = "0", inclusive = false)
    private Integer runtime;
    private String posterUrl;

    @NonNull
    @ElementCollection(targetClass = String.class)
    @JoinTable(name = "genres", joinColumns = @JoinColumn(name = "movie_id"))
    private List<String> genres;

    @NonNull
    @ElementCollection(targetClass = String.class)
    @JoinTable(name = "directors", joinColumns = @JoinColumn(name = "movie_id"))
    private List<String> directors;

    @NonNull
    @ElementCollection(targetClass = String.class)
    @JoinTable(name = "actors", joinColumns = @JoinColumn(name = "movie_id"))
    private List<String> actors;

    @OneToMany(mappedBy = "movie", cascade = CascadeType.ALL)
    private Set<Review> reviews;
    
    @ManyToMany(mappedBy = "watchLater")
    private Set<User> watchedLater;
    

    public Movie(@NotNull String title, @NotNull String plot, @NotNull String releaseYear, Integer runtime, String posterUrl,
                 @NotNull List<String> genres, @NotNull List<String> directors, @NotNull List<String> actors) {
        this.title = title;
        this.plot = plot;
        this.rating = 0F;
        this.releaseYear = releaseYear;
        this.runtime = runtime;
        this.posterUrl = posterUrl;
        this.genres = genres;
        this.directors = directors;
        this.actors = actors;
        this.reviews = new HashSet<>();
    }

    public void updateRating(){
        float rating = 0;

        for(Review r: reviews){
            rating += r.getRating();
        }
        rating /= reviews.size();

        this.rating = rating;
    }
}

Hibernate logs:

Hibernate: 
    select
        m1_0.id,
        m1_0.plot,
        m1_0.poster_url,
        m1_0.rating,
        m1_0.release_year,
        m1_0.runtime,
        m1_0.title 
    from
        movie m1_0 
    order by
        m1_0.runtime 
    offset
        ? rows 
    fetch
        first ? rows only
2023-10-27T07:53:34.982+02:00 TRACE 162717 --- [nio-8080-exec-1] org.hibernate.orm.jdbc.bind              : binding parameter [1] as [INTEGER] - [0]
2023-10-27T07:53:34.983+02:00 TRACE 162717 --- [nio-8080-exec-1] org.hibernate.orm.jdbc.bind              : binding parameter [2] as [INTEGER] - [20]

Answer 1

I was able to run a very similar example locally and the MovieSpecification.hasTitleLike() filter on lower() is working.

A few questions:

1. What do you mean when you say the filtering is not working in any way? Do you mean it's not filtering by title and basically just doing a Select *?

2. Can you provide the db vendor you are using and version? i.e. MySQL 8.2.0, H2 etc...

3. Can you provide the full Movie class?

4. Finally, to help debug, you should be able to see more information if you turn on Hibernate logging with the following application.properties settings:

    spring.jpa.properties.hibernate.show_sql=true
    spring.jpa.properties.hibernate.format_sql=true
    logging.level.org.hibernate.orm.jdbc.bind=TRACE
   

Hibernate Logs:

 select
        m1_0.id,
        m1_0.title 
    from
        movie m1_0 
    where
        lower(m1_0.title) like ? escape '' 
    order by
        m1_0.id offset ? rows fetch first ? rows only

2023-10-26T12:47:49.585-07:00 TRACE 29220 --- [           main] org.hibernate.orm.jdbc.bind              : binding parameter [1] as [VARCHAR] - [jurassic park]
2023-10-26T12:47:49.585-07:00 TRACE 29220 --- [           main] org.hibernate.orm.jdbc.bind              : binding parameter [2] as [INTEGER] - [10]
2023-10-26T12:47:49.585-07:00 TRACE 29220 --- [           main] org.hibernate.orm.jdbc.bind              : binding parameter [3] as [INTEGER] - [10]

EDIT

After looking at your code that defines the Specification it looks like instead of:

specification.and(MovieSpecifications.hasTitleLike(params.getTitle())));

you need to do:

specification = specification.and(MovieSpecifications.hasTitleLike(params.getTitle()));

This is because specification.add() returns a new specification. Just calling the .add() method on its own doesn't do much as there is no specification object to assign the value to.

I created a pull request against your branch that you listed below in the comments but I believe this is your issue. I ran it locally with a test and confirmed the code now works. Hope that helps!