CODEWITHSUNDEEP
javaspringjpaspring-data-jpaspring-data
Pavlyuckenko Ilya
Pavlyuckenko Ilya

Reputation: 13

Spring Data JPA: query ManyToMany, select by count

I have 2 entities: Book and Author. Each mapped to appropriate table. Also I have third table book_author because these two entities have relation many-to-many. I need to select from DB all the books in which the number of authors is equal to count.

Book:

    @Entity(name = "Book")
    @Table(name = "book")
    public class Book {
        @Id
        @GeneratedValue(strategy = GenerationType.AUTO)
        private int idbook;

        @NotNull
        @Size(max = 100)
        private String bookName;
        private int bookYearWriting;

        @ManyToMany(fetch = FetchType.LAZY,   
            cascade = {
                    CascadeType.PERSIST,        
                    CascadeType.MERGE
            })
        @JoinTable(name = "author_book",
            joinColumns = { @JoinColumn(name = "book_id") },
            inverseJoinColumns  = { @JoinColumn(name = "author_id") }
        )
        private List<Author> authors = new ArrayList<>();
    ...
    }

Author:

    @Entity(name = "Author")
    @Table(name = "author")
    public class Author {
        @Id
        @GeneratedValue(strategy = GenerationType.AUTO)
        private int idauthor;

        @NotNull
        @Size(max = 100)
        private String authorName;

        @ManyToMany(fetch = FetchType.LAZY,  
            cascade = {
                    CascadeType.PERSIST,        
                    CascadeType.MERGE
            },
            mappedBy = "authors")
        private List<Book> books = new ArrayList<>();
    ...
    }

And I want to get something like that:

    @Repository
    public interface BookRepository extends PagingAndSortingRepository<Book, Integer> {
         List<Book> findAllByAuthorsCountIs(int count, Pageable pageable);
    }

Please tell me how I can get this.

Upvotes: 0

Views: 1398

Answers (2)

Kishan Suchak
Kishan Suchak

Reputation: 11

you can use collection size in Hql query.

For example ...

from Book book left join book.authors where book.authors.size == 3

For more information please refer below link...

https://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html

@Query("select book from Book book left join book.authors where book.authors.size == ?1") List findAllByAuthorsCountIs(int count, Pageable pageable);

Upvotes: 1

i.bondarenko
i.bondarenko

Reputation: 3572

Hope this helps:

@Repository
public interface BookRepository extends PagingAndSortingRepository<Book, Integer> {
    @Query("select b from Book b left join b.authors a group by b.id having count(b) = ?1 ")
    List<Book> findAllByAuthorsCountIs(long count, Pageable pageable);
}

Upvotes: 0

Related Questions