<!-- 基础 ResultMap -->
<resultMap id="BaseResultMap" type="com.example.User">
    <id column="id" property="id"/>
    <result column="user_name" property="userName"/>
    <result column="email" property="email"/>
    <result column="status" property="status"/>
</resultMap>

<!-- 继承 + 关联(推荐 Join 方式) -->
<resultMap id="UserWithDept" extends="BaseResultMap" type="com.example.UserVO">
    <association property="dept" javaType="Department"
        resultMap="com.example.DeptMapper.BaseResultMap"
        columnPrefix="dept_"/>  <!-- ← 同名字段加前缀 -->
    <collection property="roles" ofType="Role"
        resultMap="com.example.RoleMapper.BaseResultMap"
        columnPrefix="role_"/>
</resultMap>

<!-- ✅ 推荐：Join 查询 → 一次SQL，无N+1 -->
<select id="selectUserWithDept" resultMap="UserWithDept">
    SELECT u.id, u.user_name, u.email, u.status,
           d.id AS dept_id, d.dept_name AS dept_dept_name,  <!-- ← 加前缀 -->
           r.id AS role_id, r.role_name AS role_role_name
    FROM sys_user u
    LEFT JOIN sys_dept d ON u.dept_id = d.id
    LEFT JOIN sys_user_role ur ON u.id = ur.user_id
    LEFT JOIN sys_role r ON ur.role_id = r.id
    WHERE u.id = #{id}
</select>

<!-- ❌ 避免：嵌套 select 导致 N+1 -->
<resultMap id="UserWithDeptNested" extends="BaseResultMap" type="com.example.UserVO">
    <association property="dept" javaType="Department"
        column="dept_id" select="selectDeptById"/>  <!-- ← 每次查询一个部门 -->
</resultMap>
<!-- 如果查100个用户 → 1(主查询) + 100(部门查询) = 101次SQL -->

<select id="findByCondition" resultMap="BaseResultMap">
    SELECT id, user_name, email, status, create_time
    FROM sys_user
    <where>  <!-- ← 自动处理 AND/OR 前缀 -->
        <if test="username != null and username != ''">
            AND user_name LIKE CONCAT('%', #{username}, '%')  <!-- ✅ 防注入 -->
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
        <if test="deptId != null">
            AND dept_id = #{deptId}
        </if>
    </where>
    <if test="orderBy != null">
        ORDER BY ${orderBy}  <!-- ⚠️ 必须白名单校验 -->
    </if>
</select>

<!-- 批量插入 -->
<insert id="batchInsert">
    INSERT INTO sys_user (user_name, email, status)
    VALUES
    <foreach collection="list" item="user" separator=",">
        (#{user.userName}, #{user.email}, #{user.status})
    </foreach>
</insert>

<!-- 动态更新(set 自动去尾逗号) -->
<update id="updateSelective">
    UPDATE sys_user
    <set>
        <if test="userName != null">user_name = #{userName},</if>
        <if test="email != null">email = #{email},</if>
        <if test="status != null">status = #{status},</if>
    </set>
    WHERE id = #{id}
</update>

<!-- choose/when/otherwise 多分支 -->
<select id="findByCondition" resultMap="BaseResultMap">
    SELECT * FROM sys_user
    <where>
        <choose>
            <when test="queryType == 'name'">AND user_name LIKE CONCAT('%', #{keyword}, '%')</when>
            <when test="queryType == 'email'">AND email = #{keyword}</when>
            <otherwise>AND status = 'ACTIVE'</otherwise>
        </choose>
    </where>
</select>

<sql id="BaseColumns">
    id, user_name, email, status, create_time, update_time
</sql>

<select id="selectById" resultMap="BaseResultMap">
    SELECT <include refid="BaseColumns"/>
    FROM sys_user WHERE id = #{id}
</select>

@Select("SELECT * FROM sys_user WHERE id = #{id}")
@Results(id = "userMap", value = {
    @Result(column = "user_name", property = "userName"),
    @Result(column = "dept_id", property = "deptId")
})
User findById(Long id);

// ✅ 安全：校验排序字段在白名单中
private static final Set<String> SORT_COLUMNS = Set.of(
    "create_time", "update_time", "user_name", "status"
);

public List<User> findByCondition(String orderBy, boolean asc) {
    if (!SORT_COLUMNS.contains(orderBy)) {
        throw new SecurityException("非法排序字段: " + orderBy);
    }
    // 传入 ${orderBy} 安全使用
}