JPA 链表查询，子查询操作

最新推荐文章于 2024-10-12 17:05:28 发布

XStorms

最新推荐文章于 2024-10-12 17:05:28 发布

阅读量3.6k

点赞数

分类专栏： java 文章标签： JPAQueryFactory联表查询 JPAQueryFactory子查询

本文链接：https://blog.csdn.net/datszhang/article/details/90693137

版权

java 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

@Autowired
private JPAQueryFactory queryFactory;

//联表查询操作
1>
QMmFileDefEntity qDefEntity = QMmFileDefEntity.mmFileDefEntity;
QMmFileDtlEntity qDtlEntity = QMmFileDtlEntity.mmFileDtlEntity;
QMmLinkEntity qLinkEntity = QMmLinkEntity.mmLinkEntity;
JPAQuery<Tuple> tupleJPAQuery = queryFactory.select(qDefEntity,qDtlEntity).from(qDtlEntity)
        .innerJoin(qDefEntity).on(qDefEntity.defId.eq(qDtlEntity.defId))
        .leftJoin(qLinkEntity).on(qLinkEntity.sourceId.eq(qDtlEntity.mediaId)
                .and(qLinkEntity.sourceTable.eq(DefaultsValue.MM_FILE_DTL))
                .and(qLinkEntity.sourceType.in(setEpisodeSourceType()))
                .and(qLinkEntity.showType.in(MediaConstant.ShowType.HORIZONTAL_COVER.getCode(),MediaConstant.ShowType.VERTICAL_COVER.getCode()))
                .and(qLinkEntity.dflag.eq(DefaultsValue.BYTE_TWO)))
        .where(qDtlEntity.dflag.eq(DefaultsValue.BYTE_TWO).and(qDefEntity.mediaType.eq(MediaConstant.MediaType.VIDEO.getCode())));

List<Tuple> tuples = queryFactory.select(qDtlEntity,qLinkEntity).from(qDtlEntity)
         .innerJoin(qLinkEntity).on(qLinkEntity.sourceId.in(bo.getMediaIds())
               .and(qLinkEntity.sourceTable.eq(DefaultsValue.MM_FILE_DTL)
               .and(qLinkEntity.mediaType.eq(MediaConstant.MediaType.VIDEO.getCode()))
               .and(qLinkEntity.sourceType.in(getVideoSourceType()))))
         .where(qDtlEntity.mediaId.in(bo.getMediaIds()).and(qDtlEntity.dflag.eq(DefaultsValue.BYTE_TWO)))
       .fetch();

List<Tuple> dtlTuples = queryFactory
        .select(qDtlEntity.defId,qDtlEntity.mediaId.max(),qDtlEntity.gmtModified,qDtlEntity.sort,qDtlEntity.count(),
                qDtlEntity.dtlName,qDtlEntity.collectTotal,qDtlEntity.likeTotal,qDtlEntity.playTotal,qDtlEntity.shareTotal,qDtlEntity.vedioId)
        .from(qDtlEntity)
        .where(qDtlEntity.defId.in(bo.getDefIds()).and(qDtlEntity.dflag.eq(DefaultsValue.BYTE_TWO)))
        .groupBy(qDtlEntity.defId).fetch();

Set<Long> defIds = new HashSet<>();
tuples.forEach(row -> {
    MmFileDtlEntity dtlEntity = row.get(qDtlEntity);
    VideoPlayListVo vo = new VideoPlayListVo();
    dtlConversionCollectionPlayVo(vo,dtlEntity);
    vo.setCurrentMediaId(dtlEntity.getMediaId());
    vo.setMediaId(null);
    MmLinkEntity linkEntity = row.get(qLinkEntity);
    vo.setDuration(linkEntity.getDuration());
    listVos.add(vo);
    defIds.add(dtlEntity.getDefId());
});

//子查询操作

JPAQuery<MmFileDefEntity> query = queryFactory.selectFrom(qDefEntity)
        .where(qDefEntity.mediaType.eq(MediaConstant.MediaType.VIDEO.getCode())
                .and(qDefEntity.cateType.eq(MediaConstant.CateType.WEMEDIA.getCode())
                        .or(qDefEntity.cateType.eq(MediaConstant.CateType.VIDEO.getCode())
                                .and(qDefEntity.cateCode.eq(SysCategroupEnums.CEnName.TVPLAY.getCEnName())))
                )
                .and(qDefEntity.dflag.eq(DefaultsValue.BYTE_TWO))
                .and(JPAExpressions.select(qDtlEntity.count())
                        .from(qDtlEntity)
                        .where(qDtlEntity.defId.eq(qDefEntity.defId)
                                .and(qDtlEntity.dflag.eq(DefaultsValue.BYTE_TWO)))
                        .groupBy(qDtlEntity.defId).goe(DefaultsValue.LONG_ONE))
        );

JPAQueryFactory 多条件动态查询

public RestVo<PageVo<VideoListVo>> videoReviewList(PageBo<VideoListBo> bo){
    //TODO 入参组装查询
    JPAQuery<Tuple> jDtlEntityQuery = queryVideoList(bo);
    long total = jDtlEntityQuery.fetchCount();
    List<Tuple> results = jDtlEntityQuery.offset((bo.getPage() - 1) * bo.getSize()).limit(bo.getSize()).fetch();
}

private JPAQuery<Tuple> queryVideoList(PageBo<VideoListBo> bo){
    QMmFileDefEntity qDefEntity = QMmFileDefEntity.mmFileDefEntity;
    QMmFileDtlEntity qDtlEntity = QMmFileDtlEntity.mmFileDtlEntity;
    QMmLinkEntity qLinkEntity = QMmLinkEntity.mmLinkEntity;
    JPAQuery<Tuple> tupleJPAQuery = queryFactory.select(qDefEntity,qDtlEntity).from(qDtlEntity)
            .innerJoin(qDefEntity).on(qDefEntity.defId.eq(qDtlEntity.defId))
            .leftJoin(qLinkEntity).on(qLinkEntity.sourceId.eq(qDtlEntity.mediaId)
                    .and(qLinkEntity.sourceTable.eq(DefaultsValue.MM_FILE_DTL))
                    .and(qLinkEntity.sourceType.in(setEpisodeSourceType()))
                    .and(qLinkEntity.showType.in(MediaConstant.ShowType.HORIZONTAL_COVER.getCode(),MediaConstant.ShowType.VERTICAL_COVER.getCode()))
                    .and(qLinkEntity.dflag.eq(DefaultsValue.BYTE_TWO)))
            ;
    //类似1=1
    Predicate predicate = qDefEntity.isNotNull().or(qDefEntity.isNull());
    if(null != bo.getParam()){
        String regx = "^[1-9]\\d*$";
        Pattern pattern = Pattern.compile(regx);
        if(StringUtils.isNotBlank(bo.getParam().getUserParam())){
            if(pattern.matcher(bo.getParam().getUserParam()).matches()){
                predicate = ExpressionUtils.and(predicate,qDefEntity.userId.eq(Long.valueOf(bo.getParam().getUserParam()))
                        .or(qDefEntity.nickName.likeIgnoreCase(bo.getParam().getUserParam())));
            }else {
                predicate = ExpressionUtils.and(predicate,qDefEntity.nickName.likeIgnoreCase(bo.getParam().getUserParam()));
            }
        }

        if(null != bo.getParam().getCheckStatus()){
            if(MediaConstant.PersonCheck.CHECKING.getCode() == bo.getParam().getCheckStatus()){
                predicate = ExpressionUtils.and(predicate,qDtlEntity.machineCheck.eq(bo.getParam().getCheckStatus()));
            }else if(MediaConstant.PersonCheck.NORMAL.getCode() == bo.getParam().getCheckStatus()){
                predicate = ExpressionUtils.and(predicate,qDtlEntity.fileStatus.eq(MediaConstant.FileStatus.NORMAL_FILE_STATUS.getCode()));
            }else if(MediaConstant.PersonCheck.NOT_PASS.getCode() == bo.getParam().getCheckStatus()){
                predicate = ExpressionUtils.and(predicate,qDtlEntity.fileStatus.ne(MediaConstant.FileStatus.NORMAL_FILE_STATUS.getCode()));
            }
        }
        if(StringUtils.isNotBlank(bo.getParam().getStar())){
            predicate = ExpressionUtils.and(predicate,qDefEntity.star.eq(bo.getParam().getStar()));
        }
       
    }
    predicate = ExpressionUtils.and(predicate,qDtlEntity.dflag.eq(DefaultsValue.BYTE_TWO).and(qDefEntity.mediaType.eq(MediaConstant.MediaType.VIDEO.getCode())));
    return tupleJPAQuery.where(predicate);
}

参考：https://blog.csdn.net/phapha1996/article/details/83614975