JPA Native Query Result Set Mapping with repeating data and a list

Question

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

you want you select to be distinct I'm guessing... – Pat B May 11 '16 at 19:19 — Pat B, May 11 '16 at 19:19

score 2 · Accepted Answer · answered May 11 '16 at 19:35

2

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.

answered May 11 '16 at 19:35

K.Nicholas

10,956
4
46
66

right, but can't SqlResultSetMapping help me out here? – JohnL May 11 '16 at 20:03
No I don't think so. It will map columns, or entities, but not lists. – K.Nicholas May 11 '16 at 20:05
That's disappointing. this is going to be a little painful to implement then, Thanks Nicholas – JohnL May 11 '16 at 20:59

score 2 · Answer 2 · answered May 12 '16 at 02:34

As @Nicholas said, SqlResultSetMapping will map only columns or entities. So IMHO, this is one of work around solution for your special mapping case

Your SqlResultSetMapping could be

@SqlResultSetMapping(name = "PersonSalesAddressSummaryDTOMapping",
    classes = @ConstructorResult(
            targetClass = PersonSalesAddressSummary.class,
            columns = {               
                    @ColumnResult(name = "id", type = Integer.class),
                    @ColumnResult(name = "name"),
                    @ColumnResult(name = "address"),
                    @ColumnResult(name = "city"),
                    @ColumnResult(name = "salestotal", type = Integer.class),
                    @ColumnResult(name = "salesmonth"),
            })

)

And PersonSalesAddressSummary DTO, sorry for long and detail source code

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Optional;

public class PersonSalesAddressSummary {

    private static Map<PersonSales, List<Address>> mapPersonSales = new HashMap<>();

    private Person person;
    private Sales sales;
    private List<Address> addresses;

    public PersonSalesAddressSummary(Integer id, String name, String address, String city, Integer salestotal, String salesmonth) {
        Person p = new Person(id, name);
        Sales s = new Sales(salestotal, salesmonth);
        PersonSales ps = new PersonSales(p,s);

        Address a = new Address(address,city);

        Optional<List<Address>> addressList =  Optional.ofNullable(mapPersonSales.get(ps));
        if(addressList.isPresent()){
            addressList.get().add(a);
        }else{
            List<Address> addresses = new ArrayList<>();
            addresses.add(a);
            mapPersonSales.put(ps, addresses);
        }
    }

    class PersonSales {
        private Person person;
        private Sales sales;

        public PersonSales(Person person, Sales sales) {
            this.person = person;
            this.sales = sales;
        }

        public Person getPerson() {
            return person;
        }

        public void setPerson(Person person) {
            this.person = person;
        }

        public Sales getSales() {
            return sales;
        }

        public void setSales(Sales sales) {
            this.sales = sales;
        }

        @Override
        public boolean equals(Object o) {
            if (this == o) return true;
            if (o == null || getClass() != o.getClass()) return false;
            PersonSales that = (PersonSales) o;
            return Objects.equals(person.getId(), that.getPerson().getId())
                    && Objects.equals(person.getName(), that.getPerson().getName())
                    && Objects.equals(sales.getAmount(), that.getSales().getAmount())
                    && Objects.equals(sales.getMonth(), that.getSales().getMonth())
                    ;
        }

        @Override
        public int hashCode() {
            return Objects.hash(person.getId(), person.getName(), sales.getAmount(), sales.getMonth());
        }

        @Override
        public String toString() {
            final StringBuilder sb = new StringBuilder("PersonSalesAddressSummary ").append("\n");
            sb.append("Person = ").append(person.getId()).append(" ").append(person.getName()).append("\n");
            sb.append("Sales = ").append(sales.getAmount()).append(" ").append(sales.getMonth()).append("\n");
            return sb.toString();
        }
    }

    class Person {

        private Integer id;
        private String name;

        public Person(Integer id, String name) {
            this.id = id;
            this.name = name;
        }

        public Integer getId() {
            return id;
        }

        public void setId(Integer id) {
            this.id = id;
        }

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        @Override
        public boolean equals(Object o) {
            if (this == o) return true;
            if (o == null || getClass() != o.getClass()) return false;
            Person person = (Person) o;
            return Objects.equals(id, person.id) &&
                    Objects.equals(name, person.name);
        }

        @Override
        public int hashCode() {
            return Objects.hash(id, name);
        }
    }

    class Sales {
        private Integer amount;
        private String month;

        public Sales(Integer amount, String month) {
            this.amount = amount;
            this.month = month;
        }

        public Integer getAmount() {
            return amount;
        }

        public void setAmount(Integer amount) {
            this.amount = amount;
        }

        public String getMonth() {
            return month;
        }

        public void setMonth(String month) {
            this.month = month;
        }

        @Override
        public boolean equals(Object o) {
            if (this == o) return true;
            if (o == null || getClass() != o.getClass()) return false;
            Sales sales = (Sales) o;
            return Objects.equals(amount, sales.amount) &&
                    Objects.equals(month, sales.month);
        }

        @Override
        public int hashCode() {
            return Objects.hash(amount, month);
        }
    }

    class Address {
        private String address;
        private String city;

        public Address(String address, String city) {
            this.address = address;
            this.city = city;
        }

        public String getAddress() {
            return address;
        }

        public void setAddress(String address) {
            this.address = address;
        }

        public String getCity() {
            return city;
        }

        public void setCity(String city) {
            this.city = city;
        }

        @Override
        public String toString() {
            final StringBuilder sb = new StringBuilder("Address ");
            sb.append(" = ").append(address);
            sb.append(" ").append(city);
            return sb.toString();
        }
    }

    public Person getPerson() {
        return person;
    }

    public void setPerson(Person person) {
        this.person = person;
    }

    public Sales getSales() {
        return sales;
    }

    public void setSales(Sales sales) {
        this.sales = sales;
    }

    public List<Address> getAddresses() {
        return addresses;
    }

    public void setAddresses(List<Address> addresses) {
        this.addresses = addresses;
    }

    public static void main(String[] args){

        new PersonSalesAddressSummary(1, "John", "123", "This Street",      100, "June");
        new PersonSalesAddressSummary(1, "John", "456", "This Street",      100, "June");
        new PersonSalesAddressSummary(1, "John", "789", "There Street",     100, "June");
        new PersonSalesAddressSummary(2, "Bill", "987", "Apple Street",     321, "April");
        new PersonSalesAddressSummary(2, "Bill", "654", "Banana Street",    321, "April");
        new PersonSalesAddressSummary(2, "Bill", "321", "Orange Street",    321, "April");
        new PersonSalesAddressSummary(2, "Bill", "741", "Pear Street",      321, "April");
        new PersonSalesAddressSummary(3, "Mary", "951", "Oak Ave",          195, "May");

        mapPersonSales.entrySet().stream().forEach(System.out::println);
    }
}

Output

PersonSalesAddressSummary 
Person = 3 Mary
Sales = 195 May
=[Address  = 951 Oak Ave]

PersonSalesAddressSummary 
Person = 2 Bill
Sales = 321 April
=[Address  = 987 Apple Street, Address  = 654 Banana Street, Address  = 321 Orange Street, Address  = 741 Pear Street]

PersonSalesAddressSummary 
Person = 1 John
Sales = 100 June
=[Address  = 123 This Street, Address  = 456 This Street, Address  = 789 There Street]

If I have any mistake, please correct me, thanks a lot

thanks @PatB, I think it is better to extract distinctAddress method from constructor for more meaningful — Lam Le, May 12 '16 at 14:48

JPA Native Query Result Set Mapping with repeating data and a list

2 Answers2