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;
}

Asgar · Accepted Answer · 2024-05-06 11:51:34Z

0

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.

edited May 6, 2024 at 11:51

answered May 6, 2024 at 11:40

Asgar

2,4102 gold badges10 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using @SqlResultSetMapping to map to list

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related