春季启动数据@query到DTO [英] spring boot data @query to DTO

问题描述

我想将查询结果分配给DTO对象。 DTO如下所示：

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;
    }
            
}

查询如下：

@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]

推荐答案

在我的项目中，我使用了投影，如下所示：

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);
    
    }

将结果投影到的接口：

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.

此外，如果使用 SELECT table.column 表示法，请务必定义与实体名称匹配的别名，如示例所示

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

在您的情况下，请更改 @Query ，如下所示：

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();

并创建界面 MetricDTO 如下所示：

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

还要确保返回类型为 getSum（）和 getCount（）是正确的，这可能因数据库而异。

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

这篇关于春季启动数据@query到DTO的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！