Querying using class in Spring

i’m trying to do this query

List<Product> findProductByCategory_CategoryNameAndPriceBetween(String category, double min, double max);

but the return of the query is an empty list. What am I doing wrong? I also tried without the underscore between Category and Category Name. This are the class category and product:

@Entity
public class Category {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name="ID")
    private Long id;

    @Column(name = "NAME")
    private String categoryName;
    
    @OneToMany(mappedBy = "category", fetch = FetchType.EAGER)
    private List<Product> products;

...
}

@Entity
@Table(name="PRODUCT")
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name="ID")
    private Long id;

    @Column(name="PRICE")
    private Double price;

    @Column(name="BRAND")
    private String brand;

    @Column(name="MODEL")
    private String model;

    //@Lob
    @Column(name="DESCRIPTION")
    private String description;

    @Column(name = "IMAGE_URL")
    private String imageUrl;

    @ManyToOne
    @JoinColumn(name = "CATEGORY_ID", referencedColumnName = "ID")
    private Category category;

And this is how I have implemented the repository:

@Repository
public interface ProductDao extends JpaRepository<Product,Long> {
...

Answer

With the implementation below, your query is doing just fine.

Please note that I’ve added an alternative implementation:

List<Product> findByCategoryAndPriceBetween(Category category, double min, double max);

I suspect that when you are testing this, you are not saving any products because your code is missing cascading inside Category class. Hence, I’ve added my implementation used during tests.

Category class

package no.mycompany.myapp.misc;

import lombok.Data;

import javax.persistence.*;
import java.util.ArrayList;
import java.util.List;

@Data
@Entity
public class Category {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "ID")
    private Long id;

    @Column(name = "NAME")
    private String categoryName;

    @OneToMany(mappedBy = "category", cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    private List<Product> products = new ArrayList<>();
}

Product class

package no.mycompany.myapp.misc;

import lombok.Getter;
import lombok.Setter;

import javax.persistence.*;

// cannot use @Data here because of circular ref
@Getter
@Setter
@Entity
@Table(name="PRODUCT")
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name="ID")
    private Long id;

    @Column(name="PRICE")
    private Double price;

    @Column(name="BRAND")
    private String brand;

    @Column(name="MODEL")
    private String model;

    //@Lob
    @Column(name="DESCRIPTION")
    private String description;

    @Column(name = "IMAGE_URL")
    private String imageUrl;

    @ManyToOne
    @JoinColumn(name = "CATEGORY_ID", referencedColumnName = "ID")
    private Category category;
}

ProductDao

public interface ProductDao extends JpaRepository<Product, Long> {
    List<Product> findProductByCategory_CategoryNameAndPriceBetween(String category, double min, double max);
    List<Product> findByCategoryAndPriceBetween(Category category, double min, double max);
}

Let’s add a couple of tests about this:

package no.mycompany.myapp.misc;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.boot.test.autoconfigure.orm.jpa.TestEntityManager;
import org.springframework.test.context.ActiveProfiles;

import static org.assertj.core.api.AssertionsForClassTypes.assertThat;

@DataJpaTest
public class ProductDaoTest {

    @Autowired
    ProductDao productDao;

    @Autowired
    TestEntityManager testEntityManager;

    @Test
    public void findByCategoryAndPriceBetween_singleCategoryWithSingleProductInDb_expectSingleProduct() {
        var category = createCategoryWithSingleProduct();
        testEntityManager.persist(category);
        var result = productDao.findByCategoryAndPriceBetween(category, 0.0, 2.0);
        assertThat(result.size()).isEqualTo(1);
    }

    @Test
    public void findByCategoryAndPriceBetween_singleCategoryWithSingleProductInDb_expectEmptyResult() {
        var category = createCategoryWithSingleProduct();
        testEntityManager.persist(category);
        var result = productDao.findByCategoryAndPriceBetween(category, 5.0, 6.0);
        assertThat(result.size()).isEqualTo(0);
    }

    @Test
    public void findProductByCategory_CategoryNameAndPriceBetween_singleCategoryWithSingleProductInDb_expectSingleProduct() {
        var category = createCategoryWithSingleProduct();
        testEntityManager.persist(category);
        var result = productDao.findProductByCategory_CategoryNameAndPriceBetween(category.getCategoryName(), 0.0, 2.0);
        assertThat(result.size()).isEqualTo(1);
    }

    private static Category createCategoryWithSingleProduct() {
        Product product = new Product();
        product.setPrice(1.00);

        Category category = new Category();
        category.setCategoryName("test");
        category.getProducts().add(product);
        product.setCategory(category);

        return category;
    }
}