Ed Degeyter

gundum,

Are you using the 'column' attribute in the elements that represent a relationship? For instance,

<many-to-one name="pojo_name" column="foreign_key" />

I've let hibernate generate the tables in this example ( https://coderanch.com/t/216384/ORM/java/Query-woes-determined-but-struggling ) and the foreign keys were what I labelled them.

Hope this helps...

Kri, I'm not sure what you are asking. If you want some examples of those relationships, I would recommend the following links:
http://www.xylax.net/hibernate/manytomany.html
http://www.xylax.net/hibernate/onetomany.html

Hope this helps...

kri shan,

Sorry I dissappeared for a lil bit. I've been putting extra long hours at work to implement hibernate.. I needed a few days of nothing to make up for it.

As far as your diamonds question, I too am using DBDesigner to build my DB Schema. By default, it uses the EER (Extended Entity-Relationship) symbols. I just found a way to change it to crows foot which I prefer although I'm not sure if thats the norm.

The half-filled diamond is a one-to-many relationship (one rescue center has many pets), and the clear diamond is a one-to-one relationship (one rescue center has one vet). Hope this helps... I'll be continuing to investigate a solution for my hibernate questions.

According to the 5.1 MySQL reference manual, "For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it. In the future, the implementation will be extended to store this information in the table specification file so that it may be retrieved by mysqldump and ODBC. At a later stage, foreign key constraints will be implemented for MyISAM tables as well."

http://dev.mysql.com/doc/refman/5.1/en/ansi-diff-foreign-keys.html

You can create tables with foreign keys (using DBDesigner), but you're storage engine is what enforces them. To my knowledge, MyISAM treats foreign keys like every other column.

kri shan,

Thanks for the response! As to why I used InnoDB... when I thought about applying foreign keys and utilizing transactions, I thought InnoDB was the right storage engine for the job. I'd also like to experiment with views, and some of the more advanced db features. I don't keep up with all the capabilities of all the storage engines. Does MyISAM ignore foreign keys? Why do you ask? I'm here to learn, and would be interested to hear your thoughts.


As far as my search for hibernate answers...

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

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,


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):
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?

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

// 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?

// 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?
[ June 20, 2006: Message edited by: Ed Degeyter ]

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 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:

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?

[ June 20, 2006: Message edited by: Ed Degeyter ]
[ June 20, 2006: Message edited by: Ed Degeyter ]