Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 15 posts ] 
Author Message
 Post subject: How to match on subclass specific field with query criteria?
PostPosted: Wed Aug 25, 2004 9:37 am 
Newbie

Joined: Wed Aug 25, 2004 9:15 am
Posts: 2
Location: Dublin
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


Top
 Profile  
 
 Post subject:
PostPosted: Wed Sep 22, 2004 5:02 pm 
Beginner
Beginner

Joined: Fri Mar 26, 2004 8:19 am
Posts: 49
>> .add(Expression.eq("objectType","dog")

should be

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


Top
 Profile  
 
 Post subject:
PostPosted: Sat Oct 02, 2004 4:20 pm 
Beginner
Beginner

Joined: Mon Dec 15, 2003 11:34 am
Posts: 22
did you ever figure out the answer to this question ?


Top
 Profile  
 
 Post subject:
PostPosted: Mon Oct 04, 2004 12:24 pm 
Newbie

Joined: Wed Aug 25, 2004 9:15 am
Posts: 2
Location: Dublin
> did you ever figure out the answer to this question ?

no :-(((


Top
 Profile  
 
 Post subject: Criteria and joined-subclass
PostPosted: Mon Feb 07, 2005 2:57 pm 
Newbie

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?


Top
 Profile  
 
 Post subject:
PostPosted: Mon Feb 07, 2005 3:12 pm 
Newbie

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.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Feb 07, 2005 3:33 pm 
Newbie

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.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Feb 07, 2005 6:11 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
Negative.

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


Top
 Profile  
 
 Post subject:
PostPosted: Mon Feb 07, 2005 6:54 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
P.S. I fixed the NPE now, give a QueryException.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Feb 07, 2005 7:12 pm 
Newbie

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!


Top
 Profile  
 
 Post subject:
PostPosted: Fri Dec 15, 2006 5:19 pm 
Regular
Regular

Joined: Fri Nov 03, 2006 4:57 pm
Posts: 60
where does the fieldOffice come from in the createCriteria("fieldOffice") method....


Top
 Profile  
 
 Post subject:
PostPosted: Sun Dec 17, 2006 12:10 am 
Newbie

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>


Top
 Profile  
 
 Post subject: Criteria query against Oracle user types
PostPosted: Tue Jun 17, 2008 5:05 pm 
Newbie

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


Last edited by thomasks on Tue Jun 24, 2008 7:31 pm, edited 1 time in total.

Top
 Profile  
 
 Post subject:
PostPosted: Tue Jun 24, 2008 7:29 pm 
Newbie

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()


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jun 25, 2008 11:57 am 
Expert
Expert

Joined: Tue May 13, 2008 3:42 pm
Posts: 919
Location: Toronto & Ajax Ontario www.hibernatemadeeasy.com
Thanks thomasks! I was running into the exact same issue and it fixed it!

_________________
Cameron McKenzie - Author of "Hibernate Made Easy" and "What is WebSphere?"
http://www.TheBookOnHibernate.com Check out my 'easy to follow' Hibernate & JPA Tutorials


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 15 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.