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