I have a commerce application which has a variety of methods for returning a list of products. In order to handle things like pagination and sorting consistently, the API is a little abstract. In order to support this abstraction, I need to use Hibernate's Criteria API. It has been working great for getting lists by product category or vendor or for more free text searching, but now I'm stumped.

My Product objects have a Set of strings of arbitrary attributes ("clearance", "made in USA", etc). I want to find all products which have a specific value among the members of their "attribute set". That is, all products for which 'product.getAttributes().contains("VALUE")' is true.

I've had success with queries of members of sets where the members are entities, but I can't seem to figure out (or find in the docs) how to do it when the members of the set are Strings. The intuitive code I tried doesn't seem to navigate the <set> mapping at all -- I'm looking for generated SQL which joins OZO_PRODUCT to OZO_PRODUCT_ATTRIBUTE but it's not there.

Here's the fragment I added to the criteria:
crit.add(Restrictions.eq("attributes", "(string literal which might be a member of the set)"));
which produced the generated SQL below, which does not have this join. It's kind of obvious to me that this wouldn't be correct, as the set would not equal a String.

I want to be able to do something kind of like this (inspired by HQL expressions):
crit.add(Restrictions.eq("any(attributes)", attribute));
or
crit.createAlias("attributes", "attr").add(Restrictions.eq("any(attr)", attribute));

I have an inkling that maybe I could do something with Subqueries, DetachedCriteria, and/or Projections, but I haven't absorbed those enough to see how to get there.

I was able to make it work using a sqlRestriction, but that seems really clumsy:

crit.add(Restrictions.sqlRestriction("{alias}.id in (select productId from ozo_product_attribute where value = ?)", attribute, Hibernate.STRING));

Is this the best I can hope for?

Hibernate version:
3.0.5
Mapping documents:

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping
>
<class
name="mypackage.model.catalog.Product"
table="OZO_PRODUCT"
>

<id
name="id"
column="id"
type="java.lang.Long"
>
<generator class="native">
<param name="sequence">SEQ_OZO_PRODUCT_ID</param>
<!--
To add non XDoclet generator parameters, create a file named
hibernate-generator-params-Product.xml
containing the additional parameters and place it in your merge dir.
-->
</generator>
</id>

<version
name="version"
column="version"
type="java.lang.Long"
/>

<property
name="briefDescription"
type="java.lang.String"
update="true"
insert="true"
column="briefDescription"
length="2000"
/>

<set
name="categories"
table="OZO_PRODUCT_CATEGORY"
lazy="true"
inverse="false"
cascade="none"
sort="unsorted"
>

<key
column="productId"
>
</key>

<many-to-many
class="mypackage.model.catalog.Category"
column="categoryId"
outer-join="auto"
/>

</set>

<property
name="createdDate"
type="java.util.Date"
update="true"
insert="true"
column="createdDate"
/>

<property
name="itemNumber"
type="java.lang.String"
update="true"
insert="true"
column="itemNumber"
length="255"
/>

<property
name="longDescription"
type="java.lang.String"
update="true"
insert="true"
column="longDescription"
length="4000"
/>

<property
name="name"
type="java.lang.String"
update="true"
insert="true"
column="name"
length="255"
/>

<many-to-one
name="vendor"
class="mypackage.model.catalog.vendor.Vendor"
cascade="save-update"
outer-join="auto"
update="true"
insert="true"
column="vendorId"
not-null="true"
/>

<property
name="vendorId"
type="java.lang.Long"
update="false"
insert="false"
column="vendorId"
not-null="true"
/>

<property
name="status"
type="java.lang.String"
update="true"
insert="true"
column="status"
/>

<set
name="attributes"
table="OZO_PRODUCT_ATTRIBUTE"
lazy="false"
cascade="none"
sort="unsorted"
>

<key
column="productId"
>
</key>

<element
column="value"
type="string"
not-null="false"
unique="false"
/>

</set>

<property
name="lessAllowed"
type="boolean"
update="true"
insert="true"
column="lessAllowed"
/>

<property
name="maxQuantity"
type="java.lang.Integer"
update="true"
insert="true"
column="maxQuantity"
/>

<property
name="minQuantity"
type="java.lang.Integer"
update="true"
insert="true"
column="minQuantity"
/>

<property
name="multipleQuantity"
type="java.lang.Integer"
update="true"
insert="true"
column="multipleQuantity"
/>

<component
name="price"
class="mypackage.model.catalog.Price"
>
<property
name="msrp"
type="java.lang.Double"
update="true"
insert="true"
column="pricemsrp"
/>

<property
name="displayType"
type="int"
update="true"
insert="true"
column="pricedisplayType"
/>

<set
name="priceRange"
table="OZO_PRODUCT_PRICE_RANGE"
lazy="false"
cascade="all"
sort="natural"
>

<key
column="productId"
>
</key>

<composite-element
class="mypackage.model.catalog.PriceRange"
>

<property
name="lowQty"
type="int"
update="true"
insert="true"
column="lowQty"
/>

<property
name="amount"
type="java.lang.Double"
update="true"
insert="true"
column="amount"
/>

</composite-element>

</set>

<property
name="calculationType"
type="int"
update="true"
insert="true"
column="pricecalculationType"
/>

<property
name="unit"
type="java.lang.String"
update="true"
insert="true"
>
<column
name="pricepricingUnit"
/>
</property>

<property
name="base"
type="java.lang.Double"
update="true"
insert="true"
column="pricebase"
/>

</component>

<component
name="availability"
class="mypackage.model.catalog.Availability"
>
<property
name="country"
type="int"
update="true"
insert="true"
column="availabilitycountry"
/>

<property
name="leadTime"
type="int"
update="true"
insert="true"
column="availabilityleadTime"
/>

<property
name="shipDate"
type="java.util.Date"
update="true"
insert="true"
column="availabilityshipDate"
/>

<property
name="type"
type="int"
update="true"
insert="true"
column="availabilitytype"
/>

</component>

<component
name="customization"
class="mypackage.model.catalog.CustomizationSet"
>

<parent
name="product"
/>

<property
name="allowLogo"
type="boolean"
update="true"
insert="true"
column="customizationallowLogo"
/>

<property
name="baseCharge"
type="java.lang.Double"
update="true"
insert="true"
column="customizationbaseCharge"
/>

<property
name="textLimit"
type="int"
update="true"
insert="true"
column="customizationtextLimit"
/>

<property
name="type"
type="int"
update="true"
insert="true"
column="customizationtype"
/>

</component>

<list
name="allVariableGroups"
table="OZO_PRODUCT_VARIABLE_GROUP"
lazy="true"
cascade="all-delete-orphan"
>

<key
column="productId"
>
</key>

<index
column="displayPosition"
/>

<many-to-many
class="mypackage.model.catalog.VariableGroup"
column="variableGroupId"
outer-join="auto"
/>

</list>

<list
name="assets"
table="OZO_Product_Asset_Info"
lazy="true"
inverse="false"
cascade="all-delete-orphan"
>

<key
column="productId"
>
</key>

<index
column="displayPosition"
/>

<many-to-many
class="mypackage.model.asset.AssetInfo"
column="assetInfoId"
outer-join="auto"
/>

</list>

<property
name="costPerUnit"
type="java.lang.Double"
update="true"
insert="true"
column="costPerUnit"
/>

<property
name="mfrProductNumber"
type="java.lang.String"
update="true"
insert="true"
column="deereProductNumber"
/>

<property
name="manufacturingLineCode"
type="java.lang.String"
update="true"
insert="true"
column="manufacturingLineCode"
/>

<!--
class: mypackage.model.catalog.Product
database: HSQLDB
-->
<property
name="variable"
type="boolean"
formula="(select (count(*) > 0) from ozo_product_variable_group pvg where pvg.productid = id)"
update="false"
insert="false"
/>

<property
name="variableSurcharge"
type="boolean"
formula="(select (count(*) > 0) from ozo_product_variable_group pvg, ozo_variable_option vo where pvg.productid = id and pvg.variableGroupId = vo.variableGroupId and vo.surcharge > 0)"
update="false"
insert="false"
/>

<property
name="quantityDiscountAvailable"
type="boolean"
formula="(select (count(*) > 0) from ozo_product_price_range ppr where ppr.productId = id)"
update="false"
insert="false"
/>

<property
name="promotionAvailable"
type="boolean"
formula="(select (count(*) > 0) from OZO_PROMO_PRODUCT_COMPONENT ppc where ppc.productId = id)"
update="false"
insert="false"
/>

<property
name="imageCount"
type="int"
formula="(select count(*) from ozo_asset_info ai, ozo_product_asset_info pai
where pai.assetInfoId = ai.id and pai.productId = id and ai.type = 'image')"
update="false"
insert="false"
/>

</class>

</hibernate-mapping>


The generated SQL (show_sql=true):

Hibernate: /* criteria query */ select top ? this_.id as id1_, this_.version as version3_1_, this_.briefDescription as briefDes3_3_1_, this_.createdDate as createdD4_3_1_, this_.itemNumber as itemNumber3_1_, this_.longDescription as longDesc6_3_1_, this_.name as name3_1_, this_.vendorId as vendorId3_1_, this_.status as status3_1_, this_.lessAllowed as lessAll10_3_1_, this_.maxQuantity as maxQuan11_3_1_, this_.minQuantity as minQuan12_3_1_, this_.multipleQuantity as multipl13_3_1_, this_.pricemsrp as pricemsrp3_1_, this_.pricedisplayType as pricedi15_3_1_, this_.pricecalculationType as priceca16_3_1_, this_.pricepricingUnit as pricepr17_3_1_, this_.pricebase as pricebase3_1_, this_.availabilitycountry as availab19_3_1_, this_.availabilityleadTime as availab20_3_1_, this_.availabilityshipDate as availab21_3_1_, this_.availabilitytype as availab22_3_1_, this_.customizationallowLogo as customi23_3_1_, this_.customizationbaseCharge as customi24_3_1_, this_.customizationtextLimit as customi25_3_1_, this_.customizationtype as customi26_3_1_, this_.costPerUnit as costPer27_3_1_, this_.mfrProductNumber as
Pr28_3_1_, this_.manufacturingLineCode as manufac29_3_1_, (select (count(*) > 0) from ozo_product_variable_group pvg where pvg.productid = this_.id) as formula3_1_, (select (count(*) > 0) from ozo_product_variable_group pvg, ozo_variable_option vo where pvg.productid = this_.id and pvg.variableGroupId = vo.variableGroupId and vo.surcharge > 0) as formula4_1_, (select (count(*) > 0) from ozo_product_price_range ppr where ppr.productId = this_.id) as formula5_1_, (select (count(*) > 0) from OZO_PROMO_PRODUCT_COMPONENT ppc where ppc.productId = this_.id) as formula6_1_, (select count(*) from ozo_asset_info ai, ozo_product_asset_info pai where pai.assetInfoId = ai.id and pai.productId = this_.id and ai.type = 'image') as formula7_1_, v1_.id as id0_, v1_.version as version11_0_, v1_.address1 as address3_11_0_, v1_.address2 as address4_11_0_, v1_.city as city11_0_, v1_.country as country11_0_, v1_.phone as phone11_0_, v1_.state as state11_0_, v1_.zip as zip11_0_, v1_.contactName as contact10_11_0_, v1_.email as email11_0_, v1_.catalogManagerEmail as catalog12_11_0_, v1_.dailyOrderNotification as dailyOr13_11_0_, v1_.perOrderNotification as perOrde14_11_0_, v1_.businessName as busines15_11_0_, v1_.salesEnabled as salesEn16_11_0_, v1_.minimumDollarAmount as minimum17_11_0_, v1_.surchargeDollarAmount as surchar18_11_0_, v1_.active as active11_0_, v1_.createdDate as created20_11_0_, v1_.supplierNumber as supplie21_11_0_ from OZO_PRODUCT this_ inner join OZO_VENDOR v1_ on this_.vendorId=v1_.id where this_.status=? and v1_.salesEnabled=? and v1_.active=? and this_.id=? order by this_.costPerUnit asc

You need to create a "sub" criteria. The first example in section "Associations", in chapter "Criteria Queries" of the ref doc, is exactly what you need:



So for you it would be more like


where crit is the criteria that you've already set up for the class that contains the set of attributes.

The problem is that here is no form of the method Restrictions.like() (or Restrictions.eq() which is what I really want) that takes a single String parameter.

I've used the strategy you suggest when the members of the set are entities, but in this case, the members of the Set "attributes" are Strings. Therefore, there is no property name I can provide as the first argument to Restrictions.eq()

I had actually tried this before I posted:

[code]
crit.createCriteria("attributes").add(Restrictions.eq("this", attribute));
[code]

but that results in:
[code]
org.hibernate.MappingException: collection was not an association: com.johndeere.orderzone.model.catalog.Product.attributes
[code]

Nifty, never thought about how to do that.
Have you tried playing with Restrictions.sqlRestriction? You can use the column names in that, instead of the attribute names. I've never used it though, so I can't help more than that..

That's where I had left off when I posted originally. That works, so I won't complain, but this does seem like it deserves first-class treatment in the Criteria API. Kind of a corner case, I guess.

I'm sure the developers would welcome a patch from some concerned user :) If there's an easy-but-inelegant workaround (sqlRestriction) and an elegant-but-laborious solution (convert attributes to a simple class of id+string), then the dev team aren't going to want to put much work into this.

I'm having exactly the same problem, but with a Set of java.lang.Long items. I suppose that if Longs had some getValue() / setValue() methods, they would be accesible trough Hibernate and there would be no problem at all, but since Longs aren't "beans", there are nothing to do. :-(