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

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 
Author Message
 Post subject: Lazy loading, filtering and ternary join
PostPosted: Mon Mar 12, 2007 2:14 pm 
Newbie

Joined: Mon Dec 27, 2004 8:10 pm
Posts: 7
Location: Redwood City, CA
Hibernate version: 3.2.1.ga

Hi all, I have a strange mapping issue that I've been fighting for several days now. I'm dealing with the all-to-often feared legacy database schema...

Customer
-- customer_id

CustomerAddress
-- address_id
-- various address fields

CustomerAddressLink
-- customer_id
-- address_id
-- street_address_indicator
-- mailing_address_indicator

AddressCountyLink
-- customer_id
-- address_id
-- state
-- county

So, what I would like is to get a Customer class, and call getAddresses(), and have it retrieve the addresses I care about (I also need the indicators in the link table). I want the addresses to be lazy loaded though, and I want them to be filtered as well based upon the state/county being passed to me when I get the customer object. The rough sql I would like to occur when I call getAddresses is:

select CustomerAddress.*, CustomerAddressLink.*
from CustomerAddress
inner join CustomerAddressLink
on CustomerAddressLink.address_id = CustomerAddress.address_id
inner join AddressCountyLink
on AddressCountyLink.address_id = CustomerAddress.address_id
and AddressCountyLink.customer_id = CustomerAddress.customer_id
where CustomerAddressLink.customer_id = ?

I've tried specifying this with a Criteria query, which seems to work, but seems to break lazy loading. I've specified the collection mapping in the Customer mapping file as follows:

<set name="addresses" inverse="true" lazy="true" fetch="subselect">
<key column="customer_id"/>
<one-to-many class="CustomerAddress"/>
</set>

Remember that my CustomerAddress class is mapped to both the CustomerAddress and CustomerAddressLink tables. I also defined my criteria as follows:

criteria.setFetchMode("addresses", FetchMode.SELECT)
.createCriteria("addresses")
.add(Restrictions.eq("county", ?))
.add(Restrictions.eq("state", ?));

I also tried using filters, but I can't seem to get those to work across multiple tables, though they are working great for all other tables associated with Customer a bit more directly (it seems they weren't intended for that, but it would sure be cool if you could specify HQL to all mapped tables in filters).

Can anybody point me in the direction that I might *best* be able to map these tables and write this Criteria object. I've trolled all over the forums, and found many similar questions, but most either don't have answers or are just different enough to not really solve the problem I'm solving. It's almost like I have a many-to-many association with two mapping tables (CustomerAddressLink and AddressCountyLink).


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 

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.