Mybatis left join 一对多及多对多查询配置

一对一查询配置

<!-- 一对一查询 ，一个订单对应一个用户-->
    <select id="findOrdersUser" resultType="com.OrdersCustom" resultMap="OrdersUserResultMap">
    SELECT
       t1.*,
       t2.*
    FROM
        orders t1
    LEFT JOIN
        t_user t2
    WHERE 
        t1.user_id=t2.id
    </select>

    <resultMap type="com.Orders" id="OrdersUserResultMap">
        <id column="id" property="id"/>
        <result column="user_id" property="userid"/>
        <result column="number" property="number"/>
         <result column="createtime" property="createTime"/>
         <result column="note" property="note"/>
         <!-- 订单关联用户 -->
         <association property="user" javaType="com.User">
            <id column="user_id" property="id"/>
             <result column="username" property="username"/>
             <result column="sex" property="sex"/>
             <result column="address" property="address"/>
         </association>
    </resultMap>

一对多查询（left join 主要是为了当多方为null时一方可以查）

<!-- 一对多查询，一个订单对应多个订单明细 -->
  <select id="findOrdersUser" resultType="com.OrdersCustom" resultMap="OrdersUserResultMap">
    SELECT
       t1.*,
       t2.*
    FROM
        orders t1
    LEFT JOIN
        t_user t2
    WHERE 
        t1.user_id=t2.id
    </select>

     <resultMap type="com.Orders" id="ordersAndOrderDetailResultMap">
        <id column="id" property="id"/>
        <result column="user_id" property="userid"/>
        <result column="number" property="number"/>
         <result column="createtime" property="createTime"/>
         <result column="note" property="note"/>
         <!-- 关联订单详细 -->
         <collection property="orderdetails" ofType="com.OrderDetail">
            <id column="orderdetail_id" property="id"/>
            <result column="items_id" property="itemsId"/>
            <result column="items_num" property="itemsNum"/>
            <result column="orders_id" property="ordersId"/>
         </collection>
    </resultMap>

多对多查询

<!-- 多对多查询，查询用户以及用户购买的商品信息，一个用户对应多个订单，一个订单对应多个订单明细，一个订单明细对应一个商品 -->
    <select id="findUserAndItemsResultMap" resultMap="userAndItemsResultMap">
        SELECT 
                   t1.*,
                   t2.username,
                   t2.sex,
                   t2.address,
                   t3.id orderdetail_id,
                   t3.items_id,
                   t3.items_num,
                   t3.orders_id,
                   t4.itemsname items_name,
                   t4.detail items_detail,
                   t4.price items_price
            FROM
                  orders t1,
                  t_user t2,
                  orderdetail t3,
                  items t4
            WHERE t1.user_id =  t2.id AND  t3.orders_id=t1.id AND t3.items_id = t4.id
    </select>
    <resultMap type="com.User" id="userAndItemsResultMap">
        <!-- 用户信息 -->
        <id column="user_id" property="id"/>
        <result column="username" property="username"/>
        <result column="sex" property="sex"/>
        <result column="address" property="address"/>
        <!-- 一个用户对应多个订单 -->
        <collection property="ordersList" ofType="com.Orders">
                 <id column="id" property="id"/>
                 <result column="user_id" property="userid"/>
                <result column="number" property="number"/>
                <result column="createtime" property="createTime"/>
                <result column="note" property="note"/>
                <!-- 一个订单对应多个订单明细 -->
                <collection property="orderdetails" ofType="com.OrderDetail">
                    <id column="orderdetail_id" property="id"/>
                    <result column="items_id"   property="itemsId"/>
                    <result column="items_num"  property="itemsNum"/>
                    <result column="orders_id"  property="ordersId"/>
                    <!-- 一个订单明细对应一个商品 -->
                    <association property="items" javaType="com.Items">
                        <id column="items_id" property="id"/>
                        <result column="items_name" property="itemsName"/>
                        <result column="items_detail" property="detail"/>
                        <result column="items_price" property="price"/>
                    </association>
                </collection>
         </collection>
    </resultMap>