mybatisplus 联表分页
时间: 2023-07-01 15:28:23 浏览: 63
Mybatis-plus 支持联表查询和分页查询,可以通过以下步骤实现联表分页查询:
1. 定义需要查询的实体类,例如:
```java
public class UserOrderVO implements Serializable {
private static final long serialVersionUID = 1L;
private User user;
private Order order;
//Getter and Setter
}
```
2. 编写 SQL 语句,使用 LEFT JOIN 进行联表查询,并使用 LIMIT 实现分页,例如:
```sql
SELECT u.*, o.*
FROM user u
LEFT JOIN order o ON u.id = o.user_id
ORDER BY u.id DESC
LIMIT #{offset}, #{limit}
```
3. 编写 Mapper 接口,定义联表分页查询的方法,例如:
```java
public interface UserOrderMapper extends BaseMapper<User> {
List<UserOrderVO> selectUserOrderPage(Page<UserOrderVO> page);
}
```
4. 编写 Service 层方法,调用 Mapper 接口方法进行查询,例如:
```java
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserOrderMapper userOrderMapper;
@Override
public IPage<UserOrderVO> selectUserOrderPage(Page<UserOrderVO> page) {
return userOrderMapper.selectUserOrderPage(page);
}
}
```
5. 在 Controller 中调用 Service 层方法,例如:
```java
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
private UserService userService;
@GetMapping("/order")
public Result<IPage<UserOrderVO>> selectUserOrderPage(Page<UserOrderVO> page) {
return Result.success(userService.selectUserOrderPage(page));
}
}
```
注意:在配置 mybatis-plus 分页插件时,需要设置为使用自定义的分页类,如下所示:
```java
@Configuration
public class MybatisPlusConfig {
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
return interceptor;
}
@Bean
public MybatisPlusPropertiesCustomizer mybatisPlusPropertiesCustomizer() {
return properties -> properties.getGlobalConfig().setSqlParser(new MybatisPlusSqlParser());
}
@Bean
public PaginationInterceptor paginationInterceptor() {
PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
paginationInterceptor.setDialectType(DbType.MYSQL.getDb());
paginationInterceptor.setSqlParser(new MybatisPlusSqlParser());
paginationInterceptor.setOverflow(true);
paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize());
paginationInterceptor.setLocalPage(true); // 开启局部分页
paginationInterceptor.setLimit(1000); // 设置单页最大限制数,默认 500 条,-1 不受限制
paginationInterceptor.setMaxLimit(10000); // 设置最大限制数,超过最大限制数时,会抛出异常
return paginationInterceptor;
}
}
```