This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes Object Relational Mapping and the fly likes Query woes - determined but struggling Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Query woes - determined but struggling" Watch "Query woes - determined but struggling" New topic
Author

Query woes - determined but struggling

Ed Degeyter
Greenhorn

Joined: Jun 20, 2006
Posts: 7
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

Joined: Jun 20, 2006
Posts: 7
(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 ]
kri shan
Ranch Hand

Joined: Apr 08, 2004
Posts: 1371
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

Joined: Jun 20, 2006
Posts: 7
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

Joined: Apr 08, 2004
Posts: 1371
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

Joined: Jun 20, 2006
Posts: 7
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.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

You are right Ed, MyISAM only pretends to support foreign keys, which is a really curious implementation decision from the MYSQL people!


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
kri shan
Ranch Hand

Joined: Apr 08, 2004
Posts: 1371
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

Joined: Apr 08, 2004
Posts: 1371
Hi Ed, Any update ?
Ed Degeyter
Greenhorn

Joined: Jun 20, 2006
Posts: 7
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

Joined: Apr 08, 2004
Posts: 1371
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

Joined: Jun 20, 2006
Posts: 7
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...
 
 
subject: Query woes - determined but struggling
 
Similar Threads
Hibernate Inheritance..
Colon : in for{} loop?
Displaying things...need help badly!! Please!
Tree creation problem
Error using getParameterValues method