如何使用@SqlResultSetMapping映射单个Person至地址集合？

你的问题核心在于：用LISTAGG聚合后的字符串结果，无法直接通过@SqlResultSetMapping映射成List<Address>集合。以下提供两种可行的实现思路：

方案一：利用Hibernate实体关联自动映射（推荐） #

如果你的Person和Address实体本身已定义一对多关联（比如Person类里有@OneToMany(mappedBy = "person") private List<Address> addresses;），完全不需要用LISTAGG聚合，直接通过关联查询让Hibernate自动处理集合映射。

1. 修改原生SQL语句 #

去掉聚合逻辑，返回关联的原始数据：

SELECT p.ID, p.Name, a."Person-ID", a.Address, a.City
FROM PERSON p
JOIN Address a ON p.ID = a."Person-ID"
WHERE p.ID = 1

2. 配置@SqlResultSetMapping #

同时映射Person和Address实体，Hibernate会自动将同ID的地址合并到Person的集合中：

@SqlResultSetMapping(
    name = "PersonWithAddressesMapping",
    entities = {
        @EntityResult(
            entityClass = Person.class,
            fields = {
                @FieldResult(name = "id", column = "ID"),
                @FieldResult(name = "name", column = "Name")
            }
        ),
        @EntityResult(
            entityClass = Address.class,
            fields = {
                @FieldResult(name = "personId", column = "Person-ID"),
                @FieldResult(name = "address", column = "Address"),
                @FieldResult(name = "city", column = "City")
            }
        )
    }
)

3. 执行查询 #

直接获取Person实例，其addresses集合会自动填充：

Query query = entityManager.createNativeQuery(sql, "PersonWithAddressesMapping");
query.setParameter("id", 1);
Person person = (Person) query.getSingleResult();
// person.getAddresses() 即为该用户的地址集合

方案二：如果必须用LISTAGG聚合，手动拆分字符串转集合 #

若因业务限制必须用聚合后的字符串，需通过自定义DTO手动拆分字符串生成Address集合。

1. 定义结果DTO #

编写带构造方法的DTO，在构造时拆分聚合字符串：

public class PersonWithAddressesDto {
    private Integer id;
    private String name;
    private List<Address> addresses;

    // 构造方法接收聚合后的字符串参数并拆分
    public PersonWithAddressesDto(Integer id, String name, String addressStr, String cityStr) {
        this.id = id;
        this.name = name;
        this.addresses = new ArrayList<>();
        String[] addressArr = addressStr.split(",");
        String[] cityArr = cityStr.split(",");
        for (int i = 0; i < addressArr.length; i++) {
            Address addr = new Address();
            addr.setAddress(addressArr[i]);
            addr.setCity(cityArr[i]);
            addr.setPersonId(id);
            this.addresses.add(addr);
        }
    }

    // getter、setter方法
}

2. 配置@SqlResultSetMapping #

使用@ConstructorResult映射到DTO的构造方法：

@SqlResultSetMapping(
    name = "PersonWithAddressesDtoMapping",
    classes = {
        @ConstructorResult(
            targetClass = PersonWithAddressesDto.class,
            columns = {
                @ColumnResult(name = "ID", type = Integer.class),
                @ColumnResult(name = "Name", type = String.class),
                @ColumnResult(name = "address_list", type = String.class),
                @ColumnResult(name = "city_list", type = String.class)
            }
        )
    }
)

3. 修改SQL并执行 #

给聚合列起别名，匹配映射配置：

SELECT 
    p.ID, 
    p.Name,
    LISTAGG(a.Address, ',') AS address_list, 
    LISTAGG(a.City, ',') AS city_list
FROM PERSON p
JOIN Address a ON p.ID = a."Person-ID"
WHERE p.ID = 1
GROUP BY p.ID, p.Name

执行查询获取DTO：

Query query = entityManager.createNativeQuery(sql, "PersonWithAddressesDtoMapping");
query.setParameter("id", 1);
PersonWithAddressesDto dto = (PersonWithAddressesDto) query.getSingleResult();
// dto.getAddresses() 即为拆分后的地址集合

注意：要确保LISTAGG使用的分隔符不会出现在地址/城市内容中，避免拆分错误，必要时更换为无冲突的分隔符。

内容的提问来源于stack exchange，提问作者sensen ol