首先,我将列出在查询中使用的三个模型
产品实体:
@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:查询指定的联接
正在获取,但获取的关联的所有者不在中
选择列表
非常奇怪,但我尝试替换join fetch->参加
我明白了为什么会出现这个错误,hibernate对数据库进行了这样的查询:
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投影不能正确工作,但元组工作正常?
