JPA mapping native query result to non entity DTO

Tartar picture Tartar · Mar 4, 2019 · Viewed 9.2k times · Source

I have a complex native query and I am trying to map its result to a non-entity DTO class. I am trying to use JPA's SqlResultSetMapping with ConstructorResult

My DTO class

@Data
public class Dto {

    private Long id;

    private String serial;

    private Long entry;

    private int numOfTasks;
}

My entity class, which has the repository interface that I will call this native query result.

@SqlResultSetMapping(
        name = "itemDetailsMapping",
        classes = {
                @ConstructorResult(
                        targetClass = Dto.class,
                        columns = {
                                @ColumnResult(name = "ID"),
                                @ColumnResult(name = "SERIAL"),
                                @ColumnResult(name = "ENTRY"),
                                @ColumnResult(name = "TASKS")
                        }
                )
        }
)

@NamedNativeQuery(name = "getItemDetails", query = "complex query is here", resultSetMapping = "itemDetailsMapping")
@Entity
@Data
public class Item {}

Repository

@Repository
public interface ItemRepository extends JpaRepository<Item, Long> {

    ...     

    List<Dto> getItemDetails();

}

When I call the getItemDetails() from ItemRepository I have the following error:

org.springframework.data.mapping.PropertyReferenceException: No property itemDetails found for type Item

What is the proper way to use SqlResultSetMapping and ConstructorResult and solve this problem.

Any help would be appreciated.

Answer

Simon Martinelli picture Simon Martinelli · Mar 4, 2019

To use named queries the name of the named query must have the entity name as prefix:

@NamedNativeQuery(name = "Item.getItemDetails", 
                 query = "complex query is here", resultSetMapping = "itemDetailsMapping")

Then the interface method must have the same name as the named query without the prefix:

List<Dto> getItemDetails();

-

Read more about Spring Data JPA and named queries in the reference doc https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.named-queries