如何在JPA-Hibernate中执行数据透视查询？ [英] How to execute pivot queries in JPA-Hibernate?

问题描述

在MYSQL中，我有这个查询：

我的表

 
 
计划
 ------------------- 
 process_id（int）
 project_id（int） 
期间（日期时间）
 total_hour（双倍）
 
 

 
 
 SELECT process_id，project_id，
 sum（if（period =2013-04-01 00:00:00，total_hour，0））as april，
 sum（if（period = （如果（期间=2013-06-01 00:00:00，total_hour，0））作为六月，
总和（2013-05-01 00:00:00，total_hour，0） 
 FROM planning 
 GROUP BY process_id，project_id; 
 
 

我需要将此clausule SQL转换为用于JPA吗？

  SELECT p.process_id，p.project_id，
 sum（当p.period =2013-04-01 00:00:00，然后是p.total_hour else 0结束时）作为四月，
 sum（p.period =2013-05- 01 00:00:00然后是p.total_hour else 0 end），可能是
 sum（p.period =2013-06-01 00:00:00然后是p.total_hour else 0 end的情况） as 6月，
 FROM计划p 
 GROUP BY p.process_id，p.project_id; 
  

但您仍然可以使用原生查询。

要映射结果集：可以手动将结果集映射到新的bean（如果您愿意，可以使用推土机），或者您可以使用一些@SqlResultSetMapping，请参阅如何使用JPA本机查询选择多个具有相同名称的列？

In MYSQL I have this query:

My table

    planning
    -------------------
    process_id (int)
    project_id (int)
    period (datetime)
    total_hour (double)

    SELECT process_id, project_id,
    sum( if (period="2013-04-01 00:00:00",total_hour,0)) as april,
    sum( if (period="2013-05-01 00:00:00",total_hour,0)) as may,
    sum( if (period="2013-06-01 00:00:00",total_hour,0)) as june
    FROM planning
    GROUP BY process_id,project_id;

I need convert this clausule SQL for use in JPA ?

解决方案

SQL to JPQL

SELECT p.process_id, p.project_id,
sum( case when p.period="2013-04-01 00:00:00" then p.total_hour else 0 end ) as april,
sum( case when p.period="2013-05-01 00:00:00" then p.total_hour else 0 end ) as may,
sum( case when p.period="2013-06-01 00:00:00" then p.total_hour else 0 end ) as june,
FROM Planning p
GROUP BY p.process_id, p.project_id;

But you can still use native queries too.

To map your resultset : you can map manually your resultset to a new bean (use dozer if you prefer), or you can use some @SqlResultSetMapping see How to select multiple columns with the same name using JPA native query?

这篇关于如何在JPA-Hibernate中执行数据透视查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！