Spring Data JPA中本地查询结果到DTO的映射方案咨询

以下是几种替代手动映射的高效方案，均基于你提供的实体、DTO和原生SQL查询场景：

方案1：JPA @SqlResultSetMapping + Stream聚合 #

先通过JPA映射将原生SQL结果转为扁平DTO，再用Stream API聚合为嵌套结构，避免手动遍历处理。

步骤1：定义扁平中间DTO #

用于接收原生SQL的每行关联结果：

public record FlatDataAddressDto(
    Long id,
    String name,
    String type,
    Long addressId,
    String street
) {}

步骤2：添加结果映射配置 #

在Data实体类上添加@SqlResultSetMapping，指定SQL结果到扁平DTO的映射规则：

@Entity
@SqlResultSetMapping(
    name = "FlatDataAddressMapping",
    classes = @ConstructorResult(
        targetClass = FlatDataAddressDto.class,
        columns = {
            @ColumnResult(name = "id", type = Long.class),
            @ColumnResult(name = "name", type = String.class),
            @ColumnResult(name = "type", type = String.class),
            @ColumnResult(name = "addressid", type = Long.class),
            @ColumnResult(name = "street", type = String.class)
        }
    )
)
public class Data {
    // 原有实体代码...
}

步骤3：修改Repository查询 #

指定使用上述结果映射：

public interface DataRepository extends CrudRepository<Data, Long> {
    @Query(value = """
        select d.id, name, type, a.id as addressid, a.street as street 
        from data d inner join address a ON d.id = a.data_id
        """, nativeQuery = true, resultSetMapping = "FlatDataAddressMapping")
    List<FlatDataAddressDto> getDataFlat();
}

步骤4：Stream聚合为嵌套DTO #

编写聚合逻辑，将扁平结果合并为带地址列表的DataJoinDto：

// 定义分组用的临时Key
private record DataKey(Long id, String name, String type) {}

public List<DataJoinDto> convertToNestedDto(List<FlatDataAddressDto> flatList) {
    return flatList.stream()
        .collect(Collectors.groupingBy(
            dto -> new DataKey(dto.id(), dto.name(), dto.type()),
            Collectors.mapping(
                flatDto -> new AddressDto(flatDto.addressId(), flatDto.street()),
                Collectors.toList()
            )
        ))
        .entrySet().stream()
        .map(entry -> new DataJoinDto(
            entry.getKey().id(),
            entry.getKey().name(),
            entry.getKey().type(),
            entry.getValue()
        ))
        .collect(Collectors.toList());
}

方案2：MapStruct自动映射 + Stream聚合 #

利用MapStruct自动生成映射代码，减少手动编写映射逻辑的工作量。

步骤1：添加MapStruct依赖（Maven） #

<dependency>
    <groupId>org.mapstruct</groupId>
    <artifactId>mapstruct</artifactId>
    <version>1.5.5.Final</version>
</dependency>
<dependency>
    <groupId>org.mapstruct</groupId>
    <artifactId>mapstruct-processor</artifactId>
    <version>1.5.5.Final</version>
    <scope>provided</scope>
</dependency>

步骤2：定义MapStruct映射接口 #

@Mapper(componentModel = "spring")
public interface DataAddressMapper {
    AddressDto flatToAddressDto(FlatDataAddressDto flatDto);

    @Mapping(target = "addresses", ignore = true)
    DataJoinDto flatToDataJoinDto(FlatDataAddressDto flatDto);
}

步骤3：Service层聚合映射 #

@Service
public class DataService {
    private final DataRepository dataRepository;
    private final DataAddressMapper mapper;

    public DataService(DataRepository dataRepository, DataAddressMapper mapper) {
        this.dataRepository = dataRepository;
        this.mapper = mapper;
    }

    public List<DataJoinDto> getDataWithAddresses() {
        List<FlatDataAddressDto> flatList = dataRepository.getDataFlat();
        return flatList.stream()
            .collect(Collectors.groupingBy(
                dto -> new DataKey(dto.id(), dto.name(), dto.type()),
                Collectors.mapping(mapper::flatToAddressDto, Collectors.toList())
            ))
            .entrySet().stream()
            .map(entry -> new DataJoinDto(
                entry.getKey().id(),
                entry.getKey().name(),
                entry.getKey().type(),
                entry.getValue()
            ))
            .collect(Collectors.toList());
    }
}

方案3：自定义Repository实现直接处理Tuple #

无需中间DTO，直接在Repository实现类中处理Tuple结果并聚合：

步骤1：定义自定义Repository接口 #

public interface DataRepositoryCustom {
    List<DataJoinDto> getDataWithList();
}

步骤2：修改原Repository继承自定义接口 #

public interface DataRepository extends CrudRepository<Data, Long>, DataRepositoryCustom {
}

步骤3：编写Repository实现类 #

@Repository
public class DataRepositoryImpl implements DataRepositoryCustom {
    private final EntityManager entityManager;

    public DataRepositoryImpl(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Override
    public List<DataJoinDto> getDataWithList() {
        List<Tuple> tuples = entityManager.createNativeQuery("""
                select d.id, name, type, a.id as addressid, a.street as street 
                from data d inner join address a ON d.id = a.data_id
                """, Tuple.class)
            .getResultList();

        // 聚合逻辑
        private record DataKey(Long id, String name, String type) {}
        
        return tuples.stream()
            .collect(Collectors.groupingBy(
                tuple -> new DataKey(
                    tuple.get("id", Long.class),
                    tuple.get("name", String.class),
                    tuple.get("type", String.class)
                ),
                Collectors.mapping(
                    tuple -> new AddressDto(
                        tuple.get("addressid", Long.class),
                        tuple.get("street", String.class)
                    ),
                    Collectors.toList()
                )
            ))
            .entrySet().stream()
            .map(entry -> new DataJoinDto(
                entry.getKey().id(),
                entry.getKey().name(),
                entry.getKey().type(),
                entry.getValue()
            ))
            .collect(Collectors.toList());
    }
}

注意事项 #

• 原生SQL的列名需与映射配置中的名称完全匹配（例如addressid需和DTO字段对应）
• 若需保留无关联Address的Data记录，需将内连接改为左连接
• Stream API的分组聚合为单次遍历处理，数据量较大时仍保持高效

内容的提问来源于stack exchange，提问作者Yadier Betancourt Martínez