I have a question on how Batch Reading works with 1-many relationships and Lazy Loading.
Example Object Graph -
Country has 1-m relationship with State, State also has a 1-1 relationship with Country for “back-reference”.
State has a 1-m relationship with City, City also has a 1-1 relationship with State for “back-reference”
Both the above relationships use Transparent Indirection and Batch Reading.
Country is fetched by a named queried by the Primary Key.
A list of States belonging to that country is displayed.
Once the user selects a State the list cities in that the selected State needs to be displayed.
What Happens -
I query a Country by it’s primary key USA, and I get back one object back as expected.
Query TopLink generates – Select * from CountryTable where country = ?
bind => [USA]
I get a list of states by executing myCountry.getStateList and I get back 50 objects back as expected.
Query TopLink generates – Select * CountryTable t0, StateTable t1 WHERE ((t1.country = ?) AND (t0. country = t1. country))
bind => [USA]
Now I want to get a list of cities in one of the states in the above state list; but when I do a myState.getCityList I get back all the cities in the country USA instead of the that one state.
Query TopLink generates – Select * FROM StateTable t3, StateTable t2, CityTable t1, CountryTable t0 WHERE ((((t1.state = t3.state) AND (t1.country = t3.country)) AND (t3.country = ?)) AND ((t0.country = t1.country) AND ((t2.state = t1.state) AND (t2.country = t1.country))))
bind => [USA]
Why is TopLink fetching all the cities based on the country and not the state? Is this normal behavior, if so why?
I only want data for that one state and not for the country.
Is there a way to get only the cities in the one state? Are there any Workbench settings or programmatic way of overriding this behavior?
We use TopLink 10.1.3.5
Yes, this is a result of setting batch reading on the mapping. Batch reading is a performance option for use cases where the application will regularly iterate over results from queries and access each result in the same way - in an all or nothing type approach. If you are not going to access every country's state returned from queries, you will not want to use batch writing on the mapping level. It doesn't seem to make sense to use it for the case you have provided. Any query for say, every country in the world, accessing one of the returned country object's list of states will end up bringing in every country's list of states. Same with cities.
I'd recommend removing the batch reading setting from the mapping and setting it at the query level when useful, and is described in the documentation here:
The link http://docs.oracle.com/cd/E17904_01/web.1111/b32441/rlmapcfg.htm#CHDHBCEI shows how to set or unset this option on the mapping.
Is there any other way to fetch 1-m lazy loaded child objects with only one query?
We chose Batch as it issues only one query and not the n number for each child record which causes too many database trips and hence affects performance.
I'm not sure I understand. Triggering a 1:m results in 1 query to bring in all the children referenced by the relationship for the single parent. Batching allows that one query to bring in all children for all parent objects read in from the initial query. If you want to be selective on which children you bring back, you can query directly on the children, or requery to get only the parent objects you are going to use to iterate over to get the children (and set batch reading on the relationship).
In this case though, you probably don't need batch reading set on the country->state relationship, but might on the state->city relationship.