Using @SqlResultSetMapping to map to list

Question

Person

ID	Name
1	Luis
2	Frank

Address

Person-ID	Address	City
1	Samantha Road	x
1	Franklin	y

In my Hibernate native query I want to map the result of one person to a set of addresses, such like the query below.

SELECT ID, LISTAGG(Address, ','), LISTAGG(City, ',') 
FROM PERSON
JOIN Address ON ID = Person-ID
WHERE ID= 1
GROUP BY ID

I am not sure how to implement the SqlResultSetMapping

@SqlResultSetMapping(name = "Result",
        entities = { @EntityResult(entityClass = Person.class,
                fields = { @FieldResult(name = "id", column = "ID"),
                          {adresses but how...} }) })

public class Result{
    @Id
    int rowNumber;
    private List<Address> adresses;
}

Answer 1

That is not possible. The result of an SQL query is always a table. It does not have tables inside the table. That is why you cannot map it into collection directly. There can be work arounds but no, directly you cannot.

LISTAGG() is the equivalent of GROUP_CONCAT() in MySQL. This will not provide you with a list, rather it will provide you with concatenated value as STRING. What you choose to do with this STRING value is upto you.

For example, you can create variables like

public class Result{

    int rowNumber;

    private String concattedAddresses;

    private List<Address> adresses;

  public Result(int rowNumber, String concattedAddresses){
    this.rowNumber=rowNumber;
    this.concattedAddresses=concattedAddresses;
    this.adresses=Arrays.asList(concattedAddresses.split(","));
  }

}

You can change your @SqlResultSetMapping accordingly.