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);

}

CodePudding user response：

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!!

CodePudding user response：

Well, I had made two mistakes:

1. Needed to set fetch type to eager, or have a ManyToMany relationship

    @OneToMany(fetch = FetchType.EAGER)
    private List<Category> category;

    @ManyToMany
    private List<Category> category;

2. I had page indexed from 1 instead of 0, thus giving me empty result.

Pageable page = PageRequest.of(0, 1);
Page<Book> book = BookDao.getBooksByCat(page);