CODEWITHSUNDEEP
javaspringhibernatespring-data-jpahql
Vinci
Vinci

Reputation: 341

HQL many to many repository method

I'm trying to create the repository method so that I can show, while accessing book's details all of its authors (and vice versa). It's many to many relationship, and model classes looks like this:

Author (model) class:

@Entity
@Table(name = "author")
public class Author {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "author_id")
    private int authorId;
    @Column(name = "name")
    private String name;
    @Column(name = "surname")
    private String surname;
    @Column(name = "date_of_birth")
    private Date dateOfBirth;

    @Column(name = "alive")
    private boolean alive;

    @ManyToMany(cascade = CascadeType.ALL, mappedBy="authors")
    @JoinTable(name = "book_author",
            joinColumns = @JoinColumn(name = "book_id"),
            inverseJoinColumns = @JoinColumn(name = "author_id"))
    private Set<Book> books = new HashSet<Book>();

Book (model) class

@Entity
@Table(name = "book")
public class Book {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "book_id")
    private int bookId;
    @Column(name = "isbn")
    private long isbn;
    @Column(name = "title")
    private String title;
    @Column(name = "title_original")
    private String titleOriginal;
    @Column(name = "premiere_date")
    private Date premiereDate;
    @ManyToMany(cascade = CascadeType.ALL, mappedBy="books")
    @JoinTable(name = "book_author", joinColumns = { @JoinColumn(name = "book_id") }, inverseJoinColumns = { @JoinColumn(name = "author_id") })
    private Set<Author> authors = new HashSet<Author>(0);

BookRepository class:

@Repository
public interface BookRepository extends CrudRepository<Book, Integer> {

    List<Book> findByIsbn(@Param("isbn") long isbn);
    List<Book> findByTitle(@Param("title") String title);
    List<Book> findByTitleOriginal(@Param("titleOriginal") String titleOriginal);
    @Query("SELECT b FROM Book b WHERE b.premiereDate BETWEEN :startYear AND :endYear ORDER BY b.premiereDate")
    public List<Book> getBooksBetweenDate(@Param("startYear")int startYear, @Param("endYear")int endYear);




@Query("SELECT b FROM Book b JOIN b.authors a WHERE a.authorId =:idAuthor")
public List<Book> getBookAuthors(???)

I think that my model class and hql query in repository are OK, but I have no idea how to create method getBookAuthors.

Thank you for your help in advance.

Upvotes: 0

Views: 178

Answers (1)

Amer Qarabsa
Amer Qarabsa

Reputation: 6574

you need to specify the join table in once side (the owning entity side ) and mappedBy in the other side, so forexample in your Book entity 

  @ManyToMany(cascade = CascadeType.ALL. mappedBy="books")
   private Set<Author> authors = new HashSet<Author>(0);

So now you have a bidirectional relation between book and author, if you want to retrieve the authors for a book all you need is to retrieve the book using your repository and then get its author

 Book book = BookRepository.findOne(bookId);//bookId contains the id of your book
Set<Author> authors = book.getAuthors();//im supposing you have getter for your authers in Book class

Upvotes: 2

Related Questions