MyBatisPlus——多表查询——多条件查询——分页查询

1. MyBatis多表查询

实现复杂关系映射，可以使用@Results注解，@Result注解，@One注解，@Many注解组合完成复杂关系的配置。
这四个注解由MyBatis提供，MyBatis-Plus只是对单表的操作进行了增强，对于多表的查询并没有做任何的修改。

下边我们就一起实现以下

• 查询用户返回用户信息和订单信息
• 查询订单返回订单信息和用户信息

1.1 引入依赖

<?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.guo</groupId>
    <artifactId>springboot-Mybatis-plus</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <version>2.7.0</version>
        <artifactId>spring-boot-starter-parent</artifactId>
        <relativePath/>
    </parent>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <!--SpringBoot框架web项目起步依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--mybatisPlus集成SpringBoot起步依赖-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.1</version>
        </dependency>
        <!--MySQL 驱动依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!--druid 数据连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.20</version>
        </dependency>
    </dependencies>
    <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>
        </plugins>
    </build>
</project>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

1.2 核心配置文件

#连接数据库
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/study?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
spring.datasource.username=root
spring.datasource.password=123456
#数据源配置 ———— druid
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
# 配置日志输出格式——————打印SQL日志
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
1
2
3
4
5
6
7
8
9

1.3 启动上加注解@MapperScan

@MapperScan("com.guo.springboot.mapper")
1

1.4 创建实体User和Order

User.java

@TableName("t_user")
public class User {
    @TableId(type = IdType.AUTO)
    private int id;
    private String name;
    private String password;
    private String birthday;
    /**
     * 使用MyBatisPlus的接口实现BaseMapper类的时候必须要写这个注解 @TableField(exist = false)
     * (告诉它这个属性在数据库中是不存在的，我们需要去做映射)
     * */
    @TableField(exist = false)
    private List<Order> orders;
		//此处省略Get和Set方法
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

Order.java

@TableName("t_order")
public class Order {
    @TableId(type = IdType.AUTO)
    private long id;
    private Time orderTime;
    private String total;
    /**
     * 使用MyBatisPlus的接口实现BaseMapper类的时候必须要写这个注解 @TableField(exist = false)
     * (告诉它这个属性在数据库中是不存在的，我们需要去做映射)
     * */
    @TableField(exist = false)
    private User user;
    //此处省略Get和Set方法
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

1.5 创建UserMapper和OrderMapper接口@One——@Many

Mapper之间的调用，需要找到表与表之间的映射关系，
一对一@One
一对多@Many

UserMapper.java

package com.guo.springboot.mapper;
import com.guo.springboot.entity.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
@Mapper
public interface UserMapper extends BaseMapper<User>{
    //根据用户id查看用户信息
    @Select("select * from t_user where id=#{id}")
    User selectById(int id);
    //查询用户机器所有的订单
    //Mapper之间的调用需要借助@Many()
    //@Many  表示一对多
    @Select("select * from t_user")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(column = "name",property = "name"),
            @Result(column = "password",property = "password"),
            @Result(column = "id",property = "orders",javaType = List.class,
                    many = @Many(select = "com.guo.springboot.mapper.OrderMapper.selectByUid")
            )
    })
    List<User> selectAllUserAndOrder();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

OrderMapper.java

package com.guo.springboot.mapper;
import com.guo.springboot.entity.Order;
import com.guo.springboot.entity.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
@Mapper
public interface OrderMapper {
    //根据用户id查询订单
    @Select("select * from t_order where uid = #{uid}")
    Order selectByUid(int uid);
    //查询所有的订单，同时查询订单的用户
    //@One 表示一对一的关系
    @Select("select * from t_order")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(column = "ordertime",property = "ordertime"),
            @Result(column = "total",property = "total"),
            @Result(column = "uid",property = "user",javaType = User.class,
                    one = @One(select = "com.guo.springboot.mapper.UserMapper.selectById")
            ),
    })
    List<Order> selectAllOrderAndUser();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

1.6 创建Controller

UserController.java

@RestController
public class UserController {
    @Autowired
    private UserMapper userMapper;
    @Autowired
    private OrderMapper orderMapper;
    @GetMapping("/userandorder")
    public List userAndOrder(){
        List<User> users = userMapper.selectAllUserAndOrder();
        return users;
    }
    @GetMapping("userById")
    public User userById(int id){
        User user = userMapper.selectById(id);
        return user;
    }
    @GetMapping("/orderanduser")
    public List orderAndUser(){
        return orderMapper.selectAllOrderAndUser();
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

1.7 运行访问URL结果截图

1.7.1 查询用户返回用户信息和订单信息

1.7.2 查询订单返回订单信息和用户信息

2. MyBatisPlus多条件查询——QueryWrapper

官网地址：https://baomidou.com/pages/10c804/#abstractwrapper

2.1 控制层调用MyBatisPlus中BaseMapper多条件查询

UserController.java

//多条件查询
@GetMapping("/user/find")
public List findByCond(){
    QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
    userQueryWrapper.eq("name","zhangsan");  //查找用户表中所有名字等于zhangsan的信息
    return userMapper.selectList(userQueryWrapper);
}
1
2
3
4
5
6
7

2.2 运行访问URL结果截图

2.2.1 浏览器访问截图

2.2.2 访问SQL日志打印

3. MyBatisPlus分页查询

引入依赖(配置类中使用的Interceptor拦截器需要jar包)

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-extension</artifactId>
    <version>3.5.1</version>
</dependency>
1
2
3
4
5

3.1 创建配置类MyBatisPlusConfig

MyBatisPlusConfig.java

package com.guo.springboot.config;

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 MyBatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor paginationInterceptor(){
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();  //创建一个MybatisPlus拦截器
        PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor(DbType.MYSQL);//分页的拦截器
        interceptor.addInnerInterceptor(paginationInnerInterceptor);
        return interceptor;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

3.2 创建控制层UserController

UserController.java

//分页查询
@GetMapping("/user/findByPage")
public IPage findByPage(){
    //设置起始页和每页的条数
    Page<User> userPage = new Page<>(0,2);
    IPage iPage = userMapper.selectPage(userPage, null);
    return iPage;
}
1
2
3
4
5
6
7
8

3.2 运行访问URL结果截图

3.3 访问SQL日志打印