java在将SQL查询重写为HQL查询时遇到问题
下面是我在SQL中的原始查询:
SELECT o.offering_number,
o.english_description,
o.french_description,
fop.price_amount,
fop2.price_amount,
fop.price_type_code,
fop.offering_id,
fop2.offering_id
from facility_offering_price fop
join offering o on fop.offering_id = o.offering_id
inner join
(select
fop1.offering_id,
fop1.price_amount
from facility_offering_price fop1
WHERE fop1.price_type_code = 5
AND fop1.price_status_code = 1
) fop2 on fop.offering_id = fop2.offering_id
WHERE fop.price_start_date = '15-10-28'
AND fop.price_status_code IN (1,2)
/*AND (price_status_code IS NULL)*/
AND fop.price_type_code = 5
/*AND (o.offering_number IS NULL)*/
ORDER BY o.offering_number ASC, fop.price_sequence_number ASC;
以下是HQL中的上述查询:
@Query("SELECT new com.frontier.dto.productprice.PendingPriceProduct("
+ " fop.facilityPhysicalOffering.offeringNumber,"
+ " fop.facilityPhysicalOffering.offering.description.englishDescription,"
+ " fop.facilityPhysicalOffering.offering.description.frenchDescription,"
+ " fop.priceAmount,"
+ " fop2.priceAmount,"
+ " fop.priceStatusCode"
+ ")"
+ " from FacilityOfferingPrice fop"
+ " INNER JOIN (SELECT fop1.offeringId, fop1.priceAmount FROM FacilityOfferingPrice fop1 WHERE "
+ " fop1.id.priceTypeCode = com.frontier.domain.type.PriceType.REG_RETAIL_PRICE"
+ " AND fop1.priceStatusCode = com.frontier.domain.type.OfferingPriceSts.CURRENT" +
" ) fop2 on fop.offeringId = fop2.offeringId"
+ " WHERE fop.priceStartDate = :priceStartDate"
+ " AND fop.priceStatusCode IN (:priceStatusCodes)"
+ " AND (:priceStatusCode IS NULL OR fop.priceStatusCode = :priceStatusCode)"
+ " AND fop.id.priceTypeCode = com.frontier.domain.type.PriceType.REG_RETAIL_PRICE"
+ " AND (:fromOfferingNumber IS NULL OR fop.facilityPhysicalOffering.offeringNumber >=:fromOfferingNumber)"
+ " ORDER BY fop.facilityPhysicalOffering.offeringNumber ASC, fop.id.priceSequenceNumber ASC")
List<PendingPriceProduct> findPendingPriceProducts(
@Param("priceStartDate") LocalDate priceStartDate,
@Param("priceStatusCodes") List<OfferingPriceSts> priceStatusCodes,
@Param("priceStatusCode") OfferingPriceSts priceStatusCode,
@Param("fromOfferingNumber") Long fromOfferingNumber,
Pageable pageable);
错误消息:意外标记:(靠近第1行第372列)。 因此,连接语法有问题。在我用这个连接替换子查询之前,一切都很好
谢谢你的帮助
更新:
新查询:
@Query("SELECT "
+ " new com.frontier.dto.productprice.PendingPriceProduct("
+ " fop.facilityPhysicalOffering.offeringNumber,"
+ " fop.facilityPhysicalOffering.offering.description.englishDescription,"
+ " fop.facilityPhysicalOffering.offering.description.frenchDescription,"
+ " fop.priceAmount,"
+ " fop1.priceAmount,"
+ " fop.priceStatusCode"
+ ")"
+ " from FacilityOfferingPrice fop"
+ " INNER JOIN FacilityOfferingPrice fop1 on fop.offeringId = fop1.offeringId"
+ " WHERE fop.priceStartDate = :priceStartDate"
+ " AND fop.priceStatusCode IN (:priceStatusCodes)"
+ " AND (:priceStatusCode IS NULL OR fop.priceStatusCode = :priceStatusCode)"
+ " AND fop.id.priceTypeCode = com.frontier.domain.type.PriceType.REG_RETAIL_PRICE"
+ " AND (:fromOfferingNumber IS NULL OR fop.facilityPhysicalOffering.offeringNumber >=:fromOfferingNumber)"
+ " ORDER BY fop.facilityPhysicalOffering.offeringNumber ASC, fop.id.priceSequenceNumber ASC")
List<PendingPriceProduct> findPendingPriceProducts(
@Param("priceStartDate") LocalDate priceStartDate,
@Param("priceStatusCodes") List<OfferingPriceSts> priceStatusCodes,
@Param("priceStatusCode") OfferingPriceSts priceStatusCode,
@Param("fromOfferingNumber") Long fromOfferingNumber,
Pageable pageable);
新错误: 需要加入的路径`InvalidPathException:无效路径:“fop1。价格金额'
# 1 楼答案
我不确定您是否可以在HQL中编写这样的子查询,顺便问一下,您真的需要子查询吗?如果你像那样重写你的初始查询
到HQL的转换应该非常简单