Mybatis（五）：一对多级联查询

àì夳堔傛蜴生んèń 2022-06-02 07:46 182阅读 0赞

主要是使用`<collection>`来实现关联。

直接上代码，一看就懂。

User.java

package cn.saytime.domain;
    
    import java.util.List;
    
    /** * Created by L on 2018/1/3. */
    public class User { 
    
        private Long id;
        private String username;
        private Integer age;
        //private IdCard idCard;
    
        // 一对多
        private List<Order> orderList;
    
    }

Order 订单

package cn.saytime.domain;
    
    import java.math.BigDecimal;
    
    /** * 订单 * Created by L on 2018/1/5. */
    public class Order { 
    
        private Long id;
        private String order_no;
        private BigDecimal price;
    
    }

UserMapper.java

package cn.saytime.mapper;
    
    import cn.saytime.domain.IdCard;
    import cn.saytime.domain.User;
    import java.util.List;
    
    /** * @author L * @ClassName cn.saytime.mapper.UserMapper * @Description */
    public interface UserMapper { 
    
        /** * 根据ID查询用户，并且查询出该用户所有订单 * @param id * @return */
        User selectByIdWithOrder(Long id);
    
    }

UserMapper.xml

<resultMap id="userWithOrderMap" type="cn.saytime.domain.User">
            <id property="id" column="uid" javaType="java.lang.Long"></id>
            <result property="username" column="username" javaType="java.lang.String"></result>
            <result property="age" column="age" javaType="java.lang.Integer"></result>
            <collection property="orderList" javaType="java.util.List" ofType="cn.saytime.domain.Order">
                <id property="id" column="oid" javaType="java.lang.Long"></id>
                <result property="order_no" column="order_no" javaType="java.lang.String"></result>
                <result property="price" column="price" javaType="java.math.BigDecimal"></result>
            </collection>
        </resultMap>
    
        <select id="selectByIdWithOrder" parameterType="java.lang.Long" resultMap="userWithOrderMap">
            SELECT u.id AS uid, u.username, u.age, o.id AS oid, o.order_no, o.price
            FROM tb_user u
            JOIN tb_order o ON u.id = o.uid
            WHERE u.id = #{id}
        </select>

> 需要注意的点：  
> 1. 有冲突的字段需要起别名对应  
> 2. **orderList的javaType是List，ofType才是Order**

测试：

... 省略加载UserMapper步骤
    
    
        /** * 测试根据Id查询用户并且带上订单列表信息，一对多 */
        @Test
        public void selectByIdWithOrder(){
            User user = userMapper.selectByIdWithOrder(1L);
            System.out.println(user);
        }

输出：

User{id=1, username='张三', age=18, idCard=null, orderList=[Order{id=1, order_no='123456', price=10.25}]}