SpringBoot整合MyBatis四種常用的分頁(yè)方式(詳細(xì)總結(jié))
一、準(zhǔn)備工作
1. 創(chuàng)建表結(jié)構(gòu)
CREATE TABLE `order_info` ( `id` int NOT NULL AUTO_INCREMENT, `info` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `time` datetime NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
2. 導(dǎo)入表數(shù)據(jù)
INSERT INTO `order_info` VALUES (1, '購(gòu)買(mǎi)了手機(jī)', '2022-08-25 05:34:17'); INSERT INTO `order_info` VALUES (2, '購(gòu)買(mǎi)了電腦', '2022-08-25 07:30:39'); INSERT INTO `order_info` VALUES (3, '購(gòu)買(mǎi)了護(hù)手霜', '2022-08-17 22:35:07'); INSERT INTO `order_info` VALUES (4, '購(gòu)買(mǎi)了泡面', '2022-08-23 08:35:36'); INSERT INTO `order_info` VALUES (5, '購(gòu)買(mǎi)了紙巾', '2022-07-21 15:26:06'); INSERT INTO `order_info` VALUES (6, '購(gòu)買(mǎi)了自熱米飯', '2021-06-20 13:21:06'); INSERT INTO `order_info` VALUES (7, '購(gòu)買(mǎi)了移動(dòng)硬盤(pán)', '2022-06-11 11:22:03'); INSERT INTO `order_info` VALUES (8, '購(gòu)買(mǎi)了狗糧', '2022-05-10 11:21:02'); INSERT INTO `order_info` VALUES (9, '購(gòu)買(mǎi)了貓糧', '2022-04-10 09:11:02'); INSERT INTO `order_info` VALUES (10, '購(gòu)買(mǎi)了遙控器', '2022-08-22 22:35:07'); INSERT INTO `order_info` VALUES (11, '購(gòu)買(mǎi)了褲子', '2022-08-15 08:35:36'); INSERT INTO `order_info` VALUES (12, '購(gòu)買(mǎi)了鞋子', '2022-08-21 08:35:36'); INSERT INTO `order_info` VALUES (13, '購(gòu)買(mǎi)了水杯', '2022-08-26 19:39:19');
3. 導(dǎo)入pom.xml依賴(lài)
創(chuàng)建新的工程的步驟我這里就省略了,所以廢話(huà)不多說(shuō),直接進(jìn)入正題!
注:請(qǐng)不要重復(fù)導(dǎo)入MyBatis的依賴(lài),這里為了方便展示寫(xiě)到一起了,實(shí)際運(yùn)用的時(shí)候,請(qǐng)不要混合在一起導(dǎo)入,不然可能會(huì)導(dǎo)致jar包的沖突,PageHelper和MyBatis-plus的jar包可能會(huì)產(chǎn)生沖突的。
<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <!--Mybatis依賴(lài)--> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.2.2</version> </dependency> <!--PageHelper依賴(lài)--> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper-spring-boot-starter</artifactId> <version>1.4.3</version> </dependency> <!--Mybatis-Plus依賴(lài)--> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.5.2</version> </dependency> </dependencies>
4. 配置application.yml文件
server: port: 8080 spring: datasource: username: 你的mysql用戶(hù)名 password: 你的mysql密碼 url: jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC driver-class-name: com.mysql.cj.jdbc.Driver mybatis: mapper-locations: classpath:mapping/*.xml
5. 創(chuàng)建公用的實(shí)體類(lèi)
package com.ithuang.demo.bean; import java.util.Date; public class OrderInfo { private int id; private String info; private Date time; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getInfo() { return info; } public void setInfo(String info) { this.info = info; } public Date getTime() { return time; } public void setTime(Date time) { this.time = time; } @Override public String toString() { return "OrderInfo{" + "id=" + id + ", info='" + info + '\'' + ", time=" + time + '}'; } }
二、使用原生Limit關(guān)鍵字進(jìn)行分頁(yè)
1. 項(xiàng)目整體結(jié)構(gòu)
2. 創(chuàng)建controller層
package com.ithuang.demo.controller; import com.ithuang.demo.bean.OrderInfo; import com.ithuang.demo.service.OrderInfoService; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.RestController; import javax.annotation.Resource; import java.util.List; @RestController public class OrderInfoController { @Resource private OrderInfoService orderInfoService; @GetMapping("/getOrderInfoList") public List<OrderInfo> getOrderInfoList(@RequestParam(value = "pageNow",defaultValue = "1") int pageNow, @RequestParam(value = "pageSize",defaultValue = "3") int pageSize){ return orderInfoService.getOrderInfoList(pageNow,pageSize); } }
3. 創(chuàng)建service層
package com.ithuang.demo.service; import com.ithuang.demo.bean.OrderInfo; import com.ithuang.demo.mapper.OrderInfoMapper; import org.springframework.stereotype.Service; import javax.annotation.Resource; import java.util.List; @Service public class OrderInfoService { @Resource private OrderInfoMapper orderInfoMapper; public List<OrderInfo> getOrderInfoList(int pageNow, int pageSize) { if(pageNow == 1){ pageNow = 0; } return orderInfoMapper.getOrderInfoList(pageNow,pageSize); } }
4. 創(chuàng)建mapper層
package com.ithuang.demo.mapper; import com.ithuang.demo.bean.OrderInfo; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; import java.util.List; @Mapper public interface OrderInfoMapper { List<OrderInfo> getOrderInfoList(@Param("pageNow") int pageNow,@Param("pageSize") int pageSize); }
5. 創(chuàng)建xml文件
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.ithuang.demo.mapper.OrderInfoMapper"> <select id="getOrderInfoList" parameterType="int" resultType="com.ithuang.demo.bean.OrderInfo"> SELECT * FROM order_info limit #{pageNow},#{pageSize} </select> </mapper>
6. 使用postman進(jìn)行測(cè)試,測(cè)試結(jié)果如下
三、借助MyBatis提供的第三方PageHelper分頁(yè)插件1
在MyBatis中配置了分頁(yè)攔截器(PageInterceptor),就是在執(zhí)行相關(guān)的Sql之前會(huì)做一些攔截的操作,這里通過(guò)調(diào)用startPage的方法,其實(shí)就是在查詢(xún)getOrderInfoList之前會(huì)自動(dòng)加上limit;這里通過(guò)setLocalPage方法,將分頁(yè)信息保存在當(dāng)前之后線(xiàn)程當(dāng)中,查詢(xún)方法與之處于同一個(gè)線(xiàn)程,共享ThreadLocal當(dāng)中的數(shù)據(jù),最后將getOrderInfoList查詢(xún)好的數(shù)據(jù)結(jié)果放到PageInfo當(dāng)中即可。
擴(kuò)展:PageHelper.startPage(int PageNum,int PageSize):用來(lái)設(shè)置頁(yè)面的位置和展示的數(shù)據(jù)條目數(shù),我們?cè)O(shè)置每頁(yè)展示5條數(shù)據(jù)。PageInfo用來(lái)封裝頁(yè)面信息,返回給前臺(tái)界面。PageInfo中的一些我們需要用到的參數(shù)如下表:
PageInfo.list | 結(jié)果集 |
PageInfo.pageNum | 當(dāng)前頁(yè)碼 |
PageInfo.pageSize | 當(dāng)前頁(yè)面顯示的數(shù)據(jù)條目 |
PageInfo.pages | 總頁(yè)數(shù) |
PageInfo.total | 數(shù)據(jù)的總條目數(shù) |
PageInfo.prePage | 上一頁(yè) |
PageInfo.nextPage | 下一頁(yè) |
PageInfo.isFirstPage | 是否為第一頁(yè) |
PageInfo.isLastPage | 是否為最后一頁(yè) |
PageInfo.hasPreviousPage | 是否有上一頁(yè) |
PageHelper.hasNextPage | 是否有下一頁(yè) |
(1)引入pom.xml
<!-- 分頁(yè)插件 --> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper-spring-boot-starter</artifactId> <version>1.2.5</version> </dependency>
(2)打開(kāi)application.properties,添加如下幾行配置信息
#分頁(yè)插件 pagehelper.helper-dialect=mysql pagehelper.params=count=countSql pagehelper.reasonable=true pagehelper.support-methods-arguments=true
1. 項(xiàng)目整體結(jié)構(gòu)
2. 創(chuàng)建controller層
Mapper文件中, SQL不用增加 limit分頁(yè)指令,需要什么直接查就可以,Pagehelper 可以自動(dòng)實(shí)現(xiàn)分頁(yè)!
package com.ithuang.demo.controller; import com.ithuang.demo.bean.OrderInfo; import com.ithuang.demo.service.OrderInfoService; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.RestController; import javax.annotation.Resource; import java.util.List; @RestController public class OrderInfoController { @Resource private OrderInfoService orderInfoService; @GetMapping("/getOrderInfoList") public List<OrderInfo> getOrderInfoList(@RequestParam(value = "pageNow",defaultValue = "1") int pageNow, @RequestParam(value = "pageSize",defaultValue = "3") int pageSize){ return orderInfoService.getOrderInfoList(pageNow,pageSize); } }
2. 創(chuàng)建Service層
package com.ithuang.demo.service; import com.github.pagehelper.PageHelper; import com.github.pagehelper.PageInfo; import com.ithuang.demo.bean.OrderInfo; import com.ithuang.demo.mapper.OrderInfoMapper; import org.springframework.stereotype.Service; import javax.annotation.Resource; import java.util.List; @Service public class OrderInfoService { @Resource private OrderInfoMapper orderInfoMapper; public List<OrderInfo> getOrderInfoList(int pageNow, int pageSize) { PageHelper.startPage(pageNow,pageSize); List<OrderInfo> orderInfoList = orderInfoMapper.getOrderInfoList(); PageInfo<OrderInfo> userPageInfo = new PageInfo<>(orderInfoList); return userPageInfo.getList(); } }
3. 創(chuàng)建Mapper層
package com.ithuang.demo.mapper; import com.ithuang.demo.bean.OrderInfo; import org.apache.ibatis.annotations.Mapper; import java.util.List; @Mapper public interface OrderInfoMapper { List<OrderInfo> getOrderInfoList(); }
5. 編寫(xiě)xml文件
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.ithuang.demo.mapper.OrderInfoMapper"> <select id="getOrderInfoList" parameterType="int" resultType="com.ithuang.demo.bean.OrderInfo"> SELECT * FROM order_info </select> </mapper>
6. 使用postman進(jìn)行測(cè)試,測(cè)試結(jié)果如下
四、借助MyBatis提供的第三方PageHelper分頁(yè)插件2
1. 依賴(lài)pom.xml
<dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper</artifactId> <version>5.2.0</version> </dependency>
2. 配置application.properties
#分頁(yè)插件 pagehelper.helper-dialect=mysql pagehelper.params=count=countSql pagehelper.reasonable=true pagehelper.support-methods-arguments=true
3. 實(shí)現(xiàn)分頁(yè)功能
①首頁(yè)超鏈接
<a th:href="@{/get/page/1}" rel="external nofollow" >顯示分頁(yè)數(shù)據(jù)</a>
②controller方法
@RequestMapping("/get/page/{pageNo}") public String getPage( @PathVariable("pageNo") Integer pageNo, Model model) { // PageInfo 對(duì)象封裝了和分頁(yè)相關(guān)的所有信息 PageInfo<Emp> pageInfo = empService.getPageInfo(pageNo); // 將 PageInfo 對(duì)象存入模型 model.addAttribute("pageInfo", pageInfo); return "emp-page"; }
③service 方法
@Override public PageInfo<Emp> getPageInfo(Integer pageNo) { // 1、確定每頁(yè)顯示數(shù)據(jù)的條數(shù) int pageSize = 5; // 2、設(shè)定分頁(yè)數(shù)據(jù):開(kāi)啟分頁(yè)功能。開(kāi)啟后,后面執(zhí)行的 SELECT 語(yǔ)句會(huì)自動(dòng)被附加 LIMIT 子句, // 而且會(huì)自動(dòng)查詢(xún)總記錄數(shù) PageHelper.startPage(pageNo, pageSize); // 3、正常執(zhí)行查詢(xún) List<Emp> empList = empMapper.selectAll(); // 4、封裝為 PageInfo 對(duì)象返回 return new PageInfo<>(empList); }
④頁(yè)面展示
<tr> <td colspan="5"> <span th:each="targetNum : ${pageInfo.navigatepageNums}"> <!-- 不是當(dāng)前頁(yè)顯示為超鏈接 --> <a th:if="${targetNum != pageInfo.pageNum}" th:href="@{/employee/page/}+${targetNum}" rel="external nofollow" th:text="'['+${targetNum}+']'">目標(biāo)頁(yè)面的頁(yè)碼</a> <!-- 是當(dāng)前頁(yè)不需要顯示為超鏈接 --> <span th:if="${targetNum == pageInfo.pageNum}" th:text="'['+${targetNum}+']'">當(dāng)前頁(yè)頁(yè)碼</span> </span> </td> </tr> <tr> <td colspan="5"> <span th:if="${pageInfo.hasPreviousPage}"> <a th:href="@{/employee/page/1}" rel="external nofollow" >首頁(yè)</a> <a th:href="@{/employee/page/}+${pageInfo.prePage}" rel="external nofollow" >上一頁(yè)</a> </span> [[${pageInfo.pageNum}]]/[[${pageInfo.pages}]] [[${pageInfo.pageNum}+'/'+${pageInfo.pages}]] <span th:if="${pageInfo.hasNextPage}"> <a th:href="@{/employee/page/}+${pageInfo.nextPage}" rel="external nofollow" >下一頁(yè)</a> <a th:href="@{/employee/page/}+${pageInfo.pages}" rel="external nofollow" >末頁(yè)</a> </span> </td> </tr> <tr> <td colspan="5"> <!-- 綁定值改變事件響應(yīng)函數(shù) --> <input id="jumpToPageNumInput" type="text" name="jumpToPageNum" placeholder="請(qǐng)輸入您想直接跳轉(zhuǎn)的頁(yè)碼" /> </td> </tr>
五、借助MyBatis-Plus攔截器進(jìn)行分頁(yè)查詢(xún)
1. 項(xiàng)目整體結(jié)構(gòu)
2. 編寫(xiě)主配置類(lèi)
package com.ithuang.demo.config; import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor; import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; @Configuration public class MybatisPlusConfig { @Bean public MybatisPlusInterceptor mybatisPlusInterceptor(){ MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); interceptor.addInnerInterceptor(new PaginationInnerInterceptor()); return interceptor; } }
3. 創(chuàng)建controller層
package com.ithuang.demo.controller; import com.ithuang.demo.bean.OrderInfo; import com.ithuang.demo.service.OrderInfoService; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.RestController; import javax.annotation.Resource; import java.util.List; @RestController public class OrderInfoController { @Resource private OrderInfoService orderInfoService; @GetMapping("/getOrderInfoList") public List<OrderInfo> getOrderInfoList(@RequestParam(value = "pageNow",defaultValue = "1") int pageNow, @RequestParam(value = "pageSize",defaultValue = "3") int pageSize){ return orderInfoService.getOrderInfoList(pageNow,pageSize); } }
4. 創(chuàng)建Service層
package com.ithuang.demo.service; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.ithuang.demo.bean.OrderInfo; import com.ithuang.demo.mapper.OrderInfoMapper; import org.springframework.stereotype.Service; import javax.annotation.Resource; import java.util.List; @Service public class OrderInfoService { @Resource private OrderInfoMapper orderInfoMapper; public List<OrderInfo> getOrderInfoList(int pageNow, int pageSize) { Page<OrderInfo> page= new Page<>(pageNow,pageSize); IPage<OrderInfo> iPage = orderInfoMapper.selectPage(page,null); return iPage.getRecords(); } }
5. 創(chuàng)建Mapper層
package com.ithuang.demo.mapper; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.ithuang.demo.bean.OrderInfo; import org.apache.ibatis.annotations.Mapper; @Mapper public interface OrderInfoMapper extends BaseMapper<OrderInfo> { }
6. 使用postman進(jìn)行測(cè)試,測(cè)試結(jié)果如下
六、借助MyBabtis提供的RowBounds進(jìn)行分頁(yè)查詢(xún)
RowBounds它是在SQL執(zhí)行的結(jié)果進(jìn)行截取分頁(yè)的,所以不適合大量數(shù)據(jù)的截取和分頁(yè),它適合在查詢(xún)較少的結(jié)果集當(dāng)中使用。
1. 項(xiàng)目整體結(jié)構(gòu)
2. 創(chuàng)建controller層
package com.ithuang.demo.controller; import com.ithuang.demo.bean.OrderInfo; import com.ithuang.demo.service.OrderInfoService; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.RestController; import javax.annotation.Resource; import java.util.List; @RestController public class OrderInfoController { @Resource private OrderInfoService orderInfoService; @GetMapping("/getOrderInfoList") public List<OrderInfo> getOrderInfoList(@RequestParam(value = "pageNow",defaultValue = "1") int pageNow, @RequestParam(value = "pageSize",defaultValue = "3") int pageSize){ return orderInfoService.getOrderInfoList(pageNow,pageSize); } }
3. 創(chuàng)建Service層
package com.ithuang.demo.service; import com.ithuang.demo.bean.OrderInfo; import com.ithuang.demo.mapper.OrderInfoMapper; import org.apache.ibatis.session.RowBounds; import org.springframework.stereotype.Service; import javax.annotation.Resource; import java.util.List; @Service public class OrderInfoService { @Resource private OrderInfoMapper orderInfoMapper; public List<OrderInfo> getOrderInfoList(int pageNow, int pageSize) { RowBounds rowBounds = new RowBounds(pageNow,pageSize); return orderInfoMapper.getOrderInfoList(rowBounds); } }
4. 創(chuàng)建Mapper層
package com.ithuang.demo.mapper; import com.ithuang.demo.bean.OrderInfo; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.session.RowBounds; import java.util.List; @Mapper public interface OrderInfoMapper { List<OrderInfo> getOrderInfoList(RowBounds rowBounds); }
5. 創(chuàng)建xml文件
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.ithuang.demo.mapper.OrderInfoMapper"> <select id="getOrderInfoList" resultType="com.ithuang.demo.bean.OrderInfo"> SELECT * FROM order_info </select> </mapper>
6. 使用postman進(jìn)行測(cè)試,測(cè)試結(jié)果如下
以上就是SpringBoot整合MyBatis四種常用的分頁(yè)方式(詳細(xì)總結(jié))的詳細(xì)內(nèi)容,更多關(guān)于SpringBoot整合MyBatis的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章!
相關(guān)文章
Java實(shí)戰(zhàn)項(xiàng)目之斗地主和斗牛游戲的實(shí)現(xiàn)
讀萬(wàn)卷書(shū)不如行萬(wàn)里路,只學(xué)書(shū)上的理論是遠(yuǎn)遠(yuǎn)不夠的,只有在實(shí)戰(zhàn)中才能獲得能力的提升,本篇文章手把手帶你用Java實(shí)現(xiàn)一個(gè)斗地主和一個(gè)斗牛游戲,大家可以在過(guò)程中查缺補(bǔ)漏,提升水平2021-11-11Spring Boot實(shí)現(xiàn)Undertow服務(wù)器同時(shí)支持HTTP2、HTTPS的方法
這篇文章考慮如何讓Spring Boot應(yīng)用程序同時(shí)支持HTTP和HTTPS兩種協(xié)議。小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2018-12-12Java使用定時(shí)器編寫(xiě)一個(gè)簡(jiǎn)單的搶紅包小游戲
這篇文章主要為大家介紹了Java如何使用定時(shí)器編寫(xiě)一個(gè)簡(jiǎn)單的搶紅包小游戲,文中的示例代碼講解詳細(xì),感興趣的小伙伴可以嘗試一下2022-07-07SpringBoot 設(shè)置傳入?yún)?shù)非必要的操作
這篇文章主要介紹了SpringBoot 設(shè)置傳入?yún)?shù)非必要的操作,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-02-02java開(kāi)發(fā)分布式服務(wù)框架Dubbo原理機(jī)制詳解
這篇文章主要為大家介紹了java開(kāi)發(fā)分布式服務(wù)框架Dubbo的原理機(jī)制詳解,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步2021-11-11使用springboot結(jié)合vue實(shí)現(xiàn)sso單點(diǎn)登錄
這篇文章主要為大家詳細(xì)介紹了如何使用springboot+vue實(shí)現(xiàn)sso單點(diǎn)登錄,文中示例代碼介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2019-06-06