包含聚合和子查询的JPA查询的格式？

Question

@Entity
@Table(name = "INSTRUCTION")
public class Instruction {

    @Id
    @Column(name = "ID", nullable = false, unique = true)
    public Long id;

    @Column(name = "CURRENT_STATUS", nullable = false)
    private InstructionState currentStatus;

    @Column(name = "SUBTYPE", nullable = false)
    private InstructionAction subtype;

    //Getters & Setters
}

我想用JPA编写以下查询，以检索按current_status和subtype分组的指令的计数。我知道以下SQL可以工作：

SELECT CURRENT_STATUS, SUBTYPE, COUNT(*) count 
FROM (SELECT ID, CURRENT_STATUS, SUBTYPE 
        FROM INSTRUCTION 
        WHERE VALUE_DATE= '17-JUN-21'  
        AND LAST_UPDATED >= '16-JUN-21' 
        AND LAST_UPDATED < '17-JUN-21' 
        GROUP BY ID, CURRENT_STATUS, SUBTYPE)
GROUP BY CURRENT_STATUS, SUBTYPE;

我想从这个查询中获取结果，并将其映射到一个新的对象调用instructionCount:

public class InstructionCount {

    private InstructionState status;
    private InstructionAction subType;
    private Integer count;

    public InstructionCount(final InstructionState status, final InstructionAction subType, final Integer count) {
        this.status = status;
        this.subType = subType;
        this.count = count;
    }

    //Getters and setters
}

问题所在

@Query(value = "SELECT new com.modles.InstructionCount(CURRENT_STATUS status, SUBTYPE subType, COUNT(*) count) \n" +
            "FROM (SELECT ID, CURRENT_STATUS, SUBTYPE \n" +
            "\t\tFROM LTD_RTGS_CASHINSTRUCTION \n" +
            "\t\tWHERE VALUE_DATE= :valueDate  \n" +
            "\t\tAND LAST_UPDATED >= :lastUpdatedFrom \n" +
            "\t\tAND LAST_UPDATED < :lastUpdatedTo \n" +
            "\t\tGROUP BY ID, CURRENT_STATUS, SUBTYPE)\n" +
            "GROUP BY CURRENT_STATUS, SUBTYPE", nativeQuery = true)
    List<InstructionCount> findInstructionCounts(@Param("valueDate") LocalDate valueDate, @Param("lastUpdatedFrom") LocalDateTime lastUpdatedFrom, @Param("lastUpdatedTo") LocalDateTime lastUpdatedTo);

Answer 1

下面的查询应该工作得很好，不需要您的内部/子查询

SELECT CURRENT_STATUS, SUBTYPE, COUNT(ID)  
FROM INSTRUCTION 
        WHERE VALUE_DATE= '17-JUN-21'  
        AND LAST_UPDATED >= '16-JUN-21' 
        AND LAST_UPDATED < '17-JUN-21' 
GROUP BY CURRENT_STATUS, SUBTYPE;

当返回结果为

指令计数