I’m trying to map the results of a native SQL query executed through Spring Data JPA to a Data Transfer Object (DTO) class. The query retrieves data from two related tables: Data and Address.

Here are the details:

Entity Classes:

@Entity
public class Data {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private Long id;

    private String name;

    @Column(length = 500)
    private String description;

    private String type;

    private LocalDateTime date;

    @OneToMany(fetch = FetchType.LAZY)
    private List<Address> addresses;

}

@Entity
public class Address {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private Long id;

    private String street;

    @ManyToOne(fetch = FetchType.LAZY)
    private Data data;

}

DataRepository:

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)
  List<Tuple> getDataWithList();
}

This interface defines a custom query named getDataWithList that uses a native SQL query to retrieve data from the Data and Address tables with an inner join.
DTO Class:

public record DataJoinDto(
        Long id,
        String name,
        String type,
        List<AddressDto> addresses
) { }

public record AddressDto(Long id, String street) { }

I want to map the elements in the List returned by the query to instances of the DataJoinDto class. I need to use NATIVE QUERIES.

I’ve attempted manual mapping, but it’s not ideal:

    public List<DataJoinDto> mapTuplesToDataJoinDtos(List<Tuple> tuples) {
        List<DataJoinDto> dataJoinDtos = new ArrayList<>();
        for (Tuple tuple : tuples) {
            Long dataId = (Long) tuple.get("id");
            String dataName = (String) tuple.get("name");
            String dataType = (String) tuple.get("type");

            List<AddressDto> addressDtos = new ArrayList<>();
            for (Tuple addressTuple : tuples) {
                if (dataId.equals(addressTuple.get("id"))) {
                    Long addressId = (Long) addressTuple.get("addressid");
                    String street = (String) addressTuple.get("street");
                    addressDtos.add(new AddressDto(addressId, street));
                }
            }
            dataJoinDtos.add(new DataJoinDto(dataId, dataName, dataType, addressDtos));
        }
        return dataJoinDtos;
    }

This approach iterates through the tuples list twice, resulting in a time complexity of O(n^2), which can be inefficient for larger datasets.

I’m seeking a more efficient and potentially cleaner way to achieve the mapping.