Announcement Announcement Module
Collapse
No announcement yet.
MySql query redesign to JPA query Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySql query redesign to JPA query

    Hi,

    can any one please help me how to redesign this mysql query into jpql(jpa query).

    SELECT delivered.ls_fk_artikel, delivered.SUM_Amount_Delivered, sold.SUM_Amount_Sold, (coalesce(delivered.SUM_Amount_Delivered,0) - coalesce(sold.SUM_Amount_Sold,0)) AS Amount_Stock
    FROM
    (
    SELECT
    Sum(ls_main.ls_menge) AS SUM_Amount_Delivered,
    ls_main.ls_fk_artikel,
    ls_main.ls_ean,
    ls_main.ls_artikelnr
    FROM
    ls_head
    Inner Join ls_main ON ls_head.ls_head_id = ls_main.fk_ls_head
    WHERE
    ls_head.fk_filialen = '2410' AND
    ls_head.storno IS NULL
    GROUP BY
    ls_main.ls_fk_artikel
    )
    AS delivered
    left JOIN
    (
    SELECT
    coalesce(Sum(sales.anzahl), 0) AS SUM_Amount_Sold,
    sales.fk_artikel,
    sales.ean,
    sales.artikel_nr
    FROM
    sales
    WHERE
    sales.fk_filialen = '2410'
    GROUP BY
    sales.fk_artikel
    )
    AS sold ON delivered.ls_fk_artikel = sold.fk_artikel;

    lsmain is one table, where I have to get delivered information, sales is sold data and I have to caliculate the difference between the delivered and sold stock.

    Currently I am using the below query

    Query query = this.entityManager.createQuery("select l, sum(l.ls_menge), s, sum(s.anzahl) from LsMainImpl l, SalesImpl s inner join l.fk_ls_head h where h.fk_filialen.filial_id = " + filial_id + " and h.storno is null and l.ls_fk_artikel.artikel_id = s.fk_artikel.artikel_id and s.fk_filialen.filial_id = " + filial_id + " group by l.ls_fk_artikel.artikel_id");

    but it taking 250 seconds..where as the above query takes only 3 seconds.

    can any one please help me how to do that.

    Thanks in Advance,


    Best Regards,
    Raja.
    Last edited by raja_jan09; Jun 24th, 2011, 08:15 AM.

  • #2
    Hello

    use code tags

    but it taking 250 seconds..where as the above query takes only 3 seconds.
    Consider that the SQL practically talk directly with the DB and jpql must do the magic about the ORM process:
    • work thinking in Entities and not in tables
    • generate internally the SQL

    And your query is little complex since you are using two inner SELECT and retrieving specifics fields, and even using aggregate functions, such query to what Object is returned or assigned? a special POJO or a Object[] ?

    If performance is an issue for you, you could consider use for this method Spring JDBC, anyway you are working directly with a SQL for JPA

    Comment

    Working...
    X