geekologue

Hibernate Criteria supports relation but not collection based conditions so we had to use HQL for this.

   1 
   2 <class name="Asset" table="ASSETS">
   3     <id name="id" column="AS_ID">
   4         <generator class="native" />
   5     </id>
   6     <!-- ... -->
   7     <map name="customAttributes" table="ASSET_CUSTOM_ATTRIBUTES_VALUES" lazy="true" sort="natural">
   8         <key column="ACAV_ASID" />
   9         <map-key-many-to-many column="ACAV_CAID" class="AssetTypeCustomAttribute"/>
  10         <element type="string" column="ACAV_VALUE"/>
  11     </map>
  12 </class>

We wanted to keep the customAttributes collection lazy but pre-fetch for certain occasions, as well as query based on map key and value  pairs. Here's an example of HQL used for searching:

   1 select asset from Asset asset 
   2    left join fetch asset.customAttributes as customAttribute 
   3 where  ((index(customAttribute) = ? and customAttribute = ?) 
   4         and (index(customAttribute) = ? and customAttribute = ?) )