java - 如何为给定的SQL查询编写JPA查询

请建议如何为给定的SQL查询编写JPA EntityManagercreateQuery：

select
    sum(case when Status_2 = 'Allocated' then 1 else 0 end) as Allocated,
    sum(case when Status_2 = 'Bench' then 1 else 0 end) as Bench
    from userbean where Organizational_Unit = 'SIDG Java'
    union
select
    sum(case when Status_2 = 'Allocated' then 1 else 0 end) as Allocated,
    sum(case when Status_2 = 'Bench' then 1 else 0 end) as Bench
    from userbean where Organizational_Unit = 'SIDG Microsoft'

最佳答案

可以将本机sql查询与jpa一起使用：

Query q = em.createNativeQuery("select
    sum(case when Status_2 = 'Allocated' then 1 else 0 end) as Allocated,
    sum(case when Status_2 = 'Bench' then 1 else 0 end) as Bench
    from userbean where Organizational_Unit = 'SIDG Java'
    union
select
    sum(case when Status_2 = 'Allocated' then 1 else 0 end) as Allocated,
    sum(case when Status_2 = 'Bench' then 1 else 0 end) as Bench
    from userbean where Organizational_Unit = 'SIDG Microsoft'");

List<Object[]> result= q.getResultList();

// for each line retrieved
for (Object[] currentLine : result) {
    System.out.println("Allocated=" + currentLine[0]
                    + ", Bench=" + currentLine[1] ;
}

没有保证，但你可以试试。