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

如题：要实现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了。