复杂条件查询:使用JPA而不是NativeQuery

Question

我在Java EE项目中有一个NativeQuery for MySQL：

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

这是一个按常规生成的查询，具体取决于用户输入。我不喜欢使用本机查询，所以我现在正在使用它，我计划用标准查询来更改它，但我需要帮助：我不知道如何用标准查询取代这种查询。在这种情况下，有没有办法或可以使用本机查询，我应该停止担心它？

谢谢

Answer 1

很遗憾，不能在from子句中使用JPA子查询结果。Criteria查询和JPQL查询中都没有。这看起来是将查询转换为JPA查询的最大问题。

其次，也没有Round函数。但使用CriteriaBuilder#selectCase()来克服这一点应该不是问题

另请参阅：

• JPA2条件中From子句中的子查询
• 关于Subquery的EclipseLink指南