How to query distinct values from map with given key in Hibernate?

Question

I have following Hibernate 3 mapping for User class:

<class name="org.test.User" table="users">

... some other mapping...

    <map name="metadata" table="user_metadata">
        <cache usage="transactional"/>
        <key column="user_id" not-null="true" foreign-key="FK_USERMETADATA_USER_ID"/>
        <map-key type="string" column="datum_key" length="255" />
        <element type="string" column="datum_value" length="1024" not-null="true"/>
    </map>            
</class>

I would like to retrieve a list of all datum_values with given datum_key.

In SQL the query would look like:

select distinct datum_value from user_metadata where datum_key = 'Member Type'

What would be the most feasible approach? Create a new Hibernate mapping for the user_metadata as well? Or try to come up a HQL/JPA QL query using indices/elements returning (possibly scalar) values? So far I tried HQL/JPA QL approach a bit and I did not get too far.

Answer 1

There is no equivalent to your above SQL query in HQL. indices() function as well as index dereferencing can only be used in 'WHERE' clause.

The easiest solution is to map your SQL query as named query and run it as scalar native SQL query.