I am using JPA and Hibernate 4.3.1 as the JPA provider. I am using JPQL to write my queries. When I write a JPQL to fetch values from an Entity which has a Map referencing another table, it is failing.
Below is my POJO:
public class Book {
@Id
@Column(name="ID", updatable=false, unique=true)
private String id;
@ElementCollection
@MapKeyColumn(name="language")
@Column(name="text")
@CollectionTable(name = "BOOK_TITLES")
private Map<String, String> title = Collections.emptyMap();
// other fields
}
I need to fetch all books whose title is a particular value. A Book can have title in multiple languages. Hence for one Book ID, there can be multiple records in the BOOK_TITLES table. Below is the JPQL:
SELECT b FROM Book b WHERE value(b.t) IN :title
But the HQL that gets formed for this results in the subquery returning more than 1 row:
select book0_.ID as ID1_0_, ...<other fields>
from my_library.BOOKS book0_ cross join my_library.BOOK_TITLES title1_
where book0_.ID=title1_.Book_ID
and ((select title1_.TEXT from my_library.BOOK_TITLES title1_
where book0_.ID=title1_.Book_ID) in (?))
This works fine if there is just 1 title for a book. But if there are more titles for one book, then this fails because the subquery returns more than 1 row.
I tried the solution in StackOverflowQuestion which talked about reversing the VALUE(m) = :value to :value IN VALUE(m). But this does not work for me and it says unexpected token ( found.
Is there a way to alter the JPQL such that my HQL gets formed as below: I do not want to use Criteria Builder.
select book0_.ID as ID1_0_, ...<other fields>
from my_library.BOOKS book0_ cross join my_library.BOOK_TITLES title1_
where book0_.ID=title1_.Book_ID and title1_.TEXT IN (?)
