---
req_id: REQ-USR-004
date: 2026-05-15
spec_ref: docs/superpowers/specs/2026-05-15-REQ-USR-004.md
---

# REQ-USR-004 查询用户 Implementation Plan

> **Execution:** Parent skill `feature-tdd` executes this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.

**Goal:** 实现 `GET /api/v1/users` 分页 + 多字段筛选 + 排序的用户列表查询；服务端做白名单 + 类型转换 + 越界矫正；输出 PageResult<UserListItemVo>（JOIN sys_employee/sys_department 取员工名 / 部门名）。

**Architecture:**
- 复用 REQ-USR-002 / 003 已建的鉴权 + 角色守卫 + 异常处理。
- 新增 `UserListService` 单一职责；动态 SQL 通过 MyBatis XML（@Select script 也可，本任务用 XML 便于维护）实现 JOIN + WHERE 动态拼接。
- 白名单映射：queryField / sortField / matchMode / sortOrder 全部在 service 层校验后才进 SQL。
- 越界矫正在 service 层：先查目标 page，若 records 为空但 total>0 → 重算 lastPage 再查。
- PageResult 引入为通用类（放 common.response 包，供后续 REQ 复用）。

**Tech Stack:** 复用 Spring Boot 3 + MyBatis-Plus（本 REQ 用 mapper XML 写动态查询）+ Jakarta Validation。

---

## Schema 改动

无。

---

## 文件变更清单

**新增（通用）**：
- `backend/src/main/java/com/xly/erp/common/response/PageResult.java`

**新增（业务）**：
- `backend/src/main/java/com/xly/erp/module/usr/dto/UserQueryReq.java`
- `backend/src/main/java/com/xly/erp/module/usr/vo/UserListItemVo.java`
- `backend/src/main/java/com/xly/erp/module/usr/service/UserListService.java`
- `backend/src/main/java/com/xly/erp/module/usr/service/impl/UserListServiceImpl.java`
- `backend/src/main/resources/mapper/usr/SysUserMapper.xml`

**修改**：
- `backend/src/main/java/com/xly/erp/common/response/ErrorCode.java`（新增 INVALID_ENUM_PARAM=40003）
- `backend/src/main/java/com/xly/erp/module/usr/mapper/SysUserMapper.java`（新增 selectByQuery + countByQuery）
- `backend/src/main/java/com/xly/erp/module/usr/controller/UserController.java`（新增 GET / list 方法）

**测试**：
- `backend/src/test/java/com/xly/erp/module/usr/dto/UserQueryReqValidationTest.java`
- `backend/src/test/java/com/xly/erp/module/usr/service/UserListServiceImplTest.java`
- `backend/src/test/java/com/xly/erp/module/usr/controller/UserControllerListTest.java`

---

## 约束常量

**ErrorCode 新增**：

| 常量 | 值 | HTTP |
|---|---|---|
| `INVALID_ENUM_PARAM` | `40003` | 400 |

> ErrorCode.toHttpStatus(40003) → 400/100=400，已在现有映射，新增常量即可。

**白名单常量**（全部定义在 `UserListServiceImpl` 的 `static final Map`）：

```
SORT_FIELDS = {"tCreateDate", "tLastLoginDate", "sUsername", "sUserCode"}

QUERY_FIELD_TO_SQL = {
    "username":       "u.sUsername",
    "employeeName":   "e.sEmployeeName",
    "userCode":       "u.sUserCode",
    "departmentName": "d.sDepartmentName",
    "userType":       "u.sUserType",
    "isDeleted":      "u.iIsDeleted",
    "lastLoginDate":  "u.tLastLoginDate",
    "createdBy":      "u.sCreatedBy"
}

MATCH_MODES = {"contains", "notContains", "equals"}
SORT_ORDERS = {"asc", "desc"}

USER_TYPES = {"NORMAL", "SUPER_ADMIN"}

DEFAULT_PAGE = 1
DEFAULT_SIZE = 20
MAX_SIZE = 100
DEFAULT_SORT_FIELD = "tCreateDate"
DEFAULT_SORT_ORDER = "desc"
DEFAULT_MATCH_MODE = "contains"
```

**API 形状**：

```
GET /api/v1/users?page=1&size=20&sortField=tCreateDate&sortOrder=desc
                 &queryField=username&matchMode=contains&queryValue=ali
                 &userType=NORMAL&isDeleted=false
@RequireSuperAdmin
→ Result<PageResult<UserListItemVo>>

PageResult<T> {
  List<T> records;
  long total;
  int page;
  int size;
}

UserListItemVo {
  Integer userId, String username, String employeeName, String userCode,
  String departmentName, String userType, String language,
  Boolean isDeleted, LocalDateTime lastLoginDate,
  String createdBy, LocalDateTime createdDate
}

UserQueryReq {
  Integer page,         // @Min(1)
  Integer size,         // @Min(1) @Max(100)
  String sortField,
  String sortOrder,
  String queryField,
  String matchMode,
  String queryValue,
  String userType,
  Boolean isDeleted
}
```

---

## 任务步骤

### Task 1: ErrorCode 新增 40003 + PageResult 通用类

**Files:**
- Modify: `backend/src/main/java/com/xly/erp/common/response/ErrorCode.java`
- Create: `backend/src/main/java/com/xly/erp/common/response/PageResult.java`
- Modify: `backend/src/test/java/com/xly/erp/common/response/ErrorCodeTest.java`

**API shape:**
- `ErrorCode.INVALID_ENUM_PARAM = 40003`
- `ErrorCode.toHttpStatus(40003) == 400`
- `PageResult<T> { records: List<T>; total: long; page: int; size: int }` + @Builder

- [ ] **Step 1: 写失败测试** `ErrorCodeTest#httpMappings_coverNewCodes_v004` 验 40003→400
- [ ] **Step 2: 实现最小代码**
- [ ] **Step 3: 子会话验证 PASS**
- [ ] **Step 4: Commit** `feat(usr): ErrorCode 新增 40003 + PageResult 通用类 REQ-USR-004`

### Task 2: UserQueryReq DTO + UserListItemVo

**Files:**
- Create: `backend/src/main/java/com/xly/erp/module/usr/dto/UserQueryReq.java`
- Create: `backend/src/main/java/com/xly/erp/module/usr/vo/UserListItemVo.java`
- Create: `backend/src/test/java/com/xly/erp/module/usr/dto/UserQueryReqValidationTest.java`

**API shape:**
- `UserQueryReq` 所有字段可选；jakarta 注解只用 `@Min(1)`（page）、`@Min(1) @Max(100)`（size）；其他枚举值在 service 层做白名单校验（不用 @Pattern，因为 @Pattern 失败会落到 40001，本 REQ 要 40003）
- `UserListItemVo` 字段同 spec § 输出

- [ ] **Step 1: 写失败测试** 5 个用例：
  - 全空合法（PATCH 风格）
  - page=0 → @Min(1) 失败
  - size=101 → @Max(100) 失败
  - size=0 → @Min(1) 失败
  - 全合法字段 → pass
- [ ] **Step 2: 实现最小代码**
- [ ] **Step 3: 子会话验证 PASS**
- [ ] **Step 4: Commit** `feat(usr): UserQueryReq + UserListItemVo + PageResult REQ-USR-004`

### Task 3: SysUserMapper.selectByQuery + countByQuery (XML)

**Files:**
- Modify: `backend/src/main/java/com/xly/erp/module/usr/mapper/SysUserMapper.java`（声明方法）
- Create: `backend/src/main/resources/mapper/usr/SysUserMapper.xml`（动态 SQL）
- Modify: `backend/src/main/resources/application.yml`（mybatis-plus.mapper-locations: classpath*:/mapper/**/*.xml）
- Modify: `backend/src/main/resources/application-test.yml`（同上）
- Create: `backend/src/test/java/com/xly/erp/module/usr/mapper/SysUserMapperQueryTest.java`

**API shape:**
- `SysUserMapper#selectByQuery(@Param("p") QueryParams p) : List<UserListItemRow>`
- `SysUserMapper#countByQuery(@Param("p") QueryParams p) : long`
- `QueryParams` — 内部 record / DTO，包含已通过白名单校验的字段：`sqlSortField`（列名）, `sqlSortOrder`（asc/desc）, `sqlQueryColumn`（已映射列名 OR null）, `matchMode`, `queryValue`, `userType`, `isDeleted`（Integer 0/1 或 null）, `offset`, `limit`

> service 层把 spec 入参规范化为 `QueryParams`，mapper XML 用 `${}` 拼接 `sqlSortField` / `sqlSortOrder` / `sqlQueryColumn`（白名单值），用 `#{}` 绑定 queryValue / userType / isDeleted / offset / limit。

XML 关键片段（仅作为 plan 锁定的契约，TDD 实现可改细节）：

```xml
<sql id="baseFrom">
  FROM sys_user u
  LEFT JOIN sys_employee e ON e.iIncrement = u.iEmployeeId
  LEFT JOIN sys_department d ON d.iIncrement = e.iDepartmentId
</sql>

<sql id="whereClause">
  <where>
    <if test="p.sqlQueryColumn != null and p.queryValue != null and p.queryValue != ''">
      <choose>
        <when test="p.matchMode == 'contains'">
          AND ${p.sqlQueryColumn} LIKE CONCAT('%', #{p.queryValue}, '%')
        </when>
        <when test="p.matchMode == 'notContains'">
          AND (${p.sqlQueryColumn} NOT LIKE CONCAT('%', #{p.queryValue}, '%')
               OR ${p.sqlQueryColumn} IS NULL)
        </when>
        <otherwise>
          AND ${p.sqlQueryColumn} = #{p.queryValue}
        </otherwise>
      </choose>
    </if>
    <if test="p.userType != null">AND u.sUserType = #{p.userType}</if>
    <if test="p.isDeleted != null">AND u.iIsDeleted = #{p.isDeleted}</if>
  </where>
</sql>

<select id="selectByQuery" resultType="com.xly.erp.module.usr.vo.UserListItemVo">
  SELECT u.iIncrement AS userId, u.sUsername AS username,
         e.sEmployeeName AS employeeName, u.sUserCode AS userCode,
         d.sDepartmentName AS departmentName, u.sUserType AS userType,
         u.sLanguage AS language, u.iIsDeleted AS isDeleted,
         u.tLastLoginDate AS lastLoginDate, u.sCreatedBy AS createdBy,
         u.tCreateDate AS createdDate
  <include refid="baseFrom"/>
  <include refid="whereClause"/>
  ORDER BY u.${p.sqlSortField} ${p.sqlSortOrder}
  LIMIT #{p.offset}, #{p.limit}
</select>

<select id="countByQuery" resultType="long">
  SELECT COUNT(*)
  <include refid="baseFrom"/>
  <include refid="whereClause"/>
</select>
```

> MyBatis-Plus 默认 `mapper-locations: classpath*:/mapper/**/*.xml`，但需在 application.yml 显式声明以确保 XML 被加载。当前 application.yml 仅声明了 mybatis-plus 配置项，未声明 mapper-locations；本任务添加。

- [ ] **Step 1: 写失败测试** `SysUserMapperQueryTest`：
  - `count_noFilters_returnsAllRows`
  - `select_withSortByUsername_ascending`
  - `select_withQueryFieldUsername_contains`
  - `select_joinsEmployeeAndDepartment_returnsBothNames`
- [ ] **Step 2: 实现最小代码**
- [ ] **Step 3: 子会话验证 PASS**
- [ ] **Step 4: Commit** `feat(usr): SysUserMapper 动态查询 XML + JOIN 员工/部门 REQ-USR-004`

### Task 4: UserListService 白名单 + 越界矫正

**Files:**
- Create: `backend/src/main/java/com/xly/erp/module/usr/service/UserListService.java`
- Create: `backend/src/main/java/com/xly/erp/module/usr/service/impl/UserListServiceImpl.java`
- Create: `backend/src/test/java/com/xly/erp/module/usr/service/UserListServiceImplTest.java`

**API shape:**
- `UserListService#list(UserQueryReq req) : PageResult<UserListItemVo>`
- 内部规范化流程：
  1. 应用默认值（page=1, size=20, sortField=tCreateDate, sortOrder=desc, matchMode=contains）
  2. 白名单校验：sortField / sortOrder / queryField / matchMode / userType — 不在白名单抛 `BizException(40003)` 或 `BizException(40001)` 按入参类型决定
  3. queryField→sqlQueryColumn 映射；queryValue 转换（对 isDeleted 列：'true'→1, 'false'→0；其他不在 {true,false,0,1} 抛 40001）
  4. 越界矫正：先查 `selectByQuery(目标 page)`；若 records 空 && total>0 → 重算 lastPage 再查；响应 page 反映实际页

> userType 入参既可作 explicit query param 也可作 queryField=userType+queryValue。两条路径都要走白名单校验。

- [ ] **Step 1: 写失败测试** 12 个用例覆盖 spec 验收 5-21
- [ ] **Step 2: 实现最小代码**
- [ ] **Step 3: 子会话验证 PASS**
- [ ] **Step 4: Commit** `feat(usr): UserListService 白名单校验 + 动态查询 + 越界矫正 REQ-USR-004`

### Task 5: UserController GET / + 端到端测试

**Files:**
- Modify: `backend/src/main/java/com/xly/erp/module/usr/controller/UserController.java`（追加 GET / 方法）
- Create: `backend/src/test/java/com/xly/erp/module/usr/controller/UserControllerListTest.java`

**API shape:**
- `@GetMapping @RequireSuperAdmin list(@Valid UserQueryReq req) : Result<PageResult<UserListItemVo>>`
- 用 `@ModelAttribute` 或省略让 Spring 默认从 query 绑定 DTO

端到端测试（覆盖 spec § 验收 1-26）：

GET 路径（admin token）：
- `list_default_returnsAllUsersSortedByCreateDateDesc`
- `list_pagination_secondPage`
- `list_sizeOver100_returns400_40001`
- `list_pageZero_returns400_40001`
- `list_sortByUsernameAsc`
- `list_sortFieldInvalid_returns400_40003`
- `list_sortOrderInvalid_returns400_40001`
- `list_queryByUsernameContains`
- `list_queryByUsernameEquals_returnsExactOne`
- `list_queryByUsernameNotContains`
- `list_queryByEmployeeName_joinsCorrectly`
- `list_queryByDepartmentName_multiLevelJoin`
- `list_queryByUserType_equals`
- `list_queryByIsDeletedTrue_filtersDeleted`
- `list_queryFieldInvalid_returns400_40003`
- `list_matchModeInvalid_returns400_40003`
- `list_queryFieldWithoutValue_skipsCondition`
- `list_explicitUserTypeFilter`
- `list_explicitUserTypeInvalid_returns400_40001`
- `list_explicitIsDeletedFalse_filtersActiveOnly`
- `list_composedFilters_andSemantics`（queryField+queryValue + userType + isDeleted）
- `list_pageBeyondTotal_returnsLastPage`
- `list_normalUserToken_returns403_40301`
- `list_noAuthHeader_returns401_40101`
- `list_responseDoesNotContainPasswordField`
- `list_emptyTable_returnsZeroTotal`（drop + recreate 用户为空时）

- [ ] **Step 1: 写失败测试**
- [ ] **Step 2: 实现最小代码**
- [ ] **Step 3: 子会话验证 PASS**
- [ ] **Step 4: Commit** `feat(usr): GET /api/v1/users controller + 端到端测试 REQ-USR-004`

---

## 提交计划

| Task | Commit message |
|---|---|
| 1 | `feat(usr): ErrorCode 新增 40003 + PageResult 通用类 REQ-USR-004` |
| 2 | `feat(usr): UserQueryReq + UserListItemVo + PageResult REQ-USR-004` |
| 3 | `feat(usr): SysUserMapper 动态查询 XML + JOIN 员工/部门 REQ-USR-004` |
| 4 | `feat(usr): UserListService 白名单校验 + 动态查询 + 越界矫正 REQ-USR-004` |
| 5 | `feat(usr): GET /api/v1/users controller + 端到端测试 REQ-USR-004` |