Mybatis对象关联数据库表【对多关联AND对一关联】

一对多分成两部分对一、对多

1. 准备两张表oder、customer 

2. 对多关联实现

对多关联Customer类下创建一个List集合OrderBean类

public class Customer {

    private Integer id;
    private String name;

    //对多关联
    private List<OrderBean> orderBeanList;

    public Customer() {
    }

    @Override
    public String toString() {
        return "Customer{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", orderBeanList=" + orderBeanList +
                '}';
    }
}

数据库语句采用LEFT JOIN。。。ON。。。来实现关联

补充 left join左链接,以左表为参照,显示所有数据,右表中没有则以null显示。

            通俗讲 left 以 left join 左侧的表为主表

 对多关联 <collection property="orderBeanList" ofType="OrderBean">

    <select id="getCustomerWithOrderBeanList" resultMap="CustomerResultMap">
        SELECT t1.*, t2.id AS order_id, t2.orderMoney, t2.customerId
        FROM customer t1
                 LEFT JOIN `order` t2 ON t1.id = t2.customerId
        WHERE t1.id = #{value};
    </select>
    <resultMap id="CustomerResultMap" type="Customer">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <!-- 对多关联 -->
        <collection property="orderBeanList" ofType="OrderBean">
            <id property="id" column="order_id"/>
            <result property="orderMoney" column="orderMoney"/>
        </collection>
    </resultMap>

3. 对一关联实现

对一关联 OrderBean 类下创建一个Customer类

public class OrderBean {
    private Integer id;
    private Integer orderMoney;

    //对一关联
    private Customer customer;

    public OrderBean() {
    }

    @Override
    public String toString() {
        return "OrderBean{" +
                "id=" + id +
                ", orderMoney=" + orderMoney +
                ", customer=" + customer +
                '}';
    }
}

数据库语句采用INNER JOIN。。。ON。。。来实现关联

补充inner join内连接,显示两个表中有联系的所有数据。

            通俗讲 查找的数据是左右两张表共有的。

  对一关联 <association property="customer" javaType="Customer">

    <select id="getOrderBeanWithCustomer" resultMap="OrderBeanResultMap">
        SELECT t1.*, t2.name
        FROM `order` t1
                 INNER JOIN customer t2 ON t1.customerId = t2.id
        WHERE t1.id = #{value};
    </select>
    <resultMap id="OrderBeanResultMap" type="OrderBean">
        <id property="id" column="id"/>
        <result property="orderMoney" column="orderMoney"/>
        <!-- 对一关联 -->
        <association property="customer" javaType="Customer">
            <id property="id" column="customerId"/>
            <result property="name" column="name"/>
        </association>
    </resultMap>