HQL query for searching (one to many relation)

Question

I have two entities, Book and Author.

@Entity
@Table(name="book") 
class Book {
    private int id;
    private Map<Integer, Author> authors = new HashMap<Integer, Author>();

    @Id
    @GeneratedValue
    @Column(name="id_book")
    public int getId() {
        return this.id;
    }

    @OneToMany(fetch=FetchType.EAGER)
    @JoinTable(name="book_author", joinColumns= {@JoinColumn(name="id_book")},
            inverseJoinColumns = {@JoinColumn(name="id_author", table="author")})
    @MapKeyColumn(name="order")
    public Map<Integer, Author> getAuthors() {
        return authors;
    }


}

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

    private int id;
    private String lastname;
    private String firstname;

    @Id
    @Column(name="id_author")
    @GeneratedValue
    public int getId() {
        return id;
    }

    public String getLastname() {
        return lastname;
    }

    public String getFirstname() {
        return firstname;
    }

}

A book has many authors listed in a particular order. Now I am trying to create a HQL such that I can get a list of book from a particular author with particular firstname or lastname or both. I am confused on how to use join between two entities. Any idea?

Thanks in advance.

Answer 1

First: You have a one-to-many relation between books and authors. One book can have many authors, but one author only can have written one single book. If a real person has written many books, then he needs many rows in the table author, one for each of his books. This might not be what you've intended, but you've defined the relation like this.

The one to many relation works on database side with the id of the book in the table author. Make this id available in Java by creating a getter getBookID()in Author. Then you can use either the HQL statement

from Book b inner join Author a 
   where b.id  = a.bookId
      and a.lastname = :ln
      and a.firstname = :fn

or

from Book b where b.id in (select bookId from Author a
                              where a.lastname = :ln
                              and a.firstname = :fn)

Second: Now you might prefer one Author can have many books. Then you have a many-to-many relation. For this it is recommendable to introduce a cross table which contains the many to many relationship. This cross table contains only two columns, the book id and the author id, and both Book and Author have a one-to-many relation to it (and Author doesn't have the bookId any more). The HQL statements are similar to the first case except that they go over three tables.

Edit: With your Book_Author table: For your select you have to create a class BookAuthor, which is mapped to that table. Then you can use either the HQL statement

from Book b inner join BookAuthor ba inner join Author a 
   where b.id  = ba.bookId
      and ba.authorId = a.id
      and a.lastname = :ln
      and a.firstname = :fn

or

from Book b where b.id in (select ba.bookId from BookAuthor ba, Author a
                              where ba.authorId = a.id
                                 and a.lastname = :ln
                                 and a.firstname = :fn)