0

Have SQL request, nice works, IDEA does not hihglight code. But:

org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [ua.com.markovka.model.ClientDTO]

How to fix it and get request information?

@Query(value = "SELECT cl.id, cl.name, cl.phone, cl.status, cl.card, ca_date, ca.comment, vi_date, vi_sum from clients cl LEFT JOIN "
        + "(SELECT client_id, Max(date) as ca_date, comment FROM calls GROUP BY client_id) as ca ON ca.client_id=cl.id JOIN "
        + "(SELECT client_id, Max(date) as vi_date, SUM(amount) as vi_sum FROM visits GROUP BY client_id) AS vi ON vi.client_id=cl.id",
    nativeQuery = true
)
List<ClientDTO> findAllDto();

The ClientDTO class

import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@Getter
@NoArgsConstructor
@Setter
public class ClientDTO {
    private long clientId;
    private String clientName;
    private String clientPhone;
    private String clientStatus;
    private int card;
    private String lastCallDate;
    private String comment;
    private String visitDate;
    private int visitsSum;

    public ClientDTO(long clientId, String clientName, String clientPhone, String clientStatus, int card, String lastCallDate, String comment, String visitDate, int visitsSum) {
        this.clientId = clientId;
        this.clientName = clientName;
        this.clientPhone = clientPhone;
        this.clientStatus = clientStatus;
        this.card = card;
        this.lastCallDate = lastCallDate;
        this.comment = comment;
        this.visitDate = visitDate;
        this.visitsSum = visitsSum;
    }

    @Override
    public String toString() {
        return  clientName +";"+
                clientPhone + ";"+
                clientStatus + ";"+
                card +";"+
                visitDate +";"+
                visitsSum +";"+
                lastCallDate +";"+
                comment;
    }
}
Anthony Raymond
  • 7,434
  • 6
  • 42
  • 59
  • Can you add the ClientDTO code please? – Anthony Raymond Nov 29 '18 at 09:30
  • Possible duplicate of [Spring JPA native query with Projection gives "ConverterNotFoundException"](https://stackoverflow.com/questions/49500309/spring-jpa-native-query-with-projection-gives-converternotfoundexception) – Robert Niestroj Nov 29 '18 at 10:01
  • How can i use join, Max() etc., at that type of query? – Igor Vasiliev Nov 29 '18 at 10:15
  • 1
    Use `@Entity` and `@SqlResultSetMapping` `@SqlResultSetMapping( name = "ClientDTOResult", classes = { @ConstructorResult( targetClass = ClientDTO.class, columns = { @ColumnResult(name = "id", type = Long.class), @ColumnResult(name = "name", type = String.class), ....... } ) })` Do not forget to annotate the ID attribute with `@Id` – K.Kostadinov Nov 29 '18 at 11:48

0 Answers0