如何将JPA创建的本机查询映射到投影

Question

我正试图使用Spring Data JPA createNativeQuery从postgreSQL数据库中获取计数。但是，查询返回的是null，而不是实际值。

下面是JPACreateNativeQuery语句：

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;

我需要帮助绘制"；提交"挂起"；以及"；totalApplications"；实例，返回如下结果。

预期结果为：

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

我得到的结果：

{
"data": [
null
]

如果有任何帮助，我将不胜感激。

Answer 1

我不知道为什么，但我相信SELECT null AS id会导致提取空记录。

如果您不想获取id，那么您可以将投影与自定义RowMapper或DTO投影一起使用。

见下文：

@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]

}
}

Answer 2

您不应该在这里使用类型化查询，请尝试类似的方法

Query q = entityManager.createNativeQuery("SELECT " +
" COALESCE(sum(case when (IS_QUERIED = false AND SUBMITTED = true) " +
" then AMOUNT else 0 end),0) AS pending,  " +
" COALESCE(sum(case when ( (SUBMITTED = true)) then 1 else 0 end),0) AS submitted, " +
" COALESCE(sum(AMOUNT),0) AS totalApplications FROM ANNUAL_RETURNS ");
List <Object[]> countList=q.getResultList();
Object[] obj = countList.get(0);
for (Object value : obj) {
System.out.println(value);
}
long pending = ((BigInteger)obj[0]).longValue();
long submitted = ((BigInteger)obj[1]).longValue();
long total = ((BigInteger)obj[2]).longValue();

return ...; // prepare the values to your convinience

编辑

将null更改为0，以避免在结果中出现可能的null值

编辑

固定空表上的NPE