2026-05-15-REQ-USR-004.md 13.1 KB

Edit Raw Blame History Permalink



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（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 实现可改细节）：
<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>
内部规范化流程：


应用默认值（page=1, size=20, sortField=tCreateDate, sortOrder=desc, matchMode=contains）
白名单校验：sortField / sortOrder / queryField / matchMode / userType — 不在白名单抛 BizException(40003) 或 BizException(40001) 按入参类型决定
queryField→sqlQueryColumn 映射；queryValue 转换（对 isDeleted 列：'true'→1, 'false'→0；其他不在 {true,false,0,1} 抛 40001）
越界矫正：先查 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