How to query for Map values with Spring Data JPA?

Question

so my database model goes like this: I have Stores and every Store has a localized name. So I have chosen to represent the localized name as a Map like this:

public class Store {
   private Map<Locale,LocalizedValue> name;
}

as you can see it's a Map of <Locale, LocalizedValue> where the LocalizedValue is a class like this:

@Embeddable
public class LocalizedValue {

   @Column(name = "value")
   private String value;
}

and it all works great. However I get to a problem where I want to query my Spring Data JPA repository and find all the stores with a given english name. So my repository method looks like this:

Store findByName(Map.Entry<Locale, LocalizedValue> name);

but it throws this exception:

 2014-10-07 23:49:55,862 [qtp354231028-165] ERROR: Parameter value [en=Some Value] did not match expected type [com.test.LocalizedValue(n/a)]; nested exception is java.lang.IllegalArgumentException: Parameter value [en=Some Value] did not match expected type [com.test.LocalizedValue (n/a)]
org.springframework.dao.InvalidDataAccessApiUsageException: Parameter value [en=Some Value] did not match expected type [com.test.LocalizedValue (n/a)]; 
nested exception is java.lang.IllegalArgumentException: Parameter value [en=Some Value] did not match expected type [com.test.LocalizedValue (n/a)]
    at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:384)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:216)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:417)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)

So then I changed my repository method to be like this:

Store findByName(LocalizedValue name);

but then I got this exception:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'name1_.pk' in 'where clause'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)

I also tried using Contains in the query method - still no luck.

So my question is: Is there a way to query for the stores with English name 'Some Value'?

Answer 1

This requires a manually defined query something like this:

interface StoreRepository extends Repository<Store, Long> {

  @Query("select s from Store s join s.map m where ?1 in (VALUE(m))"
  Optional<Store> findByName(String name);
}

It basically tells the persistence provider to expand the map values and check whether the given parameter is in the list of expanded values.

Answer 2

You have not posted your mappings but there seems to me to be a fundamental issue with way that Embeddables are keyed.

This is the only obvious way I can see to map the association:

@Entity
@Table(name = "stores")
public class Store {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private Long id;

    @ElementCollection
    @CollectionTable(name = "store_names", joinColumns = @JoinColumn(name = "store_id"))
    @MapKeyColumn(name = "locale")
    private Map<Locale, LocalizedName> names;

    public Map<Locale, LocalizedName> getNames() {
        return names;
    }

    public String getName(Locale locale) {
        return names.get(locale).getName();
    }
}


@Embeddable
public class LocalizedName {

    @Column(name = "name")
    private String name;

    @SuppressWarnings("unused")
    private LocalizedName() {

    }

    public LocalizedName(String name) {
        this.name = name;
    }

    public String getName(){
        return name;
    }
}

The following test passes:

@Test
public void testLoadStore() {
    Store store = repository.findOne(1l);
    Assert.assertNotNull(store);
    Assert.assertEquals("EN Name", store.getName(Locale.ENGLISH));
    Assert.assertEquals("DE Name", store.getName(Locale.GERMAN));
}

The issue with this is however that 'Locale' can never be a property of LocalisedName otherwise Hibernate complains of a duplicate column mapping. See the bug report:

https://hibernate.atlassian.net/browse/HHH-5393

So while it is possible to write a query method:

public interface StoreRepository extends JpaRepository {

@Query(value = "from Store s join s.names n where n.name = ?1")
Store findByName(String name);

}

for which the following test passes

@Test
public void testLoadByName() {
    Store store = repository.findByName("EN Name");
    Assert.assertNotNull(store);
    Assert.assertEquals("EN Name", store.getName(Locale.ENGLISH));
    Assert.assertEquals("DE Name", store.getName(Locale.GERMAN));
}

as far as I can see this can never take Locale into account as it is not a property of LocalizedName.

Answer 3

I had a simmilar chalenge today but I wanted to find all Entities having at least one times the given name. Using spring data jpa 2.3.7 for me the following with shorter syntax worked:

interface StoreRepository extends Repository<Store, Long> {

  @Query("select s from Store s where :name in (VALUE(s.name))"
  List<Store> findByName(String name);
}

But I saw that if you have the same value for multiple keys than you will have the same Entity multiple times in the resultlist. So I had to use distinct keyword

  @Query("select distinct s from Store s where :name in (VALUE(s.name))"