一对多查询

join + collection

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

@Data
public class User {
    private Long id;
    private String name;
    private List<Order> orders;
}

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

<resultMap id="userMap" type="User">
    <id property="id" column="user_id"/>
    <result property="name" column="name"/>

    <collection property="orders" ofType="Order">
        <id property="id" column="order_id"/>
        <result property="amount" column="amount"/>
    </collection>
</resultMap>

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

collection + select

存在N+1的问题，查询效率慢

<collection property="orders" 
            column="user_id" 
            select="selectOrdersByUserId"/>

分步查询（推荐）

SELECT id, name FROM user LIMIT 10

SELECT * FROM orders WHERE user_id IN (...)

@Mapper
public interface UserMapper {

    List<UserVO> selectUserPage();
}

<select id="selectUserPage" resultType="com.xxx.vo.UserVO">
    SELECT 
        id,
        name
    FROM user
    WHERE is_deleted = 0
    LIMIT 10
</select>

@Mapper
public interface OrderMapper {

    List<OrderVO> selectByUserIds(@Param("userIds") List<Long> userIds);
}

<select id="selectByUserIds" resultType="com.xxx.vo.OrderVO">
    SELECT 
        id,
        amount,
        user_id
    FROM orders
    WHERE user_id IN
    <foreach collection="userIds" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

@Service
@RequiredArgsConstructor
public class UserService {

    private final UserMapper userMapper;
    private final OrderMapper orderMapper;

    public List<UserVO> getUserWithOrders() {

        // ① 查用户（分页）
        List<UserVO> users = userMapper.selectUserPage();

        if (users.isEmpty()) {
            return users;
        }

        // ② 提取 userId
        List<Long> userIds = users.stream()
                .map(UserVO::getId)
                .toList();

        // ③ 批量查订单
        List<OrderVO> orders = orderMapper.selectByUserIds(userIds);

        // ④ 按 userId 分组
        Map<Long, List<OrderVO>> orderMap = orders.stream()
                .collect(Collectors.groupingBy(OrderVO::getUserId));

        // ⑤ 组装数据
        for (UserVO user : users) {
            user.setOrders(orderMap.getOrDefault(user.getId(), new ArrayList<>()));
        }

        return users;
    }
}