How to Map a JPA create native query to projections

I am trying to get count from a postgreSQL database using Spring Data JPA createNativeQuery. However, the query is returning null instead of the actual values.

Here is the JPA createNativeQuery statement below:

Query q = entityManager.createNativeQuery("SELECT null AS id, 
count(case when (IS_QUERIED = false AND SUBMITTED = true) 
then 1 else null end) AS pending,  
count(case when ( (SUBMITTED = true)) then 1 else null end) AS submitted, 
count(*) AS totalApplications FROM ANNUAL_RETURNS ", AnnualReturn.class);
//Note: AnnualReturn is the name of the @Entity class

List <AnnualReturn> countList=q.getResultList();

return countList;

I need help mapping the “submitted”, “pending” and “totalApplications” instances from my query in a way that returns the result as below.

Result expected is:

"data": {
        "totalApplications": 2000,
        "submitted": 560,
        "pending": 60,
    }

Result am getting:

{
    "data": [
        null
    ]

I would appreciate any help.

Answer

I do not know why but I believe SELECT null AS id is causing null record to be fetched.

If you don’t want id to be fetched then you can use projection with custom RowMapper or DTO projection.

See below:

@Entity
@Data
@ToString
class A {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    Long id;
    String name;
    int age;

    public A() {
    }
    public A(String name, int age) {
        this.name = name;
        this.age = age;
    }
}

interface ARepo extends JpaRepositoryImplementation<A, Long> {
}

@Component
@RequiredArgsConstructor
class Init {
    final ARepo repo;
    final EntityManager em;

    @EventListener
    public void init(ContextRefreshedEvent evt) {
        repo.save(new A("XX", 5));
        repo.save(new A("ZZ", 6));
        repo.save(new A("AA", 11));
        repo.save(new A("AB", 12));

        Query q = em.createNativeQuery("select 0 as id, a.name as name, a.age as age  from A a where a.total > 10 ", A.class);

        System.out.println(q.getResultList()); //fetches AA and BB 
        //if you change the query to select null as id  it would return [ null, null]
          
    }
}