文章目录

• 1. 描述
• • 1.1 MybatisPlusInterceptor
  • 1.2 InnerInterceptor
• 2. 实现
• • 2.1 不带条件的分页查询
  • 2.2 带条件的分页查询
  • 2.3 简述Page类
• 3. 注意事项
• • 3.1 UncategorizedSQLException异常
  • 3.2 不做记录总数的统计

1. 描述

1.1 MybatisPlusInterceptor

我们在开发的过程中，经常会遇到分页操作，其分为逻辑分页和物理分页，具体可参考我的博文：逻辑分页和物理分页

如果你用的是Mybatis-Plus框架，可用MybatisPlusInterceptor按如下配置分页代码：

/*** @author 念兮为美* @datetime 2022/11/28 14:10* @desc mybatis plus 配置类*/
@Configuration
public class MybatisPlusConfig {@Beanpublic MybatisPlusInterceptor mybatisPlusInterceptor() {MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();// 设置为使用 MYSQL 方言interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));return interceptor;}
}

如果你用的是低版本的spring boot，可用PaginationInterceptor按如下配置：

/*** @author 念兮为美* @datetime 2022/11/27 15:22* @desc mybatis plus 配置类*/
@Configuration
public class MybatisPlusConfig {@Beanpublic PaginationInterceptor paginationInterceptor() {return new PaginationInterceptor();}
}

MybatisPlusInterceptor插件是核心插件，目前代理了Executor#query、Executor#update、StatementHandler#prepare方法，其属性：

private List interceptors = new ArrayList<>();

1.2 InnerInterceptor

注意List泛型中的InnerInterceptor，mybatis-plus提供的插件都将基于此接口来实现功能，目前已有的功能如下：

1. 自动分页： PaginationInnerInterceptor

2. 多租户：TenantLineInnerInterceptor

3. 动态表名：DynamicTableNameInnerInterceptor

4. 乐观锁：OptimisticLockerInnerInterceptor

5. sql性能规范： IllegalSQLInnerInterceptor

6. 防止全表更新与删除: BlockAttackInnerInterceptor

【注意】使用多个功能需要注意顺序关系，建议使用如下顺序：

1. 多租户

2. 动态表名

3. 分页，乐观锁

4. sql性能规范,防止全表更新与删除

总结：对sql进行单次改造的优先放入，不对sql进行改造的最后放入。

2. 实现

2.1 不带条件的分页查询

1. 编写查询展示类

/*** @author 念兮为美* @datetime 2023/2/2 09:58* @desc 用户查询结果*/
@NoArgsConstructor
@Data
@ApiModel(description = "用户返回结果")
public class UserPageVo {@ApiModelProperty(name = "username", value = "用户名")private String username;@ApiModelProperty(name = "nickname", value = "昵称")private String nickname;@ApiModelProperty(name = "userType", value = "用户类型")private String userType;
}

2. 编写mapper类

@Mapper
public interface UserMapper extends BaseMapper {@Select("select " +"username,nickname,user_type as userType " +"from " +"user " +"order by " +"create_time desc")List findPageUsers(Page page);
}

3. 编写service类

@Service
public class UserService extends ServiceImpl {@Resource private UserMapper userMapper;public Page findPageUsers() {Long currentPage = 4L;Long pageSize = 3L;Page page = new Page<>(currentPage, pageSize);List pageUsers = userMapper.findPageUsers(page);page.setRecords(pageUsers);return page;}
}

4. 编写controller类

@Api(tags = "用户模块")
@RestController
@RequestMapping("/user")
@Slf4j
@Validated
public class UserController {@Autowired private UserService userService;@ApiOperationSupport(author = "念兮为美")@ApiOperation(value = "用户查询接口")@GetMapping("/findPageUsers")public Page findPageUsers() {return userService.findPageUsers();}
}

5. 测试运行结果

JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@2ce7d43] will not be managed by Spring
==>  Preparing: SELECT COUNT(*) AS total FROM user
==> Parameters: 
<==    Columns: total
<==        Row: 24
<==      Total: 1
==>  Preparing: select username,nickname,user_type as userType from user order by create_time desc LIMIT ?,?
==> Parameters: 9(Long), 3(Long)
<==    Columns: username, nickname, userType
<==        Row: cs, null, admin
<==        Row: lin, null, admin
<==        Row: test3, null, TEST4
<==      Total: 3

2.2 带条件的分页查询

public class UserService extends ServiceImpl {@Resource private UserMapper userMapper;public Page findPageUsers() {Long currentPage = 1L;Long pageSize = 3L;Page page = new Page<>(currentPage, pageSize);QueryWrapper queryWrapper = new QueryWrapper<>();queryWrapper.eq("username", "test5");Page userPage = userMapper.selectPage(page, queryWrapper);return userPage;}

运行结果：

JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@475454ae] will not be managed by Spring
==>  Preparing: SELECT COUNT(*) AS total FROM user WHERE deleted = false AND (username = ?)
==> Parameters: test2(String)
<==    Columns: total
<==        Row: 1
<==      Total: 1
==>  Preparing: SELECT username,nickname,user_type FROM user WHERE deleted=false AND (username = ?) LIMIT ?
==> Parameters: test2(String), 3(Long)
<==    Columns: username, nickname, user_type
<==        Row:  test2, null,TEST6
<==      Total: 1

2.3 简述Page类

简单分页模型, 有如下几个主要属性

/*** 查询数据列表*/
protected List records = Collections.emptyList();/*** 总数*/
protected long total = 0;/*** 每页显示条数，默认 10*/
protected long size = 10;/*** 当前页*/
protected long current = 1;

3. 注意事项

3.1 UncategorizedSQLException异常

在编写mapper.xml中的SQL语句时，或者使用@select注解编写SQL语句时，语句末尾不能使用 ; 结尾，原因是在做分页的时候会在编写的SQL语句后面拼接上limit语句, 导致出现SQL语法错误(UncategorizedSQLException)。

可以参考我的这篇博文：全网最详细的org.springframework.jdbc.UncategorizedSQLException的多种解决方法

3.2 不做记录总数的统计

new Page(queryParam.getCurrent(), queryParam.getSize())的两个参数表示：当前页和每页数量

如果业务需求只需要查询分页做上下页切换而不需要记录总数，可以设置第三个参数false就可以不查询count()，以提高性能，即new Page(queryParam.getCurrent(), queryParam.getSize(), false) ，如下代码所示：

/*** @author 念兮为美* @datetime 2022/11/28 14:20* @desc 用户服务实现类*/
@Service
public class UserService extends ServiceImpl {@Resource private UserMapper userMapper;public Page findPageUsers() {Long currentPage = 4L;Long pageSize = 3L;Page page = new Page<>(currentPage, pageSize, false);List pageUsers = userMapper.findPageUsers(page);page.setRecords(pageUsers);return page;}

从输出结果看，既没有count操作，如下所示：

JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@466c39de] will not be managed by Spring
==>  Preparing: select username,nickname,user_type as userType from user order by create_time desc LIMIT ?,?
==> Parameters: 9(Long), 3(Long)
<==    Columns: username, nickname, userType
<==        Row: cs, null, admin
<==        Row: lin, null, admin
<==        Row: test3, null, TEST4
<==      Total: 3