Mybatis框架-16：多表操作之一对多——查询所有

Myth丶恋晨 2022-01-21 06:37 221阅读 0赞

**我们这里的需求是，查询所有的客户，并把客户拥有的所有订单都查出来**

**准备工作：**

数据库中存在一对多关系的表，工程中有对应的实体类

**客户实体类**

@Data
    public class Customer {
        private Integer cust_id;
        private String cust_name;
        private String cust_profession;
        private String cust_phone;
        private String email;
        private List<Order> orders = new ArrayList<Order>();
    }

**订单实体类**

@Data
    public class Order {
        private Integer orderId;
        private String orderName;
        private String orderNum;
        private Customer customer;
    }

# 第一步：编写mapper #

这个是客户的mapper接口

List<Customer> getAllCustomer();

# 第二步：编写sql语句 #

因为我们这里涉及到了两个对象的接收，所有要使用resultMap类型，值得注意的是，这里一个用户有多个订单，我们在实体类中使用的是list集合接收，所以这里封装的时候，我们采用的是collectin这个类型进行封装

<resultMap id="allCustomerMap" type="com.ctbu.domain.Customer">
            <id column="cust_id" property="cust_id"/>
            <result column="cust_name" property="cust_name"/>
            <result column="cust_phone" property="cust_phone"/>
            <result column="cust_profession" property="cust_profession"/>
            <result column="email" property="email"/>
            <collection property="orders" ofType="com.ctbu.domain.Order">
                <id property="orderId" column="orderId"/>
                <result property="orderName" column="orderName"/>
                <result property="orderNum" column="orderNum"/>
            </collection>
        </resultMap>
    
        <select id="getAllCustomer" resultMap="allCustomerMap">
            select * from `customer` as c left join `order` as o on c.cust_id = o.orderCustId;
        </select>

# 第三步:编写测试类进行查询 #

@Test
        public void test7(){
            SqlSession session = MyUtils.openSession();
            CustomerMapper mapper = session.getMapper(CustomerMapper.class);
            List<Customer> allCustomer = mapper.getAllCustomer();
            for (Customer customer : allCustomer) {
                System.out.println(customer);
            }
            session.close();
        }