MyBatis如何实现多表联合查询

MyBatis 是一个优秀的持久层框架，它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以使用简单的 XML 或注解来配置和映射原生类型、接口和 Java 的 POJO（Plain Old Java Objects，普通的 Java 对象）为数据库中的记录。

要在 MyBatis 中实现多表联合查询，你可以使用以下几种方法：

1. ResultMap：这是 MyBatis 中最强大的特性之一，它允许你自定义如何从数据库结果集中映射对象。你可以定义一个复杂的 ResultMap 来映射多个表的数据到一个 Java 对象中。

<resultMap id="userResultMap" type="User">
    <id property="id" column="user_id"/>
    <result property="name" column="user_name"/>
    <!-- 映射其他字段 -->
    <association property="department" javaType="Department">
        <id property="id" column="department_id"/>
        <result property="name" column="department_name"/>
        <!-- 映射其他字段 -->
    </association>
</resultMap>

<select id="selectUserWithDepartment" resultMap="userResultMap">
    SELECT
        u.id as user_id,
        u.name as user_name,
        d.id as department_id,
        d.name as department_name
    FROM
        users u
    LEFT JOIN
        departments d ON u.department_id = d.id
    WHERE
        u.id = #{userId}
</select>

2. 嵌套查询：你可以在 ResultMap 中定义一个嵌套的查询，这样 MyBatis 会先执行主查询，然后根据主查询的结果去执行嵌套的查询。

<resultMap id="userResultMap" type="User">
    <id property="id" column="user_id"/>
    <result property="name" column="user_name"/>
    <!-- 映射其他字段 -->
    <association property="department" column="department_id" select="selectDepartmentById"/>
</resultMap>

<select id="selectUserWithDepartment" resultMap="userResultMap">
    SELECT
        u.id as user_id,
        u.name as user_name,
        u.department_id
    FROM
        users u
    WHERE
        u.id = #{userId}
</select>

<select id="selectDepartmentById" resultType="Department">
    SELECT
        id as department_id,
        name as department_name
    FROM
        departments
    WHERE
        id = #{departmentId}
</select>

3. 注解方式：如果你更喜欢使用注解而不是 XML 配置，你可以在 Mapper 接口的方法上使用 @Select 注解来编写 SQL 查询，并使用 @Results 和 @Result 注解来定义映射关系。

public interface UserMapper {
    @Select("SELECT u.id as user_id, u.name as user_name, d.id as department_id, d.name as department_name " +
            "FROM users u " +
            "LEFT JOIN departments d ON u.department_id = d.id " +
            "WHERE u.id = #{userId}")
    @Results({
        @Result(property = "id", column = "user_id"),
        @Result(property = "name", column = "user_name"),
        @Result(property = "department", column = "department_id", one = @One(select = "selectDepartmentById"))
    })
    User selectUserWithDepartment(int userId);

    @Select("SELECT id as department_id, name as department_name FROM departments WHERE id = #{departmentId}")
    Department selectDepartmentById(int departmentId);
}

在实际应用中，你可以根据自己的需求选择合适的方法来实现多表联合查询。通常情况下，ResultMap 提供了最大的灵活性和控制力。