queryDSL复杂查询的分页和自定义排序实现

谁践踏了优雅 2024-04-19 15:02 53阅读 0赞

### 概要目录 ###

*   *  一、首先自定义存储库的定义规范和引用方式，要特别注意，\[可参考我之前关于自定义存储库的总结\](https://blog.csdn.net/qq\_42105629/article/details/100099643)
     *   *  1.1、代码示例
         *  1.2、不足之处

## 一、首先自定义存储库的定义规范和引用方式，要特别注意，[可参考我之前关于自定义存储库的总结][Link 1] ##

![在这里插入图片描述][20190919110238561.png]

### 1.1、代码示例 ###

package com.wondertek.oes.workbench.manage.repository.customize;
    
    import com.google.common.collect.Lists;
    import com.querydsl.core.QueryResults;
    import com.querydsl.core.Tuple;
    import com.querydsl.core.types.Predicate;
    import com.querydsl.jpa.impl.JPAQuery;
    import com.wondertek.oes.workbench.commons.enums.NodeResultEnum;
    import com.wondertek.oes.workbench.commons.model.*;
    import com.wondertek.oes.workbench.manage.vo.baseQuery.AssetInfoVo;
    import com.wondertek.oes.workbench.manage.vo.baseQuery.AssetPageVo;
    import com.wondertek.oes.workbench.manage.vo.baseQuery.McnRecordVo;
    import org.springframework.data.domain.Page;
    import org.springframework.data.domain.PageImpl;
    import org.springframework.data.domain.Pageable;
    import org.springframework.stereotype.Repository;
    
    import javax.persistence.EntityManager;
    import javax.persistence.PersistenceContext;
    import java.util.List;
    
    @Repository
    public class EnRepositoryCustomImpl implements EnRepositoryCustom { 
    
        @PersistenceContext
        private EntityManager em;
    
        /** * mcn详情查询 * * @param groupId 工作组编号 * @param programId 节目编号 * @return List */
        @Override
        public List<McnRecordVo> McnRecordInfo(Long groupId, Long programId) { 
            JPAQuery<McnRecordVo> query = new JPAQuery<>(em);
    
            QMcnInfo m = QMcnInfo.mcnInfo;
            QSubpInfo s = QSubpInfo.subpInfo;
            QTask t = QTask.task;
            QWorkGroup w = QWorkGroup.workGroup;
    
            List<Tuple> list = query.select(
                    m.programId,
                    s.contentId,
                    m.assetId,
                    s.assetName,
                    m.disposeChannel,
                    m.endTime,
                    t.name,
                    m.url,
                    w.groupName)
                    .from(m).leftJoin(s).on(m.programId.eq(s.programId)).leftJoin(t).on(m.taskId.eq(t.taskId)).leftJoin(w).on(m.groupId.eq(w.groupId))
                    .where(m.programId.eq(programId)).where(m.groupId.eq(groupId))
                    .fetchResults().getResults();
            return this.tupleToMcnRecordVo(list);
        }
    
        /** * 动态条件查询mcn展现运营 * * @param predicate 动态条件 * @return List<McnRecordVo> */
        @Override
        public Page<McnRecordVo> McnRecordList(Predicate predicate, Pageable pageable) { 
    
            JPAQuery<McnRecordVo> query = new JPAQuery<>(em);
    
            QMcnInfo m = QMcnInfo.mcnInfo;
            QSubpInfo s = QSubpInfo.subpInfo;
            QTask t = QTask.task;
            QWorkGroup w = QWorkGroup.workGroup;
    
            QueryResults<Tuple> queryResults = query.select(
                    m.programId,
                    s.contentId,
                    m.assetId,
                    s.assetName,
                    m.disposeChannel,
                    m.endTime,
                    t.name,
                    w.groupName,
                    m.mcnStatus,
                    m.shareType)
                    .from(m).leftJoin(s).on(m.programId.eq(s.programId)).leftJoin(t).on(m.taskId.eq(t.taskId)).leftJoin(w).on(m.groupId.eq(w.groupId))
                    .where(predicate)
                    .orderBy(m.endTime.desc())
                    .offset(pageable.getOffset()).limit(pageable.getPageSize()).fetchResults();
    
            List<Tuple> tuples = queryResults.getResults();
            List<McnRecordVo> vos = this.tupleToMcnRecordVo(tuples);
            return new PageImpl<>(vos, pageable, queryResults.getTotal());
        }
    
        /** * 查询展现运营列表 * * @param predicate 动态条件 * @return List<AssetPageVo> */
        @Override
        public Page<AssetPageVo> AssetPageList(Predicate predicate, Pageable pageable) { 
    
            JPAQuery<McnRecordVo> query = new JPAQuery<>(em);
    
            QCatalogInfo ca = QCatalogInfo.catalogInfo;
            QPublishInfo pub = QPublishInfo.publishInfo;
    
            QueryResults<Tuple> queryResults = query.select(
                    ca.assetId,
                    ca.assetName,
                    pub.programId,
                    pub.productName)
                    .from(ca).leftJoin(pub).on(ca.assetId.eq(pub.assetId))
                    .where(predicate)
                    .orderBy(pub.publishTime.desc())
                    .offset(pageable.getOffset()).limit(pageable.getPageSize()).fetchResults();
    
            List<Tuple> tuples = queryResults.getResults();
            long total = queryResults.getTotal();
            List<AssetPageVo> vos = this.tupleToAssetPageVo(tuples);
            return new PageImpl<>(vos, pageable, total);
        }
    
        /** * 展现运营详情展示 * * @param groupId 工作组ID * @param programId 节目ID * @return AssetInfoVo */
        @Override
        public AssetInfoVo AssetRecordInfo(Long groupId, Long programId) { 
    
            JPAQuery<AssetInfoVo> query = new JPAQuery<>(em);
    
            QPublishInfo pub = QPublishInfo.publishInfo;
            QWorkGroup wg = QWorkGroup.workGroup;
            QTask ta = QTask.task;
    
            List<Tuple> list = query.select(
                    pub.programId,
                    pub.productName,
                    pub.assetId,
                    pub.assetName,
                    wg.groupName,
                    ta.name)
                    .from(pub).leftJoin(wg).on(pub.groupId.eq(wg.groupId)).leftJoin(ta).on(pub.taskId.eq(ta.taskId))
                    .where(pub.publishStatus.eq(NodeResultEnum.PublishStatus.FINISHED.getKey()), pub.programId.eq(programId), pub.groupId.eq(groupId))
                    .fetchResults().getResults();
    
            if (list != null && list.size() > 0) { 
                Tuple tu = list.get(0);
                return new AssetInfoVo(tu.get(pub.programId),
                        tu.get(pub.productName),
                        tu.get(pub.assetId),
                        tu.get(pub.assetName),
                        tu.get(wg.groupName),
                        tu.get(ta.name));
            }
            return null;
        }
    
        /** * 抽取公共方法,mcn详情 * * @param tuple 入参 * @return List<McnRecordVo> */
        private List<McnRecordVo> tupleToMcnRecordVo(List<Tuple> tuple) { 
            QMcnInfo m = QMcnInfo.mcnInfo;
            QSubpInfo s = QSubpInfo.subpInfo;
            QTask t = QTask.task;
            QWorkGroup w = QWorkGroup.workGroup;
    
            List<McnRecordVo> list1 = Lists.newArrayList();
            for (Tuple tu : tuple) { 
                McnRecordVo vo = new McnRecordVo();
                vo.setUrl(tu.get(m.url));
                vo.setProgramId(tu.get(m.programId));
                vo.setContentId(tu.get(s.contentId));
                vo.setAssetId(tu.get(m.assetId));
                vo.setAssetName(tu.get(s.assetName));
                vo.setDisposeChannel(tu.get(m.disposeChannel));
                vo.setDisposeTime(tu.get(m.endTime));
                vo.setTaskName(tu.get(t.name));
                vo.setGroupName(tu.get(w.groupName));
                vo.setMcnStatus(tu.get(m.mcnStatus));
                vo.setShareType(tu.get(m.shareType));
                list1.add(vo);
            }
            return list1;
        }
    
        /** * 抽取公共方法 * * @param tuple List<Tuple> tuple * @return List<AssetPageVo> */
        private List<AssetPageVo> tupleToAssetPageVo(List<Tuple> tuple) { 
            QCatalogInfo ca = QCatalogInfo.catalogInfo;
            QPublishInfo pub = QPublishInfo.publishInfo;
    
            List<AssetPageVo> list1 = Lists.newArrayList();
            for (Tuple tu : tuple) { 
                AssetPageVo vo = new AssetPageVo();
                vo.setProgramId(tu.get(pub.programId));
                vo.setProductName(tu.get(pub.productName));
                vo.setAssetId(tu.get(ca.assetId));
                vo.setAssetName(tu.get(ca.assetName));
                list1.add(vo);
            }
            return list1;
        }
    
    
    }

### 1.2、不足之处 ###

返回的元组数据，需要我们手动去转，下面我总结了一些可直接映射我们自定义的VO对象的方法

[QueryDsl自定义返回对象][QueryDsl]

[Link 1]: https://blog.csdn.net/qq_42105629/article/details/100099643
[20190919110238561.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/2b915fb22afa4e08b5f3475aae46e156.png
[QueryDsl]: https://blog.csdn.net/qq_42105629/article/details/104791924