java每日精进 5.20【MyBatis 联表&分页查询】

1. MyBatis XML 实现分页查询

1.1 实现方式

MyBatis XML 是一种传统的 MyBatis 使用方式，通过在 XML 文件中编写 SQL 语句，并结合 Mapper 接口和 Service 层实现分页查询。分页需要手动编写两条 SQL 语句：一条查询分页数据列表，另一条查询总记录数。分页参数（如页码和每页大小）通过 LIMIT 语句手动实现。

1.2 代码解析

以下是文档中提供的 MyBatis XML 分页查询代码的解析：

1.2.1 xml代码

// 获取所有用户（分页，支持用户名模糊查询）
    @GetMapping("/xmluserpage")
    public IPage<User> getUsersByPage(@RequestParam(defaultValue = "1") int page,
                                      @RequestParam(defaultValue = "10") int size,
                                      @RequestParam(required = false) String username) {
        return userService.getUserPage(page, size, username);
    }

@Override
    public IPage<User> getUserPage(int page, int size, String username) {
        Page<User> userPage = new Page<>(page, size);
        return userMapper.selectPageCustom(userPage, username);
    }

// 自定义分页查询（单表）
    IPage<User> selectPageCustom(Page<User> page, @Param("username") String username);

<?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.home.mapper.UserMapper">

    <!-- 自定义分页查询 -->
    <select id="selectPageCustom" resultType="com.home.pojo.User">
        SELECT id, username, password, email, create_time, update_time
        FROM user
        <where>
            <if test="username != null and username != ''">
                AND username LIKE CONCAT('%', #{username}, '%')
            </if>
        </where>
    </select>

</mapper>

2. MyBatis XML 联表查询

2.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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>org.example</groupId>
  <artifactId>CRUDSystem</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>jar</packaging> <!-- 建议改为jar，Spring Boot默认打包为可执行jar -->
  <name>CRUDSystem</name>
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>17</maven.compiler.source>
    <maven.compiler.target>17</maven.compiler.target>
    <spring-boot.version>2.7.18</spring-boot.version>
    <mybatis-plus.version>3.5.3.1</mybatis-plus.version>
    <mybatis-plus-join.version>1.4.10</mybatis-plus-join.version>
  </properties>

  <dependencies>
    <!-- MyBatis Plus -->
    <dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>mybatis-plus-boot-starter</artifactId>
      <version>${mybatis-plus.version}</version>
    </dependency>
    <!-- MyBatis Plus Join -->
    <dependency>
      <groupId>com.github.yulichang</groupId>
      <artifactId>mybatis-plus-join-boot-starter</artifactId>
      <version>${mybatis-plus-join.version}</version>
    </dependency>
    <dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>mybatis-plus-extension</artifactId>
      <version>${mybatis-plus.version}</version>
    </dependency>

    <!-- MySQL 驱动 -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.33</version>
    </dependency>

    <!-- Spring Boot 核心 -->
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter</artifactId>
      <version>${spring-boot.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
      <version>${spring-boot.version}</version>
    </dependency>

    <!-- Lombok -->
    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.28</version>
    </dependency>

    <!-- 测试依赖 -->
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <version>${spring-boot.version}</version>
      <scope>test</scope>
    </dependency>
  </dependencies>

  <build>
    <finalName>CRUDSystem</finalName>
    <plugins>
      <!-- Spring Boot 打包插件 -->
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
        <version>${spring-boot.version}</version>
        <configuration>
          <excludes>
            <exclude>
              <groupId>org.projectlombok</groupId>
              <artifactId>lombok</artifactId>
            </exclude>
          </excludes>
        </configuration>
      </plugin>

      <!-- 编译插件 -->
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>3.8.1</version>
        <configuration>
          <source>17</source>
          <target>17</target>
          <release>17</release>
        </configuration>
      </plugin>
    </plugins>
  </build>
</project>

@Data
@TableName("user")
public class User {
    @TableId(type = IdType.AUTO)
    private Long id;
    private String username;
    private String password;
    private String email;
    @TableField(fill = FieldFill.INSERT)
    private LocalDateTime createTime;
    @TableField(fill = FieldFill.INSERT_UPDATE)
    private LocalDateTime updateTime;
}
@Data
@TableName("`order`")
public class Order {
    @TableId(type = IdType.AUTO)
    private Long id;
    private Long userId;
    private Long productId;
    private Integer quantity;
    private BigDecimal totalPrice;
    private Integer status;
    @TableField(fill = FieldFill.INSERT)
    private LocalDateTime createTime;
    @TableField(fill = FieldFill.INSERT_UPDATE)
    private LocalDateTime updateTime;
}
//字段平铺
@Data
public class UserOrderDetailDO extends User {
    private Long orderId;
    private BigDecimal totalPrice;
    private Integer orderStatus;
}
//字段内嵌
@Data
public class UserOrderDetail2DO extends User {
    private Order order;
}

2.2字段平铺

// 字段平铺：获取用户订单详情（分页，订单状态和用户名过滤）
    @GetMapping("/orders")
    public IPage<UserOrderDetailDO> getUserOrders(@RequestParam(defaultValue = "1") int page,
                                                  @RequestParam(defaultValue = "10") int size,
                                                  @RequestParam Integer status,
                                                  @RequestParam(required = false) String username) {
        return userService.getUserOrderPage(page, size, status, username);
    }
    @Override
    public IPage<UserOrderDetailDO> getUserOrderPage(int page, int size, Integer status, String username) {
        Page<UserOrderDetailDO> userOrderPage = new Page<>(page, size);
        return userMapper.selectListByStatusAndUsername(userOrderPage, status, username);
    }
// 字段平铺：联表查询用户和订单（状态为已支付，用户名模糊匹配）
    default IPage<UserOrderDetailDO> selectListByStatusAndUsername(Page<UserOrderDetailDO> page,
                                                                   @Param("status") Integer status,
                                                                   @Param("username") String username) {
        return selectJoinPage(page, UserOrderDetailDO.class, new MPJLambdaWrapper<User>() // 改为 selectJoinPage
                .selectAll(User.class)
                .selectAs(Order::getId, UserOrderDetailDO::getOrderId)
                .selectAs(Order::getTotalPrice, UserOrderDetailDO::getTotalPrice)
                .selectAs(Order::getStatus, UserOrderDetailDO::getOrderStatus)
                .eq(Order::getStatus, status)
                .leftJoin(Order.class, Order::getUserId, User::getId)
                .like(username != null, User::getUsername, username));
    }

等价于以下SQL

SELECT 
    u.*,
    o.id AS orderId,
    o.total_price AS totalPrice,
    o.status AS orderStatus
FROM 
    user u
LEFT JOIN 
    `order` o ON u.id = o.user_id  -- 注意这里应该是 user_id 而不是 o.id
WHERE 
    o.status = 1 
    AND u.username LIKE '%username%'

查询结果：

{
    "records": [
        {
            "id": 1,
            "username": "user1",
            "password": "password123",
            "email": "user1@example.com",
            "createTime": "2025-05-20T10:15:33",
            "updateTime": "2025-05-20T10:15:33",
            "orderId": 1,
            "totalPrice": 999.99,
            "orderStatus": 1
        }
    ],
    "total": 2,
    "size": 1,
    "current": 1,
    "orders": [],
    "optimizeCountSql": true,
    "searchCount": true,
    "maxLimit": null,
    "countId": null,
    "pages": 2
}

2.3字段嵌入

// 字段内嵌：获取用户订单详情（分页，订单状态和用户名过滤）
    @GetMapping("/orders2")
    public IPage<UserOrderDetail2DO> getUserOrders2(@RequestParam(defaultValue = "1") int page,
                                                    @RequestParam(defaultValue = "10") int size,
                                                    @RequestParam Integer status,
                                                    @RequestParam(required = false) String username) {
        return userService.getUserOrderPage2(page, size, status, username);
    }
@Override
    public IPage<UserOrderDetail2DO> getUserOrderPage2(int page, int size, Integer status, String username) {
        Page<UserOrderDetail2DO> userOrderPage = new Page<>(page, size);
        return userMapper.selectList2ByStatusAndUsername(userOrderPage, status, username);
    }
// 字段内嵌：联表查询用户和订单（状态为已支付，用户名模糊匹配）
    default IPage<UserOrderDetail2DO> selectList2ByStatusAndUsername(Page<UserOrderDetail2DO> page,
                                                                     @Param("status") Integer status,
                                                                     @Param("username") String username) {
        return selectJoinPage(page, UserOrderDetail2DO.class, new MPJLambdaWrapper<User>() // 改为 selectJoinPage
                .selectAll(User.class)
                .selectAssociation(Order.class, UserOrderDetail2DO::getOrder)
                .eq(Order::getStatus, status)
                .leftJoin(Order.class, Order::getUserId, User::getId)
                .like(username != null, User::getUsername, username));
    }

等价SQL：

SELECT 
    u.*, 
    o.*
FROM 
    t_user u
LEFT JOIN 
    t_order o ON o.user_id = u.id
WHERE 
    o.status = #{status}
    AND (#{username} IS NULL OR u.username LIKE CONCAT('%', #{username}, '%'))
LIMIT #{page.size} OFFSET #{page.offset}

返回值为：

{
    "records": [
        {
            "id": 1,
            "username": "user1",
            "password": "password123",
            "email": "user1@example.com",
            "createTime": "2025-05-20T10:15:33",
            "updateTime": "2025-05-20T10:15:33",
            "order": {
                "id": 1,
                "userId": 1,
                "productId": 1,
                "quantity": 1,
                "totalPrice": 999.99,
                "status": 1,
                "createTime": "2025-05-20T10:16:00",
                "updateTime": "2025-05-20T10:16:00"
            }
        }
    ],
    "total": 2,
    "size": 1,
    "current": 1,
    "orders": [],
    "optimizeCountSql": true,
    "searchCount": true,
    "maxLimit": null,
    "countId": null,
    "pages": 2
}