Hibernate version:3.2
I have a database with 2 tables, address and person.
Address contain street_name, street_number etc.
Mutliple person can share the same address.
We model this by person with a FK address_id
To query who is living at 'Sesame St'.
We use this HQL "from person where address.street_name = 'Sesame St'"
which is translate into SQL as a table join
select * from person p, address a where p.address_id = a.address_id
and a.street_name = 'Sesame St'
The other alternative is use 2 query.
First get the list of address_id. Then search for person that is in that address list
List addressList = session.criteria('from address where street_name = 'Sesame St')
List personList = session.criteria(from person).Restriction.in(addressList)
Those are pseudo code as I do not write them myself.
Approach 1. one query with join table
Approach 2. 2 queries. First get the list of address that we are interested. Then query the person table with the address list.
Which approach is better and why ?
I would suspect a join table query is more efficient.
Thanks for any help.
|