spring 启动数据 @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]

推荐答案

在我的项目中，我使用了projections，如下所示:

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

并创建interface MetricDTO 如下图所示:

And create interface MetricDTO like shown below:

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.

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