利用JPAL返回Dto的返回结果

[zhangzhenhuajack]

参考：https://vladmihalcea.com/spring-jpa-dto-projection/ 实际案例如下：

1. 新建WorkflowThroughputPhaseCountDto 内容如下

   @Data
   public class WorkflowThroughputPhaseCountDto {
   // 注意构造方法会被JPQL使用
   public WorkflowThroughputPhaseCountDto(Long WorkflowThroughputPhaseId, ActionStatus actionStatus, Long workflowThroughputPhaseCount) {
       this.workflowTemplatePhaseId = WorkflowThroughputPhaseId;
       this.workflowThroughputPhaseCount = workflowThroughputPhaseCount;
       this.actionStatus = actionStatus;
   }
   
   private Long workflowTemplatePhaseId;
   private Long workflowThroughputPhaseCount;
   private ActionStatus actionStatus;
   }

2. Repository里面的写法如下，注意要用到 new Dto全路径类前面的构造方法

   @Repository
   public interface WorkflowThroughputPhaseRepository extends JpaRepository<WorkflowThroughputPhase, Long> {
   @Query(value = "select new com.mega.workflow.suite.service.dto.WorkflowThroughputPhaseCountDto(wt.workflowTemplatePhaseId,wt.status,count(wt.id)) " +
           "from WorkflowThroughputPhase wt where wt.workflowId = :workflowId group by wt.status,wt.workflowTemplatePhaseId")
   List<WorkflowThroughputPhaseCountDto> findCountByWorkflowId(@Param("workflowId") Long workflowId);
   }

[zhangzhenhuajack]

JPA 利用left join 返回DTO的例子

    @Query(value = "select new com.mega.inventory.service.impl.rule.InventoryUnitDto(u.id, r.registryId, r.registryName, r.registryType, u.inventoryUnitValue, u.packageUnitValue) " +
            "from RegistryShadow r left join InventoryUnit u on r.registryId = u.registryId and r.registryType = u.registryType " +
            "where r.deleted=false " +
            "and (:#{#registryType} is null or r.registryType = :#{#registryType}) " +
            "and (:#{#registryName} is null or r.registryName like %:#{#registryName}%) " +
            "")
    Page<InventoryUnitDto> queryByRegistry(@Param("registryName") String registryName, @Param("registryType") RegistyDataTypeEnum registryType, Pageable pageable);

DTO的内容如下：

@Data
@Builder
public class InventoryUnitDto {
    public InventoryUnitDto() {
    }

    //    (r.registryId, r.registryName, r.registryType, u.id, u.inventoryUnitValue, u.packageUnitValue)
    public InventoryUnitDto(Long inventoryUnitId, Long registryId, String registryName, RegistyDataTypeEnum registryType, String inventoryUnitValue, String packageUnitValue) {
        this.inventoryUnitId = inventoryUnitId;
        this.inventoryUnitValue = inventoryUnitValue;
        this.packageUnitValue = packageUnitValue;
        this.registryId = registryId;
        this.registryName = registryName;
        this.registryType = registryType;
    }

    public Long getId(){
        return this.registryId;
    }
    @ApiModelProperty(value = "库存单位规则ID，可能为空，为空说明从来没有编辑过")
    private Long inventoryUnitId;
    @Length(max = 16, message = "库存单位值长度不能超过16")
    @ApiModelProperty(value = "库存单位值(ml/mg等)")
    private String inventoryUnitValue;

    @Length(max = 16, message = "包装单位值长度不能超过16")
    @ApiModelProperty(value = "包装单位值(瓶/袋等)")
    private String packageUnitValue;

    @NotNull(message = "物料ID不能为空")
    @ApiModelProperty(value = "注册表ID")
    private Long registryId;

    @ApiModelProperty(value = "注册表名称")
    private String registryName;

    @NotNull(message = "类型不能为空")
    @ApiModelProperty(value = "注册表类型")
    @Enumerated(value = javax.persistence.EnumType.STRING)
    private RegistyDataTypeEnum registryType;

}