How to match on subclass specific field with query criteria?

sankotm · **Posted:** Wed Aug 25, 2004 9:37 am

Hibernate version: 2.1.6

Mapping document:

Code:

<class name="Animal" table="animal">
    <id name="id" type="int" column="id">
      <generator class="native"/>
    </id>
    <property name="objectType" column="object_type" type="string" length="20"/>

    <joined-subclass name="Cat" table="cat">
      <key column="id"/>
      <property name="name" column="name" type="string"/>
    </joined-subclass>

    <joined-subclass name="Dog" table="dog">
      <key column="id"/>
      <property name="age" column="age" type="int"/>
    </joined-subclass>
</class>

Name and version of the database I'm using: MySQL 3.23.49-nt

Hi,
I've one superclass and two subclasses. Following this structure:

Code:

class Animal
int: id
String: animalType

class Cat
int: id
String: name

class Dog
int: id
int: age

I'd like to run a query criteria, which gets for example all dogs of age 5.
I got as far as:

Code:

List animals = session.createCriteria(Animal.class)
          .add(Expression.eq("objectType","dog")
          .list();

but how do I indicate, that what I'm getting are Dog objects ? When I try to add

Code:

.add(Expression.eq("age","5")

I get:

Quote:

java.lang.NullPointerException
at net.sf.hibernate.persister.NormalizedEntityPersister.toColumns(NormalizedEntityPersister.java:1099)
at net.sf.hibernate.expression.AbstractCriterion.getColumns(AbstractCriterion.java:35)
at net.sf.hibernate.expression.SimpleExpression.toSqlString(SimpleExpression.java:40)
at net.sf.hibernate.loader.CriteriaLoader.<init>(CriteriaLoader.java:64)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:3595)
at net.sf.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:238)
at net.sf.hibernate.impl.CriteriaImpl$Subcriteria.list(CriteriaImpl.java:85)
at GetAnimals.main(HQLHelper.java:186)

I know that I can write by own HQL statement, but I'd like to do it without writing any HQL code. Is there a way how to do it with Query Criteria ?

Thanks,
Michal

toastchee · **Joined:** Fri Mar 26, 2004 8:19 am **Posts:** 49

>> .add(Expression.eq("objectType","dog")

should be

.add(Expression.eq("animalType","dog")

jnjintc · **Joined:** Mon Dec 15, 2003 11:34 am **Posts:** 22

did you ever figure out the answer to this question ?

sankotm · **Posted:** Mon Oct 04, 2004 12:24 pm

> did you ever figure out the answer to this question ?

no :-(((

dlaur · **Joined:** Tue Oct 05, 2004 11:22 am **Posts:** 8

I have a more complex model, but I have encountered the same exception in Hibernate 2.1.7.

I have 4 table hierarchy/composition of offices.
One subclass of office is a regional office.
One subclass of office is a field office which contains a reference to a regional office.
One subclass of office is a hearing office which contains a reference to a field office (which in turn contains a reference to its regional office).

Code:

<class name="Office" table="OFFICE">
    <id name="id" type="int" column="id">
      <generator class="native"/>
    </id>
    <property name="officeCode" column="CODE" />
    ... properties ...
</class>

<joined-subclass name="RegionalOffice" extends="Office" table="REGIONAL">
    <key column="id"/>
    ... properties ...
</joined-subclass>

<joined-subclass name="FieldOffice" extends="Office" table="FIELD">
    <key column="id"/>
    <many-to-one name="regionalOffice" class="RegionalOffice" column="ROFFICE" />
    ... properties ...
</joined-subclass>

<joined-subclass name="HearingOffice" extends="Office" table="HEARING">
    <key column="id"/>
    <many-to-one name="fieldOffice" class="FieldOffice" column="FOFFICE" />
    ... properties ...
</joined-subclass>

(Ignore any syntax errors in the above since I typed this from memory)

When I wanted to find all the hearing offices that were associated with certain regional offices, I tried:

Code:

List list = session.createCriteria(HearingOffice.class)
          .add(Expression.eq("fieldOffice.regionalOffice.officeCode","somevalue")
          .list();

I too get this:

Quote:

java.lang.NullPointerException
at net.sf.hibernate.persister.NormalizedEntityPersister.toColumns(NormalizedEntityPersister.java:1067)
at net.sf.hibernate.expression.AbstractCriterion.getColumns(AbstractCriterion.java:42)
at net.sf.hibernate.expression.SimpleExpression.toSqlString(SimpleExpression.java:40)
at net.sf.hibernate.loader.CriteriaLoader.<init>(CriteriaLoader.java:64)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:3630)
at net.sf.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:238)
at ...

Is this a bug?

dlaur · **Joined:** Tue Oct 05, 2004 11:22 am **Posts:** 8

In reference to my previous example, the mapping is correct (enough for me anyway) since the following HQL works fine and returns the expected results.

Code:

from HearingOffice ho where ho.fieldOffice.regionalOffice.officeCode = 'someValue'

This query is part of a much more complex query and I would really like to use Criteria since it allows me to dynamically build queries much more simply than managing the code that concatenates HQL string bits.

dlaur · **Joined:** Tue Oct 05, 2004 11:22 am **Posts:** 8

This forum thread references the same exception in a different context:

http://forum.hibernate.org/viewtopic.php?t=936941

The common issue appears to be a lack of column name resolution from Criteria properties when referencing a column from the base of a joined-subclass.

gavin · **Posted:** Mon Feb 07, 2005 6:11 pm

Negative.

The problem is that in Criteria queries you must use createCriteria() to create a join, as per the documentation.

gavin · **Posted:** Mon Feb 07, 2005 6:54 pm

P.S. I fixed the NPE now, give a QueryException.

dlaur · **Joined:** Tue Oct 05, 2004 11:22 am **Posts:** 8

Eureka! Thank you very much. The QueryException will be helpful to anyone else who has this issue.

It worked perfectly after I changed my code from above to the following:

Code:

List list = session.createCriteria(HearingOffice.class)
.createCriteria("fieldOffice")
.createCriteria("regionalOffice")
.add(Expression.eq("officeCode","somevalue")).list();

Thank you for pointing me in the right direction with the gentle RTFM suggestion!

suzie · **Joined:** Fri Nov 03, 2006 4:57 pm **Posts:** 60

where does the fieldOffice come from in the createCriteria("fieldOffice") method....

dlaur · **Joined:** Tue Oct 05, 2004 11:22 am **Posts:** 8

It is the just the name of the many-to-one property.

Snipped from one of my posts above:

Code:

<joined-subclass name="HearingOffice" extends="Office" table="HEARING">
    <key column="id"/>
    <many-to-one name="fieldOffice" class="FieldOffice" column="FOFFICE" />
    ... properties ...
</joined-subclass>

thomasks · **Joined:** Fri Jun 06, 2008 3:18 pm **Posts:** 2

Using this excellent information,

http://www.hibernate.org/261.html

I have created a UserType implementation for one of my Oracle types. I can now use Hibernate to read data from the database. My Oracle type field is defined in my hbm file as,

Code:

     <property name="materialLink" type="com.apldbio.lims.database.mapping.MaterialLinkUserType">
         <column name="MATERIAL_LINK" sql-type="NAIUT_MATERIAL"/>
     </property>

Now I am having trouble extending my Hibernate Criteria queries to use this newly available-to-Hibernate type,

I have tried two approaches (both with Groovy under Grails) without success. One of the three fields within my Oracle type is 'id' and in the following two hibernate examples I am trying to search by 'id'. I've added a third Example to show how raw SQL works. Any help with getting my Criteria searches to work wold be great.

Example 1:
=======

Code:

groovy> import org.hibernate.criterion.Restrictions
groovy> ctx                                    
groovy> def sessionFactory = ctx.getBean("sessionFactory")
groovy> def session = sessionFactory.openSession()   
groovy> List list = session.createCriteria(Sample.class).add(Expression.eq("condition", "APPROVED")).add(Restrictions.eq("materialLink.id", 1)).list()
groovy> print list.size()

Exception thrown: org.hibernate.QueryException: could not resolve property: materialLink.id of: Sample

org.hibernate.QueryException: could not resolve property: materialLink.id of: Sample
   at org.hibernate.persister.entity.AbstractPropertyMapping.propertyException(AbstractPropertyMapping.java:44)
   at org.hibernate.persister.entity.AbstractPropertyMapping.toColumns(AbstractPropertyMapping.java:59)
   at org.hibernate.persister.entity.BasicEntityPropertyMapping.toColumns(BasicEntityPropertyMapping.java:31)
   at org.hibernate.persister.entity.AbstractEntityPersister.toColumns(AbstractEntityPersister.java:1354)
   at org.hibernate.loader.criteria.CriteriaQueryTranslator.getColumns(CriteriaQueryTranslator.java:434)

Example 2:
=======

Code:

groovy> import org.hibernate.criterion.Expression
groovy> ctx                                    
groovy> def sessionFactory = ctx.getBean("sessionFactory")
groovy> def session = sessionFactory.openSession()   
groovy> List list = session.createCriteria(Sample.class).add(Expression.eq("condition", "APPROVED")).createCriteria("materialLink").add(Expression.eq("id", 1)).list()

Exception thrown: org.hibernate.QueryException: not an association: materialLink

org.hibernate.QueryException: not an association: materialLink
   at org.hibernate.loader.criteria.CriteriaQueryTranslator.getPathEntityName(CriteriaQueryTranslator.java:216)

Example 3:
=======
Raw SQL that works,

Code:

select s.material_link from samples s where s.material_link.id = 810

thomasks · **Joined:** Fri Jun 06, 2008 3:18 pm **Posts:** 2

OK, I've figured it out. I'm adding this in case other people have the same issue in the future.

The secret sauce is to use the Criteria.ROOT_ALIAS final static variable followed by an underscore.

Code:

def session = sessionFactory.openSession()   
def crit = session.createCriteria(Sample.class).add(Restrictions.sqlRestriction("${Criteria.ROOT_ALIAS}_.material_link.id = 810")

def res = crit.list()
println res.size()

Cameron McKenzie · **Posted:** Wed Jun 25, 2008 11:57 am

Thanks thomasks! I was running into the exact same issue and it fixed it!