Mybatis嵌套查询与嵌套结果

87 阅读 0 评论 58 点赞

我是靠谱客的博主心灵美水壶，最近开发中收集的这篇文章主要介绍Mybatis嵌套查询与嵌套结果，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

一对多关系：一是用户，多是订单

实体类User

public class User {

    private Integer id;

    private String name;

    private Integer age;

    private List<UserOrder> orders;
    }

实体类UserOrder

public class UserOrder {

    private Integer id;

    private String name;

    private Integer userId;
}

使用嵌套结果来查询：相当于使用一条sql语句关联多张表查询出所有结果，然后根据映射给订单类属性赋值

<!--1.嵌套结果-->
<select id="selectUser" resultMap="selectUser1">
        select u.id,u.name,u.age,o.id as order_id,o.name as order_name from t_p_user u left join t_p_user_order o
        on u.id = o.user_id and u.id = #{id}
    </select>

    <resultMap id="selectUser1" type="com.seakoon.model.User">
        <id column="id" property="id" />
        <result column="name" property="name"/>
        <result column="age" property="age"/>
        <collection property="orders" ofType="com.seakoon.model.UserOrder" column="id">
            <id column="order_id" property="id" />
            <result column="order_name" property="name" />
            <result column="id" property="userId" />
        </collection>
    </resultMap>

使用嵌套查询：查询订单数据时新用一条sql来查。参数是column属性的值，如果多参数的话：使用
column=“{property1 = column1,property2 = column2…}”

<!--2.嵌套查询-->
    <select id="selectUserNew" resultMap="selectUser2">
        select id,name,age from t_p_user where id = #{id}
    </select>

    <resultMap id="selectUser2" type="com.seakoon.model.User">
        <id column="id" property="id" />
        <result column="name" property="name"/>
        <result column="age" property="age"/>
        <collection property="orders" ofType="com.seakoon.model.UserOrder" column="id" javaType="java.util.ArrayList"
        select="selectOrderById">
            <id column="order_id" property="id" />
            <result column="order_name" property="name" />
            <result column="id" property="userId" />
        </collection>
    </resultMap>

    <select id="selectOrderById" resultType="com.seakoon.model.UserOrder">
        select id,name,user_id as userId from t_p_user_order where user_id = #{id}
    </select>