spring boot 自定义sql分页查询

1、自定义sql查询分页

@Override
    public <T> Page<T> pageSQL(@Nonnull String sql, @Nonnull Pageable pageable, @Nonnull Class<T> clazz) {
        Long totalCount = totalCount(sql);
        NativeQuery nativeQuery = (NativeQuery) entityManager.createNativeQuery(sql);
        this.addScalar(nativeQuery, clazz);
        Query query = nativeQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(clazz));
        query.setFirstResult(pageable.getPageNumber());
        query.setMaxResults(pageable.getPageSize());
        List<T> list = query.getResultList();
        return new PageImpl<>(list, pageable, totalCount);
    }

    /**
     * 添加 字段类型
     * @param nativeQuery sql查询语句
     * @param clazz 类
     */
    private void addScalar(NativeQuery nativeQuery, @Nonnull Class clazz) {
        for (Field field : clazz.getDeclaredFields()) {
            nativeQuery.addScalar(field.getName(), CLASS_TYPE_MAP.get(field.getType()));
        }
        if (clazz.getSuperclass() != null) {
            addScalar(nativeQuery, clazz.getSuperclass());
        }
    }

    //@Override
    public Long totalCount(@Nonnull String sql) {
        sql = "select count(1) from (" + sql + ") countAlias";
        NativeQuery query = (NativeQuery) entityManager.createNativeQuery(sql);
        BigInteger bigInteger = (BigInteger) query.uniqueResult();
        return bigInteger.longValue();
    }

 

转载于:https://www.cnblogs.com/shaolixin/p/10791796.html