Mybatis的xml关系映射

Mybatis的xml关系映射

示例实体类：

1. 用户实体类(User.java)

public class User {
    private Integer id;
    private String name;
    private Profile profile; // 一对一关联的Profile对象
    private List<Order> orders; // 一对多关联的Order对象集合
}

public class Profile {
    private Integer id;
    private String detail;
  
}

public class Order {
    private Integer id;
    private String description;
   
}

数据类型

示例XML映射文件(UserMapper.xml)

1. 普通结果映射(简单字段映射)

<resultMap id="userResultMap" type="com.example.dto.UserDto">
    <id property="id" column="user_id"/>
    <result property="name" column="user_name"/>
</resultMap>

<select id="selectUserById" resultMap="userResultMap">
    SELECT user_id AS id, user_name AS name FROM user WHERE user_id = #{id}
</select>

或者 

<select id="selectUserById" resultType="com.example.dto.UserDto">
    SELECT * FROM user WHERE user_id = #{id}
</select>

注意：resultType :返回的是特定的对象,属性用于简单查询结果的映射，可以直接指定返回结果的类型 .resultMap: 返回的是定义的结果集映射。是一种更为灵活和强大的结果映射方式，它允许你自定义查询结果与Java对象属性之间的映射关系，
支持一对一、一对多、多对多等复杂关系映射，以及属性类型转换、嵌套结果映射等。

2. 一对一关系映射(User -> Profile)

<!-- Profile关联映射 -->
<resultMap id="userWithProfileResultMap" type="com.example.entity.User">
    <id property="id" column="user_id"/>
    <result property="name" column="user_name"/>
    <association property="profile" javaType="com.example.entity.Profile" resultMap="profileResultMap"/>
</resultMap>

<resultMap id="profileResultMap" type="com.example.entity.Profile">
    <id property="id" column="profile_id"/>
    <result property="detail" column="profile_detail"/>
</resultMap>

<select id="selectUserWithProfile" resultMap="userWithProfileResultMap">
    SELECT u.user_id AS id, u.user_name AS name, p.profile_id AS 'profile.id', p.profile_detail AS 'profile.detail'
    FROM user u
    LEFT JOIN profile p ON u.user_id = p.user_id
    WHERE u.user_id = #{userId}
</select>

3. 一对多关系映射(User -> Order)

<!-- User与多个Order关联映射 -->
<resultMap id="userWithOrdersResultMap" type="com.example.entity.User">
    <id property="id" column="user_id"/>
    <result property="name" column="user_name"/>
    <collection property="orders" ofType="com.example.entity.Order" resultMap="orderResultMap" column="user_id" foreignColumn="user_id"/>
</resultMap>

<resultMap id="orderResultMap" type="com.example.entity.Order">
    <id property="id" column="order_id"/>
    <result property="description" column="order_description"/>
</resultMap>

<select id="selectUserWithOrders" resultMap="userWithOrdersResultMap">
    SELECT u.user_id AS id, u.user_name AS name, o.order_id AS 'orders.id', o.order_description AS 'orders.description'
    FROM user u
    LEFT JOIN order o ON u.user_id = o.user_id
    WHERE u.user_id = #{userId}
</select>

4. 多对多关系映射(User <-> Role)

此处假设用户和角色之间有多对多关系，通过中间表user_role关联。

<!-- 多对多关系需要通过中间表进行映射 -->
<!-- 假设Role实体类 -->
public class Role {
    private Integer id;
    private String roleName;
    private List<User> users; // 多对多关联的User对象集合
}

<!-- 角色集合映射在User实体类中 -->
<resultMap id="userWithRolesResultMap" type="com.example.entity.User">
    <id property="id" column="user_id"/>
    <result property="name" column="user_name"/>
    <collection property="roles" ofType="com.example.entity.Role" resultMap="roleResultMap" column="{user_id=user_id, role_id=role_id}">
        <collectionProperty column="role_id" property="id"/>
    </collection>
</resultMap>

<resultMap id="roleResultMap" type="com.example.entity.Role">
    <id property="id" column="role_id"/>
    <result property="roleName" column="role_name"/>
</resultMap>

<select id="selectUserWithRoles" resultMap="userWithRolesResultMap">
    SELECT u.user_id AS id, u.user_name AS name, r.role_id AS 'roles.id', r.role_name AS 'roles.roleName'
    FROM user u
    JOIN user_role ur ON u.user_id = ur.user_id
    JOIN role r ON r.role_id = ur.role_id
    WHERE u.user_id = #{userId}
</select>

5、关于递归级联查询方式(常用于树结构，例如：菜单、部门、分类等。)

@Data
public class DepartmentTreeDto  {
    /**
     * 部门ID
     */
    private Long id;
    /**
     * 部门名称
     */
    private String name;
    /**
     * 上级部门ID
     */
    private Long parentId;

    private Integer useStatus;
    private List<DepartmentTreeDto> children;
}

 List<DepartmentTreeDto> getDepartmentsByTree(@Param("pid") Long pid,@Param("name") String name);

@Override
public DepartmentTreeDto getDepartmentsByTree() {
	//从第parent为 0 父级 开始
	List<DepartmentTreeDto> departmentsByParentId = departmentMapper.getDepartmentsByTree(0L,param.getName());
	return  departmentsByParentId;
}

mapper.xml

<mapper namespace="com.example.mapper.DepartmentMapper">
    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.example.dto.DepartmentTreeDto">
        <id column="id" property="id" />
        <result column="name" property="name" />
        <result column="parent_id" property="parentId" />
        <result column="use_status" property="useStatus" />
    </resultMap>

    <resultMap id="DepartmentWithChildren" type="com.example.dto.DepartmentTreeDto" extends="BaseResultMap">
        <collection property="children" ofType="com.example.dto.DepartmentTreeDto"
                    select="com.example.mapper.DepartmentMapper.getDepartmentsByParentId" column="id">
        </collection>
    </resultMap>

    <select id="getDepartmentsByTree" resultMap="DepartmentWithChildren">
        SELECT
            *
        FROM
            `department`
        WHERE 1=1
            and use_status = 1
            and parent_id = #{pid}
            <if test="name !=null and name != ''">
                AND name LIKE CONCAT('%',#{name},'%')
            </if>
    </select>
    <select id="getDepartmentsByParentId" resultMap="DepartmentWithChildren">
        SELECT
            *
        FROM
            `department`
        WHERE 1=1
            and parent_id = #{pid}
            and use_status = 1
    </select>
</mapper>

请注意，上述示例假设了数据库表结构和字段名称，实际项目中请根据实际情况调整。同时，MyBatis也支持多种复杂的关联查询方式，包括嵌套查询和嵌套结果等。在上述例子中，我们展示了使用嵌套结果的方式来映射关联关系。

原文链接 https://www.hanyuanhun.cn | https://node.hanyuanhun.cn