Vikram SIngh Vikram SIngh - 19 days ago 6
Java Question

How I can write JPA query for given SQL query

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'

Answer

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.

Comments