How to query for Map values with Spring Data JPA?
Solution 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.
Solution 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.
Petar Tahchiev
Updated on March 11, 2021Comments
-
Petar Tahchiev about 3 years
so my database model goes like this: I have
Store
s and everyStore
has a localized name. So I have chosen to represent the localized name as aMap
like this:public class Store { private Map<Locale,LocalizedValue> name; }
as you can see it's a Map of
<Locale, LocalizedValue>
where theLocalizedValue
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'?