HQL: querying value of java.util.Map

Question

I tried this hql query, but it throws an UnsupportedOperationException when I use actProp[:key] = :value in the following query:

Select all the actions that contain the value pairs x,y or z,y in the map actionProperties:

Query query = getSession().createQuery(
    "select a from Action a                     " +
    " join a.actionProperties actProp           " +
    "   where (index(actProp) = :key            " +
    "           and actProp[:key] = :value )    " +
    "     or  (index(actProp) = :key2           " +
    "           and actProp[:key2] = :value )   ");

The exception:

java.lang.UnsupportedOperationException
        at org.hibernate.hql.ast.tree.IdentNode.resolveIndex(IdentNode.java:67)

In the entity Action:

@CollectionOfElements(fetch = FetchType.EAGER)
private Map<String, String> actionProperties;

I also tried to use Hibernate Criteria for this, but I don't think this it is possible.

Does anybody know something to replace : actProp[:key] = :value with working code?

Answer 1

After some trial and error I finally found the solution, which is not so straightforward.

Query query = getSession().createQuery(
        " from Action a " +
        " join a.actionProperties actProp                      " +
        "   where( (index(actProp) = :key                      " +
        "           and :value in elements(a.actionProperties))" +
        "       or (index(actProp) = :key2                     " +
        "           and :value in elements(a.actionProperties)) )"
        );

So, for matching on the key I used the index() function and for matching on the value I used the elements() function.

If you know a better solution, let me know.