Spring Data JPA : query to find objects by list?

Question

I have 2 classes: book and category which have a OneToMany relationship. I want to pass a list of string containing categories. The result should return all books which match any of the category in the list. for example if a book has 5 category and it matches with only 1 on the list, it should still be returned.

This is what I came up with but its not working.

//Models: 

@Entity
@Table(name = "Book")
@Data
public class Book {
    @Id
    private String id;
    @OneToMany
    private List<Category> category;
}

@Entity
@Table(name="Category")
@Data
public class Category {
    @Id
    private int id;    
    private String category;
}

// 

@Repository
public interface BookDao extends JpaRepository<Book, Integer> {
    
    @Query("SELECT b FROM Book b join b.category c where c.category in :category")
    Page<Book> getBooksByCat(Pageable pageable, @Param("category") List<String> category);

}

Answer 1

I think the in can be replaced with equals. Since we are joining the tables right each row of book will have a corresponding category. So once joined its just filter those rows which has the category that we are looking for.

@Query("SELECT b FROM Book b join b.category c where c.category = :category")

Also I think its better to use Join fetch to get rid of performance issues and all.

@Query("SELECT b FROM Book b
join b.category c
join fetch b.category
 where c.category = :category")

I think this helps!!