Hibernate, Spring Data: class-based projections don't work with native queries: no converter found

Question

DogRepository.java:

public interface DogRepository extends JpaRepository<Dog, Long> {
...
    @Query(value = "SELECT name from dogs;", nativeQuery = true)
    List<SuperDto> withNames();
}

SuperDto.java:

public class SuperDto {
    private String name;

    public SuperDto()  {
        
    }
    
    public SuperDto(String name) {
        this.name = name;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

I get an exception

org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type

Meanwhile if SuperDto is an interface:

public interface SuperDto {
    String getName();
}

it works fine.

I realized that class-based projections don't work well with native queries.

So I tried to change the code:

    @Query(nativeQuery = true)
    List<SuperDto> withNames();

@NamedNativeQuery(name = "SuperDto.withNames",
    query = "SELECT name from dogs",
    resultSetMapping = "SuperDto")
@SqlResultSetMapping(name = "SuperDto",
    classes = @ConstructorResult(targetClass = SuperDto.class,
        columns = {@ColumnResult(name = "name")}))
public class SuperDto {...}

But now I get

Error creating bean with name 'dogController' defined in file

exception.

How do I make it work with a POJO class?

Answer 1

As mentioned in another answer, the @NamedNativeQuery annotation needs to be present in an entity class(or a mapped superclass). So what I have ended up doing in the past is to make the DTO class a dummy entity by adding the @Entity annotation and a dummy @Id field. The @NamedNativeQuery and @SqlResultSetMapping mapping annotations can then be added to this DTO/Entity class. And whatever name is specified for the query in the @NamedNativeQuery annotation needs to be specified in the @Query annotation in the repository class. Applying the same changes in this case, I was able to get the withNames method working. Changes are below:

SuperDto.java:

import org.hibernate.annotations.Subselect;

import javax.persistence.ColumnResult;
import javax.persistence.ConstructorResult;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.NamedNativeQuery;
import javax.persistence.SqlResultSetMapping;

@Entity
@Subselect("select * from dogs") // To prevent creation of a super_dto table
@NamedNativeQuery(
        name = "dog_names",
        query = "SELECT name from dogs",
        resultSetMapping = "SuperDto"
)
@SqlResultSetMapping(
        name = "SuperDto",
        classes = @ConstructorResult(
                targetClass = SuperDto.class,
                columns = {
                        @ColumnResult(name = "name")
                }
        )
)
public class SuperDto {
    @Id
    private String id = "";

    private String name;

    public SuperDto()  {
        
    }
    
    public SuperDto(String name) {
        this.name = name;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

DogRepository.java:

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface DogRepository extends JpaRepository<Dog, Long> {
    @Query(name = "dog_names", nativeQuery = true)
    List<SuperDto> withNames();
}