jpa分页

法一（本地sql查询,注意表名啥的都用数据库中的名称，适用于特定数据库的查询）

public interface userrepository extends jparepository<user, long> {

  @query(value = "select * from users where lastname = ?1",
    countquery = "select count(*) from users where lastname = ?1",
    nativequery = true)
  page<user> findbylastname(string lastname, pageable pageable);
}

 

法二（jpa已经实现的分页接口，适用于简单的分页查询）

public interface pagingandsortingrepository<t, id extends serializable>
  extends crudrepository<t, id> {

  iterable<t> findall(sort sort);

  page<t> findall(pageable pageable);
}

accessing the second page of user by a page size of 20 you could simply do something like this:

pagingandsortingrepository<user, long> repository = // … get access to a bean
page<user> users = repository.findall(new pagerequest(1, 20));

user findfirstbyorderbylastnameasc();

user findtopbyorderbyagedesc();

page<user> queryfirst10bylastname(string lastname, pageable pageable);

slice<user> findtop3bylastname(string lastname, pageable pageable);

list<user> findfirst10bylastname(string lastname, sort sort);

list<user> findtop10bylastname(string lastname, pageable pageable);

 

//service
 sort sort = new sort(sort.direction.desc,"createtime"); //创建时间降序排序
 pageable pageable = new pagerequest(pagenumber,pagesize,sort);
 this.depositrecordrepository.findallbyuseridin(userids,pageable);

//repository
page<depositrecord> findallbyuseridin(list<long> userids,pageable pageable);

 

 

法三（query注解,hql语局，适用于查询指定条件的数据）

 @query(value = "select b.roomuid from roomboard b where b.userid=:userid and b.lastboard=true order by  b.createtime desc")
    page<string> findroomuidsbyuseridpageable(@param("userid") long userid, pageable pageable);

pageable pageable = new pagerequest(pagenumber,pagesize);
page<string> page = this.roomboardrepository.findroomuidsbyuseridpageable(userid,pageable);
list<string> roomuids = page.getcontent();

可以自定义整个实体(page<user>)，也可以查询某几个字段（page<object[]>），和原生sql几乎一样灵活。

 

法四（扩充findall，适用于动态sql查询）

public interface userrepository extends jparepository<user, long> {

    page<user> findall(specification<user> spec, pageable pageable);
} 

@service
public class userservice {

    @autowired
    private userrepository userrepository;

    public page<user> getuserspage(pageparam pageparam, string nickname) {
        //规格定义
        specification<user> specification = new specification<user>() {

            /**
             * 构造断言
             * @param root 实体对象引用
             * @param query 规则查询对象
             * @param cb 规则构建对象
             * @return 断言
             */
            @override
            public predicate topredicate(root<user> root, criteriaquery<?> query, criteriabuilder cb) {
                list<predicate> predicates = new arraylist<>(); //所有的断言
                if(stringutils.isnotblank(nickname)){ //添加断言
                    predicate likenickname = cb.like(root.get("nickname").as(string.class),nickname+"%");
                    predicates.add(likenickname);
                }
                return cb.and(predicates.toarray(new predicate[0]));
            }
        };
        //分页信息
        pageable pageable = new pagerequest(pageparam.getpage()-1,pageparam.getlimit()); //页码：前端从1开始，jpa从0开始，做个转换
        //查询
        return this.userrepository.findall(specification,pageable);
    }

}

 

法五（使用entitymanager，适用于动态sql查询）

@service
@transactional
public class incomeservice{

    /**
     * 实体管理对象
     */
    @persistencecontext
    entitymanager entitymanager;

    public page<incomedaily> findincomedailysbypage(pageparam pageparam, string cpid, string appid, date start, date end, string sp) {
        stringbuilder countselectsql = new stringbuilder();
        countselectsql.append("select count(*) from incomedaily po where 1=1 ");

        stringbuilder selectsql = new stringbuilder();
        selectsql.append("from incomedaily po where 1=1 ");

        map<string,object> params = new hashmap<>();
        stringbuilder wheresql = new stringbuilder();
        if(stringutils.isnotblank(cpid)){
            wheresql.append(" and cpid=:cpid ");
            params.put("cpid",cpid);
        }
        if(stringutils.isnotblank(appid)){
            wheresql.append(" and appid=:appid ");
            params.put("appid",appid);
        }
        if(stringutils.isnotblank(sp)){
            wheresql.append(" and sp=:sp ");
            params.put("sp",sp);
        }
        if (start == null)
        {
            start = dateutil.getstartofdate(new date());
        }
        wheresql.append(" and po.bizdate >= :starttime");
        params.put("starttime", start);

        if (end != null)
        {
            wheresql.append(" and po.bizdate <= :endtime");
            params.put("endtime", end);
        }

        string countsql = new stringbuilder().append(countselectsql).append(wheresql).tostring();
        query countquery = this.entitymanager.createquery(countsql,long.class);
        this.setparameters(countquery,params);
        long count = (long) countquery.getsingleresult();

        string querysql = new stringbuilder().append(selectsql).append(wheresql).tostring();
        query query = this.entitymanager.createquery(querysql,incomedaily.class);
        this.setparameters(query,params);
        if(pageparam != null){ //分页
            query.setfirstresult(pageparam.getstart());
            query.setmaxresults(pageparam.getlength());
        }

        list<incomedaily> incomedailylist = query.getresultlist();
      if(pageparam != null) { //分页
            pageable pageable = new pagerequest(pageparam.getpage(), pageparam.getlength());
            page<incomedaily> incomedailypage = new pageimpl<incomedaily>(incomedailylist, pageable, count);
            return incomedailypage;
        }else{ //不分页
            return new pageimpl<incomedaily>(incomedailylist);
        }
    }

    /**
     * 给hql参数设置值
     * @param query 查询
     * @param params 参数
     */
    private void setparameters(query query,map<string,object> params){
        for(map.entry<string,object> entry:params.entryset()){
            query.setparameter(entry.getkey(),entry.getvalue());
        }
    }
}