首页 \ 问答 \ 如何为给定的SQL查询编写JPA查询(How I can write JPA query for given SQL query)

如何为给定的SQL查询编写JPA查询(How I can write JPA query for given SQL query)

 请建议如何为给定的SQL查询编写JPA EntityManager createQuery ：  
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'

Please suggest how can I write the JPA EntityManager createQuery for the given SQL Query: 
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'

更新时间：2023-03-21 12:03

最满意答案

 您可以使用jpa的本机SQL查询：  
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] ;
}
 
 不保证，但你可以测试。 

You can use a native sql query with 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] ;
}
 
No guarantee but you may test.

如何为给定的SQL查询编写JPA查询(How I can write JPA query for given SQL query)

最满意答案

相关问答

为什么要使用JPA而不是直接在Java文件上编写SQL查询（即直接写入JDBC）？(Why use JPA instead of directly writing SQL query on Java File (i.e. directly to JDBC)?)[2022-02-18]

如何为给定的sql查询创建hibernate条件查询(how create hibernate criteria query for given sql query)[2022-10-28]

如何编写JPA本机查询来连接两个表。(How to write JPA native query to join two tables. Eclipseink JPA,Glassfish3.x,EJB3.1, MySQL)[2023-04-23]

JPA hibernate SQL查询(JPA hibernate SQL query)[2022-09-12]

如何将此SQL查询编写为JPA查询(How to write this SQL query as a JPA query)[2023-02-19]

JPA Spring Query sql(JPA Spring Query sql)[2022-11-07]

JPA命名查询(JPA Named query)[2022-09-05]

如何在Jpa存储库中编写Sql查询？(How to write a Sql query in Jpa repository?)[2022-05-09]

JPA查询优化(JPA query optimization)[2022-11-13]

如何为给定的SQL查询编写JPA查询(How I can write JPA query for given SQL query)[2023-03-21]

相关文章

最新问答