在Spring Boot中使用Spring-data-jpa實(shí)現(xiàn)分頁查詢

更新時間：2017年07月21日 11:37:33 作者：zhengxiangwen

如何使用jpa進(jìn)行多條件查詢以及查詢列表分頁呢？下面我將介紹兩種多條件查詢方式。具體實(shí)例代碼大家參考下本文吧

在我們平時的工作中，查詢列表在我們的系統(tǒng)中基本隨處可見，那么我們?nèi)绾问褂胘pa進(jìn)行多條件查詢以及查詢列表分頁呢？下面我將介紹兩種多條件查詢方式。

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進(jìn)行配置

打開application.yml，添加以下參數(shù)，以下配置在之前的文章中介紹過，此處不做過多說明

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、編寫實(shí)體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> { 
}

此處繼承了兩個接口，后續(xù)會介紹為何會繼承這兩個接口

5、抽象service層

首先抽象出接口

public interface BookQueryService { 
 Page<Book> findBookNoCriteria(Integer page,Integer size); 
 Page<Book> findBookCriteria(Integer page,Integer size,BookQuery bookQuery); 
}

實(shí)現(xiàn)接口

@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接口，這個接口里面定義的多條件查詢的方法。當(dāng)然不管繼承哪個接口，當(dāng)你做分頁查詢時，都是需要調(diào)用findAll方法的，這個方法是jap定義好的分頁查詢方法。

findBookCriteria方法也可以使用以下方法實(shí)現(xiàn)，大家可以自行選擇

@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，默認(rèn)每頁顯示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  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: rel="external nofollow" >首頁</a></li> 
   <li th:if="${datas.hasPrevious()}"><a  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: rel="external nofollow" >上一頁</a></li> 
   <!--總頁數(shù)小于等于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  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: rel="external nofollow" th:text="${pg+1}"></a></li> 
      </span> 
    </div> 
   </div> 
   <!-- 總數(shù)數(shù)大于10時 --> 
   <div th:if="${datas.totalPages gt 10}" th:remove="tag"> 
    <li th:if="${datas.number-2 ge 0}"><a  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: rel="external nofollow" th:text="${datas.number-1}"></a></li> 
    <li th:if="${datas.number-1 ge 0}"><a  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: 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  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: rel="external nofollow" th:text="${datas.number+2}"></a></li> 
    <li th:if="${datas.number+2 lt datas.totalPages}"><a  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: rel="external nofollow" th:text="${datas.number+3}"></a></li> 
   </div> 
   <li th:if="${datas.hasNext()}"><a  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: rel="external nofollow" >下一頁</a></li> 
   <!--<li><a  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: rel="external nofollow" >尾頁</a></li>--> 
   <li><a  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>

針對無查詢條件的接口，創(chuàng)建一個名為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: rel="external nofollow" rel="external nofollow" /> 
 <link type="text/css" rel="stylesheet" th: 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>

針對有查詢條件的接口，創(chuàng)建一個名為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: rel="external nofollow" rel="external nofollow" /> 
 <link type="text/css" rel="stylesheet" th: 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！代碼都已經(jīng)完成，我們將項(xiàng)目啟動起來，看一下效果。大家可以往數(shù)據(jù)庫中批量插入一些數(shù)據(jù)，訪問

http://localhost:8080/queryBook/findBookNoQuery，顯示如下頁面