欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

jpa分页

程序员文章站 2022-05-18 20:50:22
法一(本地sql查询,注意表名啥的都用数据库中的名称,适用于特定数据库的查询) 法二(jpa已经实现的分页接口,适用于简单的分页查询) 法三(Query注解,hql语局,适用于查询指定条件的数据) 可以自定义整个实体(Page),也可以查询某几个字段(Page),和原 ......

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

jpa分页
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分页

 

法二(jpa已经实现的分页接口,适用于简单的分页查询)

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));
jpa分页
jpa分页
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);
jpa分页

 

jpa分页
//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);
jpa分页

 

 

法三(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);
} 
jpa分页
@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);
    }

}
jpa分页

 

法五(使用entitymanager,适用于动态sql查询)

jpa分页
@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()); } }
}
jpa分页