I'd like to map data from a native query to an object with a list in it.
Let's say I have this query (it's a contrived example of what I want to do)
select p.id, p.name, a.address, a.city, s.salestotal, s.salesmonth
from person p, address a, sales s where <Joined somehow>
it returns this data
1 John 123 This Street 100 June
1 John 456 That Street 100 June
1 John 789 There Street 100 June
2 Bill 987 Apple Street 321 April
2 Bill 654 Banana Street 321 April
2 Bill 321 Orange Street 321 April
2 Bill 741 Pear Street 321 April
3 Mary 951 Oak Ave 195 May
notice that the data from person and sales are repeated, but address is unique.
So I want to map it to an object that looks like this:
public class PersonSalesAddressSummary{
private Person person;
private Sales sales;
private List<Address> addresses;
//getters and setters etc...
}
where Person, Sales and Address are all mapped entities.
What I'd like to end up with is a list of 3 PersonSalesAddressSummary but I'm not sure how to set up the resultset mapping...I assume it's possible, but I don't know where to start. Even some help on how to phrase this question properly would be helpful!
Edit: To be more clear one of the objects I'd like to end up with would hold:
PersonSalesAddressSummary
Person = 1 John
Sales = 100 June
List<Address> = 123 This Street,
456 That Street,
789 There Street
At least I hope that's more clear
As far as I know you can't extract a parent->child relationship from a native query. A native query will always give you the raw rows, either as objects or maybe tuples, but it's up to you to map it to a objects.