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

}

Does it work if you add the round brackets? where c.category in (:category) — Davide D'Alto
– Davide D'Alto, Commented Jun 11, 2022 at 10:56

Mahadev K · Accepted Answer · 2022-06-11 12:33:04Z

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

answered Jun 11, 2022 at 12:33

Mahadev K

3502 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sam KC · Accepted Answer · 2022-06-11 20:01:22Z

0

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

edited Jun 11, 2022 at 20:01

answered Jun 11, 2022 at 18:45

Sam KC

611 silver badge10 bronze badges

Collectives™ on Stack Overflow

Spring Data JPA : query to find objects by list?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related