SpringDataJPA 自定义联合查询、Group by查询

1.分页自定义联合查询 - NativeQuery

/**
* 接收类，需要和查询的字段保持一致
*/
@Data
@Schema(title = "审批表、审批协同分页查询信息")
@AllArgsConstructor
@NoArgsConstructor
public class ApprovalPageRspVO implements Serializable {
    private static final long serialVersionUID = 1L;

    @Schema(description = "审批id")
    private BigInteger id;

    @Schema(description = "开发类型（1-应用开发 2-算法开发 3-数据开发）")
    private Integer devType;

    @Schema(description = "开发任务id")
    private BigInteger projectId;

    @Schema(description = "开发任务名称")
    private String projectName;

    @Schema(description = "审批任务状态（0-待审批 1-已撤回 2-成功 3-失败；approvalTaskStatus 传入时有返回）")
    private Integer approvalTaskStatus;

    @Schema(description = "审批任务进度")
    private String approvalTaskProcess;

    @Schema(description = "审批状态（0-待审批 1-已同意 2-已拒绝；approvalStatus 传入时有返回）")
    private Integer approvalClbStatus;
}

Service 实现类

import javax.persistence.EntityManager;
import javax.persistence.Query;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
	
@Service
public class ApprovalServiceImpl implements ApprovalService {

	@Autowired
	private EntityManager entityManager;
	
	public Page<ApprovalPageRspVO> pageList(GetApprovalPageVO pageVO) {
		Pageable pageable = PageRequest.of(pageVO.getPageNumber(), pageVO.getPageSize());
        StringBuilder sb = new StringBuilder();
        StringBuilder condition = new StringBuilder();
        if (null != pageVO.getDevType()) {
            condition.append(" and approval.dev_type = " + pageVO.getDevType());
        }
        if (!StringUtils.isEmpty(pageVO.getProjectName())) {
            condition.append(" and project.name like '%" + pageVO.getProjectName() + "%'");
        }

        if(pageVO.getApprovalRole() == 1){
        	// 假名需要和接收类字段名保持一致          
            sb.append("select approval.id as id, approval.dev_type as \"devType\", approval.project_id as \"projectId\", project.name as \"projectName\", approval.approval_task_status as \"approvalTaskStatus\", null as \"approvalTaskProcess\", null as \"approvalClbStatus\" " +
                    " from approval left join project on approval.project_id = project.id " +
                    " where 1=1 ");
            condition.append(" and approval.create_id = '" + principal.getUserId() + "'");
            if(null != pageVO.getApprovalTaskStatus()){
                condition.append(" and approval.approval_task_status = " + pageVO.getApprovalTaskStatus());
            }
        }

        if(pageVO.getApprovalRole() == 2){
            sb.append("select approval.id as id, approval.dev_type as \"devType\", approval.project_id as \"projectId\", project.name as \"projectName\", null as \"approvalTaskStatus\", null as \"approvalTaskProcess\", approvalClb.approval_status as \"approvalClbStatus\" " +
                    " from approval_collaboration approvalClb " +
                    " right join approval on approvalClb.approval_id = approval.id " +
                    " left join project on project.id = approval.project_id " +
                    " where 1=1 ");
            condition.append(" and approvalClb.owner_id = '" + principal.getUserId() + "'");
            if(null != pageVO.getApprovalStatus()){
                condition.append(" and approvalClb.approval_status = " + pageVO.getApprovalStatus());
            }
        }

        sb.append(condition).append(" order by approval.create_time desc");
		Query query = entityManager.createNativeQuery(sb.toString());
		query.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(ApprovalPageRspVO.class));
		int total = query.getResultList().size();
		query.setFirstResult(pageVO.getPageNumber() * pageVO.getPageSize());
		query.setMaxResults((pageVO.getPageNumber() + 1) * pageVO.getPageSize());
		List<ApprovalPageRspVO> approvalPageRspVOList = query.getResultList();
		Page<ApprovalPageRspVO> page = new PageImpl<>(approvalPageRspVOList, pageable, total);
		return page;
	}
}

2.分页自定义联合查询 - 非NativeQuery

/**
* 接收对象，需要和查询的字段保持一致
*/
@Data
@Builder
public class DeploymentDetailVO implements Serializable {

    private static final long serialVersionUID = 1L;

    @Schema(description = "部署id")
    private Long id;

    @Schema(description = "部署时间")
    private Date createTime;

    @Schema(description = "部署状态")
    private Integer status;

    @Schema(description = "代码仓库分支")
    private String repoBranch;

    @Schema(description = "代码仓库tag")
    private String repoTag;

    @Schema(description = "服务名")
    private String serviceName;

    @Schema(description = "镜像名")
    private String imageName;

	// 需要自行实现全参构造函数
    public DeploymentDetailVO(Long id, Date createTime, Integer status, String repoBranch, String repoTag, String serviceName, String imageName) {
        this.id = id;
        this.createTime = createTime;
        this.status = status;
        this.repoBranch = repoBranch;
        this.repoTag = repoTag;
        this.serviceName = serviceName;
        this.imageName = imageName;
    }
}

Repository 类

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

    @Query("select new com.zhmap.mcloud.devops.application_dev.vo.DeploymentDetailVO(dep.id, dep.createTime, dep.status, build.repoBranch, build.repoTag, p.serviceName, build.imageName) " +
            "from Deployment dep left join Build build on dep.buildId = build.id left join Project p on build.projectId = p.id where dep.projectId = :projectId and build.env = :env")
    Page<DeploymentDetailVO> getDeploymentPage(@Param("projectId") long projectId, @Param("env") int env, Pageable pageable);

3.分页Group by查询 - NativeQuery

@Data
@Schema(title = "分页查询产品")
@Builder
public class ProductPageVO implements Serializable {

    private static final long serialVersionUID = 1L;

    private Long id;

    @Schema(description = "创建人")
    private String createId;

    @Schema(description = "创建时间")
    private Date createTime;

    @Schema(description = "更新人")
    private String updateId;

    @Schema(description = "更新时间")
    private Date updateTime;

    @Schema(description = "产品名称")
    private String name;

    @Schema(description = "产品图标路径")
    private String icon;

    @Schema(description = "产品描述")
    private String desc;

    @Schema(description = "产品下项目个数统计")
    private long projCount;

	// ProductPageVO 类需要自行实现全参构造函数，保证查询出来的字段和该类的属性保持一致
    public ProductPageVO(Long id, String createId, Date createTime, String updateId, Date updateTime, String name, String icon, String desc, long projCount) {
        this.id = id;
        this.createId = createId;
        this.createTime = createTime;
        this.updateId = updateId;
        this.updateTime = updateTime;
        this.name = name;
        this.icon = icon;
        this.desc = desc;
        this.projCount = projCount;
    }
}

Repository 类

	@Query("SELECT new com.zhmap.mcloud.devops.application_dev.vo.ProductPageVO(prod.id, prod.createId, prod.createTime, prod.updateId, prod.updateTime, prod.name, prod.icon, prod.desc, count(proj.id)) " +
	            " from Product as prod left JOIN Project proj on prod.id = proj.productId WHERE prod.name like CONCAT('%',:name,'%') GROUP BY prod.id")
    Page<ProductPageVO> getProductPage(@Param("name") String name, Pageable pageable);