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);}

@Overridepublic 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_timeFROM 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);}@Overridepublic 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);}
@Overridepublic 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
}