用一个非常简单的案例,来演示mp的多表分页查询过程。一张用户表里面的sys_code字段,存储的是部门表的code字段值,要根据部门id筛选出对应的用户数据。
一 初始化数据库
CREATE TABLE sys_user(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) UNIQUE,
hobby VARCHAR(20),
sys_code VARCHAR(20)
);
INSERT INTO sys_user VALUES
(NULL, '王一博', '搬砖1', 'bz'),
(NULL, '王二博', '搬砖2', 'bz'),
(NULL, '王三博', '代码1', 'dm'),
(NULL, '王四博', '代码2', 'dm'),
(NULL, '王五博', '搬砖3', 'bz'),
(NULL, '王六博', '搬砖4', 'bz'),
(NULL, '王七博', '搬砖5', 'bz'),
(NULL, '王八博', '搬砖6', 'bz'),
(NULL, '王九博', '搬砖7', 'bz'),
(NULL, '王十博', '搬砖8', 'bz'),
(NULL, '王十一', '搬砖9', 'bz'),
(NULL, '王十二', '搬砖10', 'bz'),
(NULL, '王十三', '搬砖11', 'bz'),
(NULL, '王十四', '搬砖12', 'bz');
CREATE TABLE sys_depart(
id INT,
NAME VARCHAR(20),
CODE VARCHAR(20)
);
INSERT INTO sys_depart VALUES
(10086, '财务部', 'bz'),
(20001, '研发部', 'dm')
二 搭建简单工程
2.1 pom文件
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.3</version>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.3</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
</dependency>
</dependencies>
2.2 yml文件
spring:
datasource:
url: jdbc:mysql://localhost:3306/mango
driver-class-name: com.mysql.cj.jdbc.Driver
username: root
password: root
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
2.3 配置MyBatisPlus分页插件
import com.baomidou.mybatisplus.annotation.DbType;
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 MPConfig {
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
return interceptor;
}
}
2.4 启动类
@SpringBootApplication
public class MangoApplication {
public static void main(String[] args) {
SpringApplication.run(MangoApplication.class, args);
}
}
三 业务代码
3.1 实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("sys_user")
public class User {
@TableId
private int id;
private String username;
private String hobby;
private String sysCode;
}
@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("sys_depart")
public class Depart {
@TableId
private int id;
private String name;
private String code;
}
3.2 分页代码
public interface UserService extends IService<User> {
IPage<User> listByDepartId(Page<User> page, int departId);
}
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
@Autowired
private UserMapper userMapper;
@Override
public IPage<User> listByDepartId(Page<User> page, int departId) {
return userMapper.listByDepartId(page, departId);
}
}
@Mapper
public interface UserMapper extends BaseMapper<User> {
@Select("SELECT u.* FROM sys_user u LEFT JOIN sys_depart d ON u.sys_code = d.code WHERE d.id = #{departId}")
IPage<User> listByDepartId(Page<User> page, int departId);
}
3.3 分页功能测试
@SpringBootTest
@RunWith(SpringRunner.class)
public class UserTest {
@Autowired
private UserService userService;
@Test
public void test1() {
Page<User> page = new Page<>(1, 10);
IPage<User> userIPage = userService.listByDepartId(page, 10086);
for (User user : userIPage.getRecords()) {
System.out.println("筛选出来的数据: " + user);
}
}
}
以上只是MyBatis-Plus多表分页查询功能的简单演示,大家可以试着执行一下代码,sql查询语句可以使用@Select注解,也可以使用mapper映射文件。MyBatis-Plus的分页功能,一定要配置分页插件,一定要配置分页插件,其他的就是大家熟悉的MyBatis的内容了,大家也可以尝试着一些逻辑复杂的功能,试着完成一下。