Help With Criteria Query

egervari · **Joined:** Sun Oct 26, 2003 5:05 am **Posts:** 139

Hi, I need some help in getting this criteria query working. I'd like to do a subquery but I'm not really sure how to make it work. The reason I'm using the critieria API is because it provides a cleaner solution to the alternative StringBuffer approach.

Important Mapping documents:

Code:

   <class name="com.upfactor.rns.domain.core.RnsTransaction"
      table="rns_transaction">
      <id name="id" column="id" type="long" unsaved-value="0">
         <generator class="sequence">
            <param name="sequence">rns_transaction_id_seq</param>
         </generator>
      </id>
      <many-to-one name="business" column="business_id"
         class="com.upfactor.rns.domain.business.Business" />
      <property name="cargoControlNumber" column="cargo_control_number" />
      <property name="transactionNumber" column="transaction_number" />
      <many-to-one name="serviceOption" column="service_option_id" class="com.upfactor.rns.domain.core.ServiceOption"/>
      <many-to-one name="releaseOffice" column="release_office_id" class="com.upfactor.rns.domain.core.ReleaseOffice"/>
      <many-to-one name="subLocation" column="sub_location_id" class="com.upfactor.rns.domain.core.SubLocation"/>
      <property name="containerNumber" column="container_number" />
      <bag name="histories" table="rns_transaction_history" inverse="true"
         order-by="date desc" cascade="save-update" outer-join="true" lazy="true">
         <key column="rns_transaction_id"/>
         <one-to-many class="com.upfactor.rns.domain.core.RnsTransactionHistory" />
      </bag>
   </class>

   <class
      name="com.upfactor.rns.domain.core.RnsTransactionHistory"
      table="rns_transaction_history"
   >
      <id name="id" column="id" type="long" unsaved-value="0">
         <generator class="sequence">
            <param name="sequence">rns_transaction_history_id_seq</param>
         </generator>
      </id>
      <many-to-one name="rnsTransaction" column="rns_transaction_id" class="com.upfactor.rns.domain.core.RnsTransaction"/>
      <property name="date" column="date" />
      <many-to-one name="releaseCode" column="release_code_id" class="com.upfactor.rns.domain.core.ReleaseCode"/>
      <property name="ediMessage" column="edi_message" />
      <property name="deliveryInstructions" column="delivery_instructions" />
   </class>

Code between sessionFactory.openSession() and session.close():

Code:

               Criteria criteria = session.createCriteria( RnsTransaction.class );
               criteria.setFetchMode( "histories", FetchMode.EAGER );
                    criteria.add( Expression.eq( "business.id", businessId ) );

               if( !StringUtils.isNullOrEmpty( cargoControlNumber ) ) {
                  criteria.add( Expression.eq( "cargoControlNumber", cargoControlNumber ) );
               }

               if( !StringUtils.isNullOrEmpty( transactionNumber ) ) {
                  criteria.add( Expression.eq( "transactionNumber", transactionNumber ) );
               }

               if( !StringUtils.isNullOrEmpty( containerNumber ) ) {
                  criteria.add( Expression.eq( "containerNumber", containerNumber ) );
               }

               if( !releaseOfficeId.equals( new Long( 0 ) ) ) {
                  criteria.add( Expression.eq( "releaseOffice.id", releaseOfficeId ) );
               }

               if( !subLocationId.equals( new Long( 0 ) ) ) {
                  criteria.add( Expression.eq( "subLocation.id", subLocationId ) );
               }

               Criteria historyCriteria = criteria.createCriteria( "histories", "history" );
               if( !releaseCodeId.equals( new Long( 0 ) ) ) {
                  historyCriteria.add( Expression.eq( "history.releaseCode.id", releaseCodeId ) );
               }

   // problem is on this line:
                    historyCriteria
                  .add( Expression.eq( "history.date", "select max( history.date ) from RnsTransactionHistory history where history.rnsTransaction = transaction )" ) );

               criteria.setFirstResult( firstResult );
               criteria.setMaxResults( maxResults );

               return criteria.list();

Full stack trace of any exception that occurs:

Code:

java.lang.ClassCastException
   at net.sf.hibernate.type.TimestampType.set(TimestampType.java:35)
   at net.sf.hibernate.type.NullableType.nullSafeSet(NullableType.java:48)
   at net.sf.hibernate.type.NullableType.nullSafeSet(NullableType.java:35)
   at net.sf.hibernate.loader.Loader.bindPositionalParameters(Loader.java:749)
   at net.sf.hibernate.loader.Loader.prepareQueryStatement(Loader.java:788)
   at net.sf.hibernate.loader.Loader.doQuery(Loader.java:265)
   at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
   at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
   at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
   at net.sf.hibernate.loader.CriteriaLoader.list(CriteriaLoader.java:118)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:3613)
   at net.sf.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:238)
   at com.upfactor.rns.resource.hibernate.RnsTransactionResourceImpl$4.doInHibernate(RnsTransactionResourceImpl.java:162)
   at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:176)
   at org.springframework.orm.hibernate.HibernateTemplate.executeFind(HibernateTemplate.java:196)
   at com.upfactor.rns.resource.hibernate.RnsTransactionResourceImpl.findAllByCriteria(RnsTransactionResourceImpl.java:124)
   at com.upfactor.rns.resource.RnsTransactionResourceTest.testFindAllByCriteria(RnsTransactionResourceTest.java:201)

Name and version of the database you are using:
postgres 7.5

mveitas · **Posted:** Tue Aug 24, 2004 10:34 am

Code:

historyCriteria.add( Expression.eq( "history.date", "select max( history.date ) from RnsTransactionHistory history where history.rnsTransaction = transaction )" ) );

If I am not mistaken, according to the API, http://www.hibernate.org/hib_docs/api/, the .eq() method of Expression takes a String and then a Object value. The value you are setting for history.date needs to be of type java.util.Date

egervari · **Joined:** Sun Oct 26, 2003 5:05 am **Posts:** 139

Yeah, but I didn't know how to do subqueries in the criteria API as it didn't have any examples. I guess it just can't be done so I have to do a string-buffer-based example :/

skye · **Joined:** Tue Feb 08, 2005 11:59 am **Posts:** 2

I recently figured out how to perform subqueries with the criteria facility in hibernate aproximately 2.1.4 and later. Note this example works in hibernate 2.1.4 and later and has not been tested in hibernate 3.

For purposes of example, we have three tables/objects in our data model. We have a 'person', 'car', and 'carOwner' tables/objects. The carOwner table has two foriegn key colums, one to car and one to person, called 'car' and 'owner' repectively. Both these relationships are defined as one-to-many from the car/person table to the carOwner table, defined as 'cars' and 'owners' respectively. The carOwner table has many-to-one relationships mapping back to person and car.

For this example, we would like to query for cars that have been owned by the same three people. so the query will look something like the following:

Code:

cars = hibernateSession.createCriteria(Car.class)
                 .add(example)
   .createCriteria("owners")
        .add(Expression.eq("owner", person1))

   .createCriteria("car")
                .createCriteria("owners")
         .add(Expression.eq("owner", person2))

                .createCriteria("car")
   .createCriteria("owners")
        .add(Expression.eq("owner", person3))
            .list();

This code only returns cars who have been owned by person1, person2, and person3 where person1, person2, person3 are person objects mapped to the person table. The leap of intuition for me was realizing I could in effect "walk" the object model back from the many-to-many table "carOwners" back to the car table by the createCriteria("car") statements, where I could then define a new subquery on the carOwner table.
The second line demonstrates the use of the example. the properties specified in the example are applied to the subqueries without needing to repeat the line in each subquery. For example this could allow me to modify the query so that only Fords owned by all three people are returned (assuming Ford is specified in the example).

-s

attia · **Joined:** Wed Dec 10, 2003 3:44 pm **Posts:** 12

skye wrote:

I recently figured out how to perform subqueries with the criteria facility in hibernate aproximately 2.1.4 and later.
For this example, we would like to query for cars that have been owned by the same three people. so the query will look something like the following:

Code:

cars = hibernateSession.createCriteria(Car.class)
                 .add(example)
   .createCriteria("owners")
        .add(Expression.eq("owner", person1))

   .createCriteria("car")
                .createCriteria("owners")
         .add(Expression.eq("owner", person2))

                .createCriteria("car")
   .createCriteria("owners")
        .add(Expression.eq("owner", person3))
            .list();

The leap of intuition for me was realizing I could in effect "walk" the object model back from the many-to-many table "carOwners" back to the car table by the createCriteria("car") statements, where I could then define a new subquery on the carOwner table.

This should be added to FAQs!
I have been searching an answer like this in the forums for more than a hour... :(

ithamar · **Joined:** Sun Apr 24, 2005 9:27 pm **Posts:** 1

This is a good example.

But how you do this same query if you don't map and you dont have a object that maps the relation.

This is if you dont have a carOwner object and you have from car a collection of owners. If its possible to make this operation

Thanks in advance for your help.