时间戳

首页 > 解决方案 > 尝试在 Spring Data JPA 中使用 createNativeQuery 从数据库中获取计数但获取 NULL

java - 尝试在 Spring Data JPA 中使用 createNativeQuery 从数据库中获取计数但获取 NULL

问题描述

我正在尝试使用 Spring Data JPA createNativeQuery 从 postgreSQL 数据库中获取计数。但是，查询返回的是 null 而不是实际值，即使查询自然应该返回一些值，就像它在数据库控制台中运行时所做的那样。

这是 API 的控制器代码


@PersistenceContext
    EntityManager em;

    @PostMapping("/date-sum")
    public ResponseEntity<Object> sumRecordsWithDate(@RequestBody SearcherDto searcherDto) {

 Query q = em.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 a LEFT JOIN 
COMPANY c ON c.id= a.COMPANY_FK WHERE (a.FINANCIAL_YEAR_END >=:startDate 
AND a.FINANCIAL_YEAR_END <=:endDate) AND                    
(c.COMPANY_TYPE_FK=:type OR :type=0) AND (a.PROCESS_TYPE_FK =:process OR 
 (:process1=9542 AND :process2=9594 AND :process3=9598)) ", AnnualReturn.class);

//type set the parameters to avoid postreSQL type-error

        q.setParameter("process", new TypedParameterValue(LongType.INSTANCE, processType));
        q.setParameter("process1", new TypedParameterValue(LongType.INSTANCE, processType1));
        q.setParameter("process2", new TypedParameterValue(LongType.INSTANCE, processType2));
        q.setParameter("process3", new TypedParameterValue(LongType.INSTANCE, processType3));
        q.setParameter("type", new TypedParameterValue(LongType.INSTANCE, companyType));
        
// these are never null, so we don't need to type-set them.
        q.setParameter("startDate", start);
        q.setParameter("endDate", end);
        List <AnnualReturn> countList=q.getResultList();


        return ResponseEntity.ok(new JsonResponse("See Data Object for Details", countList));
    }

这是年回报实体类的样子：

@Entity
@Table(name="ANNUAL_RETURNS")
public class AnnualReturn implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID")
    private Long id;

    @Column(name = "IS_QUERIED")
    private Boolean queried;

    @Column(name = "FINANCIAL_YEAR_END")
    private String financialYearEnd;
 
    @ManyToOne
    @JoinColumn(name = "COMPANY_FK")
    private Company company;

    @ManyToOne
    @JoinColumn(name = "PROCESS_TYPE_FK",referencedColumnName = "id")
    private Process_Type processType;

}

这是我期望得到的结果：

"status": "OK",
    "success": true,
    "errorCode": 0,
    "data": {
        "totalApplications": 2000,
        "submitted": 560,
        "queried": 60,
    },
    "message": "See Data Object for Details"

这就是我得到的：

{
    "status": "OK",
    "success": true,
    "errorCode": 0,
    "data": [
        null
    ],
    "message": "See Data Object for Details"

这是我从邮递员发送的请求：

{
"startDate": "2014-06-11",
"endDate": "2020-10-14",
"processTypeFk":9542,
"companyTypeId": 6995

    }

请帮助我提出任何想法或建议，以使计数查询返回提交、查询和总计应用程序的实际值。

标签： javapostgresqlspring-data-jpa

解决方案

首先，如果全部，结果不能直接映射到 AnnualReturn.class ，因为要执行大量 sql 操作来获取结果。列表 countList=q.getResultList(); 相反，它将是：

List <Object> countList=q.getResultList();
Iterator iterator = countList.iterator();
    while (iterator.hasNext()) {
        Object row = (Object) iterator.next();
       //set to your new Result Object
       ResultObject obj = new ResultObject();
        obj.setTotalApplications(row[0]); 
        obj.setSubmitted(row[1]);
        obj.setQueried(row [2]);
        // create object and add to list 
        list.add(obj);
    }
    
    Class ResultObject {
        int totalApplications ;
        int submitted;
        int queried ;
    //getter setter
    
    }

推荐阅读