spring boot data @query to DTO

Question

I want to assign the result of a query to a DTO object. The DTO looks like this:

@Getter
@Setter
@NoArgsConstructor
public class Metric {
    private int share;
    private int shareholder;

    public Metric(int share, int shareholder) {
        this.share = share;
        this.shareholder = shareholder;
    }
            
}

And the query looks like the following:

@RepositoryRestResource(collectionResourceRel = "shareholders", path = "shareholders")
public interface ShareholderRepository extends PagingAndSortingRepository<Shareholder, Integer> {
    @Query(value = "SELECT new com.company.shareholders.sh.Metric(SUM(s.no_of_shares),COUNT(*)) FROM shareholders s WHERE s.attend=true")
    Metric getMetrics();
}

However, this didn't work, as I got the following exception:

Caused by:org.hibernate.QueryException: could not resolve property: no_of_shares of:com.company.shareholders.sh.Shareholder[SELECT new com.company.shareholders.sh.Metric(SUM(s.no_of_shares),COUNT(*)) FROM com.company.shareholders.sh.Shareholder s WHERE s.attend=true]

Answer 1

In my project I've used projections to this like shown below:

@Repository
public interface PeopleRepository extends JpaRepository<People, Long> {
    
    @Query(value = "SELECT p.name AS name, COUNT(dp.people_id) AS count " +
                   "FROM people p INNER JOIN dream_people dp " +
                   "ON p.id = dp.people_id " +
                   "WHERE p.user_id = :userId " +
                   "GROUP BY dp.people_id " +
                   "ORDER BY p.name", nativeQuery = true)
    List<PeopleDTO> findByPeopleAndCountByUserId(@Param("userId") Long userId);
    
    @Query(value = "SELECT p.name AS name, COUNT(dp.people_id) AS count " +
                   "FROM people p INNER JOIN dream_people dp " +
                   "ON p.id = dp.people_id " +
                   "WHERE p.user_id = :userId " +
                   "GROUP BY dp.people_id " +
                   "ORDER BY p.name", nativeQuery = true)
    Page<PeopleDTO> findByPeopleAndCountByUserId(@Param("userId") Long userId, Pageable pageable);
    
    }

The interface to which the result is projected:

public interface PeopleDTO {    
    String getName();
    Long getCount();    
}

The fields from the projected interface must match the fields in this entity. Otherwise field mapping might break.

Also if you use SELECT table.column notation always define aliases matching names from entity as shown in example.

In your case change @Query like shown below:

@Query(value = "SELECT new " + 
               "SUM(s.no_of_shares) AS sum,COUNT(*) AS count FROM " +
               "shareholders s WHERE s.attend=true", nativeQuery = true)
MetricDTO getMetrics();

And create interface MetricDTO like shown below:

public interface MetricDTO {
    Integer getSum();    
    Long getCount();    
}

Also make sure the return type of getSum() and getCount() is correct this may vary based not database.

Answer 2

First, you can have a look at the Spring Data JPA documentation, you can find some help at this section : Class-based Projections (DTOs).

There is also a paragraph titled Avoid boilerplate code for projection DTOs, where they advise you to use Lombok's @Value annotation, to produce an immutable DTO. This is similar to Lombok's @Data annotation, but immutable.

If you apply it to your example, the source will look like :

@Value
public class MetricDto {

    private int share;
    private int shareholder;

}

Then, as your query is a NativeQuery, specifiy it in your Spring Data Repository. You can find help in the documentation : Native Queries. You will need something like :

@Query(value = "SELECT new 
   com.company.shareholders.sh.MetricDto(SUM(s.no_of_shares),COUNT(*)) FROM 
   shareholders s WHERE s.attend=true", nativeQuery = true)
   MetricDto getMetrics();

Answer 3

Query query = sessionFactory.getCurrentSession()
              .createNativeQuery(stringQuery).unwrap(org.hibernate.query.Query.class);

((NativeQueryImpl) query).setResultTransformer(new AliasToBeanResultTransformer(DtoClass.class));

Answer 4

You are writing a mixed query of native and jpql; no_of_shares is your column name in the database, but jpa is expecting you to provide not native syntax so try to replace no_of_shares with the corresponding field in your entity class. Or just add nativeQuery = true to make jpa understand it's a native query.