I need help w/ something that might be basic, but I'm having difficulty finding a concise answer to.
I am attempting to perform a "complex" query and return back a proper object graph of my results.
My scenario is similar to a Salesperson tracking system, where Salesperson has a residential State/Region, and they have many "coverage" State/Regions.
Tables & Relationships:
Person-1--------1->StateRegion
Person-1--------0..*->CoverageRegions-1----------1->StateRegion
Person.residentialStateRegion is a column contain 'MD'
Person.residentialStateRegion is an FK column to StateRegion table
CoverageRegions.PersonId is a FK column to Person
CoverageRegions.StateRegion is a column containing 'MD'
Details:
I want to search for Salespersons by their residential State/Region OR coverage region... i.e. I provide 'MD' as a state.
Rather than trying to explain all thing things I've tried and failed with, I'll provide the SQLQuery that successfully gets the distinct set of Person records, which I can turn in to my "Person" objects. No Problems so far...
Code:
SELECT Person.*
FROM PERSON Person
WHERE
Person.PERSON_ID = (
SELECT DISTINCT(PERSON.PERSON_ID)
FROM PERSON
LEFT OUTER JOIN COVERAGE_REGION ON COVERAGE_REGION.PERSON_ID = PERSON.PERSON_ID
WHERE (
COVERAGE_REGION.STATE_REGION_CODE IN ('MD','VA','PA')
OR PERSON.RESIDENTIAL_STATE_REGION_CODE IN ('MD','VA','PA')
)
)
The above is executed in Hibernate w/:
Code:
SQLQuery query= this.getSession().createSQLQuery(hsql.toString());
query.addEntity("Person", Person.class);
My issue comes in when I want to get the associated CoverageRegion objects that the Person object contains. Yes, lazy loading works, but given this is a search function that provides data to a servlet, I don't want n*1 more SELECT statements occuring unnessarily.
What is the correct way to do this, best practice or otherwise? Do they need to be included as another LEFT OUTER JOIN in the top level query, then post-processed manually in Java to create the correct object graph, or is there some inherent Hibernate functionality that I have yet to understand?
Hibernate version:
3.0.5
Mapping documents:
Available on request, but this example is slightly abstracted due to the sensitive nature of the actual scenario.
Assume that:
Person contains a Set of CoverageRegions ("coverageRegions")
Person contains a single StateRegion, keyed off of stateRegionCode (so far presents no issues for loading).
CoverageRegion contains a single StateRegion, keyed off stateRegionCode.
Thanks,
David
Login
FAQ
Search