How to use Criteria API with discriminators?

chipman · **Posted:** Wed Feb 15, 2006 8:15 pm

I have a table-per-class-hierarchy situation and I'm trying to use the Criteria API to query for objects by their type. For example, take the following mapping snippet which maps a User abstract class and its two subclasses, Employee & Contractor:

Code:

<class name="User" table="user" discriminator-value="null">
   <id name="id" type="long" column="userid">
      <generator class="native" />
   </id>
   <discriminator column="usertype" type="integer" />
   
   <subclass name="Employee" discriminator-value="1">
      <property name="employeeId" column="employeeid" />
   </subclass>

   <subclass name="Contractor" discriminator-value="2">
      <property name="contactNumber" column="telnumber" />
   </sublass>
</class>

Now, how could I build a Criterion to limit my query results to Employee users only? I might be missing something simple here, but any help would be much appreciated.
Thomas

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Code:

Criteria c = session.createCriteria(Employee.class);

Yep, something simple was missed :)

chipman · **Posted:** Thu Feb 16, 2006 5:46 pm

yeah, i realized afterwards that I should have been a bit more clear. I'm building the query at runtime and may need to return more than one type of User subclass at once. So lets say there are actually four types:
- Employee
- Contractor
- Visitor
- Shareholder

The user needs to be able to pick one or more of these types to search on. So, they could choose Contactors and Visitors and the method that builds the Critera would need to include both of these types. So it wouldn't work using session.createCriteria(Employee.class); or session.createCriteria(User.class);

Any ideas?

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

As far as I'm aware, there is no equivalent in Criteria to the ".class" functionality offered by HQL queries. However, you have options:

- you can use DetachedCriteria for each subtype (Employee, Shareholder) then Disjunction/Restrictions.or them together in a User.class Criteria, or
- use Restrictions.sqlRestriction on the discriminator column.

chipman · **Posted:** Thu Feb 16, 2006 7:26 pm

I was hoping I would have access to the "class" as a property so that I could do something like:

Code:

criteria.add(Expression.eq("class", Employee.class));
but not surprisingly, it's not that easy.

Anyway, I think I follow the two options you listed. I don't want to go the second route as I'd then have to reference the integer discriminator values in the Java code.

As for the first option, if I create DetachedCriteria for each of the subclasses in the query as follows:

Code:

DetachedCriteria employeeCriteria = DetachedCriteria.forClass(Employee.class);
DetachedCriteria shareholderCriteria = DetachedCriteria.forClass(Shareholder.class);

how would I then merge them with my original User.class Criteria?

thanks a lot for your help so far

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

I think that it would be something along these lines:

Code:

Criteria userCrit = session.createCriteria(User.class);
DetachedCriteria empCrit =
  DetachedCriteria.forClass(Employee.class)
                  .add(Restrictions.ilike("name", sName);
DetachedCriteria shrCrit =
  DetachedCriteria.forClass(Shareholder.class)
                  .add(Restrictions.ilike("name", sName);
Disjunction orList = new Disjunction().add(empCrit, shrCrit);

userCrit.add(Property.forName("name").eq(orList));

Note that this uses subqueries and is therefore pretty inefficient. It's also speculative, I've never tried it. I'd prefer the sqlRestriction route. If you're worried about using discriminator values in outside of the data access layer, then you can just create classes in the data access layer like this:

Code:

public class EmployeeRestriction extends SQLCriterion
{
  public EmployeeRestriction()
  {
    super("({alias}.usertype = 1)");
  }
}

This is probably the most efficient way to do things, and will certainly work. You can then use a User.class Criteria, and call userCrit.add(new EmployeeRestriction()) to limit the user type to Employee.

james_nemesh · **Joined:** Thu Dec 01, 2005 11:34 am **Posts:** 19

tenwit wrote:

I think that it would be something along these lines:

Code:

Criteria userCrit = session.createCriteria(User.class);
DetachedCriteria empCrit =
  DetachedCriteria.forClass(Employee.class)
                  .add(Restrictions.ilike("name", sName);
DetachedCriteria shrCrit =
  DetachedCriteria.forClass(Shareholder.class)
                  .add(Restrictions.ilike("name", sName);
Disjunction orList = new Disjunction().add(empCrit, shrCrit);

userCrit.add(Property.forName("name").eq(orList));

Note that this uses subqueries and is therefore pretty inefficient. It's also speculative, I've never tried it. I'd prefer the sqlRestriction route. If you're worried about using discriminator values in outside of the data access layer, then you can just create classes in the data access layer like this:

Code:

public class EmployeeRestriction extends SQLCriterion
{
  public EmployeeRestriction()
  {
    super("({alias}.usertype = 1)");
  }
}

This is probably the most efficient way to do things, and will certainly work. You can then use a User.class Criteria, and call userCrit.add(new EmployeeRestriction()) to limit the user type to Employee.

I'm not sure how the second approach is supposed to work. I've got a similar problem I've been searching the forums for.

There's no public constructor for SQLCriterion, so that approach doesn't work. I just plugged that class into an IDE, and it's not valid. Otherwise, it would have been nice.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

There's a protected constructor for it. You're extending it. It works, you just have to not be excessively literal when reading my sample code.

mmc · **Posted:** Mon Sep 04, 2006 6:29 am

I think you can access the 'class' in a simple manner via a Restriction and you can 'OR' them using Disjunction as already described. The following should achieve what you want:

Code:

Criteria criteriaUser = session.createCriteria(User.class);

// Refer to the sub-types of interest. 
Criterion criterionEmployee    = Restrictions.eq("class", Employee.class);
Criterion criterionShareholder = Restrictions.eq("class", Shareholder.class);

// Set the 'OR'.
Disjunction disjunction = Restrictions.disjunction();
disjunction.add(criterionEmployee);
disjunction.add(criterionShareholder);

// Add the 'OR'd subtypes.
criteriaUser.add(disjunction);

pasha · **Joined:** Sat Nov 27, 2004 4:13 am **Posts:** 137

you could map your discriminator column to a no insert/no update property, and then easily use it in your HQL or Criteria...

james_nemesh · **Joined:** Thu Dec 01, 2005 11:34 am **Posts:** 19

mmc wrote:

I think you can access the 'class' in a simple manner via a Restriction and you can 'OR' them using Disjunction as already described. The following should achieve what you want:

Code:

Criteria criteriaUser = session.createCriteria(User.class);

// Refer to the sub-types of interest. 
Criterion criterionEmployee    = Restrictions.eq("class", Employee.class);
Criterion criterionShareholder = Restrictions.eq("class", Shareholder.class);

// Set the 'OR'.
Disjunction disjunction = Restrictions.disjunction();
disjunction.add(criterionEmployee);
disjunction.add(criterionShareholder);

// Add the 'OR'd subtypes.
criteriaUser.add(disjunction);

Now, I can't be sure, but I think that class restrictions are for HQL only, and not criteria. I think there are a number of other posts on the forum that say that.

Best way to check would be to test it, though.

james_nemesh · **Joined:** Thu Dec 01, 2005 11:34 am **Posts:** 19

pasha wrote:

you could map your discriminator column to a no insert/no update property, and then easily use it in your HQL or Criteria...

We found something similar, which was to map each of the subtypes that the discriminator chooses against to a getter method and the cfg.hbm document, and then we're able to select that class directly by using a restriction on the parent class.

If the parent abstract class is Foo, and Foo has a T getValue() method, then the discriminator maps to subclasses of Foo. We added getFooDouble() getFooString(), etc to the parent class, then we can use any of those in the Criteria query.

I was hoping to have some 'smarter' way to query out what I wanted, but hibernate isn't smart enough to look at whatever value I pass into the query and determine the subtype of Foo.

It's too bad you can't join Criteria Queries together more easily, or I could specifically start a criteria query against the subtype I wanted, and do an association to the main query.