Postgres Query to JPA Conversion

Question

I have a postgres query that i want to execute with JPA.

Can any one tell me how to right following query in jpa

SELECT  (
        SELECT COUNT(*)
        FROM "RECEIPT" Where "STATUS"='1'
        ) AS count1,
        (
        SELECT COUNT(*)
        FROM   "RECEIPT"  Where "STATUS"='1'
        ) AS count2;

This returns

count1 count2
11      11

In jpa i tried following.

Query query = em.createQuery("SELECT (SELECT count(*)  FROM SMSReceipt Where status='1' ) AS count1,(SELECT count(*)  FROM SMSReceipt Where status='8' ) AS count2 ");

But this is not working

Please suggest me how to do it

score 1 · Accepted Answer · edited May 23 '17 at 12:31

1

You need to use createNativeQuery() method to run plain sql

Query query = em.createNativeQuery("SELECT (SELECT count(*)  FROM SMSReceipt Where status='1' ) AS count1,(SELECT count(*)  FROM SMSReceipt Where status='8' ) AS count2 ");

createQuery() is used to run JPQL.

Also Check -JPA's EntityManager createQuery() vs createNamedQuery() vs createNativeQuery()

edited May 23 '17 at 12:31

Community

1
1

answered Jan 15 '16 at 06:42

singhakash

7,891
6
31
65

Postgres Query to JPA Conversion

1 Answers1