java - 复杂条件查询:使用JPA代替NativeQuery

我的Java EE项目中有一个针对MySQL的NativeQuery：

SELECT
    *,
    ROUND((price_2-price_1)*100/price_1,2) AS varprice_1,
    ROUND((quantity_2-quantity_1)*100/quantity_1,2) AS varcant_1,
    ROUND((price_3-price_2)*100/price_2,2) AS varprice_2,
    ROUND((quantity_3-quantity_2)*100/quantity_2,2) AS varcant_2,
    1
FROM (
    SELECT
        c.id_customer AS id_customer,
        c.name AS customer,
        r.id_rep AS id_rep,
        r.descr AS rep,
        a.id_article AS id_article,
        a.name AS article,
        ROUND(SUM(if(docdate BETWEEN '2013-06-30' AND '2013-12-30',quantity ,0)),2) AS quantity_1,
        ROUND(SUM(if(docdate BETWEEN '2013-06-30' AND '2013-12-30',net_price,0)),2) AS price_1,
        ROUND(SUM(if(docdate BETWEEN '2012-06-30' AND '2012-12-30',quantity ,0)),2) AS quantity_2,
        ROUND(SUM(if(docdate BETWEEN '2012-06-30' AND '2012-12-30',net_price,0)),2) AS price_2,
        ROUND(SUM(if(docdate BETWEEN '2011-06-30' AND '2011-12-30',quantity ,0)),2) AS quantity_3,
        ROUND(SUM(if(docdate BETWEEN '2011-06-30' AND '2011-12-30',net_price,0)),2) AS price_3,
        1
    FROM documento d
    RIGHT JOIN pedido_cabezal pc ON d.id_documento = pc.id_documento
    LEFT JOIN pedido_linea pl ON pc.id_documento = pl.id_documento
    LEFT JOIN article a ON pl.id_article = a.id_article
    LEFT JOIN customer c ON pc.id_customer=c.id_customer
    LEFT JOIN rep r ON c.id_rep=r.id_rep
    WHERE (
        (docdate BETWEEN '2013-06-30' AND '2013-12-30') OR
        (docdate BETWEEN '2012-06-30' AND '2012-12-30') OR
        (docdate BETWEEN '2011-06-30' AND '2012-12-30')
        )
    GROUP BY a.id_article
) subq
ORDER BY price_1 DESC

这是一个由数据生成的查询，具体取决于用户输入。我不喜欢使用本机查询，所以我现在正在使用它，并计划用条件查询来更改它，但我需要帮助：我无法找出如何用条件查询替换此类查询。在这种情况下使用本机查询是有办法还是可以，我应该停止担心它？
谢谢你

最佳答案

不幸的是，您不能在from子句中使用JPA子查询结果。既不在条件查询中，也不在JPQL查询中。这看起来是将查询转换为JPA查询的最大问题。
其次，也没有Round功能。但使用CriteriaBuilder#selectCase()来克服这一点应该不是问题
另见：
Subquery in From claus in JPA2 Criteria
EclipseLink guide about Subquery