记jap hibernate entitymanage nativesql 分页查询

package com.css.bpm.platform.sys.resource.repository.impl;

import com.css.bpm.platform.constants.YNEnum;
import com.css.bpm.platform.sys.resource.repository.SysResourceNativeRepository;
import com.css.bpm.platform.sys.resource.repository.entity.SysResourceEntity;
import com.css.bpm.platform.sys.resource.viewobjects.SysResourceParam;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.AliasToBeanResultTransformer;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;
import org.springframework.util.ObjectUtils;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.math.BigInteger;
import java.util.List;

@Repository
public class SysResourceNativeRepositoryImpl implements SysResourceNativeRepository {

    @PersistenceContext
    EntityManager em;

    @Override
    public Page<SysResourceEntity> findResourceListBySysResourceParam(SysResourceParam sysResourceParam){

        StringBuffer sql = new StringBuffer(
                "select a.res_id resId,a.res_url resUrl,a.res_name resName,a.res_dir_id resDirId,a.sort sort,a.description description,a.create_user as createUser,a.create_time createTime,a.update_user updateUser,a.update_time updateTime " +
                        "from sys_resource a left join sys_resource_auth b on a.res_id = b.res_id ");
        StringBuffer whereSql = new StringBuffer(" where 1=1 and a.is_valid = '"+YNEnum.Y.toString()+"' and b.is_valid = '"+YNEnum.Y.toString()+"'" );

            if(!ObjectUtils.isEmpty(sysResourceParam.getResName())){
                whereSql.append(" and res_name like :resName ");
            }
            if(!ObjectUtils.isEmpty(sysResourceParam.getResUrl())){
                whereSql.append(" and res_url like :resUrl ");
            }
            if(!ObjectUtils.isEmpty(sysResourceParam.getRoleId())){
                whereSql.append(" and b.role_id = :roleId ");
            }

        StringBuffer orderSql = new StringBuffer(" order by a.sort ");
        Query query = em.createNativeQuery(sql.append(whereSql).append(orderSql).toString());

        StringBuilder countSql = new StringBuilder(
                "select count(a.res_id) " +
                        "from sys_resource a left join sys_resource_auth b on a.res_id = b.res_id and a.is_valid = '"+ YNEnum.Y.toString()+ "' and b.is_valid = '"+YNEnum.Y.toString()+"'" );
        Query countQuery = em.createNativeQuery(countSql.append(whereSql).toString());


            if(!ObjectUtils.isEmpty(sysResourceParam.getResName())){
                query.setParameter("resName","%"+sysResourceParam.getResName()+"%");
                countQuery.setParameter("resName","%"+sysResourceParam.getResName()+"%");
            }
            if(!ObjectUtils.isEmpty(sysResourceParam.getResUrl())){
                query.setParameter("resUrl","%"+sysResourceParam.getResUrl()+"%");
                countQuery.setParameter("resUrl","%"+sysResourceParam.getResUrl()+"%");
            }

            if(!ObjectUtils.isEmpty(sysResourceParam.getRoleId())){
                query.setParameter("roleId",sysResourceParam.getRoleId());
                countQuery.setParameter("roleId",sysResourceParam.getRoleId());
            }

        query.unwrap(NativeQueryImpl.class).setResultTransformer(new AliasToBeanResultTransformer(SysResourceEntity.class));

        Pageable pageable = PageRequest.of(sysResourceParam.getCurPage() - 1 ,sysResourceParam.getPageSize());
        query.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
        query.setMaxResults(pageable.getPageSize());
        List<SysResourceEntity> list = query.getResultList();
        BigInteger count = null;
        if(list.size()>0){
            count =  (BigInteger) countQuery.getSingleResult();
        }else{
            count = new BigInteger("0");
        }
        Page<SysResourceEntity> sysResourcePage = new PageImpl<SysResourceEntity>(list, pageable,count.longValue());
        return sysResourcePage;
    }
}