How I can write JPA query for given SQL query

Question

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'

I wanted provided SQL query which fits into em.createQuery( Answer ) — Vikram SIngh
– Vikram SIngh, Commented Nov 16, 2016 at 13:05
I mean how I can get JPQL query EntityManager.createQuery(Answer) ... Provided SQL Query Above — Vikram SIngh
– Vikram SIngh, Commented Nov 16, 2016 at 13:12
select bean from userbean bean where bean.organiz =: java or bean.organiz=:microsoft and bean.status_2 =: allocated or bean.status_2. =: bench --> this will return to you list list of size will be your answer , honestly dont understand what you mean — Mithat Konuk
– Mithat Konuk, Commented Nov 16, 2016 at 13:19
I mean can you modify SQL given query above into JPQL query EntityManager.createQuery( Answer ) — Vikram SIngh
– Vikram SIngh, Commented Nov 16, 2016 at 13:22

davidxxx · Accepted Answer · 2016-11-16 13:28:12Z

2

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.

answered Nov 16, 2016 at 13:28

davidxxx

132k23 gold badges231 silver badges228 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How I can write JPA query for given SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related