Hibernate：将JPQL查询投影到DTO问题

Question

首先，我将列出我在查询中使用的三个模型

产品实体：

@Entity
@Table(name = "product")
public class ProductEntity extends BaseEntity {
  //some fields

   @ManyToOne(fetch = FetchType.LAZY)
   @JoinColumn(name = "owner_id")
   private PartnerEntity owner;

   @OneToMany(
            mappedBy = "product",
            fetch = FetchType.LAZY
    )
    private List<StockProductInfoEntity> stocks;
 }

合作伙伴实体：

@Entity
@Table(name = "partner")
public class PartnerEntity extends AbstractDetails {
    //some fields

    @OneToMany(
            mappedBy = "owner",
            fetch = FetchType.LAZY
    )
    private List<ProductEntity> products;
 }

和StockProductInfoEntity：

@Entity
@Table(name = "stock_product")
public class StockProductInfoEntity extends BaseEntity {
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "product_id")
    private ProductEntity product;

    //other fields
    @Column(name = "rest")
    private int rest;
}

我想从数据库产品中获取所有股票的合作伙伴计算计数。为了方便起见，我创建了一个简单的DTO：

@Getter
@AllArgsConstructor
public class ProductCountDTO {
    private ProductEntity productEntity;
    private int count;

    //hack for hibernate
    public ProductCountDTO(ProductEntity productEntity, long count) {
        this.productEntity = productEntity;
        this.count = (int) count;
    }
}

并在JPA存储库中编写JPQL查询：

@Query("select new ru.oral.market.persistence.entity.product.util.ProductCountDTO(p, sum(stocks.rest))"+ 
            " from ProductEntity p" +
            " join fetch p.owner owner" +
            " join p.stocks stocks" +
            " where p.id = :id" +
            " group by p, owner")
    Optional<ProductCountDTO> findProductWithCount(@Param("id") long id);

但我的应用程序甚至没有启动，因为查询验证有问题。我收到以下消息：

原因：组织。冬眠QueryException：查询指定了联接提取，但提取的关联的所有者不在选择列表中

很奇怪，但我试着换了-

select
            productent0_.id as col_0_0_,
            sum(stocks2_.rest) as col_1_0_ 
        from
            product productent0_ 
        inner join
            partner partnerent1_ 
                on productent0_.owner_id=partnerent1_.user_id 
        inner join
            stock_product stocks2_ 
                on productent0_.id=stocks2_.product_id 
        where
            productent0_.id=? 
        group by
            productent0_.id ,
            partnerent1_.user_id

但是为什么他只拿产品id而不拿其他东西呢？此查询使用元组工作并从产品和合作伙伴获取所有字段

  @Query("select p, sum(stocks.rest) from ProductEntity p" +
            " join fetch p.owner owner" +
            " join p.stocks stocks" +
            " where p.id = :id" +
            " group by p, owner")
    Optional<Tuple> findProductWithCount(@Param("id") long id);

这产生了我想要的本机查询：

select
            productent0_.id as col_0_0_,
            sum(stocks2_.rest) as col_1_0_,
            partnerent1_.user_id as user_id31_12_1_,
            productent0_.id as id1_14_0_,
            productent0_.brand_id as brand_i17_14_0_,
            productent0_.commission_volume as commissi2_14_0_,
            productent0_.created as created3_14_0_,
            productent0_.description as descript4_14_0_,
            productent0_.height as height5_14_0_,
            productent0_.length as length6_14_0_,
            productent0_.long_description as long_des7_14_0_,
            productent0_.name as name8_14_0_,
            productent0_.old_price as old_pric9_14_0_,
            productent0_.owner_id as owner_i18_14_0_,
            productent0_.pitctures as pitctur10_14_0_,
            productent0_.price as price11_14_0_,
            productent0_.status as status12_14_0_,
            productent0_.updated as updated13_14_0_,
            productent0_.vendor_code as vendor_14_14_0_,
            productent0_.weight as weight15_14_0_,
            productent0_.width as width16_14_0_,
            partnerent1_.about_company as about_co1_12_1_,
            partnerent1_.bik as bik2_12_1_,
            partnerent1_.bank_inn as bank_inn3_12_1_,
            partnerent1_.bank_kpp as bank_kpp4_12_1_,
            partnerent1_.bank as bank5_12_1_,
            partnerent1_.bank_address as bank_add6_12_1_,
            partnerent1_.checking_account as checking7_12_1_,
            partnerent1_.correspondent_account as correspo8_12_1_,
            partnerent1_.company_name as company_9_12_1_,
            partnerent1_.company_inn as company10_12_1_,
            partnerent1_.company_kpp as company11_12_1_,
            partnerent1_.ogrn as ogrn12_12_1_,
            partnerent1_.okato as okato13_12_1_,
            partnerent1_.actual_address as actual_14_12_1_,
            partnerent1_.director as directo15_12_1_,
            partnerent1_.full_name as full_na16_12_1_,
            partnerent1_.legal_address as legal_a17_12_1_,
            partnerent1_.short_name as short_n18_12_1_,
            partnerent1_.country as country19_12_1_,
            partnerent1_.discount_conditions as discoun20_12_1_,
            partnerent1_.discounts as discoun21_12_1_,
            partnerent1_.logo as logo22_12_1_,
            partnerent1_.min_amount_order as min_amo23_12_1_,
            partnerent1_.min_shipment as min_shi24_12_1_,
            partnerent1_.min_sum_order as min_sum25_12_1_,
            partnerent1_.own_delivery as own_del26_12_1_,
            partnerent1_.own_production as own_pro27_12_1_,
            partnerent1_.phones as phones28_12_1_,
            partnerent1_.return_information as return_29_12_1_,
            partnerent1_.site as site30_12_1_ 
        from
            product productent0_ 
        inner join
            partner partnerent1_ 
                on productent0_.owner_id=partnerent1_.user_id 
        inner join
            stock_product stocks2_ 
                on productent0_.id=stocks2_.product_id 
        where
            productent0_.id=? 
        group by
            productent0_.id ,
            partnerent1_.user_id

但是这不是很方便。为什么DTO投影不能正常工作，但元组可以正常工作？

Answer 1

由于Vlad已经解释了原因，我将专注于另一种解决方案。必须在SELECT子句和GROUP BY子句中指定您真正感兴趣的所有属性是一项繁重的工作。如果您在Hibernate之上使用Blaze-持久性实体视图，这可能如下所示

@EntityView(ProductEntity.class)
public interface ProductCountDTO {
    // Or map the ProductEntity itself if you like..
    @Mapping("this")
    ProductView getProduct();
    @Mapping("sum(stocks.rest)")
    int getCount();
}

@EntityView(ProductEntity.class)
public interface ProductView {
    // Whatever mappings you like
}

通过Spring数据或DeltaSpike数据集成，您甚至可以这样使用它

<代码>可选

它将生成如下所示的JPQL查询

SELECT
  p /* All the attributes you map in ProductView  */,
  sum(stocks_1.rest)
FROM
  ProductEntity p
LEFT JOIN
  p.stocks stocks_1
GROUP BY
  p /* All the attributes you map in ProductView */

也许试一试？https://github.com/Blazebit/blaze-persistence#entity-view-usage

神奇的是，Blaze Persistence在遇到聚合函数时自动处理GROUP BY，如果至少使用了一个聚合函数，则将使用的每个非聚合表达式放入GROUP BY子句中。当直接使用实体视图而不是实体时，您将不会面临连接获取问题，因为实体视图只会将实际映射的字段放入JPQL和SQL的结果SELECT子句中。即使您直接或通过ProductCountDTO使用实体，在后台使用的查询生成器也会优雅地处理组中实体类型的选择，就像您在Hibernate中所期望的那样。

Answer 2

因为这就是Hibernate目前的实现方式。

因为您在DTO投影中使用了一个实体，顾名思义，它应该用于DTO，而不是实体，Hibernate将假定您要按标识符分组，因为它不应该按所有实体属性分组。

元组被破坏了，它只在MySQL中工作，而在Oracle或PostgreSQL中不工作，因为聚合查询选择了GROUP BY子句中不存在的列。

然而，根据JPA规范，这并不是必须的。尽管如此，您仍然应该提供一个复制测试用例并打开一个问题，以便这两种情况下的行为相同。

无论如何，一旦修复，它仍将按标识符分组。如果还想选择实体和分组依据，则必须使用本机SQL查询和Hibernate结果转换程序将结果集转换为对象图。

此外，获取实体和聚合是一种代码气味。很可能，您需要DTO投影或只读视图。

仅当您要修改实体时，才应获取实体。否则，DTO投影更有效，也更直接。