Query woes - determined but struggling

Edde · **Joined:** Sun Jun 18, 2006 9:41 pm **Posts:** 5

DISCLAIMER: A long post, but I hope to show everyone that I have tried to figure it out on my own. Any assistance would be much appreciated!!

Hibernate 3.2.CR2
MySQL 5.0 / InnoDB

A RescueCenter contains 1 Vet and 0...* Pet objects. A Pet contains 1 data:PetData. Both PetData.size and Vet.age are of type Integer so that null values are stored when empty. For the front-end, I have an HTML page using JSF to populate and search the DB.

I understand the basic select, insert, update, and delete SQL commands. I'm shaky when it comes to the different JOINs. I could really use some help with the proper syntax for querying with hibernate.

(1) PROPER ID ASSIGNMENT: When saving a rescue center, I don't want to insert duplicate vet and pet_data. To ensure this, should I check for a row with identical data in those rows.. if one is found, set that object to the pet/rescue center. For example:

Code:

for( int i=0; i < pets.size(); i++ ) {
    PetData result = (PetData)session.createCriteria( PetData.class ).add( Example.create( petData ) ).uniqueResult();

    if( result != null ) {
        pets.set( i, result );
    }
}

After checking (and replacing) all the vet/pet_data dupes, I then save the rescue center [ session.save / .saveOrUpdate( rescueCenter ) ]. Is this the best (or only) way to handle this situation?

Edde · **Joined:** Sun Jun 18, 2006 9:41 pm **Posts:** 5

(2) SEARCHING FOR OBJECT(s): Now imagine theres a webpage that allows you to search any of those fields. Lets say a person enters "Boston Terrier" in the breed textfield and clicks search. I want a list of all the rescue centers that have that breed with their associated vet, vet_note, pets (all of them), and pet_notes. In short, all the info contained in a rescue center.

Since it ignores null properties, the createCriteria().add( Example ) syntax of (1) is an easy way to return PetData objects containing that breed. I had hoped it would be as simple as replacing PetData with RescueCenter, but instead it returned all the RescueCenters, regardless if it contained a PetData of that breed or not. For example:

Code:

 // Replace below with JSF backing beans
PetData petData = new PetData();
petData.setBreed( "Boston Terrier" );
Pet pet = new Pet();
pet.setData( petData );
RescueCenter rescueCenter = new RescueCenter();
rescueCenter.getPets().add( pet );

// This returns the correct PetData object
List result = session.createCriteria( PetData.class ).add( Example.create( petData ).ignoreCase() ).list();

// This returns all of the RescueCenter objects in the DB
List result = session.createCriteria( RescueCenter.class ).add( Example.create( rescueCenter ).ignoreCase() ).list();

Hibernate: select this_.idrescue_center as idrescue1_0_, this_.idvet as idvet0_0_, this_.vet_note as vet3_0_0_ from rescue_center this_ where (1=1)

What if I wanted to do a search for a breed and a particular vet. Is there a way to search for a RescueCenter without specifying every property I'm searching for like I did with createCriteria( PetData )?

I was able to get the result I wanted by using the query below, but do I have to dynamically build the where clause (excluding all the null properties) each time a search is submitted?

Code:

// It works, but I am still shaky with joins
String query = "select rc from RescueCenter as rc
   join rc.pets as pet
   join rc.vet as vet
   join pet.data as pdata
   where pdata.breed = :breed
   and vet.name = :vet;

session.createQuery( query )
  .setParameter( "breed", "Boston Terrier" )
  .setParameter( "vet", "Tom Yugteveht" )
  .list();

In short, can I populate a object that contains other objects and search for it without specifying any properties? If so, how? If not, whats a solution?

RocketJocket · **Joined:** Wed Jun 21, 2006 9:04 am **Posts:** 1

id like to see an answer to this question also.

belugabob · **Posted:** Wed Jun 21, 2006 11:37 am

I'll try to digest the problem and help you out later - but for now I have one suggestion...

The age of a vet should not be stored in the database - it should be calculated from their date of birth (which should be in the DB).

If you only store the age, you'll have to update this field every year, or they'll never age (Female vets may like this feature, though). Even if you did choose to do things this way, you'd still need their DOB, so that you knew when to update the age field, so you may just as well use the DOB on it's own, anyway.

Will get back to you on an answer to your original question a.s.a.p.

Bob

Edde · **Joined:** Sun Jun 18, 2006 9:41 pm **Posts:** 5

Bob,

Thanks for the response!! About the fields (i.e., age), they aren't representative of the actual data that will be in the db. I just put some fields in there to test out queries. I will use your suggestion for DOB though... thanks!

I did experiment a lil further with trying to use createCriteria to accomplish the query I'm after.

Code:

Vet vet = new Vet();
vet.setName( "Tom Yugteveht" );
RescueCenter rescueCenter = new RescueCenter();
rescueCenter.setVetNote( "good with dogs" );
rescueCenter.setVet( vet );

List result = session.createCriteria( RescueCenter.class ).add( Example.create( rescueCenter ) )
         .createCriteria( "vet" ).add( Example.create( vet ) ).list();

This returns only the rescue centers that have that vet note and vet name... so theres a way to search more than one object. I tried to add multiple criterias, but could only add criterias of properties from the last criteria. For instance,

Code:

PetData pdata = new PetData();
pdata.setBreed( "Boston Terrier" );
Pet pet = new Pet();
pet.setData( pdata );
List list = new ArrayList();
list.add( pet );
rescueCenter.setPets( list );

List result = session.createCriteria( RescueCenter.class ).add( Example.create( rescueCenter ) )
         .createCriteria( "vet" ).add( Example.create( vdata ) )
         .createCriteria( "pets" ).add( Example.create( pdata ) ).list();

could not resolve property: pets of: Vet

I also couldn't figure out how to do an Example with pets (a list):

Code:

List result = session.createCriteria( RescueCenter.class ).add( Example.create( rescueCenter ) )
         .createCriteria( "pets" ).createCriteria( "data" ).add( Example.create( pdata ) ).list();

collection was not an association: pets

If I could add Examples for all the objects (Rescue Center, Vet, Pet(s), and PetData(s)) in one Criteria, then I could search using the JSF backing beans without having to specify every non-null property in a query. Is this possible?

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

Investiage Criteria.createAlias: in your example code, there is no reason to use Criteria.createCriteria(), it's just going to make things complicated for you. Rewrite your Criteria using createAlias, it'll help in several ways.

One of the problems with using query by example is that you can't query for one property having more than one value. For example, you can't query for a rescue centre where the vet note is a given value or null. Don't use query by example for this, use normal Criteria.