hibernate criteria filtering on a set of enum values

Question

Hi I'm using Hibernate 3.2 with a 1.6 JDK and Mysql 5.0 I'm trying to use the criteria api to put together a dynamic filter. However when I add a Restriction on a Set of Enums that is a property of my Criteria object I get a org.hibernate.exception.GenericJDBCException.

my code is like this:

public class FollowUp {
    ...
    public Set<AdminCategory> getAdminCategories() {...}
    public void setAdminCategories(Set<AdminCategory> _arg) { ... }

}

My hibernate mapping file has a set of Enum values as specified in the hibernate documentation: http://docs.jboss.org/hibernate/stable/core/reference/en/html/collections.html#collections-ofvalues. The file (FollowUp.hbm.xml ) is like this

<hibernate-mapping>
    <typedef class="dao.util.HibernateAdminCategoryType" name="main-category" />
    <class name="FollowUp" table="follow_up">
    <!-- other properties here -->
    <set name="mainCategories" table="follow_up_main_categories" fetch="join" lazy="false">
             <key column="fup_id"/>
             <element column="identifier" type="main-category"/>
     </set>
     <!-- other stuff -->
     </class>
</hibernate-mapping>

the criteria filter code is like this:

public void runFilter(FollowUpFilter _filter) {
    Criteria criteria = this.getSession().createCriteria(FollowUp.class);
    if (_filter.hasMainCategories()) {
        criteria.add(Restrictions.in("mainCategories", _filter.getMainCategories()));    
    }
    criteria.setResultTransformer( Criteria.DISTINCT_ROOT_ENTITY );

    criteria.setProjection(Projections.rowCount());
    Integer count = (Integer) criteria.uniqueResult();
    _filter.setTotalSize(count);
}

When I run my tests (with sql output) I get the following error:

Statement parameter 1 not set.
org.hibernate.exception.GenericJDBCException: could not execute query

the sql output is as follows:

select
    count(*) as y0_ 
from
    follow_up this_ 
where
    this_.id in (
        ?
    ) 

Does anyone know the correct way to filter on a set of enum values in a Criteria (in Hibernate 3.2)?

Cheers Simon

Answer 1

I know this is an old question, but I've just encountered silimar issue and found solution.

You have to join mainCategories and query it using elements property. Just like that:

session.createCriteria(FollowUp.class)
    .createAlias("mainCategories", "mainCategories")
    .add(Restrictions.eq("mainCategories.elements", Category.ITEM))
    .list() 

Answer 2

It seems like you are passing an incompatible type of parameter. For example it expects long but you pass int. Set your log level to trace and see what hibernate is expecting so you can decide passing an array or a set or something else.

Answer 3

I looked at a few different pages including the following:

Including this one which looks like an elegant solution for folks using hibernate 3.5 or JPA 2.0 (with annotations):

Hibernate Criteria API - adding a criterion: string should be in collection

This one eventually pointed me to a solution: https://forum.hibernate.org/viewtopic.php?f=1&t=942839&start=0

In the end I did a manual subselect, I'm not happy with it but it works. I can't believe that this is the preferred solution but I couldn't find any other. It's about as ugly as a solution can be and still warrant the name :-(

public void runFilter(AdminNoteFilter _filter) {
   assert _filter != null;
   Criteria criteria = this.getSession().createCriteria(FollowUp.class);
   if (_filter.hasMainCategories()) {
      CriteriaQueryTranslator cqt = null;

      Criterion mainCategoriesCriterion = HibernateFilterUtil.getEnumIdentifierCriterion(_filter.getMainCategories(),
         "{alias}.id in " +
         "(select fup.id " +
         "from follow_up fup, follow_up_main_categories v " +
         "where fup.id = v.fup_id and v.identifier in (" + HibernateFilterUtil.SUBSTITUE_QUESTION_MARKS + "))");
      criteria.add(mainCategoriesCriterion);

   }


   List<FollowUp> adminNotes = (List<FollowUp>) criteria.list();
}

/**
 * constructs a criterion for filtering on a collection of enums using a subselect.
 * <br />https://stackoverflow.com/questions/2967199/hibernate-criteria-filtering-on-a-set-of-enum-values
 * <br />https://forum.hibernate.org/viewtopic.php?f=1&t=942839&start=0
 *
 * @param _enums     non null non empty
 * @param _subSelect non null must contain the {@link #SUBSTITUE_QUESTION_MARKS} string to be substituted
 * @return the Criterion that can be added to a Criteria
 */
private static Criterion getEnumIdentifierCriterion(Set<? extends MarshallableEnum> _enums, String _subSelect) {
   assert _enums != null;
   assert _enums.size() > 0;
   assert _subSelect != null;
   assert _subSelect.contains(SUBSTITUE_QUESTION_MARKS);

   Set<String> identifiersSet = MarshallableEnumUtil.getIdentifiersFromMarshallableEnums(_enums);
   String[] identifiers = identifiersSet.toArray(Constants.EMPTY_STRING_ARRAY);

   // taken from
   //https://forum.hibernate.org/viewtopic.php?f=1&t=942839&start=0
   final org.hibernate.type.Type[] types = new org.hibernate.type.Type[identifiers.length];
   Arrays.fill(types, org.hibernate.Hibernate.STRING);

   final StringBuilder questionMarks = new StringBuilder();
   for (int i = 0; i < identifiers.length; i++) {
      if (i > 0) {
         questionMarks.append(",");
      }
      questionMarks.append("?");
   }
   // substitute in the question marks to the sub select subselect
   String finalSubSelect = _subSelect.replace(SUBSTITUE_QUESTION_MARKS, questionMarks);

   return Restrictions.sqlRestriction(finalSubSelect, identifiers, types);
}

private static Set<String> getIdentifiersFromMarshallableEnums(Set<? extends MarshallableEnum> _enums) {
   Set<String> retSet = new HashSet<String>();
   for (MarshallableEnum tmpEnum : _enums) {
      retSet.add(tmpEnum.getIdentifier());
   }
   return retSet;
}

Hope this helps, if you anyone finds a better solution for the version of hibernate (3.2) please post it to help the community

Cheers Simon