CODEWITHSUNDEEP
javamysqlhibernatejpa
Vikram SIngh
Vikram SIngh

Reputation: 43

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'

Upvotes: 1

Views: 100

Answers (1)

davidxxx
davidxxx

Reputation: 131556

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.

Upvotes: 2

Related Questions