• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

Query woes - determined but struggling

 
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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:



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 ]
 
Ed Degeyter
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
(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:



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?




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 ]
 
Ranch Hand
Posts: 1491
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Ed Degeyter, Why are you using MySQL 5.0/InnoDB instead of
MySQL 5.0/MYISAM standard/default table type ? Any specific reason ?
 
Ed Degeyter
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.


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,




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


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?
 
kri shan
Ranch Hand
Posts: 1491
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ed Degeyter, MyISAM supports foreign keys. I created my sql scripts for my data model thru DBDesigner tool. I got the foreign key support for MyISAM thru DBDesigner4 tool. My DB is MySQL 5.
 
Ed Degeyter
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You are right Ed, MyISAM only pretends to support foreign keys, which is a really curious implementation decision from the MYSQL people!
 
kri shan
Ranch Hand
Posts: 1491
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Ed, In your data model, what is that blank diamond symbol between vet & rescue_center and half-filled diamond symbol between pets & rescue_center ? What relationship diamond symbol represents ?
If it's a object model diamond symbol represents Agreegation.
 
kri shan
Ranch Hand
Posts: 1491
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Ed, Any update ?
 
Ed Degeyter
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
kri shan
Ranch Hand
Posts: 1491
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Ed, Thanks for your reply. I have many-to-many realtionship between 2 tables. Hence i added a link table and split many-to-many to one-to-many & one-to-many relationships. How can i mention the relationship in the hiberante mapping file ?(Whether many-to-many (or) 2 one-to-many ?
 
Ed Degeyter
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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...
 
Liar, liar, pants on fire! refreshing plug:
Smokeless wood heat with a rocket mass heater
https://woodheat.net
reply
    Bookmark Topic Watch Topic
  • New Topic