Complex criteria query: using JPA instead of NativeQuery

Question

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?

Thank you

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

3

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.

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

Thank you. So what you think about the need to use native queries for this kind of things? I'm asking because I've noticed that obviously a native query is faster and use less memory than a jpql query, so I'm thinking if using native queries is generally acceptable for statistical purposes or heavy calculation. – moretti.fabio Jan 02 '14 at 18:26
In particular circumstances the use of native queries is necessary. If you like portability and ease of maintainance, it's better to avoid it. If the number of items to sum up it's not to big, you can for instance retrieve the items in a JPA Criteria query and sum them up using java. Or if the number of items is big you can consider to save the sums in a db table. – perissf Jan 02 '14 at 19:04

Complex criteria query: using JPA instead of NativeQuery

1 Answers1

See also:

Linked