SpringJPA中多表关联查询并返回自定义dto带分页

雨点打透心脏的1/2处 2023-01-23 10:59 2阅读 0赞

如题：要实现SpringJPA中多表关联查询并返回自定义dto带分页功能，使用SpringJAP的查询时暂时没找到合适的方法，也不想强制去给实体间做关联，于是有如下代码：

一、建立自己需要返回的dto（这里要注意一点：dto的属性必须和你的查询语句的字段属性一致，否则会报错）

/**
     * 设备备品备件信息Dto数据对象信息类
     * 
     **/
    @Data
    public class ZiYuanBeiJianDto  implements Serializable {
        /**
         * 主键id<br>
         */
        private String id;
    
        /**
         * 备件id
         */
        private String beiJianId;
    
        /**
         * 备件名称
         */
        private String beiJianName;
    
        /**
         * 备件型号
         */
        private String beiJianXingHao;
    
        /**
         * 备件单位
         */
        private String beiJianUnits;
    
        /**
         * 应备数量
         */
        private Float yingBeiNum;
    
        /**
         * 库存数量
         */
        private Float kuCunNum;
    
    
        /**
         * 备注
         */
        private String beiZhu;
    
        /**
         * 设备id
         */
        private String pid;
    }

二、定义Service层接口方法：

/**
     *  这是业务层实现接口
     *  
     */
    public interface ZiYuanBeiJianService {
    
    
    
        /**
         * 
         * @param pageNo 起始页
         * @param pageSize 每页最大条数
         * @param params 查询参数
         * @return 返回查询结果
         */
        Page<ZiYuanBeiJianDto> findZiYuanBeiJianByPage(int pageNo, int pageSize, ZiYuanBeiJianDto params);
    
    
    
    }

三、Service层接口实现

/**
     *   这是业务层实现类
     *   
     * */
    @Service
    public class ZiYuanBeiJianServiceImpl implements ZiYuanBeiJianService {
        @Autowired
        private ZiYuanBeiJianRep ziYuanBeiJianRep;
        @Autowired
        private BeiJianKuCunRep beiJianKuCunRep;
    
        @PersistenceContext
        private EntityManager entityManager;
    
    
    
    
        @Override
        public Page<ZiYuanBeiJianDto> findZiYuanBeiJianByPage(int pageNo, int pageSize, ZiYuanBeiJianDto ziYuanBeiJianDto) {
            String pid = ziYuanBeiJianDto.getPid();
            if (StringUtils.isBlank(pid)) {
                pid = "-1";
            }
            Pageable pageable = PageRequest.of(pageNo-1, pageSize);
            StringBuilder countSelectSql = new StringBuilder();
            countSelectSql.append(" SELECT count(1) FROM ");
            countSelectSql.append(" syzy_ziyuanbeijian a LEFT JOIN syzy_beijiankucun b ON a.beiJianId=b.id ");
    
    
            StringBuilder selectSql = new StringBuilder();
            selectSql.append(" SELECT a.*,b.kuCunNum FROM syzy_ziyuanbeijian a LEFT JOIN syzy_beijiankucun b ON a.beiJianId=b.id ");
            Map<String, Object> params = new HashMap<>();
            StringBuilder whereSql = new StringBuilder();
            whereSql.append("  WHERE a.pid = :pid ");
    
            params.put("pid", pid);
    
            //====================拼装查询条件====================//
    //        String qcolumnName = ziYuanBeiJianDto.getQcolumnName();
    //        String qcolumnValue = ziYuanBeiJianDto.getQcolumnValue();
    //        if (StringUtils.isNoneBlank(qcolumnName, qcolumnValue)) {
    //            whereSql.append(" AND a." + qcolumnName + " like :" + qcolumnName + " ");
    //            params.put(qcolumnName, "%" + qcolumnValue + "%");
    //        }
            //增加你的查询条件
            if(StringUtils.isNoneBlank(ziYuanBeiJianDto.getBeiJianName())){
                whereSql.append(" AND a.beiJianName like :beiJianName");
                params.put("beiJianName", "%" + ziYuanBeiJianDto.getBeiJianName() + "%");
            }
            String orderSql = "  order by a.createTime asc ";
            String countSql = new StringBuilder().append(countSelectSql).append(whereSql).toString();
            Query countQuery = this.entityManager.createNativeQuery(countSql);
    
            for (String key : params.keySet()) {
                countQuery.setParameter(key, params.get(key));
            }
    
    
            BigInteger totalCount = (BigInteger) countQuery.getSingleResult();
            String querySql = new StringBuilder().append(selectSql).append(whereSql).append(orderSql).toString();
    
            Query query = entityManager.createNativeQuery(querySql);
            for (String key : params.keySet()) {
                query.setParameter(key, params.get(key));
            }
            query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(ZiYuanBeiJianDto.class));
    
            query.setFirstResult((int) pageable.getOffset());
            query.setMaxResults(pageable.getPageSize());
            List<ZiYuanBeiJianDto> resultList = query.getResultList();
            Page<ZiYuanBeiJianDto> page = new PageImpl<>(resultList, pageable, totalCount.longValue());
            return page;
        }
    
    
    
    
    
    
    
    }

剩下的就是controller调用service了。