jpa分页
程序员文章站
2022-05-18 20:50:22
法一(本地sql查询,注意表名啥的都用数据库中的名称,适用于特定数据库的查询) 法二(jpa已经实现的分页接口,适用于简单的分页查询) 法三(Query注解,hql语局,适用于查询指定条件的数据) 可以自定义整个实体(Page),也可以查询某几个字段(Page
法一(本地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()); } }
}
上一篇: 如何使用伪类选择器
下一篇: 浅谈Vue下的components模板