JPA's Map query by JPQL failed

Question

I am storing a Map in JPA , which stores a keyword translation in each language . such as one object stores Locale.ENGLISH -> "Father" , Locale.CHINESE -> "PaPa". And another object stores Locale.ENGLISH -> "Mother" , Locale.CHINESE -> "MaMa";

Here is my working design :

public class Relation {
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private long id;

  @ElementCollection
  @MapKeyColumn(name="locale")
  @Column(name="value")
  @CollectionTable(name = "RelationName", joinColumns = @JoinColumn(name = "relation_id"))
  private Map<Locale, String> langMap = new HashMap<>();

  // other fields skipped
}

It works well , I can store many keyword translations to DB. But when query with JPQL , it has some problems :

For example , I want to find which Relation has English key with value "Father" :

This is my code :

Relation r = em.createQuery("select r from Relation r join r.langMap m where ( KEY(m) = :locale and VALUE(m) = :value ) " , Relation.class)
  .setParameter("locale" , locale)
  .setParameter("value" , value)
  .getSingleResult();

It generates this strange / not-working SQL :

Hibernate: 
    select
        relation0_.id as id1_18_
    from
        Relation relation0_ 
    inner join
        RelationName langmap1_ 
            on relation0_.id=langmap1_.relation_id 
    where
        langmap1_.locale=? 
        and (
            select
                langmap1_.value 
            from
                RelationName langmap1_ 
            where
                relation0_.id=langmap1_.relation_id
        )=?
00:16:12.038 WARN  o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 1242, SQLState: 21000
00:16:12.038 ERROR o.h.e.jdbc.spi.SqlExceptionHelper - Subquery returns more than 1 row

I don't know why it generates that strange subquery.

I can solve this problem by Criteria :

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Relation> criteria = builder.createQuery(Relation.class);
Root<Relation> root = criteria.from(Relation.class);
criteria.select(root);
MapJoin<Relation , Locale , String> mapJoin = root.joinMap("langMap");
criteria.where(builder.and(
  builder.equal(mapJoin.key(), locale) ,
  builder.equal(mapJoin.value() , value))
);

return em.createQuery(criteria).getSingleResult();

It generates correct SQL ( where langmap1_.locale=? and langmap1_.value=? ) and works well.

But I feel Criteria is too complicated. And I wonder why the JPQL failed? How to correct the JPQL ?

Thanks.

Env :

JPA2 , Hibernate 4.2.3 , MySQL dialect

Answer 1

I had the same problem. It looks like accessing map by ref (without VALUE()) already gives you a map entry value, i.e. the next JPQL should be transformed to a valid SQL:

select r from Relation r join r.langMap m where ( KEY(m) = :locale and m = :value )

Answer 2

I had a similar problem using the JPQL VALUE() operator with Hibernate. It seems that Hibernate implements the VALUE() operator like the java.util.Map.values() method in Java. It generates a subquery that returns all values in the map, i.e. all rows of the mapping table that are related to the entity holding the Map attribute. As soon as you have more then one key/value pair in the map, a comparison expression, which expects scalar expressions as operands, will fail.

What you can do is to turn the comparison expression around and convert it to an IN expression.

Instead of:

select r from Relation r join r.langMap m 
  where ( KEY(m) = :locale and VALUE(m) = :value )

You can write:

select r from Relation r join r.langMap m 
  where ( KEY(m) = :locale and :value in (VALUE(m)) )

I hope this way your query will work.

Answer 3

The correct JPQL could be like this:

SELECT r FROM Relation r JOIN r.langMap map
WHERE map[:locale] = :value