JPA map error <KEY, VALUE request> by JPQL

Question

I store a map in JPA, which stores the translation of keywords in each language. for example, 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 } 

This works well, I can store many keyword translations in DB. But when querying with JPQL, it has some problems:

For example, I want to find what relationship the English key has with the 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 weird / broken 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 ohejdbc.spi.SqlExceptionHelper - SQL Error: 1242, SQLState: 21000 00:16:12.038 ERROR ohejdbc.spi.SqlExceptionHelper - Subquery returns more than 1 row 

I do not know why it generates this 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 the correct SQL ( where langmap1_.locale=? and langmap1_.value=? ) And works well.

But I feel that the Criteria are too complex. And I wonder why JPQL failed? How to fix JPQL?

Thanks.

Env:

JPA2, Hibernate 4.2.3, MySQL dialect

Answer 1

I had a similar problem using the JPAL VALUE () operator with Hibernate. Hibernate seems to implement the VALUE () operator, like the java.util.Map.values ​​() method in Java. It creates a subquery that returns all the values ​​on the map, i.e. All rows in the mapping table associated with the object containing the Map attribute. As soon as there is more than one key / value pair on the map, a comparison expression that expects scalar expressions as operands will not be executed.

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

Instead:

 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)) ) 

Hope your request will work.

Answer 2

I had the same problem. It looks like accessing the map using ref (without VALUE ()) already gives you the value of the map entry, i.e. The following JPQL must be converted to valid SQL:

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

Answer 3

The correct JPQL might be like this:

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