在Spring Boot中使用Spring-data-jpa实现分页查询

程序员文章站 2023-11-25 17:43:28

在我们平时的工作中，查询列表在我们的系统中基本随处可见，那么我们如何使用jpa进行多条件查询以及查询列表分页呢？下面我将介绍两种多条件查询方式。 1、引入起步依赖&nbs...

在我们平时的工作中，查询列表在我们的系统中基本随处可见，那么我们如何使用jpa进行多条件查询以及查询列表分页呢？下面我将介绍两种多条件查询方式。

1、引入起步依赖

<dependency> 
 <groupid>org.springframework.boot</groupid> 
 <artifactid>spring-boot-starter-web</artifactid> 
</dependency> 
<dependency> 
 <groupid>org.springframework.boot</groupid> 
 <artifactid>spring-boot-starter-thymeleaf</artifactid> 
</dependency> 
<dependency> 
 <groupid>org.springframework.boot</groupid> 
 <artifactid>spring-boot-starter-data-jpa</artifactid> 
</dependency>

2、对thymeleaf和jpa进行配置

打开application.yml，添加以下参数，以下配置在之前的文章中介绍过，此处不做过多说明

spring: 
 thymeleaf: 
 cache: true 
 check-template-location: true 
 content-type: text/html 
 enabled: true 
 encoding: utf-8 
 mode: html5 
 prefix: classpath:/templates/ 
 suffix: .html 
 excluded-view-names: 
 template-resolver-order: 
 datasource: 
  driver-class-name: com.mysql.jdbc.driver 
  url: jdbc:mysql://localhost:3306/restful?useunicode=true&characterencoding=utf-8&usessl=false 
  username: root 
  password: root 
  initialize: true 
 init-db: true 
 jpa: 
  database: mysql 
  show-sql: true 
  hibernate: 
  ddl-auto: update 
  naming: 
   strategy: org.hibernate.cfg.improvednamingstrategy

3、编写实体bean

@entity 
@table(name="book") 
public class book { 
 @id 
 @generatedvalue(strategy = generationtype.identity) 
 @column(name = "id", updatable = false) 
 private long id; 
 @column(nullable = false,name = "name") 
 private string name; 
 @column(nullable = false,name = "isbn") 
 private string isbn; 
 @column(nullable = false,name = "author") 
 private string author; 
 public book (string name,string isbn,string author){ 
  this.name = name; 
  this.isbn = isbn; 
  this.author = author; 
 } 
 public book(){ 
 } 
 //此处省去get、set方法 
} 
public class bookquery { 
 private string name; 
 private string isbn; 
 private string author; 
 //此处省去get、set方法 
}

4、编写repository接口

@repository("bookrepository") 
public interface bookrepository extends jparepository<book,long> 
  ,jpaspecificationexecutor<book> { 
}

此处继承了两个接口，后续会介绍为何会继承这两个接口

5、抽象service层

首先抽象出接口

public interface bookqueryservice { 
 page<book> findbooknocriteria(integer page,integer size); 
 page<book> findbookcriteria(integer page,integer size,bookquery bookquery); 
}

实现接口

@service(value="https://my.oschina.net/wangxincj/blog/bookqueryservice") 
public class bookqueryserviceimpl implements bookqueryservice { 
 @resource 
 bookrepository bookrepository; 
 @override 
 public page<book> findbooknocriteria(integer page,integer size) { 
  pageable pageable = new pagerequest(page, size, sort.direction.asc, "id"); 
  return bookrepository.findall(pageable); 
 } 
 @override 
 public page<book> findbookcriteria(integer page, integer size, final bookquery bookquery) { 
  pageable pageable = new pagerequest(page, size, sort.direction.asc, "id"); 
  page<book> bookpage = bookrepository.findall(new specification<book>(){ 
   @override 
   public predicate topredicate(root<book> root, criteriaquery<?> query, criteriabuilder criteriabuilder) { 
    list<predicate> list = new arraylist<predicate>(); 
    if(null!=bookquery.getname()&&!"".equals(bookquery.getname())){ 
     list.add(criteriabuilder.equal(root.get("name").as(string.class), bookquery.getname())); 
    } 
    if(null!=bookquery.getisbn()&&!"".equals(bookquery.getisbn())){ 
     list.add(criteriabuilder.equal(root.get("isbn").as(string.class), bookquery.getisbn())); 
    } 
    if(null!=bookquery.getauthor()&&!"".equals(bookquery.getauthor())){ 
     list.add(criteriabuilder.equal(root.get("author").as(string.class), bookquery.getauthor())); 
    } 
    predicate[] p = new predicate[list.size()]; 
    return criteriabuilder.and(list.toarray(p)); 
   } 
  },pageable); 
  return bookpage; 
 } 
}

此处我定义了两个接口，findbooknocriteria是不带查询条件的，findbookcriteria是带查询条件的。在此处介绍一下上面提到的自定义repository继承的两个接口，如果你的查询列表是没有查询条件，只是列表展示和分页，只需继承jparepository接口即可，但是如果你的查询列表是带有多个查询条件的话则需要继承jpaspecificationexecutor接口，这个接口里面定义的多条件查询的方法。当然不管继承哪个接口，当你做分页查询时，都是需要调用findall方法的，这个方法是jap定义好的分页查询方法。

findbookcriteria方法也可以使用以下方法实现，大家可以自行选择

@override 
 public page<book> findbookcriteria(integer page, integer size, final bookquery bookquery) { 
  pageable pageable = new pagerequest(page, size, sort.direction.asc, "id"); 
  page<book> bookpage = bookrepository.findall(new specification<book>(){ 
   @override 
   public predicate topredicate(root<book> root, criteriaquery<?> query, criteriabuilder criteriabuilder) { 
    predicate p1 = criteriabuilder.equal(root.get("name").as(string.class), bookquery.getname()); 
    predicate p2 = criteriabuilder.equal(root.get("isbn").as(string.class), bookquery.getisbn()); 
    predicate p3 = criteriabuilder.equal(root.get("author").as(string.class), bookquery.getauthor()); 
    query.where(criteriabuilder.and(p1,p2,p3)); 
    return query.getrestriction(); 
   } 
  },pageable); 
  return bookpage; 
 }

6、编写controller

针对有查询条件和无查询条件，我们分别编写一个controller，默认每页显示5条，如下

@controller 
@requestmapping(value = "https://my.oschina.net/querybook") 
public class bookcontroller { 
 @autowired 
 bookqueryservice bookqueryservice; 
 @requestmapping("/findbooknoquery") 
 public string findbooknoquery(modelmap modelmap,@requestparam(value = "https://my.oschina.net/wangxincj/blog/page", defaultvalue = "https://my.oschina.net/wangxincj/blog/0") integer page, 
      @requestparam(value = "https://my.oschina.net/wangxincj/blog/size", defaultvalue = "https://my.oschina.net/wangxincj/blog/5") integer size){ 
  page<book> datas = bookqueryservice.findbooknocriteria(page, size); 
  modelmap.addattribute("datas", datas); 
  return "index1"; 
 } 
 @requestmapping(value = "https://my.oschina.net/findbookquery",method = {requestmethod.get,requestmethod.post}) 
 public string findbookquery(modelmap modelmap, @requestparam(value = "https://my.oschina.net/wangxincj/blog/page", defaultvalue = "https://my.oschina.net/wangxincj/blog/0") integer page, 
        @requestparam(value = "https://my.oschina.net/wangxincj/blog/size", defaultvalue = "https://my.oschina.net/wangxincj/blog/5") integer size, bookquery bookquery){ 
  page<book> datas = bookqueryservice.findbookcriteria(page, size,bookquery); 
  modelmap.addattribute("datas", datas); 
  return "index2"; 
 } 
}

7、编写页面

首先我们编写一个通用的分页页面，新建一个叫page.html的页面

<!doctype html> 
<html xmlns="http://www.w3.org/1999/xhtml" 
  xmlns:th="http://www.thymeleaf.org" 
  xmlns:layout="http://www.ultraq.net.nz/thymeleaf/layout" 
  layout:decorator="page"> 
<body> 
<div th:fragment="pager"> 
 <div class="text-right" th:with="baseurl=${#httpservletrequest.getrequesturl().tostring()},pars=${#httpservletrequest.getquerystring() eq null ? '' : new string(#httpservletrequest.getquerystring().getbytes('iso8859-1'), 'utf-8')}"> 
  <ul style="margin:0px;" class="pagination" th:with="newpar=${new java.lang.string(pars eq null ? '' : pars).replace('page='+(datas.number), '')}, 
            curtmpurl=${baseurl+'?'+newpar}, 
            cururl=${curtmpurl.endswith('&') ? curtmpurl.substring(0, curtmpurl.length()-1):curtmpurl}" > 
   <!--<li th:text="${pars}"></li>--> 
   <li><a href="https://my.oschina.net/wangxincj/blog/#" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" th:href="https://my.oschina.net/wangxincj/blog/@{${cururl}(page=0)}" rel="external nofollow" >首页</a></li> 
   <li th:if="${datas.hasprevious()}"><a href="https://my.oschina.net/wangxincj/blog/#" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" th:href="https://my.oschina.net/wangxincj/blog/@{${cururl}(page=${datas.number-1})}" rel="external nofollow" >上一页</a></li> 
   <!--总页数小于等于10--> 
   <div th:if="${(datas.totalpages le 10) and (datas.totalpages gt 0)}" th:remove="tag"> 
    <div th:each="pg : ${#numbers.sequence(0, datas.totalpages - 1)}" th:remove="tag"> 
      <span th:if="${pg eq datas.getnumber()}" th:remove="tag"> 
       <li class="active"><span class="current_page line_height" th:text="${pg+1}">${pagenumber}</span></li> 
      </span> 
     <span th:unless="${pg eq datas.getnumber()}" th:remove="tag"> 
       <li><a href="https://my.oschina.net/wangxincj/blog/#" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" th:href="https://my.oschina.net/wangxincj/blog/@{${cururl}(page=${pg})}" rel="external nofollow" th:text="${pg+1}"></a></li> 
      </span> 
    </div> 
   </div> 
   <!-- 总数数大于10时 --> 
   <div th:if="${datas.totalpages gt 10}" th:remove="tag"> 
    <li th:if="${datas.number-2 ge 0}"><a href="https://my.oschina.net/wangxincj/blog/#" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" th:href="https://my.oschina.net/wangxincj/blog/@{${cururl}(page=${datas.number}-2)}" rel="external nofollow" th:text="${datas.number-1}"></a></li> 
    <li th:if="${datas.number-1 ge 0}"><a href="https://my.oschina.net/wangxincj/blog/#" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" th:href="https://my.oschina.net/wangxincj/blog/@{${cururl}(page=${datas.number}-1)}" rel="external nofollow" th:text="${datas.number}"></a></li> 
    <li class="active"><span class="current_page line_height" th:text="${datas.number+1}"></span></li> 
    <li th:if="${datas.number+1 lt datas.totalpages}"><a href="https://my.oschina.net/wangxincj/blog/#" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" th:href="https://my.oschina.net/wangxincj/blog/@{${cururl}(page=${datas.number}+1)}" rel="external nofollow" th:text="${datas.number+2}"></a></li> 
    <li th:if="${datas.number+2 lt datas.totalpages}"><a href="https://my.oschina.net/wangxincj/blog/#" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" th:href="https://my.oschina.net/wangxincj/blog/@{${cururl}(page=${datas.number}+2)}" rel="external nofollow" th:text="${datas.number+3}"></a></li> 
   </div> 
   <li th:if="${datas.hasnext()}"><a href="https://my.oschina.net/wangxincj/blog/#" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" th:href="https://my.oschina.net/wangxincj/blog/@{${cururl}(page=${datas.number+1})}" rel="external nofollow" >下一页</a></li> 
   <!--<li><a href="https://my.oschina.net/wangxincj/blog/#" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" th:href="https://my.oschina.net/wangxincj/blog/@{${cururl}(page=${datas.totalpages-1})}" rel="external nofollow" >尾页</a></li>--> 
   <li><a href="https://my.oschina.net/wangxincj/blog/#" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" th:href="https://my.oschina.net/wangxincj/blog/${datas.totalpages le 0 ? cururl+'page=0':cururl+'&page='+(datas.totalpages-1)}" rel="external nofollow" >尾页</a></li> 
   <li><span th:utext="'共'+${datas.totalpages}+'页 / '+${datas.totalelements}+' 条'"></span></li> 
  </ul> 
 </div> 
</div> 
</body> 
</html>

针对无查询条件的接口，创建一个名为index1.html的页面并引入之前写好的分页页面，如下

<!doctype html> 
<html lang="en" xmlns:th="http://www.w3.org/1999/xhtml"> 
<head> 
 <meta charset="utf-8"/> 
 <title>title</title> 
 <script type="text/javascript" th:src="https://my.oschina.net/wangxincj/blog/@{/jquery-1.12.3.min.js}"></script> 
 <script type="text/javascript" th:src="https://my.oschina.net/wangxincj/blog/@{/bootstrap/js/bootstrap.min.js}"></script> 
 <link type="text/css" rel="stylesheet" th:href="https://my.oschina.net/wangxincj/blog/@{/bootstrap/css/bootstrap-theme.min.css}" rel="external nofollow" rel="external nofollow" /> 
 <link type="text/css" rel="stylesheet" th:href="https://my.oschina.net/wangxincj/blog/@{/bootstrap/css/bootstrap.css}" rel="external nofollow" rel="external nofollow" /> 
</head> 
<body> 
 <table class="table table-hover"> 
  <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>isbn</th> 
   <th>author</th> 
  </tr> 
  </thead> 
  <tbody> 
  <tr th:each="obj : ${datas}"> 
   <td th:text="${obj.id}">${obj.id}</td> 
   <td th:text="${obj.name}">${obj.name}</td> 
   <td th:text="${obj.isbn}">${obj.isbn}</td> 
   <td th:text="${obj.name}">${obj.author}</td> 
  </tr> 
  </tbody> 
 </table> 
  <div th:include="page :: pager" th:remove="tag"></div> 
</body> 
</html>

针对有查询条件的接口，创建一个名为index2.html的页面并引入之前写好的分页页面，如下

<!doctype html> 
<html lang="en" xmlns:th="http://www.w3.org/1999/xhtml"> 
<head> 
 <meta charset="utf-8"/> 
 <title>title</title> 
 <script type="text/javascript" th:src="https://my.oschina.net/wangxincj/blog/@{/jquery-1.12.3.min.js}"></script> 
 <script type="text/javascript" th:src="https://my.oschina.net/wangxincj/blog/@{/bootstrap/js/bootstrap.min.js}"></script> 
 <link type="text/css" rel="stylesheet" th:href="https://my.oschina.net/wangxincj/blog/@{/bootstrap/css/bootstrap-theme.min.css}" rel="external nofollow" rel="external nofollow" /> 
 <link type="text/css" rel="stylesheet" th:href="https://my.oschina.net/wangxincj/blog/@{/bootstrap/css/bootstrap.css}" rel="external nofollow" rel="external nofollow" /> 
</head> 
<body> 
<form th:action="@{/querybook/findbookquery}" th:object="${bookquery}" th:method="get"> 
 <div class="form-group"> 
  <label class="col-sm-2 control-label" >name</label> 
  <div class="col-sm-4"> 
   <input type="text" class="form-control" id="name" placeholder="请输入名称" th:field="*{name}"/> 
  </div> 
  <label class="col-sm-2 control-label">isbn</label> 
  <div class="col-sm-4"> 
   <input type="text" class="form-control" id="isbn" placeholder="请输isbn" th:field="*{isbn}"/> 
  </div> 
 </div> 
 <div class="form-group"> 
  <label class="col-sm-2 control-label" >author</label> 
  <div class="col-sm-4"> 
   <input type="text" class="form-control" id="author" placeholder="请输author" th:field="*{author}"/> 
  </div> 
  <div class="col-sm-4"> 
   <button class="btn btn-default" type="submit" placeholder="查询">查询</button> 
  </div> 
 </div> 
</form> 
 <table class="table table-hover"> 
  <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>isbn</th> 
   <th>author</th> 
  </tr> 
  </thead> 
  <tbody> 
  <tr th:each="obj : ${datas}"> 
   <td th:text="${obj.id}">${obj.id}</td> 
   <td th:text="${obj.name}">${obj.name}</td> 
   <td th:text="${obj.isbn}">${obj.isbn}</td> 
   <td th:text="${obj.name}">${obj.author}</td> 
  </tr> 
  </tbody> 
 </table> 
  <div th:include="page :: pager" th:remove="tag"></div> 
</body> 
</html>

ok！代码都已经完成，我们将项目启动起来，看一下效果。大家可以往数据库中批量插入一些数据，访问

http://localhost:8080/querybook/findbooknoquery，显示如下页面

在Spring Boot中使用Spring-data-jpa实现分页查询