在我們平時的工作中，查詢列表在我們的系統中基本隨處可見，那么我們如何使用jpa進行多條件查詢以及查詢列表分頁呢？下面我將介紹兩種多條件查詢方式。
1、引入起步依賴
|
1
|
|
2
3
4
5
6
7
8
9
10
11
12
|
<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，添加以下參數，以下配置在之前的文章中介紹過，此處不做過多說明
|
1
|
|
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
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
|
1
|
|
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
@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接口
|
1
|
|
2
3
4
|
@Repository("bookRepository") public interface BookRepository extends JpaRepository<Book,Long> ,JpaSpecificationExecutor<Book> { } |
此處繼承了兩個接口，后續會介紹為何會繼承這兩個接口
5、抽象service層
首先抽象出接口
|
1
|
|
2
3
4
|
public interface BookQueryService { Page<Book> findBookNoCriteria(Integer page,Integer size); Page<Book> findBookCriteria(Integer page,Integer size,BookQuery bookQuery); } |
實現接口
|
1
|
|
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
@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方法也可以使用以下方法實現，大家可以自行選擇
|
1
|
|
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
@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條，如下
|
1
|
|
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
@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的頁面
|
1
|
|
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
|
<!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的頁面并引入之前寫好的分頁頁面，如下
|
1
|
|
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
<!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的頁面并引入之前寫好的分頁頁面，如下
|
1
|
|
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
<!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，顯示如下頁面
訪問http://localhost:8080/queryBook/findBookQuery，顯示頁面如下，可以輸入查詢條件進行帶條件的分頁查詢：
總結
以上所述是小編給大家介紹的在Spring Boot中使用Spring-data-jpa實現分頁查詢，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對服務器之家網站的支持！
原文鏈接：http://blog.csdn.net/zhengxiangwen/article/details/63815551
