jpa，querydsl 原生sql 查询分页，动态参数

1.  entityManager  createNativeQuery 查询功能

   
public DTO_PageData exeSqlClass(String mysql, Class s, Map parameter,Integer pageindex,Integer pagesize){
        DTO_PageData pageData= DTO_PageData.builder().build();
        Query querycount = entityManager.createNativeQuery(String.format("select count(1) from ( %s)",mysql));
        Query query = entityManager.createNativeQuery(mysql,s);
        if(parameter!=null&¶meter.size()>0){
            parameter.forEach((k,v)->{
                query.setParameter(k,v);
                querycount.setParameter(k,v);}
            );
        }

        //总记录数
        Integer total=  querycount.getResultList().size();
        pageData.setPageCount(total);
        int start = (pageindex - 1) * pagesize;
        query.setMaxResults(pagesize);
        query.setFirstResult(start);
        //页数
        pageData.setPageIndex(pageindex);
        //页显示数
        pageData.setPageSize(pagesize);
        //页数据内容
        pageData.setList(query.getResultList());
        return pageData;
    }

2.oracle sql直接分页

 
    public Tuple exeSqlClassPage(String mysql, Class s, Map parameter,String order, Integer pageindex, Integer pagesize){
        String countsql = String.format("select count(1) from (%s)", mysql);
        List list = exeSql(countsql);
        Integer o = (Integer) list.get(0);
        String pagesql = StringUtil.formatIfArgs("select * from  (select  temp_table.*,row_number() over ( order by %s ) n" +
                "                        from (select * from (%s)) temp_table)  where rn between %s and %s ",order, mysql, (pageindex - 1)*pagesize + 1, pageindex*pagesize);
        Query query = entityManager.createNativeQuery(pagesql,s);
        if(parameter!=null&¶meter.size()>0) {
            parameter.forEach((k, v) -> {
                        query.setParameter(k, v);
                    }
            );
        }
        List resultList = query.getResultList();
        return new Tuple(o,resultList);
    }