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