0

I have an entity Book which contain List of Author entity.

Book ManyToMany Author

Book Entity

public class Book{
   ...

   @ManyToMany
   @JoinTable(name="BookAuthor",
    joinColumns={@JoinColumn(name="bookId")},
    inverseJoinColumns={@JoinColumn(name="authorId")})
   private List<Author> authors;

   ...
}

Note: There is no backward reference from Author to Book in Author entity.

I am trying to get all the books that have the exact list of authors.

This is what I have come up with so far:-

public List<Book> getBooksFromAuthors(List<Author> authors) {

   TypedQuery<Book> bookTypedQuery = entityManager.createQuery("SELECT b FROM Book b where b.authors = :authors", Book.class)
    .setParameter("authors", authors);
   return bookTypedQuery.getResultList(); // Error at this line
}

This gives an error:-

org.hibernate.exception.SQLGrammarException: could not extract ResultSet

How should I modify my query to get the desired result?

halfer
  • 19,824
  • 17
  • 99
  • 186
Parag Kadam
  • 3,620
  • 5
  • 25
  • 51
  • just a doubt, have you enabled hibernate logs? if so what query is it printing? – Sandeep Jan 02 '19 at 11:24
  • My original query is much bigger than this, the above is a toned down version of it. But what I can say is that the query makes a join with BookAuthor table. – Parag Kadam Jan 02 '19 at 11:26
  • 1
    clearly, just based on reading the JPA spec (free on the internet for all to see), you cannot do "List = List". You can use the IN operator –  Jan 02 '19 at 11:26
  • @BillyFrost: So you mean I should do something like `b.authors.id in :authorIds`. Please not that I am matching the exact authors with the books and IN would probably return books that consists any(I want all) of the authors in the `authorList`. – Parag Kadam Jan 02 '19 at 11:30
  • 1
    Take a look here, it may be helpful: https://stackoverflow.com/questions/24090712/how-do-i-write-a-jpa-criteria-query-that-matches-a-collection-exactly – Lorelorelore Jan 02 '19 at 11:32
  • can you try passing list of author ids and do a 'IN' query. - https://stackoverflow.com/questions/3126366/doing-an-in-query-with-hibernate – Sandeep Jan 02 '19 at 11:33
  • @Sandeep : Will using 'IN' give books with exactly matching list of authorIds? – Parag Kadam Jan 02 '19 at 11:38
  • @ParagKadam it works for exact as well as partial match also. – Sandeep Jan 02 '19 at 11:49
  • I want it to work exclusively for an exact match. – Parag Kadam Jan 02 '19 at 11:51
  • I mean you should look at basic JPQL docs before just guessing. IN syntax is well documented ... "value IN collection". –  Jan 02 '19 at 11:57

0 Answers0