公共表表达式，以值开头的语句在 java 中给出错误

Question

我有这个 CTE，我会在 Java 中实现：
它在 IBM 伊塞尔 7.3 DB2 机器上运行。

WITH params (from_date, to_date, outlet, product_number)
AS (
    values(TO_DATE('01.11.2018', 'DD.MM.YYYY'),
        TO_DATE('18.12.2018', 'DD.MM.YYYY'),
        'BLK' ,
        49 )
    ),
product
AS (
    SELECT DISTINCT cpp.competitor_products_id product
    FROM yxdb.competitor_product_prices cpp
    INNER JOIN yxdb.competitor_products_comparisons cpc ON cpc.competitor_products_id = cpp.competitor_products_id
        AND cpc.deleted = 0
    INNER JOIN yxdb.outlets o ON o.outlets_id = cpc.outlets_id
        AND o.deleted = 0
    INNER JOIN params ON cpp.price_date > params.from_date
        AND cpc.product_number = params.product_number
        AND o.short_name = params.outlet
    WHERE cpp.deleted = 0
    )
select * from product;

它要长得多，所以params表被多次使用
在Java中实现时，我将Java中的硬编码日期和其他参数替换为？1.2等。我也尝试过命名参数，但都不起作用。它们都给出了＜code＞[SQL0418]，参数标记或NULL的使用无效。

Java 代码片段：

@RepositoryRestResource
    public interface CompetitorPriceDateRepository extends JpaRepository<CompetitorPriceDateEntity, Long> {
    @Query(value = "WITH params (from_date, to_date, outlet, product_number) "
                + " AS ( "
                + "     values(TO_DATE( :fromDate , 'DD.MM.YYYY'), "
                + "         TO_DATE( :toDate , 'DD.MM.YYYY'), "
                + "         :outlet , "
                + "         :productNumber ) "
                + "     ), "
                + " product "
                + " AS ( "
                + "     SELECT DISTINCT cpp.competitor_products_id product "
                + "     FROM yxdb.competitor_product_prices cpp "
                + "     INNER JOIN yxdb.competitor_products_comparisons cpc ON +" cpc.competitor_products_id = cpp.competitor_products_id "
                + "         AND cpc.deleted = 0 "
                + "     INNER JOIN yxdb.outlets o ON o.outlets_id = cpc.outlets_id "
                + "         AND o.deleted = 0 "
                + "     INNER JOIN params ON cpp.price_date > params.from_date "
                + "         AND cpc.product_number = params.product_number "
                + "         AND o.short_name = params.outlet "
                + "     WHERE cpp.deleted = 0 "
                + "     ) "
                + " select * from product ",nativeQuery = true) 
    List<CompetitorPriceDateEntity> findAllInterpolatedByDates(
                    @Param("productNumber") Integer productNumber,
                    @Param("outlet") String outlet,
                    @Param("fromDate") String fromDate,
                    @Param("toDate") String toDate
            );

Answer 1

如果没有栈跟踪，我对你的查询有什么问题没有任何帮助。SQL看起来很扎实。

尝试使用命名的本机查询

@Entity
@NamedNativeQuery(
        name = “competitor.findProducts.byDateOutletProductnumber", 
        query = "WITH params (from_date, to_date, outlet, product_number) "
            + " AS ( "
            + "     values(TO_DATE( :fromDate , 'DD.MM.YYYY'), "
            + "         TO_DATE( :toDate , 'DD.MM.YYYY'), "
            + "         :outlet , "
            + "         :productNumber ) "
            + "     ), "
            + " product "
            + " AS ( "
            + "     SELECT DISTINCT cpp.competitor_products_id product "
            + "     FROM yxdb.competitor_product_prices cpp "
            + "     INNER JOIN yxdb.competitor_products_comparisons cpc ON +" cpc.competitor_products_id = cpp.competitor_products_id "
            + "         AND cpc.deleted = 0 "
            + "     INNER JOIN yxdb.outlets o ON o.outlets_id = cpc.outlets_id "
            + "         AND o.deleted = 0 "
            + "     INNER JOIN params ON cpp.price_date > params.from_date "
            + "         AND cpc.product_number = params.product_number "
            + "         AND o.short_name = params.outlet "
            + "     WHERE cpp.deleted = 0 "
            + "     ) "
            + " select * from product ", 
        hints = { 
            @QueryHint(name = "org.hibernate.cacheable", value = "true") })
public class CompetitorPriceDateEntity { ... }

好处1:命名查询是预编译的，所以jpa不需要在每次被调用时都将查询编译成可执行的标准。

专业奖金2：查询提示可缓存或eclipselink等效