多对一查询

join + association

SELECT 
    o.id AS order_id,
    o.amount,
    u.id AS user_id,
    u.name
FROM orders o
LEFT JOIN user u ON o.user_id = u.id

Order

@Data
public class Order {
    private Long id;
    private BigDecimal amount;
    private User user;
}

User

@Data
public class User {
    private Long id;
    private String name;
}

• resultMap 映射

<resultMap id="orderMap" type="Order">
    <id property="id" column="order_id"/>
    <result property="amount" column="amount"/>

    <association property="user" javaType="User">
        <id property="id" column="user_id"/>
        <result property="name" column="name"/>
    </association>
</resultMap>

<select id="selectOrderList" resultMap="orderMap">
    SELECT 
        o.id AS order_id,
        o.amount,
        u.id AS user_id,
        u.name
    FROM orders o
    LEFT JOIN user u ON o.user_id = u.id
</select>

扁平化查询

直接使用VO去映射字段，不使用association

@Data
public class OrderVO {

    /** 订单ID */
    private Long id;

    /** 订单金额 */
    private BigDecimal amount;

    /** 用户名（扁平化） */
    private String userName;

    /** 用户ID（有时候前端需要跳转） */
    private Long userId;

}

association + select

association + select（嵌套查询）

OrderMap

<select id="selectOrderList" resultMap="orderMap">
    SELECT id, amount, user_id FROM orders
</select>

<resultMap id="orderMap" type="Order">
    <id property="id" column="id"/>
    <result property="amount" column="amount"/>

    <association property="user" 
                 column="user_id" 
                 select="com.example.UserMapper.selectUserById"/>
</resultMap>

UserMapper

<select id="selectUserById" resultType="User">
    SELECT id, name FROM user WHERE id = #{id}
</select>

缺陷

性能差：查询一次订单需要查询11次（如果存在10条数据）

SELECT id, amount, user_id FROM orders;

SELECT id, name FROM user WHERE id = 101;
SELECT id, name FROM user WHERE id = 102;
......

延迟加载

<settings>
    <setting name="lazyLoadingEnabled" value="true"/>
</settings>

当你代码里访问：

order.getUser().getName();

才会触发 SQL：

SELECT id, name FROM user WHERE id = ?

触发多个查询：

for (Order order : list) {
    System.out.println(order.getUser().getName());
}

原理：

MyBatis为association创建了一个代理对象，拦截了getter方法。只有触发了级联才会真的去数据库查询

class UserProxy extends User {
    private boolean loaded = false;

    @Override
    public String getName() {
        if (!loaded) {
            // 👉 这里才去查数据库
            loadUserFromDB();
            loaded = true;
        }
        return super.getName();
    }
}

提示

前端需要的是VO，后端虽然没有显式调用getter，但是在VO序列化成JSON时就会触发getter，此时将触发多个子查询