-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 2
  [ 24 posts ]  Go to page 1, 2  Next
  Previous topic | Next topic 
Author Message
rickla
 Post subject: How are auto joins determined?
PostPosted: Thu Sep 04, 2003 5:51 pm 
Beginner
Beginner

Joined: Thu Sep 04, 2003 5:38 pm
Posts: 29
I don't want to post a boatload of examples here, but try to first explain.

My tables are something like this:

airport->city->citystate->state
->citycountry->country

Where these (->) are all many to one. So for a city abbreviation, I map to the state code, and further to the state description, same for country.

The sql generated confuses me though. My hibernate request is set to just get all airport objects. I would expect all of the many-to-one objects to get loaded in as well, via a big join.

But it doesn't do a big join. It seems to get all the airports, then iterate thru doing two more queries for the cities, but some of these are indeed joined.

Is there anything required to get the native sql generated as joins besides mapping many-to-one? The global outer join flag is set to true, and auto for the many to one tag.

Aside from me worrying about performance, this actually fails for invalid city codes (where a join would just leave the value null). So that's really the need for all joins here.

I'll post some of my mess if it will help, but wanted to see if there was something blantant I've missed first.

rick
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Thu Sep 04, 2003 10:24 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
I'm quite certain that this is documented, but:


outer-join has three values:


* true (always join)
* false (never join)
* auto (join when the associated class has no proxy)
Top
 Profile  
 
rickla
 Post subject:
PostPosted: Fri Sep 05, 2003 12:20 am 
Beginner
Beginner

Joined: Thu Sep 04, 2003 5:38 pm
Posts: 29
yes, i do have that at auto.

I simplified my test and still no luck. My main table gets all loaded, and then it loops once to get each city object. Maybe it has something to do with naming or case, I'll have to examine that some more, I still can't see the issue.

With this simplified example I am still assuming it should be doing a join and not iterating all these times...

rick
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Fri Sep 05, 2003 12:28 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
I have no idea what you are talking about when you say "iterating".
Top
 Profile  
 
rickla
 Post subject:
PostPosted: Fri Sep 05, 2003 8:16 am 
Beginner
Beginner

Joined: Thu Sep 04, 2003 5:38 pm
Posts: 29
in the log, i see one sql statement for getting all the airport object. Then for each air object i see a query with a substitution parameter for the city, which appears once for each airport in the first query (2000+ times).

Maybe I am thinking that these log entries are something they are not, but from the logging I was assuming these are queries going over the wire (it does take a while, but I have not tried sniffing the wire).

rick
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Fri Sep 05, 2003 8:30 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
Ooohhhh I get it you are doing a HQL Query!!


Please be much mode explicit next time.


As per the Hibernate documentation, the outer-join attribute does not affect HQL.


Use either

* a JOIN FETCH clause in the HQL
* a Criteria query

you must be explicit if you want outer joins in HQL. This is a Good Thing.
Top
 Profile  
 
rickla
 Post subject:
PostPosted: Fri Sep 05, 2003 9:18 am 
Beginner
Beginner

Joined: Thu Sep 04, 2003 5:38 pm
Posts: 29
Oops! I guess i could have been doing a load right?

So what was happening was my hql wouldn't join because of what you said, but my joins after that worked because internally it was doing a load, right? That makes a lot of sense if so!

Thanks for getting me unstuck, I'll try this right away. In fact i did try the join stuff before but we were on 1.1 and it wouldn't work, I should have tried it again after going to 2.x, doh!
Top
 Profile  
 
rickla
 Post subject:
PostPosted: Fri Sep 05, 2003 2:26 pm 
Beginner
Beginner

Joined: Thu Sep 04, 2003 5:38 pm
Posts: 29
ok, close but not quite got this yet.

The join helped but I didnt'get all records, I needed outer joins.

However, i am running into one more issue.

It does indeed make a big statement that will get all records (I tried it manually), but then it creates and executes a second statement that fails.

The second statement seems to be getting the many-to-one records that have null values. This call fails (because there is no record for this particular many to one) and the whole find stops.
I have not-null set to false on these.

Can the many-to-one somehow tolerate outer joined null placeholders? Maybe my guess is way off and this is really something else.

rick
Top
 Profile  
 
rickla
 Post subject:
PostPosted: Fri Sep 05, 2003 3:34 pm 
Beginner
Beginner

Joined: Thu Sep 04, 2003 5:38 pm
Posts: 29
Thought a dump may help. I looked at the source to try to figure it out, and gather it's trying to construct the SuCityState, finds it is not there in the first sql query, and creates the second one to try and fails.

Somehow it should know that the thing being null is fine and not bother with a query attempt.

I am wondering, what internally tells hibernate the difference between a null value and a yet-to-be populated value?

Code:
2003-09-05 14:19:00,750 INFO  [STDOUT] Hibernate: select suairpor0_.airportcode
as airportcode0_, sucity1_.citycode as citycode1_, sucityco2_.cityCode as cityCo
de2_, sucountr3_.countryCode as countryCode3_, sucityst4_.cityCode as cityCode4_
, sustate5_.statecode as statecode5_, suairpor0_.airportname as airportn2_0_, su
airpor0_.airportcitycode as airportc3_0_, suairpor0_.airportsearable as airports
4_0_, suairpor0_.airportinbufferzone as airporti5_0_, suairpor0_.airportlatitude
as airportl6_0_, suairpor0_.airportlongitude as airportl7_0_, sucity1_.cityName
as cityName1_, sucityco2_.countrycode as countryc2_2_, sucountr3_.countryCode3
as countryC2_3_, sucountr3_.countryName as countryN3_3_, sucountr3_.countryNumbe
r as countryN4_3_, sucityst4_.statecode as statecode4_, sustate5_.statename as s
tatename5_ from su_airport suairpor0_ left outer join su_city sucity1_ on suairp
or0_.airportcitycode=sucity1_.citycode left outer join su_citycountry sucityco2_
on suairpor0_.airportcitycode=sucityco2_.cityCode left outer join su_country su
countr3_ on sucityco2_.countrycode=sucountr3_.countryCode left outer join su_cit
ystate sucityst4_ on suairpor0_.airportcitycode=sucityst4_.cityCode left outer j
oin su_state sustate5_ on sucityst4_.statecode=sustate5_.statecode order by  sua
irpor0_.airportcode asc
2003-09-05 14:19:04,956 INFO  [STDOUT] Hibernate: select sustate1_.statecode as
statecode0_, sustate1_.statename as statename0_, sucityst0_.cityCode as cityCode
1_, sucityst0_.statecode as statecode1_ from su_citystate sucityst0_ left outer
join su_state sustate1_ on sucityst0_.statecode=sustate1_.statecode where sucity
st0_.cityCode=?
2003-09-05 14:19:05,056 FATAL [SULOGGER.main] :VBSF Airport Cache
Exception text -> No row with the given identifier exists: AAE, of class: com.st
udentuniverse.farefilter.persistent.SuCityState
net.sf.hibernate.ObjectNotFoundException: No row with the given identifier exist
s: AAE, of class: com.studentuniverse.farefilter.persistent.SuCityState
        at net.sf.hibernate.impl.SessionImpl.throwObjectNotFound(SessionImpl.jav
a:1678)
        at net.sf.hibernate.impl.SessionImpl.internalLoad(SessionImpl.java:1717)
        at net.sf.hibernate.type.ManyToOneType.resolveIdentifier(ManyToOneType.j
ava:62)
        at net.sf.hibernate.impl.SessionImpl.initializeEntity(SessionImpl.java:1
959)
        at net.sf.hibernate.loader.Loader.doFind(Loader.java:196)
        at net.sf.hibernate.loader.Loader.find(Loader.java:620)
        at net.sf.hibernate.hql.QueryTranslator.find(QueryTranslator.java:928)
        at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1343)
        at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:76)
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Fri Sep 05, 2003 9:25 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
Quote:
The join helped but I didnt'get all records, I needed outer joins.



Then use a LEFT JOIN FETCH!


*Please, please* read the documentation!

Quote:
Can the many-to-one somehow tolerate outer joined null placeholders?



Your database should represent null values as an SQL null. Obviously.
Top
 Profile  
 
rickla
 Post subject:
PostPosted: Fri Sep 05, 2003 9:58 pm 
Beginner
Beginner

Joined: Thu Sep 04, 2003 5:38 pm
Posts: 29
I must have stated this wrong, I did do the left join fetch, and this is the problem. Believe me I've read the docs many times especially today!

I am not talking about database nulls, here's what I mean with a different example that may be clearer than my whacky city tables...

Say I have a Customer object, and one of the mappings is many-to-one for a ZipCity. The ZipCity table maps zip codes to city names, but isn't 100% complete, so I'm going to need outer joins for it all to work, allowing customers with null ZipCity objects.

Most Customers have a zip that will map, but say one has a zip code that isn't in the ZipCity table.

This is where hibernate does indeed get all the outer joined records. In normal sql, that data for the joined zip that doesn't exist would have null data.

But hibernate seems to do another query on the ZipCity table to load that unjoined zip, and it fails, and the entire query fails.

Boy even this sounds confusing, I don't know how to distill it more! Bottom line is I would be ok if the first query just came back and left a null Zip object in there, but that second attempt kills me.

rick
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Fri Sep 05, 2003 10:02 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
As I said. Hibernate requires that null references are represented as an SQL NULL. In any sane relational model, this is correct, since it is also required by the foreign key constraint.

The second query is expected. It is because your relational model is broken.


I suggest you use a UserType if you have a broken relational mode.
Top
 Profile  
 
rickla
 Post subject:
PostPosted: Fri Sep 05, 2003 10:30 pm 
Beginner
Beginner

Joined: Thu Sep 04, 2003 5:38 pm
Posts: 29
I guess fitting hibernate into old tables isn't always possible (or easy!)

I am misunderstanding something though. What's the legitimate purpose of an outer join if not to allow records with missing relations like this? Keep in mind which forum category I'm posting in!

I guess what you are saying is that for each ZipCity that I don't know the city for I should have an entry in ZipCity with a null city description? I couldn't just null the Zip that keys to zipcity because I need that information. In this case, the referential integrity is fine even if there is no matching record, so I wouldn't want a foreign key constraint.

Again I understand my tables are not good (they exist in production), and I really want to clean them up. But I still wish it were possible to have this kind of setup work to fit into existing data, ah well.
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Fri Sep 05, 2003 10:44 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
The purpose of an outer join is to allow us to return Airports that have no City.

What you have is a foreign key with business meaning, which is already Bad. But worse than that it is not always a foreign key! What you should do is use a surrogate primary key for the City table (instead of using the zipcode as the primary key). Then you can add the zipcode to whichever of the two tables you prefer.


But if that is not convenient just use a UserType. Cache all the ZipCity entries in your application at startup, and let the UserType look up the ZipCity from the cache.


P.S. I'm not sure about the US, but in Australia your model is not even correct from a business point of view, since there can be more than one city in a particular postcode.
Top
 Profile  
 
rickla
 Post subject:
PostPosted: Fri Sep 05, 2003 11:06 pm 
Beginner
Beginner

Joined: Thu Sep 04, 2003 5:38 pm
Posts: 29
Quote:
What you have is a foreign key with business meaning, which is already Bad.


I guess I disagree there. The business is king from my view, and getting a surrogate key in there isn't practical (we have other people importing this data in and that's all we get). The zip example isn't real, I realize there are more cities than one in many cases.

Look at it this way. I'm doing what's more like a report query to gather all this data. In that respect, I can join all kinds of things together. Hibernate seems more geared toward the pure relational model, but not reflecting other queries, so I guess I see that.

Still it would be cool if hibernate could tolerate a missing reference and just keep it null. In terms of a report it's would be pretty handy, especially since the HQL isn't really stripped down. It's ripe for using it instead of any jdbc, so that's why I did!

rick
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 2
  [ 24 posts ]  Go to page 1, 2  Next

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.