How to return DTO

Question

There is a select which returns:

current_id  -> integer
current_date -> date
current_position ->  character_varying (255)
current_relevant_url -> character_varying (255)
current_restrictions_probability -> character_varying (255)
current_url_changed ->  character_varying (255)
current_identifier_id -> integer
phrase_id -> integer
previous_id -> integer
previous_date -> date
previous_position -> character_varying (255)
previous_relevant_url -> character_varying (255)
previous_restrictions_probability -> character_varying (255)
previous_url_changed -> character_varying (255)
previous_identifier_id -> integer

Here it is:

@Query(value = """
select
       current.id as current_id,
       current.date as current_date,
       current.position as current_position,
       current.relevant_url as current_relevant_url,
       current.restrictions_probability as current_restrictions_probability,
       current.url_changed as current_url_changed,
       current.identifier_id as current_identifier_id,
       current.phrase_id as phrase_id,
       previous.id as previous_id,
       previous.date as previous_date,
       previous.position as previous_position,
       previous.relevant_url as previous_relevant_url,
       previous.restrictions_probability as previous_restrictions_probability,
       previous.url_changed as previous_url_changed,
       previous.identifier_id as previous_identifier_id
from (
select
       id,
       date,
       position,
       relevant_url,
       restrictions_probability,
       url_changed,
       identifier_id,
       phrase_id
from overoptimisation
where identifier_id = :currentOveroptimisationIdentifierId) current

left join (
select
       id,
       date,
       position,
       relevant_url,
       restrictions_probability,
       url_changed,
       identifier_id,
       phrase_id
from overoptimisation
where identifier_id = :previousOveroptimisationIdentifierId) previous

on current.phrase_id = previous.phrase_id
""",
    nativeQuery = true)
    List<OveroptimisationDto> report(@Param("previousOveroptimisationIdentifierId") Integer previousOveroptimisationIdentifierId,
                                     @Param("currentOveroptimisationIdentifierId") Integer currentOveroptimisationIdentifierId);

DTO:

@Getter
@Setter
public class OveroptimisationDto {
    Integer currentId;
    Date currentDate;
    String currentPosition;
    String currentRelevantUrl;
    String currentRestrictionsProbability;
    String currentUrlChanged;
    Integer currentIdentifierId;
    Integer phraseId;
    Integer previousId;
    Date previousDate;
    String previousPosition;
    String previousRelevantUrl;
    String previousRestrictionsProbability;
    String previousUrlChanged;
    Integer previousIdentifier_id;
}

Error:

org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [com.example.marketing3.arsenkin.dtos.OveroptimisationDto]

Have I made a mistake somewhere in naming?

Answer 1

As mentioned in @Jens answer, you should create an interface for projection.

public interface Overoptimisation {
    Integer getCurrentId();
    Date getCurrentDate();
    String getCurrentPosition();
    String getCurrentRelevantUrl();
    String getCurrentRestrictionsProbability();
    String getCurrentUrlChanged();
    Integer getCurrentIdentifierId();
    Integer getPhraseId();
    Integer getPreviousId();
    Date getPreviousDate();
    String getPreviousPosition();
    String getPreviousRelevantUrl();
    String getPreviousRestrictionsProbability();
    String getPreviousUrlChanged();
    Integer getPreviousIdentifier_id();
}

Then you should call to your method like:

@Query(value = "
select
       current.id as current_id,
       current.date as current_date,
       current.position as current_position,
       current.relevant_url as current_relevant_url,
       current.restrictions_probability as current_restrictions_probability,
       current.url_changed as current_url_changed,
       current.identifier_id as current_identifier_id,
       current.phrase_id as phrase_id,
       previous.id as previous_id,
       previous.date as previous_date,
       previous.position as previous_position,
       previous.relevant_url as previous_relevant_url,
       previous.restrictions_probability as previous_restrictions_probability,
       previous.url_changed as previous_url_changed,
       previous.identifier_id as previous_identifier_id
from (
select
       id,
       date,
       position,
       relevant_url,
       restrictions_probability,
       url_changed,
       identifier_id,
       phrase_id
from overoptimisation
where identifier_id = :currentOveroptimisationIdentifierId) current

left join (
select
       id,
       date,
       position,
       relevant_url,
       restrictions_probability,
       url_changed,
       identifier_id,
       phrase_id
from overoptimisation
where identifier_id = :previousOveroptimisationIdentifierId) previous

on current.phrase_id = previous.phrase_id",
    nativeQuery = true)
List<Overoptimisation> report(@Param("previousOveroptimisationIdentifierId") Integer previousOveroptimisationIdentifierId,
                                 @Param("currentOveroptimisationIdentifierId") Integer currentOveroptimisationIdentifierId);