复杂条件查询:使用JPA代替NativeQuery [英] Complex criteria query: using JPA instead of NativeQuery

问题描述

在我的Java EE项目中，此MySQL的NativeQuery:

I've in my Java EE project this 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

这是一个动态生成的查询，具体取决于用户输入.我不喜欢使用本机查询，所以我现在正在使用它，并且打算使用标准查询来更改它，但是我需要帮助:我无法弄清楚如何用标准查询来代替这种查询.在这种情况下是否有办法使用本机查询，还是应该不再担心?

this is a dinamically generated query, depending on user input. I don't like using native queries so I'm using it for now and I'm planning to change it with a criteria query, but I need help: I cannot figure out how this kind of queries can be substituted by a criteria query. Is there a way or it's ok to use native queries in cases like this and I should stop worrying about it?

谢谢

推荐答案

不幸的是，您不能在from子句中使用JPA子查询结果.在条件查询和JPQL查询中都没有.这似乎是将查询转换为JPA时遇到的最大问题.

Unfortunately you cannot use JPA subquery results in the from clause. Neither in Criteria queries, nor in JPQL ones. This looks like the biggest problem in translating your query into a JPA one.

其次，也没有Round函数.但是通过使用 CriteriaBuilder#selectCase()

Secondarily, there is no Round function, either. But it shouldn't be a problem to overcome this by using CriteriaBuilder#selectCase()

• JPA2标准中的From claus中的子查询
• 有关子查询的EclipseLink指南

• Subquery in From claus in JPA2 Criteria
• EclipseLink guide about Subquery

这篇关于复杂条件查询:使用JPA代替NativeQuery的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！