mybatis Sql查询 list数据中包含一个对象的list集合

一.查询 list数据中包含一个对象

示例场景

假设你有两个类：User 和 Address，其中 User 有一个 Address 类型的属性。你想通过一个查询获取一个 User 列表，每个 User 对象都包含一个 Address 对象。

1. 实体类定义

public class User {
    private Integer id;
    private String name;
    private Address address; // 嵌套的地址对象
 
    // getters and setters
}
 
public class Address {
    private Integer id;
    private String city;
    private String street;
 
    // getters and setters
}

2. MyBatis Mapper XML 配置

<mapper namespace="com.example.mapper.UserMapper">
    <!-- Result Map for User including nested Address -->
    <resultMap id="UserResultMap" type="User">
        <id column="user_id" property="id"/>
        <result column="user_name" property="name"/>
        <!-- Association for Address -->
        <association property="address" javaType="Address">
            <id column="address_id" property="id"/>
            <result column="city" property="city"/>
            <result column="street" property="street"/>
        </association>
    </resultMap>
    
    <!-- SQL query to fetch users with their addresses -->
    <select id="selectUsersWithAddresses" resultMap="UserResultMap">
        SELECT u.id AS user_id, u.name AS user_name, 
               a.id AS address_id, a.city, a.street 
        FROM users u 
        LEFT JOIN addresses a ON u.address_id = a.id
    </select>
</mapper>

3. Mapper 接口方法

在你的 Mapper 接口中定义一个方法来调用这个查询：

package com.example.mapper;
 
import java.util.List;
import com.example.model.User;
 
public interface UserMapper {
    List<User> selectUsersWithAddresses();
}

4. 使用 Mapper 方法

最后，在你的服务层或业务逻辑中调用这个 Mapper 方法

import com.example.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
 
@Service
public class UserService {
    @Autowired
    private UserMapper userMapper;
    
    public List<User> getUsersWithAddresses() {
        return userMapper.selectUsersWithAddresses();
    }
}

二.mybatis Sql查询 list数据中包含一个list对象集合

1. 实体类定义

// 主实体类
public class Order {
    private Long orderId;
    private String orderNo;
    private Date createTime;
    // 包含的商品列表
    private List<OrderItem> items;
    // getters and setters
}

// 子项实体类
public class OrderItem {
    private Long itemId;
    private String productName;
    private Integer quantity;
    private BigDecimal price;
    // getters and setters
}

2. Mapper XML 配置

<mapper namespace="com.example.mapper.OrderMapper">
    <!-- 定义结果映射 -->
    <resultMap id="orderResultMap" type="Order">
        <id property="orderId" column="order_id"/>
        <result property="orderNo" column="order_no"/>
        <result property="createTime" column="create_time"/>
        <!-- 嵌套集合映射 -->
        <collection property="items" ofType="OrderItem">
            <id property="itemId" column="item_id"/>
            <result property="productName" column="product_name"/>
            <result property="quantity" column="quantity"/>
            <result property="price" column="price"/>
        </collection>
    </resultMap>

    <!-- 查询订单及商品明细 -->
    <select id="getOrderWithItems" resultMap="orderResultMap">
        SELECT 
            o.order_id, 
            o.order_no, 
            o.create_time,
            i.item_id,
            i.product_name,
            i.quantity,
            i.price
        FROM orders o
        LEFT JOIN order_items i ON o.order_id = i.order_id
        WHERE o.order_id = #{orderId}
    </select>

    <!-- 查询多个订单及商品明细 -->
    <select id="getOrdersWithItems" resultMap="orderResultMap">
        SELECT 
            o.order_id, 
            o.order_no, 
            o.create_time,
            i.item_id,
            i.product_name,
            i.quantity,
            i.price
        FROM orders o
        LEFT JOIN order_items i ON o.order_id = i.order_id
        WHERE o.order_id IN 
        <foreach item="id" collection="orderIds" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>
</mapper>

3. Mapper 接口

public interface OrderMapper {
    // 查询单个订单及其商品明细
    Order getOrderWithItems(@Param("orderId") Long orderId);
    
    // 批量查询订单及其商品明细
    List<Order> getOrdersWithItems(@Param("orderIds") List<Long> orderIds);
}

4. 使用示例

// 查询单个订单
Order order = orderMapper.getOrderWithItems(12345L);
System.out.println("订单号: " + order.getOrderNo());
System.out.println("包含商品:");
for (OrderItem item : order.getItems()) {
    System.out.println(item.getProductName() + " × " + item.getQuantity());
}

// 批量查询订单
List<Long> orderIds = Arrays.asList(12345L, 12346L, 12347L);
List<Order> orders = orderMapper.getOrdersWithItems(orderIds);
for (Order o : orders) {
    System.out.println("订单号: " + o.getOrderNo());
    System.out.println("商品数量: " + o.getItems().size());
}

5. 数据库表结构参考

-- 订单表
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    order_no VARCHAR(50) NOT NULL,
    create_time DATETIME NOT NULL
);

-- 订单商品明细表
CREATE TABLE order_items (
    item_id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);