MybatisPlus实现多表分页查询

目录

一、数据库设计

用户表user:

订单表orders:

二、项目目录结构

1、pom.xml

2、application.yml

3.实体类

 User类

Order类

三、多表分页联查

1、MyBatisPlusConfig类（设置分页配置）

2、mapper层

UserMapper

OrderMapper

3、XML文件

UserMapper.xml

4、service层

UserService

UserServiceImpl

5、controller层

UserController

6、Postman实现

---

本文使用分页配置以及MybatiusPlus自带的Page类完成。实现用户表和订单表分页联查，一个用户对应多个订单，最终分页结果实现一个用户有多个订单即算为一条数据。

一、数据库设计

用户表user:

id:唯一标识

name:用户名

address：下单地址

订单表orders:

id:唯一标识

number:下单数量

product_name:产品名称

order_time:下单时间

update_time:更新时间

user_id:用户id （关联两张表的字段）

二、项目目录结构

1、pom.xml

 <!--mysql驱动-->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <!-- <version>8.0.19</version>-->
    </dependency>
    <!-- lombok 简化set get toString -->
    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
    </dependency>
    <!-- mybatis-plus -->
    <dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>mybatis-plus-boot-starter</artifactId>
      <version>3.4.3.1</version>
    </dependency>
    <!--web环境-->
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

2、application.yml

#数据库配置
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/ssm5?useUnicode=true&useSSL=false&characterEncoding=utf8&serverTimezone=GMT%2B8
    username: root
    password: 123456

server:
#设置项目启动的端口号
  port: 8080
  servlet:
#设置项目的访问路径
    context-path: /springboot_mybatis

mybatis-plus:
  global-config:
    db-config:
      id-type: auto

3.实体类

 User类

@Data
public class User {
    @TableId(type= IdType.AUTO)
    private Long id;
    private String name;
    private String address;
    @TableLogic
    private Integer isDeleted;
    //一对多关系
    @TableField(exist = false)
    private List<Order> orderList;
}

Order类

@Data
@TableName(value = "orders")
public class Order {
    @TableId(type = IdType.AUTO)
    private Long id;
    private Integer number;
    private String productName;
    private Date orderTime;
    private Date updateTime;
    private Long userId;
}

三、多表分页联查

1、MyBatisPlusConfig类（设置分页配置）

@Configuration
@MapperScan("com.wedu.mapper")
public class MyBatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        //分页插件拦截器 参数为数据库类型 将拦截器添加到拦截器对象中
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
}

2、mapper层

UserMapper

@Repository
public interface UserMapper extends BaseMapper<User> {
    //mybatis-plus实现
    Page<User> selectUserByPage(@Param("page") Page<User> page);
}

OrderMapper

使用@Select注解，根据user_id来查询订单表的所有信息

@Repository
public interface OrderMapper  extends BaseMapper<Order> {
    @Select("select * from `orders` where user_id=#{userId}")
    List<Order> selectByUserId(@Param("userId") Long userId);
}

3、XML文件

UserMapper.xml

<?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.wedu.mapper.UserMapper">
    <resultMap id="userResultMap" type="com.wedu.pojo.User">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="address" property="address"/>
        <result column="is_deleted" property="isDeleted"/>
        <collection property="orderList"  column="id"  ofType="com.wedu.pojo.Order" select="com.wedu.mapper.OrderMapper.selectByUserId" >
        </collection>
</resultMap>
    <select id="selectUserByPage1" resultMap="userResultMap">
        select * from user
    </select>
</mapper>

• 先通过用户表来查出用户信息，再通过订单表的user_id查出订单信息 ,用select调用OrderMapper中的查询语句;
• column="id"为传递的查询条件的值，就是将值赋给#{userId},目的就是将查询到的订单信息分装到用户的orderList中

4、service层

UserService

public interface UserService extends IService<User> {
    Page<User> selectUserByPage(Page<User> page);
}

UserServiceImpl

@Service
public class UserServiceImpl  extends ServiceImpl<UserMapper, User> implements UserService{
    @Resource
    private UserMapper userMapper;
    public Page<User> selectUserByPage(Page<User> page){
        return userMapper.selectUserByPage(page);
    }
}

5、controller层

UserController

@RestController
public class UserController {
    @Resource
    private UserService userService;
    /**
     * mybatis-plus实现IPage
     * @param page
     * @return
     */
    @GetMapping("/getPage")
    public Page<User> getPage(Page<User> page) {
        return userService.selectUserByPage(page);
    }
}

6、Postman实现