Map object using @Query and nativeQuery = true in Spring Boot 3.4.3

I'm working with Spring Boot and need to run a complex native SQL query with several JOINS, but I have problem to map the result in the next record (DTO):

public record FindAvailableEventResponseDto(
        UUID id,
        String name,
        String description,
        ZonedDateTime date,
        short capacity,
        short occupiedSeats,
        String organizedBy) {
}

My SQL query is:

SELECT e.id, e.name, e.description, e.date, e.capacity,
       COUNT(s.id) AS occupiedSeats, u.name as organizedBy
FROM events e
LEFT JOIN seats s ON s.eventId = e.id
LEFT JOIN users u on e.organizedBy = u.id
WHERE e.date > :currentDate
  AND s.state = 'OCCUPIED'
GROUP BY e.id, e.name, e.description, e.date, e.capacity, u.name
ORDER BY e.date ASC

Inside my interface EventRepository, I put the @Query annotation like this:

public interface EventRepository extends JpaRepository<Event, UUID> {

    @Query(value = """
            SELECT e.id, e.name, e.description, e.date, e.capacity,
            CAST(COUNT(s.id) AS smallint) AS occupiedSeats, u.name as organizedBy
            FROM events e
            LEFT JOIN seats s ON s.eventId = e.id AND s.state = 'OCCUPIED'
            LEFT JOIN users u on e.organizedBy = u.id
            WHERE e.date > :currentDate
            GROUP BY e.id, e.name, e.description, e.date, e.capacity, u.name
            ORDER BY e.date ASC;
            """, nativeQuery = true)
    List<FindAvailableEventResponseDto> findUpcomingEventsWithAvailableSeats(
            @Param("currentDate") ZonedDateTime currentDate);
}

This code throw the error:

org.springframework.orm.jpa.JpaSystemException: Cannot instantiate query result type 'com.hector.crud.events.dtos.response.FindAvailableEventResponseDto' due to: argument type mismatch\r\n\tat org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:341)\r\n\tat org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:241)\r\n\tat org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:560)\r\n\tat org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)\r\n\tat org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:343)\r\n\tat org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:160)\r\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)\r\n\tat org.springframework.data.jpa.repository.support...

What is the cleanest way to achieve this while keeping the mapping logic inside the repository?

I read about using JPL, but I prefer to use SQL in case I have to change DB engines.

In case you need search queries with similar complexity as above, what is more advisable? I have MapStruct configured in my project, but Java is not my main stack language.

Solution

Please try any of the below solution works:

Option 1: You can modify your query to return an object array or a Tuple, which Spring Data JPA can then map manually.

public interface EventRepository extends JpaRepository<Event, UUID> {
    @Query(value = """
            SELECT e.id, e.name, e.description, e.date, e.capacity,
                   COUNT(s.id) AS occupiedSeats, u.name as organizedBy
            FROM events e
            LEFT JOIN seats s ON s.eventId = e.id AND s.state = 'OCCUPIED'
            LEFT JOIN users u on e.organizedBy = u.id
            WHERE e.date > :currentDate
            GROUP BY e.id, e.name, e.description, e.date, e.capacity, u.name
            ORDER BY e.date ASC
            """, nativeQuery = true)
    List<Object[]> findUpcomingEventsWithAvailableSeats(@Param("currentDate") ZonedDateTime currentDate);
}

And wherever you are using it, you can map it to FindAvailableEventResponseDto something like below:

List<Object[]> results = eventRepository.findUpcomingEventsWithAvailableSeats(currentDate);

List<FindAvailableEventResponseDto> eventResponseDtoList = 
    results.stream()
           .map(result -> {
                UUID id = (UUID) result[0];
                String name = (String) result[1]; 
                String description = (String) result[2];
                ZonedDateTime date = (ZonedDateTime) result[3];
                short capacity = ((Number) result[4]).shortValue();
                short occupiedSeats = ((Number) result[5]).shortValue();
                String organizedBy = (String) result[6];

                return new FindAvailableEventResponseDto(id, name, description, date, capacity, occupiedSeats, organizedBy);
        }).collect(Collectors.toList());

Option 2: You can even make use of Mapper.

Create a Mapper:

 
@Mapper(componentModel = "spring")  
public interface EventMapper {

    @Mapping(source = "result[0]", target = "id")
    @Mapping(source = "result[1]", target = "name")
    @Mapping(source = "result[2]", target = "description")
    @Mapping(source = "result[3]", target = "date")
    @Mapping(source = "result[4]", target = "capacity")
    @Mapping(source = "result[5]", target = "occupiedSeats")
    @Mapping(source = "result[6]", target = "organizedBy")
    FindAvailableEventResponseDto toDto(Object[] result);
}

To use it:

@Autowired  
private EventMapper eventMapper;  
    
List<Object[]> results = eventRepository.findUpcomingEventsWithAvailableSeats(currentDate);

List<FindAvailableEventResponseDto> eventResponseDtoList = results.stream()
                      .map(eventMapper::toDto)
                      .collect(Collectors.toList());