I use Hibernate OGM for MongoDB, and mapping simple ShopItem entity with Map:
@NamedQueries({
@NamedQuery(name = ShopItem.GET_BY_NAME,
query = "select items from ShopItem items where
items.name=:name"),
@NamedQuery(name = ShopItem.GET_BY_OPTION,
query = "select items from ShopItem items
join items.options map
where ( KEY(map) = :optionKey and map = :optionValue )")
})
@Entity
public class ShopItem {
public static final String GET_BY_NAME = "ShopItem.getByName";
public static final String GET_BY_OPTION = "ShopItem.getByOption";
@Id
@GeneratedValue(generator = "uuid")
@GenericGenerator(name = "uuid", strategy = "uuid2")
private String id;
private String name;
@ElementCollection(fetch = FetchType.EAGER)
private Map<String, String> options = new HashMap<>();
//...etc
}
This mapped good. And I tried write JPQL named queries for my ShopItemRepository. I've searched NamedQuery by field 'name' and it works fine. But I need to search by both fields 'key' and 'value' and I can't write correct JPQL for this. For example i have entity in db:
{
"_id":"df111b8a-b831-4200-95b3-f80576cd7273",
"name":"Example",
"description":"My description",
"options":
{
"weight":"60 kg",
"age":"22"
}
}
And i want find all entities where options have key 'age' and have value '22'.
I try to write:
select items from ShopItem items join items.options map where ( KEY(map) = :optionKey and map = :optionValue )
or
select items from ShopItem items join items.options map where ( KEY(map) = :optionKey and :optionValue in (VALUE(map))
or
select items from ShopItem items where ( KEY(items.options) = :optionKey and items.options = :optionValue )
or
select items from ShopItem items where ( KEY(items.options) = :optionKey and :optionValue in (VALUE(items.options))
And have this error
org/hibernate/hql/ast/origin/hql/resolve/GeneratedHQLResolver.g: node from line 1:85 no viable alternative at input '('
can't look backwards more than one token in this stream
My code in repo:
List<ShopItem> result = entityManager.createNamedQuery(ShopItem.GET_BY_OPTION, ShopItem.class)
.setParameter("optionKey", key)
.setParameter("optionValue", value)
.getResultList();
Can you help me write correct JPQL query? And if this is not possible, then suggest how to do it another way.
I can't use Spring Data JPA.
Right now, the only way to query this case is using a native query:
List<ShopItem> results = session
.createNativeQuery( "{ 'options.Weight': '60 Kg' }", ShopItem.class )
.getResultList();
You can still declare the queries using @NamedNativeQuery
(instead of @NamedQuery
) but parameters aren't supported for MongoDB.