Many to many JPQL @Query with List as argument in Spring

Question

I've got a simple Product-Category many-to-many relationship, where Product has a List< Category> "categories" attribute, and Category has a List< Product> "products" attribute. JPA ORM mapping is pretty ok and working.

In the ProductRepository, I'd like to include a method that takes a partial name and a list of Category as parameter, and returns all products that contain the given name and any of the categories in the parameter list. My question is: is it possible to specify my query inside a @Query? How?

@Repository
public interface ProductRepository extends JpaRepository<Product, Integer> {

    @Query("SELECT obj FROM Product obj WHERE obj.name LIKE %?1 AND obj.categories ?????")
    List<Product> findByNameCategories(String name, List<Category> categorias);
}

Answer 1

You are almost there. You can craft your query to do what you want without having to use the @Query annotation

List<Product> findByNameStartingWithAndCategoriesIn(String name, Set<Category> categorias);

For distinct results:

Set<Product> findDistinctByNameStartingWithAndCategoriesIn(String name, Set<Category> categorias);

Spring will automatically create the proper query for you. You can also you EndingWith to wrap the parameter ending with % or Containing to have the parameter be wrapped on both sides with %

I changed your List<Category> categorias to a Set because list can have duplicate Category objects in the list while a Set will not so when it crafts the query behind the scenes you'll have unique ids in the In clause.

Spring Documentation on crafting queries