6、SpringBoot-mybatis分页实现pagehelper
系列导航
6、SpringBoot-mybatis分页实现pagehelper
9、SpringBoot-mybatis-druid多源数据多源数据
10、SpringBoot-mybatis-plus-druid多源数据
11、SpringBoot-mybatis-plus-druid多源数据事务
12、SpringBoot-mybatis-plus-ehcache
14、SpringBoot-easyexcel导出excle
完结
SpringBoot连接数据库引入mybatis的分页插件
注：mybatisplus跟mybatis的分页插件一样
1、 数据准备（oracle数据库）
Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('1001', 'RabbitMQ'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('1002', 'ZeroMQ'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('1003', 'ActiveMQ'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('1004', 'RocketMQ'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('1005', 'Apollo'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('1', '后端开发'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('2', '前端开发'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('3', '前端框架'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('4', '后端框架'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('5', '数据库'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('6', 'NoSql'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('7', '对象存储'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('8', '大数据'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('9', '操作系统'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('10', '消息队列'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('100', 'Python'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('101', 'Java'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('102', 'PHP'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('103', 'C'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('104', 'C++'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('105', 'C#'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('106', 'PHP'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('107', 'go'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('108', 'Visual Basic'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('201', 'JavaScript'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('202', 'css'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('203', 'swift'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('204', 'html5'); Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('300', 'Vue'); commit;
2、 在上一个项目的基础上pom.xml增加mybatis分页的依赖
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.example</groupId> <artifactId>demo</artifactId> <version>0.0.1-SNAPSHOT</version> <name>demo</name> <description>Demo project for Spring Boot</description> <properties> <java.version>1.8</java.version> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> <spring-boot.version>2.1.17.RELEASE</spring-boot.version> </properties> <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>com.oracle</groupId> <artifactId>ojdbc6</artifactId> <version>11.2.0.3</version> </dependency> <!-- 集成druid --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.10</version> </dependency> <!-- 集成mybatis --> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.2</version> </dependency> <!-- 集成mybatis-pagehelper分页功能 --> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper-spring-boot-starter</artifactId> <version>1.2.12</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> <exclusions> <exclusion> <groupId>org.junit.vintage</groupId> <artifactId>junit-vintage-engine</artifactId> </exclusion> </exclusions> </dependency> </dependencies> <dependencyManagement> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-dependencies</artifactId> <version>${spring-boot.version}</version> <type>pom</type> <scope>import</scope> </dependency> </dependencies> </dependencyManagement> <build> <plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-compiler-plugin</artifactId> <version>3.8.1</version> <configuration> <source>1.8</source> <target>1.8</target> <encoding>UTF-8</encoding> </configuration> </plugin> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> <version>2.1.17.RELEASE</version> <configuration> <mainClass>com.example.demo.DemoApplication</mainClass> </configuration> <executions> <execution> <id>repackage</id> <goals> <goal>repackage</goal> </goals> </execution> </executions> </plugin> </plugins> </build> </project>
3、application.properties配置
增加pagehelper的配置（如下红色部分）
spring.application.name=demo
# 应用服务 WEB 访问端口
server.port=8080
# 数据库设置
spring.datasource.driverClassName=oracle.jdbc.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@192.168.0.100:1521:orcl
spring.datasource.username=zy
spring.datasource.password=1
# druid配置
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
# druid参数调优（可选）
# 初始化大小，最小，最大
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=20
# 配置获取连接等待超时的时间
spring.datasource.maxWait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.minEvictableIdleTimeMillis=300000
# 测试连接
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
# 打开PSCache，并且指定每个连接上PSCache的大小
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
# 配置监控统计拦截的filters
spring.datasource.filters=stat
# asyncInit是1.1.4中新增加的配置，如果有initialSize数量较多时，打开会加快应用启动时间
spring.datasource.asyncInit=true
## mybatis配置
# 参数类型的包别名设置
mybatis.typeAliasesPackage=com.example.demo.domain
# 指向映射xml文件目录
mybatis.mapperLocations=classpath:mapper/*.xml
#分頁配置pagehelper
pagehelper.helperDialect=oracle
pagehelper.reasonable=true
pagehelper.supportMethodsArguments=true
pagehelper.params=count=countSql
4、工程目录
5、代码部分
druid配置类
package com.example.demo.config; import java.sql.SQLException; import javax.sql.DataSource; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Value; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.Primary; import com.alibaba.druid.pool.DruidDataSource; @Configuration public class DruidConfig { private Logger logger = LoggerFactory.getLogger(DruidConfig.class); @Value("${spring.datasource.url}") private String dbUrl; @Value("${spring.datasource.username}") private String username; @Value("${spring.datasource.password}") private String password; @Value("${spring.datasource.driver-class-name}") private String driverClassName; @Value("${spring.datasource.initial-size}") private int initialSize; @Value("${spring.datasource.min-idle}") private int minIdle; @Value("${spring.datasource.max-active}") private int maxActive; @Value("${spring.datasource.max-wait}") private int maxWait; @Value("${spring.datasource.time-between-eviction-runs-millis}") private int timeBetweenEvictionRunsMillis; @Value("${spring.datasource.min-evictable-idle-time-millis}") private int minEvictableIdleTimeMillis; // @Value("${spring.datasource.validation-query}") // private String validationQuery; @Value("${spring.datasource.test-while-idle}") private boolean testWhileIdle; @Value("${spring.datasource.test-on-borrow}") private boolean testOnBorrow; @Value("${spring.datasource.test-on-return}") private boolean testOnReturn; @Value("${spring.datasource.pool-prepared-statements}") private boolean poolPreparedStatements; @Value("${spring.datasource.max-pool-prepared-statement-per-connection-size}") private int maxPoolPreparedStatementPerConnectionSize; @Value("${spring.datasource.filters}") private String filters; @Bean //声明其为Bean实例 @Primary //在同样的DataSource中，首先使用被标注的DataSource public DataSource dataSource(){ DruidDataSource datasource = new DruidDataSource(); datasource.setUrl(this.dbUrl); datasource.setUsername(username); datasource.setPassword(password); datasource.setDriverClassName(driverClassName); //configuration datasource.setInitialSize(initialSize); datasource.setMinIdle(minIdle); datasource.setMaxActive(maxActive); datasource.setMaxWait(maxWait); datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis); datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis); // datasource.setValidationQuery(validationQuery); datasource.setTestWhileIdle(testWhileIdle); datasource.setTestOnBorrow(testOnBorrow); datasource.setTestOnReturn(testOnReturn); datasource.setPoolPreparedStatements(poolPreparedStatements); datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize); try { datasource.setFilters(filters); } catch (SQLException e) { logger.error("druid configuration initialization filter", e); } // datasource.setConnectionProperties(connectionProperties); return datasource; } }
package com.example.demo.domain; public class Block { private String blockId; private String blockName; public String getBlockId() { return blockId; } public void setBlockId(String blockId) { this.blockId = blockId; } public String getBlockName() { return blockName; } public void setBlockName(String blockName) { this.blockName = blockName; } @Override public String toString() { return "XyDicBlockT{" + "blockId='" + blockId + '\'' + ", blockName='" + blockName + '\'' + '}'; } }
package com.example.demo.mapper; import com.example.demo.domain.Block; import org.apache.ibatis.annotations.Mapper; import java.util.List; public interface BlockMapper { // 对应xml映射文件元素的ID Block selectByPrimaryKey(String blockId); // 列出知识块，对应xml映射文件元素的ID List<Block> selectBlock(); }
<?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.example.demo.mapper.BlockMapper">
<resultMap id="BaseResultMap" type="com.example.demo.domain.Block">
<result property="blockId" column="block_Id"/>
<result property="blockName" column="block_Name"/>
</resultMap>
<sql id="Base_Column_List">
block_id,block_name
</sql>
<select id="selectByPrimaryKey" parameterType="String" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from XY_DIC_BLOCK_T
where block_Id = #{blockId,jdbcType=VARCHAR}
</select>
<select id="selectBlock" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from XY_DIC_BLOCK_T
</select>
</mapper>
package com.example.demo.service; import com.example.demo.domain.Block; import java.util.List; public interface BlockService { Block getUserById(String userId); List<Block> listBlock(int page, int pageSize); }
package com.example.demo.service.impl; import javax.annotation.Resource; import com.example.demo.domain.Block; import com.example.demo.mapper.BlockMapper; import com.example.demo.service.BlockService; import com.github.pagehelper.PageHelper; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.List; @Service public class BlockServiceImpl implements BlockService { // 注入mapper类 @Autowired private BlockMapper blockMapper; @Override public Block getUserById(String blockId) { return blockMapper.selectByPrimaryKey(blockId); } @Override public List<Block> listBlock(int page, int pageSize) { List<Block> result = null; try { // 调用pagehelper分页，采用starPage方式。starPage应放在Mapper查询函数之前 PageHelper.startPage(page, pageSize); //每页的大小为pageSize，查询第page页的结果 PageHelper.orderBy("block_id ASC "); //进行分页结果的排序 result = blockMapper.selectBlock(); } catch (Exception e) { e.printStackTrace(); } return result; } }
package com.example.demo; import org.mybatis.spring.annotation.MapperScan; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.context.annotation.ComponentScan; import org.springframework.stereotype.Component; @SpringBootApplication //指定要扫描的mybatis映射类的路径 @MapperScan("com.example.demo.mapper") public class DemoApplication { public static void main(String[] args) { SpringApplication.run(DemoApplication.class, args); } }
package com.example.demo.controller; import com.example.demo.domain.Block; import com.example.demo.service.BlockService; import com.github.pagehelper.PageInfo; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.*; import java.util.List; @RestController @RequestMapping("/hello") public class HelloController { @Autowired private BlockService blockService; @GetMapping("/list") @ResponseBody public PageInfo<Block> index( @RequestParam(value="page", required=false, defaultValue="1") int page, @RequestParam(value="page-size", required=false, defaultValue="5") int pageSize) { List<Block> result = blockService.listBlock(page, pageSize); // PageInfo包装结果，返回更多分页相关信息 PageInfo<Block> pi = new PageInfo<Block>(result); return pi; } }
- 注：@RequestParam 注解获取url中的?page=1&page-size=5参数，value="page"是url中的参数名，required指参数是否必须，如果是必须URL却没有这个参数会报错，defaultValue="1"缺省值
- PageInfo PageInfo包装结果，返回更多分页相关信息
7、启动项目访问项目
{
"total": 54,
"list": [
{
"blockId": "1",
"blockName": "后端开发"
},
{
"blockId": "100",
"blockName": "Python"
},
{
"blockId": "1000",
"blockName": "Kafka"
}
],
"pageNum": 1,
"pageSize": 3,
"size": 3,
"startRow": 1,
"endRow": 3,
"pages": 18,
"prePage": 0,
"nextPage": 2,
"isFirstPage": true,
"isLastPage": false,
"hasPreviousPage": false,
"hasNextPage": true,
"navigatePages": 8,
"navigatepageNums": [
1,
2,
3,
4,
5,
6,
7,
8
],
"navigateFirstPage": 1,
"navigateLastPage": 8
}
8、使用druid的监控功能
资源丰富的的网盘资源：网盘资源大全！ 推荐一个适合零基础学习SQL的网站：不用安装数据库，在线轻松学习SQL！
